High CPU Usage by MySQL

[J.A.K]

Seemingly at random my Nagios XI server started spiking in CPU usage around 1AM. A quick check shows mysql eating up all the CPU. Tried rebooting the server and tried the database repair script. Didn't seem to help much.

Code: Select all

 PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 4641 mysql     20   0 2852804 553296  36896 S  89.2   3.4  64:50.95 /usr/libexec/mysqld --basedir=/usr

Looking at the result of SHOW PROCESSLIST doesn't make anything jump out to me, but immediately I'm unfamiliar with what a normal load looks like:

ProcessList.txt

We're currently on NagiosXI 5.8.2 so no fixes in an update to grab. Pulling table sizes I don't see anything in the GBs

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql');" | mysql -uroot -pnagiosxi --table
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------------+------------+
| Table                                      | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements                    |       0.05 |
| nagios_commands                            |       0.02 |
| nagios_commenthistory                      |       7.02 |
| nagios_comments                            |       0.01 |
| nagios_configfiles                         |       0.01 |
| nagios_configfilevariables                 |       0.01 |
| nagios_contact_addresses                   |       0.00 |
| nagios_contact_notificationcommands        |       0.01 |
| nagios_contactgroup_members                |       0.00 |
| nagios_contactgroups                       |       0.00 |
| nagios_contactnotificationmethods          |       1.93 |
| nagios_contactnotifications                |       1.35 |
| nagios_contacts                            |       0.01 |
| nagios_contactstatus                       |       0.00 |
| nagios_customvariables                     |       0.00 |
| nagios_customvariablestatus                |       0.00 |
| nagios_dbversion                           |       0.00 |
| nagios_downtimehistory                     |       0.66 |
| nagios_eventhandlers                       |       0.00 |
| nagios_externalcommands                    |       0.02 |
| nagios_flappinghistory                     |       0.00 |
| nagios_host_contactgroups                  |       0.02 |
| nagios_host_contacts                       |       0.00 |
| nagios_host_parenthosts                    |       0.03 |
| nagios_hostchecks                          |       0.26 |
| nagios_hostdependencies                    |       0.00 |
| nagios_hostescalation_contactgroups        |       0.00 |
| nagios_hostescalation_contacts             |       0.00 |
| nagios_hostescalations                     |       0.00 |
| nagios_hostgroup_members                   |       0.08 |
| nagios_hostgroups                          |       0.00 |
| nagios_hosts                               |       0.30 |
| nagios_hoststatus                          |       0.56 |
| nagios_instances                           |       0.00 |
| nagios_logentries                          |     253.70 |
| nagios_notifications                       |       2.85 |
| nagios_objects                             |       0.56 |
| nagios_processevents                       |       0.97 |
| nagios_programstatus                       |       0.00 |
| nagios_runtimevariables                    |       0.00 |
| nagios_scheduleddowntime                   |       0.01 |
| nagios_service_contactgroups               |       0.08 |
| nagios_service_contacts                    |       0.00 |
| nagios_service_parentservices              |       0.00 |
| nagios_servicechecks                       |       0.30 |
| nagios_servicedependencies                 |       0.00 |
| nagios_serviceescalation_contactgroups     |       0.00 |
| nagios_serviceescalation_contacts          |       0.00 |
| nagios_serviceescalations                  |       0.00 |
| nagios_servicegroup_members                |       0.04 |
| nagios_servicegroups                       |       0.00 |
| nagios_services                            |       0.86 |
| nagios_servicestatus                       |       1.55 |
| nagios_statehistory                        |     127.96 |
| nagios_systemcommands                      |       0.03 |
| nagios_timedeventqueue                     |       0.00 |
| nagios_timedevents                         |       0.00 |
| nagios_timeperiod_timeranges               |       0.02 |
| nagios_timeperiods                         |       0.00 |
| tbl_command                                |       0.06 |
| tbl_contact                                |       0.03 |
| tbl_contactgroup                           |       0.03 |
| tbl_contacttemplate                        |       0.03 |
| tbl_domain                                 |       0.03 |
| tbl_host                                   |       0.13 |
| tbl_hostdependency                         |       0.03 |
| tbl_hostescalation                         |       0.03 |
| tbl_hostextinfo                            |       0.03 |
| tbl_hostgroup                              |       0.03 |
| tbl_hosttemplate                           |       0.03 |
| tbl_info                                   |       0.17 |
| tbl_lnkContactToCommandHost                |       0.02 |
| tbl_lnkContactToCommandService             |       0.02 |
| tbl_lnkContactToContactgroup               |       0.02 |
| tbl_lnkContactToContacttemplate            |       0.02 |
| tbl_lnkContactToVariabledefinition         |       0.02 |
| tbl_lnkContactgroupToContact               |       0.02 |
| tbl_lnkContactgroupToContactgroup          |       0.02 |
| tbl_lnkContacttemplateToCommandHost        |       0.02 |
| tbl_lnkContacttemplateToCommandService     |       0.02 |
| tbl_lnkContacttemplateToContactgroup       |       0.02 |
| tbl_lnkContacttemplateToContacttemplate    |       0.02 |
| tbl_lnkContacttemplateToVariabledefinition |       0.02 |
| tbl_lnkHostToContact                       |       0.02 |
| tbl_lnkHostToContactgroup                  |       0.02 |
| tbl_lnkHostToHost                          |       0.02 |
| tbl_lnkHostToHostgroup                     |       0.08 |
| tbl_lnkHostToHosttemplate                  |       0.09 |
| tbl_lnkHostToVariabledefinition            |       0.02 |
| tbl_lnkHostdependencyToHost_DH             |       0.02 |
| tbl_lnkHostdependencyToHost_H              |       0.02 |
| tbl_lnkHostdependencyToHostgroup_DH        |       0.02 |
| tbl_lnkHostdependencyToHostgroup_H         |       0.02 |
| tbl_lnkHostescalationToContact             |       0.02 |
| tbl_lnkHostescalationToContactgroup        |       0.02 |
| tbl_lnkHostescalationToHost                |       0.02 |
| tbl_lnkHostescalationToHostgroup           |       0.02 |
| tbl_lnkHostgroupToHost                     |       0.02 |
| tbl_lnkHostgroupToHostgroup                |       0.02 |
| tbl_lnkHosttemplateToContact               |       0.02 |
| tbl_lnkHosttemplateToContactgroup          |       0.02 |
| tbl_lnkHosttemplateToHost                  |       0.02 |
| tbl_lnkHosttemplateToHostgroup             |       0.02 |
| tbl_lnkHosttemplateToHosttemplate          |       0.02 |
| tbl_lnkHosttemplateToVariabledefinition    |       0.02 |
| tbl_lnkServiceToContact                    |       0.02 |
| tbl_lnkServiceToContactgroup               |       0.02 |
| tbl_lnkServiceToHost                       |       0.08 |
| tbl_lnkServiceToHostgroup                  |       0.02 |
| tbl_lnkServiceToServicegroup               |       0.05 |
| tbl_lnkServiceToServicetemplate            |       0.09 |
| tbl_lnkServiceToVariabledefinition         |       0.02 |
| tbl_lnkServicedependencyToHost_DH          |       0.02 |
| tbl_lnkServicedependencyToHost_H           |       0.02 |
| tbl_lnkServicedependencyToHostgroup_DH     |       0.02 |
| tbl_lnkServicedependencyToHostgroup_H      |       0.02 |
| tbl_lnkServicedependencyToService_DS       |       0.02 |
| tbl_lnkServicedependencyToService_S        |       0.02 |
| tbl_lnkServicedependencyToServicegroup_DS  |       0.02 |
| tbl_lnkServicedependencyToServicegroup_S   |       0.02 |
| tbl_lnkServiceescalationToContact          |       0.02 |
| tbl_lnkServiceescalationToContactgroup     |       0.02 |
| tbl_lnkServiceescalationToHost             |       0.02 |
| tbl_lnkServiceescalationToHostgroup        |       0.02 |
| tbl_lnkServiceescalationToService          |       0.02 |
| tbl_lnkServiceescalationToServicegroup     |       0.02 |
| tbl_lnkServicegroupToService               |       0.02 |
| tbl_lnkServicegroupToServicegroup          |       0.02 |
| tbl_lnkServicetemplateToContact            |       0.02 |
| tbl_lnkServicetemplateToContactgroup       |       0.02 |
| tbl_lnkServicetemplateToHost               |       0.02 |
| tbl_lnkServicetemplateToHostgroup          |       0.02 |
| tbl_lnkServicetemplateToServicegroup       |       0.02 |
| tbl_lnkServicetemplateToServicetemplate    |       0.02 |
| tbl_lnkServicetemplateToVariabledefinition |       0.02 |
| tbl_lnkTimeperiodToTimeperiod              |       0.02 |
| tbl_logbook                                |       0.02 |
| tbl_mainmenu                               |       0.02 |
| tbl_permission                             |       0.02 |
| tbl_permission_inactive                    |       0.02 |
| tbl_service                                |       0.22 |
| tbl_servicedependency                      |       0.03 |
| tbl_serviceescalation                      |       0.03 |
| tbl_serviceextinfo                         |       0.03 |
| tbl_servicegroup                           |       0.03 |
| tbl_servicetemplate                        |       0.08 |
| tbl_session                                |       0.02 |
| tbl_session_locks                          |       0.02 |
| tbl_settings                               |       0.03 |
| tbl_submenu                                |       0.02 |
| tbl_timedefinition                         |       0.02 |
| tbl_timeperiod                             |       0.03 |
| tbl_user                                   |       0.03 |
| tbl_variabledefinition                     |       0.02 |
+--------------------------------------------+------------+

No errors are being generated to /var/log/mysql/mysqld.log and max_connections is fine:

Code: Select all

mysql -uroot -pnagiosxi -e "show variables like 'max_connections';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

and max we hit has only been 125

Code: Select all

mysql -uroot -pnagiosxi -e "show global status like 'Max_used_connections';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 125   |
+----------------------+-------+

Hopefully it calms down by morning, but anything else I can check to troubleshoot this?

[J.A.K]

Looks like it's calmed down after a few hours, but I'd still like to know if there was anything else I could have checked to identify what was happening.

[dchurch]

Sounds like one of the daily automated tasks is generating a lot of database activity. Did this just start happening, or has this been happening for a while now?

What's the output from this command?

Code: Select all

mysql -uroot -pnagiosxi --table <<< "select table_name as 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' from information_schema.TABLES where table_schema in ('nagios', 'nagiosql', 'nagiosxi');"

[J.A.K]

Just happened the one night and hasn't happened again. Only thing going on was we had some firewall changes later that night, but this happened about 2 hours prior and there was never any network outages.

Code: Select all

mysql -uroot -pnagiosxi --table <<< "select table_name as 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' from information_schema.TABLES where table_schema in ('nagios', 'nagiosql', 'nagiosxi');"
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements | 0.05 |
| nagios_commands | 0.02 |
| nagios_commenthistory | 7.06 |
| nagios_comments | 0.01 |
| nagios_configfiles | 0.01 |
| nagios_configfilevariables | 0.01 |
| nagios_contactgroups | 0.00 |
| nagios_contactgroup_members | 0.00 |
| nagios_contactstatus | 0.00 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.01 |
| nagios_customvariables | 0.00 |
| nagios_customvariablestatus | 0.00 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 0.66 |
| nagios_externalcommands | 0.02 |
| nagios_flappinghistory | 0.00 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalations | 0.00 |
| nagios_hostescalation_contactgroups | 0.00 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostgroups | 0.00 |
| nagios_hostgroup_members | 0.08 |
| nagios_host_contactgroups | 0.02 |
| nagios_host_contacts | 0.00 |
| nagios_host_parenthosts | 0.03 |
| nagios_instances | 0.00 |
| nagios_processevents | 0.97 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.00 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalations | 0.00 |
| nagios_serviceescalation_contactgroups | 0.00 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_servicegroups | 0.00 |
| nagios_servicegroup_members | 0.04 |
| nagios_service_contactgroups | 0.08 |
| nagios_service_contacts | 0.00 |
| nagios_service_parentservices | 0.00 |
| nagios_timeperiods | 0.00 |
| nagios_timeperiod_timeranges | 0.02 |
| xi_eventqueue | 0.03 |
| nagios_contactnotifications | 1.37 |
| nagios_contactnotificationmethods | 1.95 |
| nagios_objects | 0.54 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_eventhandlers | 0.00 |
| nagios_hostchecks | 0.25 |
| nagios_hoststatus | 0.57 |
| nagios_notifications | 2.93 |
| nagios_servicechecks | 0.39 |
| nagios_servicestatus | 1.58 |
| nagios_statehistory | 129.32 |
| nagios_systemcommands | 0.05 |
| nagios_contacts | 0.01 |
| nagios_logentries | 253.99 |
| nagios_services | 0.86 |
| nagios_hosts | 0.30 |
| tbl_contacttemplate | 0.03 |
| tbl_domain | 0.03 |
| tbl_hostdependency | 0.03 |
| tbl_hostescalation | 0.03 |
| tbl_hostextinfo | 0.03 |
| tbl_info | 0.17 |
| tbl_lnkContactToCommandHost | 0.02 |
| tbl_lnkContactToCommandService | 0.02 |
| tbl_lnkContactToContactgroup | 0.02 |
| tbl_lnkContactToContacttemplate | 0.02 |
| tbl_lnkContactToVariabledefinition | 0.02 |
| tbl_lnkContactgroupToContact | 0.02 |
| tbl_lnkContactgroupToContactgroup | 0.02 |
| tbl_lnkContacttemplateToCommandHost | 0.02 |
| tbl_lnkContacttemplateToCommandService | 0.02 |
| tbl_lnkContacttemplateToContactgroup | 0.02 |
| tbl_lnkContacttemplateToContacttemplate | 0.02 |
| tbl_lnkContacttemplateToVariabledefinition | 0.02 |
| tbl_lnkHostToContact | 0.02 |
| tbl_lnkHostToContactgroup | 0.02 |
| tbl_lnkHostToHosttemplate | 0.09 |
| tbl_lnkHostToVariabledefinition | 0.02 |
| tbl_lnkHostdependencyToHostgroup_DH | 0.02 |
| tbl_lnkHostdependencyToHostgroup_H | 0.02 |
| tbl_lnkHostescalationToContact | 0.02 |
| tbl_lnkHostescalationToContactgroup | 0.02 |
| tbl_lnkHostescalationToHost | 0.02 |
| tbl_lnkHostescalationToHostgroup | 0.02 |
| tbl_lnkHostgroupToHostgroup | 0.02 |
| tbl_lnkHosttemplateToContact | 0.02 |
| tbl_lnkHosttemplateToContactgroup | 0.02 |
| tbl_lnkHosttemplateToHost | 0.02 |
| tbl_lnkHosttemplateToHostgroup | 0.02 |
| tbl_lnkHosttemplateToHosttemplate | 0.02 |
| tbl_lnkHosttemplateToVariabledefinition | 0.02 |
| tbl_lnkServiceToContact | 0.02 |
| tbl_lnkServiceToContactgroup | 0.02 |
| tbl_lnkServiceToServicetemplate | 0.09 |
| tbl_lnkServiceToVariabledefinition | 0.02 |
| tbl_lnkServicedependencyToHost_DH | 0.02 |
| tbl_lnkServicedependencyToHost_H | 0.02 |
| tbl_lnkServicedependencyToHostgroup_DH | 0.02 |
| tbl_lnkServicedependencyToHostgroup_H | 0.02 |
| tbl_lnkServicedependencyToServicegroup_DS | 0.02 |
| tbl_lnkServicedependencyToServicegroup_S | 0.02 |
| tbl_lnkServiceescalationToContact | 0.02 |
| tbl_lnkServiceescalationToContactgroup | 0.02 |
| tbl_lnkServiceescalationToHost | 0.02 |
| tbl_lnkServiceescalationToHostgroup | 0.02 |
| tbl_lnkServiceescalationToService | 0.02 |
| tbl_lnkServiceescalationToServicegroup | 0.02 |
| tbl_lnkServicegroupToService | 0.02 |
| tbl_lnkServicegroupToServicegroup | 0.02 |
| tbl_lnkServicetemplateToContact | 0.02 |
| tbl_lnkServicetemplateToContactgroup | 0.02 |
| tbl_lnkServicetemplateToHost | 0.02 |
| tbl_lnkServicetemplateToHostgroup | 0.02 |
| tbl_lnkServicetemplateToServicegroup | 0.02 |
| tbl_lnkServicetemplateToServicetemplate | 0.02 |
| tbl_lnkServicetemplateToVariabledefinition | 0.02 |
| tbl_lnkTimeperiodToTimeperiod | 0.02 |
| tbl_mainmenu | 0.02 |
| tbl_permission | 0.02 |
| tbl_permission_inactive | 0.02 |
| tbl_servicedependency | 0.03 |
| tbl_serviceescalation | 0.03 |
| tbl_serviceextinfo | 0.03 |
| tbl_session | 0.02 |
| tbl_session_locks | 0.02 |
| tbl_settings | 0.03 |
| tbl_submenu | 0.02 |
| tbl_variabledefinition | 0.02 |
| xi_auditlog | 14.58 |
| xi_commands | 0.02 |
| xi_events | 0.05 |
| xi_meta | 0.02 |
| xi_mibs | 0.05 |
| xi_options | 0.06 |
| xi_sysstat | 0.03 |
| xi_usermeta | 1.59 |
| xi_users | 0.03 |
| xi_sessions | 0.03 |
| xi_auth_tokens | 0.03 |
| xi_deploy_jobs | 0.02 |
| xi_deploy_agents | 0.02 |
| xi_cmp_trapdata | 0.03 |
| xi_cmp_trapdata_log | 0.03 |
| xi_cmp_ccm_backups | 0.02 |
| xi_cmp_nagiosbpi_backups | 0.25 |
| xi_cmp_favorites | 0.03 |
| xi_cmp_scheduledreports_log | 0.02 |
| tbl_contact | 0.03 |
| tbl_contactgroup | 0.03 |
| tbl_host | 0.13 |
| tbl_hostgroup | 0.03 |
| tbl_lnkHostgroupToHost | 0.02 |
| tbl_lnkHostToHost | 0.02 |
| tbl_lnkHostToHostgroup | 0.08 |
| tbl_lnkHostdependencyToHost_DH | 0.02 |
| tbl_lnkHostdependencyToHost_H | 0.02 |
| tbl_lnkServiceToHost | 0.08 |
| tbl_lnkServicedependencyToService_DS | 0.02 |
| tbl_lnkServicedependencyToService_S | 0.02 |
| tbl_lnkServiceToHostgroup | 0.02 |
| tbl_lnkServiceToServicegroup | 0.05 |
| tbl_logbook | 0.02 |
| tbl_service | 0.22 |
| tbl_servicegroup | 0.03 |
| tbl_timeperiod | 0.03 |
| tbl_timedefinition | 0.02 |
| tbl_hosttemplate | 0.03 |
| tbl_servicetemplate | 0.08 |
| tbl_user | 0.03 |
| tbl_command | 0.06 |
+--------------------------------------------+------------+

[dchurch]

Edit /etc/my.cnf.d/server.cnf, adding lines under [server], e.g.:

Code: Select all

[server]
log_slow_queries = 1;
# (time in seconds)
long_query_time = 5;
slow_query_log_file = /var/log/mariadb/slow-queries.log;

Then restart mariadb:

Code: Select all

service mariadb restart

When this happens again, post the contents of the log under /var/log/mariadb/slow-queries.log so that we can diagnose further.

[J.A.K]

Don't have a MariaDB just MySQL

Code: Select all

systemctl status mariadb

Unit mariadb.service could not be found.

Code: Select all

systemctl status mysqld

● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor pres>
Active: active (running) since Sat 2021-03-20 22:07:28 EDT; 1 day 14h ago
Process: 4441 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=>
Process: 4685 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, st>
Process: 4603 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (>
Process: 4579 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, stat>
Main PID: 4641 (mysqld)
Status: "Server is operational"
Tasks: 130 (limit: 101111)
Memory: 3.4G
CGroup: /system.slice/mysqld.service
└─4641 /usr/libexec/mysqld --basedir=/usr


Don't have that file either, but I do have /etc/my.cnf.d/mysql-server.cnf. This is RHEL 8.2 if that helps.

[dchurch]

Don't have a MariaDB just MySQL

Okay, then can you please enable the MySQL slow query log?

[J.A.K]

Ok so basically same thing just a different file. I'll go ahead and enable that and hopefully if it happens again it might give me a little bit more to go off of. Thank you!

[dchurch]

All right, post back here if/when this happens again so we can diagnose further.