Slow Running Queries

[J.A.K]

After an issue with MySQL last month it was recommended we enable the slow running queries log for MySQL. I've noticed since then I see about 10 of these queries a day in the log for comment history:

# Time: 2021-05-13T12:55:22.088521Z
# User@Host: ndoutils[ndoutils] @ localhost [] Id: 403938
# Query_time: 10.063197 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 79439
SET timestamp=1620910512;
DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1557838512);

I wanted to check is this considered "normal"? Should I be cleaning up the comment history in some way? Since this appears to be targeting older than 2 years ago and we've only had XI for a year I doubt it's going to do any actual deletions for awhile.

[dchurch]

On long-running systems with mucho checks, the database can get bogged down with excessive "paper trail" type data and the software's database queries aren't properly utilizing indexes. It often just needs better thresholds to get performance back where it should be.

Open Admin => Performance Settings, then click on the Databases tab and change the following setting:

- Max Comment History Age: change to 30

See this document: Nagios XI Database Optimization

The Canary Test

You can diagnose where there's huge "paper-trail" type data collecting in your database by running the following command:

Code: Select all

mysql -uroot -pnagiosxi --table <<< "select * from (select table_name, round(((data_length + index_length) / 1024 / 1024), 2) as sz from information_schema.tables where table_schema like 'nagios%') as x order by x.sz;"

Using the table sizes there can let you know if you should tweak some more performance settings. Use your best judgment.

[J.A.K]

Ehh nagios_commenthistory is only 40MB (I assume MB) so doesn't seem outrageous, but I took your advice and dropped it to 30 so I'll keep my eyes on it. Thank you!

[ssax]

Please see this FAQ below for some good information regarding retention/cleanup:

FAQ: Can I truncate the tables first before proceeding with database repair (if I have crashed tables)?​

You can truncate before repairing the DB, it's up to you. If you want to back it up first, you'll need to repair it. If you don't care, or already have a backup, truncate it first as it will speed up the DB repair process.

NOTE: You may need to adjust the -h 127.0.0.1, the -uroot, and -pnagiosxi in the commands if your DB is housed/stored/offloaded/contained on a different server and/or you've changed the root mysql password​

If you don't care about the data, or already have a backup, you can just truncate the tables which will essentially drop and recreate the table with zero data in it (removing all historical data for the respective reports):

nagios_logentries - Impacts Event Log report length

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'TRUNCATE TABLE nagios_logentries;'

nagios_statehistory - Impacts the State History report length

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'TRUNCATE TABLE nagios_statehistory;'

nagios_commenthistory - Impacts the comment history

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'TRUNCATE TABLE nagios_commenthistory;'

These should technically work to clean the DB tables up manually (if the tables aren't crashed, if they ARE crashed, you will need to repair the database FIRST in order to run these queries):

nagios_logentries - Impacts Event Log report length

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'DELETE FROM nagios_logentries WHERE logentry_time <= (NOW() - INTERVAL 6 MONTH);'

nagios_statehistory - Impacts the State History report length

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'DELETE FROM nagios_statehistory WHERE state_time <= (NOW() - INTERVAL 6 MONTH);'

nagios_commenthistory - Impacts the comment history

Code: Select all

mysql -uroot -pnagiosxi -h 127.0.0.1 -B nagios -e 'DELETE FROM nagios_commenthistory WHERE entry_time <= (NOW() - INTERVAL 6 MONTH);'

Then you should go to Admin > Performance Settings > Databases tab and adjust ALL of the retention intervals to meet your business data policy standards to keep them cleaned up as these settings are for adjusting the retention on those DB tables.

I would lower them to the smallest possible level and utilize the XI backup/restore process and the Admin > Scheduled Backups process to offload the backups to another server. Since these XI backups contain database backups you can spin them up to grab the data and report on them if needed.

See here for more information:

https://assets.nagios.com/downloads/nag ... os-XI.pdf​

And here:​

https://assets.nagios.com/downloads/nag ... abase.pdf​