Excessive CPU usage with MySQL.

[supportcl]

Since I have updated my nagios, I have been having performance problems, especially when applying changes to the system (hosts, services are added etc)

I have already done what is indicated in the documentation but I do not have good results.

Reading another forum, they talk about deleting historical data from the database, checking I see that I have 2 large tables and I wanted to know how deleting that information affects me.

| nagios_logentries | 626.89 |
| nagios_statehistory | 475.93 |


As seen in the images, I don't know what parameter I should change there, and what impact it has on my reports.
I hope you can explain.

I have activated "slow querys" in my mysql server and this query is repeated

# Time: 2021-04-13T11:01:13.187011Z
# User@Host: root[root] @ localhost [] Id: 829132
# Query_time: 64.416918 Lock_time: 0.000000 Rows_sent: 1378547 Rows_examined: 1378547
SET timestamp=1618311608;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `nagios_logentries`;
# Time: 2021-04-13T11:02:08.543221Z
# User@Host: root[root] @ localhost [] Id: 829132
# Query_time: 51.476014 Lock_time: 0.000000 Rows_sent: 1802781 Rows_examined: 1802781
SET timestamp=1618311677;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `nagios_statehistory`;
# Time: 2021-04-13T13:48:36.432417Z
# User@Host: ndoutils[ndoutils] @ localhost [] Id: 837049
# Query_time: 5.249428 Lock_time: 0.001926 Rows_sent: 0 Rows_examined: 0
SET timestamp=1618321711;
# administrator command: Prepare;
# Time: 2021-04-13T15:15:46.841808Z
# User@Host: ndoutils[ndoutils] @ localhost [] Id: 841289
# Query_time: 68.096724 Lock_time: 0.000000 Rows_sent: 1383560 Rows_examined: 1383560
SET timestamp=1618326878;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `nagios_logentries`;
# Time: 2021-04-13T15:16:54.029660Z
# User@Host: ndoutils[ndoutils] @ localhost [] Id: 841289
# Query_time: 63.291270 Lock_time: 0.000000 Rows_sent: 1803324 Rows_examined: 1803324
SET timestamp=1618326950;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `nagios_statehistory`;
/usr/libexec/mysqld, Version: 8.0.21 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument


I have changed the server resources increasing the ram from 6 to 16 GB and cpu from 4 to 8 and now to 16. I did this to see if it was lack of resources, but no positive changes are seen.

[benjaminsmith]

Hi,

The statement:
SELECT /*!40001 SQL_NO_CACHE */ * FROM 'some table';
is used by mysqldump.

Looking at the timestamps I see:
1618311608 Tue Apr 13 2021 11:00:08 GMT+0000
1618311677 Tue Apr 13 2021 11:01:17 GMT+0000
1618321711 Tue Apr 13 2021 13:48:31 GMT+0000
1618326878 Tue Apr 13 2021 15:14:38 GMT+0000
1618326950 Tue Apr 13 2021 15:15:50 GMT+0000

That data (throwing out the timestamp of 13:48) and the SQL statement seem to imply that you are
running backups every four hours. Is that the intention? If not, please check the backup schedule.

Also, please send a System Profile and we'll review the logs for any errors.

Code: Select all

To send us your system profile. 
Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and share this a private message and then reply to this post to bring it up in the queue

.


Thank you

[supportcl]

Hello, thanks for your help, the strange thing is that accessing Scheduled Backups only makes a daily backup. Where can there be another configuration that is doing backups every 4 hours?

[gsmith]

Hi,

Take a look at the crontabs for all the users and see if they are calling a backup
script. You can look at /var/spool/cron and in /etc/cron.d to see which users have crontabs.

Also, if you know when they are going to run you could run the "top" command
and try to figure it out that way.

Thanks