Page 1 of 1
Understanding More on logentries
Posted: Tue Jun 21, 2016 8:28 am
by 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)
Re: Understanding More on logentries
Posted: Tue Jun 21, 2016 9:59 am
by 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?
Re: Understanding More on logentries
Posted: Tue Jun 21, 2016 11:53 am
by 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".
Re: Understanding More on logentries
Posted: Tue Jun 21, 2016 3:44 pm
by mcapra
chicjo01 wrote: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.
Re: Understanding More on logentries
Posted: Wed Jun 22, 2016 7:36 am
by chicjo01
Thanks for the information. Will let you know if I find anything new out.
Re: Understanding More on logentries
Posted: Wed Jun 22, 2016 8:04 am
by chicjo01
Does the below script get called when an Apply Config is executed?
/usr/local/nagiosxi/scripts/nagiosql_snapshot.sh
Re: Understanding More on logentries
Posted: Wed Jun 22, 2016 9:26 am
by 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.
Re: Understanding More on logentries
Posted: Thu Jun 23, 2016 3:42 pm
by chicjo01
Thanks for the information. I will pass it along to our DBAs. This can be closed.
Re: Understanding More on logentries
Posted: Thu Jun 23, 2016 3:48 pm
by mcapra
Closing this!