High CPU load on optimization of NDOUTILS Db

[toper]

Hi,

I use Nagios XI 2014R2.7 on Redhat 6.6, 390 hosts, 3100 service checks.

I have noticed that optimization of the NDOUTILS Database takes huge CPU load with spikes upto 40 in 1min load.

Is this normal, or can i adjust someting to get it better ? - I already set the different times for DB optimization, so
they dont run at the same time, But NDOUTILS still have this problem.

Please find attached screenshots.

Thanks,
Peter Calum
TDC
Denmark

[jdalrymple]

A few things:

1) Obviously your db is not offloaded (right?) so do you see any errors in /var/log/mysqld.log or is it business as usual just with a super heavy load?

2) Are you doing a lot of passive checks?

3) No screenshot, I assume that was intended to show the correlation between optimization and load? I've never actually seen that correlated before, but presume you're seeing a high load on mysqld at the time that the dbmaint script runs?

[toper]

Hi,

Thanks for answering :

1) Obviously your db is not offloaded (right?) so do you see any errors in /var/log/mysqld.log or is it business as usual just with a super heavy load?
No, Mysql are on same host as nagiosXI, and nothing special in the log

2) Are you doing a lot of passive checks?
No, only active checks

3) No screenshot, I assume that was intended to show the correlation between optimization and load? I've never actually seen that correlated before, but presume you're seeing a high load on mysqld at the time that the dbmaint script runs?
I have attached it now as docx (pdf's was not allowed)

ndoutils.docx

thanks,
Peter

[tmcdonald]

How many CPUs and cores do you have on this machine? A load of 40 is not that high on a system with 8 CPUs and 4 cores each.

Are you noticing any measurable slowness during this time? Or is it just the output of "top" or something that is concerning?

[toper]

Hi,

Server specs are:

IBM x3550 M2 2 CPU E5530 2.40GHz Core 4 16GB Ram RHEL 6.6 - 2.6.32-504.1.3.el6.x86_64 Uptime 260 day

Today as database optimization was running at 1420 I tested nagiosxi web under the high load and saw the following:

- High latency
- No scheduled checks
- Very slow to make a report
- Nagvis not working in 10 minutes
- Slow

I have attached some screen shots taken in the 20 min perion problems occours

After i have tried restarting
- Monitoring Engine
- Performance Grapher
- Database Backend

I wonder if i should upgrade server from 16 to 32 G ram.
Or maybe a server reboot ?

NagiosXI_screenshots.docx

thanks,
Peter
TDC
Denmark

[toper]

Addon to my last post

Screen-2015-08-04_210609.png

No network traffic under DB optimize..

thanks
Peter

[tgriep]

Could you run the following in the XI system and post the output back here in code wraps?

Code: Select all

mysqlcheck -u root -pnagiosxi --databases nagios nagiosql
du /var/lib/mysql
ls -lR  /var/lib/mysql/nagios

[toper]

Hi,

Please find printouts attached.

nagiosxi_fault.txt

thanks,
Peter Calum
TDC
DEnmark

[tgriep]

The nagios_logentries MYSQL table is very large and could be truncated to speed up the optimization process.
Here is an example of a command to truncate that table and keeping the last 60 days of data.

Code: Select all

echo 'DELETE FROM nagios_logentries WHERE entry_time < DATE_SUB(NOW(), INTERVAL 60 DAY);' |mysql -t -u root -pnagiosxi nagios

After the table is truncated, it will have to be repaired which will shrink the table and here are the instructions to do that.
https://assets.nagios.com/downloads/nag ... tabase.pdf
Give it a try and see if that helps.

[toper]

Hi

echo 'DELETE FROM nagios_logentries WHERE entry_time < DATE_SUB(NOW(), INTERVAL 60 DAY);' |mysql -t -u root -pnagiosxi nagios
was ok

I get this after trying repair

- recovering (with sort) MyISAM-table 'nagios_logentries.MYI'
Data records: 66741068
- Fixing index 1
- Fixing index 2
66741000
- Fixing index 3
- Fixing index 4
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table 'nagios_logentries.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

[root@khk26dsg5 nagios]# myisamchk -r -f nagios_logentries.MYI
- recovering (with sort) MyISAM-table 'nagios_logentries.MYI'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table 'nagios_logentries.MYI' is not fixed because of errors

should i try
echo 'TRUNCATE TABLE nagios_logentries;' |mysql -t -u root -pnagiosxi nagios

br
Peter Calum