huge CPU usage by mysql after migrate nagios 5.7.5 to rhel 8

[supportcl]

Hello, after migrating nagiosxi 5.7.5 from rhel 6.x to rhel 8.3, without problems, when activating it so that the clients send information to the server, it is seen that the mysql database is consuming a lot of CPU, something that did not happen before the migration. I have already run the script to repair the database tables but everything remains the same. We have about 650 hosts with almost 3500 services. What can I check?

[benjaminsmith]

Hi @supportcl,

Let's run a top command on the server to see which processes are using excessive CPU resources (when the system is under load). Also, PM the system profile and we'll review the logs for you.

A few more questions:

1. Are notifications be sent?
2. Can the customer access the GUI and perform necessary operations?
3. Are all the system components up and running, green status?
4. Does the CPU load go down at night? How many users are logged into the system?

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

Thanks,
Benjamin

[supportcl]

1. Are notifications be sent?
Yes
2. Can the customer access the GUI and perform necessary operations?
Yes
3. Are all the system components up and running, green status?
Yes
4. Does the CPU load go down at night? How many users are logged into the system?.

When I just fired nagios after migration, for several hours the CPU was at 200% used by mysql
Now it's down to something almost normal, but it's still a lot more CPU usage than my previous system.

There are very few users that are connected at the same time, maybe 1 or 2 no more than that.

[supportcl]

profile file


Moderator's Note: The profile has been shared with the support team but has been removed from the public forum.

[benjaminsmith]

Hi,

Thanks for sending that over, nothing really standing out in the profile. The top command at this particular point in time looks normal.

top - 17:06:56 up 23:27, 2 users, load average: 1.03, 1.34, 1.42
Tasks: 310 total, 2 running, 308 sleeping, 0 stopped, 0 zombie
%Cpu(s): 10.8 us, 3.1 sy, 0.0 ni, 86.2 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
MiB Mem : 7767.1 total, 547.6 free, 4107.0 used, 3112.5 buff/cache
MiB Swap: 8092.0 total, 7773.6 free, 318.3 used. 3015.5 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22164 mysql 20 0 2853868 897620 15236 S 12.5 11.3 1037:26 mysqld
846 root 20 0 150712 52360 19272 S 6.2 0.7 4:43.07 systemd+

The profile did not have the database log for some reason, maybe it was taking too long to generate. Can you provide a tail output of it or attach that log to the thread as well. Thanks, Benjamin

Reference
Nagios XI Log Files and Descriptions

[supportcl]

I send the last database log here, nothing strange is seen, but the database is still using a lot of CPU and no problem is seen, is there another log that I can review?

maybe with this version, is it normal for the database to use more cpu?

[root @ nagiosxi mysql] # more mysqld.log-20210205
2021-02-04T12: 58: 11.368935Z 0 [System] [MY-010116] [Server] / usr / libexec / mysqld (mysqld 8.0.21) starting as process 1333
2021-02-04T12: 58: 11.544977Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization started.
2021-02-04T12: 58: 13.679172Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization is complete.
2021-02-04T12: 58: 14.200135Z 0 [System] [MY-011323] [Server] X Plug-in ready for connections. Link address: '::' port: 33060, socket: / var / lib / mysql
/mysqlx.sock
2021-02-04T12: 58: 17.188120Z 0 [Warning] [MY-010068] [Server] The CA.pem certificate is self-signed.
2021-02-04T12: 58: 17.188471Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported
for this channel.
2021-02-04T12: 58: 17.221670Z 0 [System] [MY-010931] [Server] / usr / libexec / mysqld: ready for connections. Version: '8.0.21' socket: '/ var / lib / mysql
/mysql.sock 'port: 3306 Source distribution.
[root @ nagiosxi mysql] #

[benjaminsmith]

HI @supportcl,

It's normal for CPU to spike but it should come down. The top command output did not show signs of a sustained load based on the averages.

Let's get an output of the database table sizes, truncating very large tables will improve performance.

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', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

Also, can you attach the database configuration file to the thread?

Code: Select all

/etc/my.cnf

Thanks, Benjamin

[supportcl]

+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements | 0.01 |
| nagios_commands | 0.02 |
| nagios_commenthistory | 16.34 |
| nagios_comments | 0.01 |
| nagios_configfiles | 0.01 |
| nagios_configfilevariables | 0.01 |
| nagios_conninfo | 1.88 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.01 |
| nagios_contactgroup_members | 0.00 |
| nagios_contactgroups | 0.00 |
| nagios_contactnotificationmethods | 13.20 |
| nagios_contactnotifications | 9.24 |
| nagios_contacts | 0.01 |
| nagios_contactstatus | 0.00 |
| nagios_customvariables | 0.28 |
| nagios_customvariablestatus | 0.28 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 0.57 |
| nagios_eventhandlers | 0.02 |
| nagios_externalcommands | 0.03 |
| nagios_flappinghistory | 3.31 |
| nagios_host_contactgroups | 0.00 |
| nagios_host_contacts | 0.03 |
| nagios_host_parenthosts | 0.00 |
| nagios_hostchecks | 0.47 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalation_contactgroups | 0.00 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostescalations | 0.00 |
| nagios_hostgroup_members | 0.03 |
| nagios_hostgroups | 0.00 |
| nagios_hosts | 0.28 |
| nagios_hoststatus | 0.55 |
| nagios_instances | 0.00 |
| nagios_logentries | 477.65 |
| nagios_notifications | 18.66 |
| nagios_objects | 1.94 |
| nagios_processevents | 2.21 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.00 |
| nagios_service_contactgroups | 0.06 |
| nagios_service_contacts | 0.14 |
| nagios_service_parentservices | 0.00 |
| nagios_servicechecks | 2.11 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalation_contactgroups | 0.00 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_serviceescalations | 0.00 |
| nagios_servicegroup_members | 0.01 |
| nagios_servicegroups | 0.00 |
| nagios_services | 1.31 |
| nagios_servicestatus | 3.02 |
| nagios_statehistory | 393.11 |
| nagios_systemcommands | 0.05 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_timeperiod_timeranges | 0.01 |
| nagios_timeperiods | 0.00 |
| tbl_command | 0.05 |
| tbl_contact | 0.01 |
| tbl_contactgroup | 0.01 |
| tbl_contacttemplate | 0.01 |
| tbl_domain | 0.01 |
| tbl_host | 0.25 |
| tbl_hostdependency | 0.00 |
| tbl_hostescalation | 0.00 |
| tbl_hostextinfo | 0.00 |
| tbl_hostgroup | 0.01 |
| tbl_hosttemplate | 0.02 |
| tbl_info | 0.27 |
| tbl_lnkContactToCommandHost | 0.00 |
| tbl_lnkContactToCommandService | 0.00 |
| tbl_lnkContactToContactgroup | 0.00 |
| tbl_lnkContactToContacttemplate | 0.00 |
| tbl_lnkContactToVariabledefinition | 0.00 |
| tbl_lnkContactgroupToContact | 0.00 |
| tbl_lnkContactgroupToContactgroup | 0.00 |
| tbl_lnkContacttemplateToCommandHost | 0.00 |
| tbl_lnkContacttemplateToCommandService | 0.00 |
| tbl_lnkContacttemplateToContactgroup | 0.00 |
| tbl_lnkContacttemplateToContacttemplate | 0.00 |
| tbl_lnkContacttemplateToVariabledefinition | 0.00 |
| tbl_lnkHostToContact | 0.02 |
| tbl_lnkHostToContactgroup | 0.00 |
| tbl_lnkHostToHost | 0.00 |
| tbl_lnkHostToHostgroup | 0.00 |
| tbl_lnkHostToHosttemplate | 0.02 |
| tbl_lnkHostToVariabledefinition | 0.02 |
| tbl_lnkHostdependencyToHost_DH | 0.00 |
| tbl_lnkHostdependencyToHost_H | 0.00 |
| tbl_lnkHostdependencyToHostgroup_DH | 0.00 |
| tbl_lnkHostdependencyToHostgroup_H | 0.00 |
| tbl_lnkHostescalationToContact | 0.00 |
| tbl_lnkHostescalationToContactgroup | 0.00 |
| tbl_lnkHostescalationToHost | 0.00 |
| tbl_lnkHostescalationToHostgroup | 0.00 |
| tbl_lnkHostgroupToHost | 0.01 |
| tbl_lnkHostgroupToHostgroup | 0.00 |
| tbl_lnkHosttemplateToContact | 0.00 |
| tbl_lnkHosttemplateToContactgroup | 0.00 |
| tbl_lnkHosttemplateToHost | 0.00 |
| tbl_lnkHosttemplateToHostgroup | 0.00 |
| tbl_lnkHosttemplateToHosttemplate | 0.00 |
| tbl_lnkHosttemplateToVariabledefinition | 0.00 |
| tbl_lnkServiceToContact | 0.07 |
| tbl_lnkServiceToContactgroup | 0.00 |
| tbl_lnkServiceToHost | 0.08 |
| tbl_lnkServiceToHostgroup | 0.01 |
| tbl_lnkServiceToServicegroup | 0.00 |
| tbl_lnkServiceToServicetemplate | 0.10 |
| tbl_lnkServiceToVariabledefinition | 0.07 |
| tbl_lnkServicedependencyToHost_DH | 0.00 |
| tbl_lnkServicedependencyToHost_H | 0.00 |
| tbl_lnkServicedependencyToHostgroup_DH | 0.00 |
| tbl_lnkServicedependencyToHostgroup_H | 0.00 |
| tbl_lnkServicedependencyToService_DS | 0.00 |
| tbl_lnkServicedependencyToService_S | 0.00 |
| tbl_lnkServicedependencyToServicegroup_DS | 0.02 |
| tbl_lnkServicedependencyToServicegroup_S | 0.02 |
| tbl_lnkServiceescalationToContact | 0.00 |
| tbl_lnkServiceescalationToContactgroup | 0.00 |
| tbl_lnkServiceescalationToHost | 0.00 |
| tbl_lnkServiceescalationToHostgroup | 0.00 |
| tbl_lnkServiceescalationToService | 0.00 |
| tbl_lnkServiceescalationToServicegroup | 0.02 |
| tbl_lnkServicegroupToService | 0.01 |
| tbl_lnkServicegroupToServicegroup | 0.00 |
| tbl_lnkServicetemplateToContact | 0.00 |
| tbl_lnkServicetemplateToContactgroup | 0.00 |
| tbl_lnkServicetemplateToHost | 0.00 |
| tbl_lnkServicetemplateToHostgroup | 0.00 |
| tbl_lnkServicetemplateToServicegroup | 0.00 |
| tbl_lnkServicetemplateToServicetemplate | 0.01 |
| tbl_lnkServicetemplateToVariabledefinition | 0.00 |
| tbl_lnkTimeperiodToTimeperiod | 0.00 |
| tbl_logbook | 0.00 |
| tbl_mainmenu | 0.00 |
| tbl_permission | 0.02 |
| tbl_permission_inactive | 0.02 |
| tbl_service | 1.21 |
| tbl_servicedependency | 0.00 |
| tbl_serviceescalation | 0.00 |
| tbl_serviceextinfo | 0.00 |
| tbl_servicegroup | 0.01 |
| tbl_servicetemplate | 0.03 |
| tbl_session | 0.00 |
| tbl_session_locks | 0.00 |
| tbl_settings | 0.00 |
| tbl_submenu | 0.00 |
| tbl_timedefinition | 0.01 |
| tbl_timeperiod | 0.01 |
| tbl_user | 0.01 |
| tbl_variabledefinition | 0.25 |
| xi_auditlog | 8.18 |
| xi_auth_tokens | 1.56 |
| xi_cmp_ccm_backups | 0.02 |
| xi_cmp_favorites | 0.03 |
| xi_cmp_nagiosbpi_backups | 0.28 |
| xi_cmp_scheduledreports_log | 0.02 |
| xi_cmp_trapdata | 0.03 |
| xi_cmp_trapdata_log | 0.03 |
| xi_commands | 0.01 |
| xi_deploy_agents | 0.02 |
| xi_deploy_jobs | 0.02 |
| xi_eventqueue | 0.20 |
| xi_events | 0.07 |
| xi_incidents | 0.00 |
| xi_meta | 7.13 |
| xi_mibs | 0.05 |
| xi_options | 0.03 |
| xi_sessions | 0.03 |
| xi_sysstat | 0.01 |
| xi_usermeta | 0.56 |
| xi_users | 0.02 |
+--------------------------------------------+------------+
[root@nagiosxi ~]#

[benjaminsmith]

Hi @supportcl,

Thanks for posting that output, it does look normal. At this point, I think think it would best to open a support ticket as we may need to set up a remote session to troubleshoot this further. Please reference this thread and attach a fresh system profile to the ticket.

To open a support ticket, please go to:
https://support.nagios.com/tickets/

Regards,
Benjamin