Issues with table 'nagios_logentries'

[nixe]

The main problem is with the optimization time that takes between 20-50 minutes to finish, so while the table is optimizing they have no status of objects in Nagvis.

I tried a lot of optimizations for mysqld a tunnings ir order to achieve better performance of this myisam table and nothing worked.
Could you guide me to find a solution for this issue please ? maybe a fix for make all the optimization process in ram since hdd it is too slow for this. Or perhaps migrate this table to innoDB could solve this issue, would it be convenient ?
Thank you.

[dwhitfield]

You should run through https://assets.nagios.com/downloads/nag ... tabase.pdf but it sounds like you've already done several things, which means you are likely ready to truncate this table:

Code: Select all

mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'

If that doesn't resolve the issue, please wait for your second post to be approved (you have to post the second time first, of course), and then, follow these instructions:

Please PM your profile. You can download it by going to Admin > System Config > System Profile and click the ***Download Profile*** button towards the top. If for whatever reason you *cannot* download the profile, please put the output of View System Info (5.3.4+, Show Profile if older) in the thread (that will at least get us some info). This will give us access to many of the logs we would otherwise ask for individually. If security is a concern, you can unzip the profile take out what you like, and then zip it up again. We may end up needing something you remove, but we can ask for that specifically.

You can also generate a profile manually using the script at /usr/local/nagiosxi/html/includes/components/profile/getprofile.sh

That should generate a profile in /usr/local/nagiosxi/var/components/ which you can get off the server with an application such as FileZilla.

After you PM the profile, please update this thread. Updating this thread is the only way for it to show back up on our dashboard.

If you get an error that PROFILE BUILD FAILED, please see https://support.nagios.com/kb/article.p ... ategory=44

[nixe]

Hello, yes, I have already follow all the primary steps, repair db, etc...
This is a production enviroment that needs to have 3 years of data retention, truncate all this data is not an option for us, we need it.

[dwhitfield]

Can you PM me your Profile? You can download it by going to Admin > System Config > System Profile and click the ***Download Profile*** button towards the top. If for whatever reason you *cannot* download the profile, please put the output of View System Info (5.3.4+, Show Profile if older) in the thread (that will at least get us some info). This will give us access to many of the logs we would otherwise ask for individually. If security is a concern, you can unzip the profile take out what you like, and then zip it up again. We may end up needing something you remove, but we can ask for that specifically.

You can also generate a profile manually using the script at /usr/local/nagiosxi/html/includes/components/profile/getprofile.sh

That should generate a profile in /usr/local/nagiosxi/var/components/ which you can get off the server with an application such as FileZilla.

After you PM the profile, please update this thread. Updating this thread is the only way for it to show back up on our dashboard.

If you get an error that PROFILE BUILD FAILED, please see https://support.nagios.com/kb/article.p ... ategory=44

UPDATE: Profile received and shared with techs

[nixe]

I sended the nagios profile.
Do you have any news ?
Thanks!

[dwhitfield]

Your /usr/local needs more space. I don't know if that's what caused your original issue, but if that fills up, it will definitely cause issues.

Regarding the instructions below, if you do not have killall, you can install it via the following command:
# yum install psmisc

If psmisc is not in your repos, then instead you can check to make sure nagios is not running with
# ps -aef | grep nagios

If that document does not resolve your issue, please run the following commands in order and report any errors. You ***must*** use mariadb instead of mysqld in the commands below, ***if*** you have mariadb.
# service nagios stop
# service ndo2db stop
# service mysqld stop
# service crond stop
# service httpd stop
# killall -9 nagios
# killall -9 ndo2db
# rm -f /usr/local/nagios/var/rw/nagios.cmd
# rm -f /usr/local/nagios/var/nagios.lock
# rm -f /usr/local/nagios/var/ndo.sock
# rm -f /usr/local/nagios/var/ndo2db.lock
# rm -f /usr/local/nagiosxi/var/reconfigure_nagios.lock
# for i in `ipcs -q | grep nagios |awk '{print $2}'`; do ipcrm -q $i; done
# service mysqld start
# service ndo2db start
# service nagios start
# service httpd start
# service crond start

If that does not resolve the issue, take a look at https://support.nagios.com/kb/article.p ... ategory=92

[nixe]

Your /usr/local needs more space. I don't know if that's what caused your original issue, but if that fills up, it will definitely cause issues.

We had an space problem but I was not /usr/local/ partition, instead it was /tmp, thus those errors are corelated.

Regarding the instructions below, if you do not have killall, you can install it via the following command:
# yum install psmisc

If psmisc is not in your repos, then instead you can check to make sure nagios is not running with
# ps -aef | grep nagios

If that document does not resolve your issue, please run the following commands in order and report any errors. You ***must*** use mariadb instead of mysqld in the commands below, ***if*** you have mariadb.
# service nagios stop
# service ndo2db stop
# service mysqld stop
# service crond stop
# service httpd stop
# killall -9 nagios
# killall -9 ndo2db
# rm -f /usr/local/nagios/var/rw/nagios.cmd
# rm -f /usr/local/nagios/var/nagios.lock
# rm -f /usr/local/nagios/var/ndo.sock
# rm -f /usr/local/nagios/var/ndo2db.lock
# rm -f /usr/local/nagiosxi/var/reconfigure_nagios.lock
# for i in `ipcs -q | grep nagios |awk '{print $2}'`; do ipcrm -q $i; done
# service mysqld start
# service ndo2db start
# service nagios start
# service httpd start
# service crond start

I followed that document and incremented kernel parameters as suggested, but the performance it's the same, when the optimize process is running takes to long.

This table 'nagios_logentries' sizes arround 10 Gb, and the server has about 32 Gb (28 GB free). There is no way to use the free ram for the optimizing process ?

[dwhitfield]

Or perhaps migrate this table to innoDB could solve this issue, would it be convenient ?

I did not address the directly, but no, you *cannot* move to innodb.

I am assuming when you are talking about optimization you are talking about dbmaint? If not, what exactly are you talking about?

Your dbmaint cron is probably running every 5 minutes. Some of those tables can get pretty large, but all it's doing is truncating the old data. You could run that cron really infrequently if those are growing to quickly. It's hard to know what to try, but you could change the 5 to 10 in /etc/cron.d/nagiosxi and see how that goes. That's the least likely to cause space issues.

What version of MySQL are you on? MySQL 5.5 and 5.6 have performance improvements over 5.1. The best way to go about the upgrade is going to depend on your environment, but I would suggest moving from RHEL 6 to RHEL 7. You will need to take a look at https://assets.nagios.com/downloads/nag ... ios-XI.pdf if that's the route you take. I suggest this for a couple of reasons, but the primary reason is that you are going to have to do it eventually, so why not now?

[nixe]

Or perhaps migrate this table to innoDB could solve this issue, would it be convenient ?

I did not address the directly, but no, you *cannot* move to innodb.

Why not ?

I am assuming when you are talking about optimization you are talking about dbmaint? If not, what exactly are you talking about?

Your dbmaint cron is probably running every 5 minutes. Some of those tables can get pretty large, but all it's doing is truncating the old data. You could run that cron really infrequently if those are growing to quickly. It's hard to know what to try, but you could change the 5 to 10 in /etc/cron.d/nagiosxi and see how that goes. That's the least likely to cause space issues.

That's correct, I'm talking about that log file [/usr/local/nagiosxi/var/dbmaint.log] where /etc/cron.d/nagiosxi crons runs every 5 minutes, and when it matches the parameter "Optimize interval" (60 minutes defaul) [ from: -> Admin -> Performance Settings -> Databases -> "Optimize interval"] I can see how this process is blocking that table, but not when it's trimming tables every 5 minutes.

What version of MySQL are you on? MySQL 5.5 and 5.6 have performance improvements over 5.1. The best way to go about the upgrade is going to depend on your environment, but I would suggest moving from RHEL 6 to RHEL 7. You will need to take a look at https://assets.nagios.com/downloads/nag ... ios-XI.pdf if that's the route you take. I suggest this for a couple of reasons, but the primary reason is that you are going to have to do it eventually, so why not now?

Our priority is to solve this performance issue and need to solve this before thinking in upgrading your production environment.

[dwhitfield]

Our priority is to solve this performance issue and need to solve this before thinking in upgrading your production environment.

I realized the version would be in your mysqllog. You are running 5.1. You could see performance improvements by moving to MySQL 5.5 (http://www.oracle.com/us/products/mysql ... 364861.pdf) and then to MySQL 5.6 (the MySQL 5.7 improvements largely do not look interesting in the context of XI). The entire point of bringing it up is to resolve the performance issue you are seeing. You can install the later versions of MySQL on RHEL 6. I just suggested the OS switch because that would make the move to mariadb easier and then you get an extra bump there. You could also go MariaDB on RHEL 6, but that is more complicated. Since you are on RHEL, the move to later versions of MySQL should be easier: https://access.redhat.com/solutions/1980533 . I do not have a RHEL system on which to test an upgrade to 5.1 to 5.6 using their repo, but the official MySQL documentation says MySQL (aka Oracle) doesn't support moves from 5.1 to 5.6.

XI is not designed to run with InnoDB tables.

Did you change your /etc/cron.d/nagiosxi to decrease the frequency of running the dbmaint? You could run it once a day if you have plenty of space.

You mentioned you are doing tuning. Is https://assets.nagios.com/downloads/nag ... ios-XI.pdf the document you are using to do that? You might see some improvement by offloading, but if you anticipate any growth, I would not do that.