Understanding More on logentries

[chicjo01]

We are tracking down a problem we had that cause the database to be slow. Our DBA found the problem query to be "SELECT /*!40001 SQL_NO_CACHE */ * FROM `nagios_logentries`". Can you let us know when and where this query is getting kicked off and our DBAs want to know why there is no limit or where clause?

I have recently reduced the number of days from 30 to 7 for "Max Log Entries Age" in the Performance Setting.

Nagios XI: 5.2.9

Below count consisted of June 1st to June 26th.

Code: Select all

mysql> select count(*) FROM `nagios_logentries`;
+----------+
| count(*) |
+----------+
| 20613247 |
+----------+
1 row in set (3.11 sec)

[mcapra]

A few developers looked over this issue and were unable to find anything native to XI that would be executing a non-caching query for that particular table. I can definitely see where a query like that would stress out MySQL though.

Are there any 3rd party components you have on the system that would be querying the nagios database? Has your DBA mentioned which user executed the problem query?

[chicjo01]

We have not installed any 3rd party components. The day before was a change that gave all privileges to the Nagios user for the offloaded DB, could that have caused this to happen?

Previous Privileges: SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE TEMPORARY TABLES, EXECUTE

The affect DB instance was "ndoutils".

[mcapra]

Can you let us know when and where this query is getting kicked off and our DBAs want to know why there is no limit or where clause?

I dug a bit deeper into this, and mysqldump works by grabbing all the data using the query SELECT /*!40001 SQL_NO_CACHE */ * FROM table;. So the reason those queries are being executed is probably due to mysqldump being run.

The following XI scripts use mysqldump:

Code: Select all

/usr/local/nagiosxi/scripts/backup_xi.sh
/usr/local/nagiosxi/scripts/nagiosql_snapshot.sh
/usr/local/nagiosxi/scripts/restore_defaults.sh

I suspect one of those scripts, likely backup_xi.sh run as a result of routine backups, is the source of those queries.

[chicjo01]

Thanks for the information. Will let you know if I find anything new out.

[chicjo01]

Does the below script get called when an Apply Config is executed?

/usr/local/nagiosxi/scripts/nagiosql_snapshot.sh

[mcapra]

Yes, it indeed does. When I apply config on my testing box, I see several SQL_NO_CACHE selects in my MySQL log as well as a separate reference to that particular script.

[chicjo01]

Thanks for the information. I will pass it along to our DBAs. This can be closed.

[mcapra]

Closing this!