Weekly issue - DB connection threads peaks

[lvaillant]

Hello

Since I updated Nagios XI from 5.8.4 to 5.8.6 Sept. 9th, my Nagios server hangs every week-end because of too many DB connections.

Nagios -cpu & mysql threads.jpg

It goes from less than 100 connections during the week to the limit I configured in MariaDB setup (currently 500 conn.).
(no DB conn peaks before the update).

The result is that my Nagios instance becomes unavailable and I have to check services, reboot server and repair databases every monday.

The server is running an up2date RHEL 7.9 and related mariaDB server.

I do not undestand why, even if I suspect a Nagios weekly cron job, and how to solve this recurring issue.
I'm currently digging Nagios and MariaDB log files.

Do you have any clue?
Thank you in advance.

[gsmith]

Hi,

Please send us your System Profile so we can look at the logs and configs.

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


Thanks

[lvaillant]

Hi.

Done.
Thank you.

[gsmith]

Hi,

I received the System Profile - thanks.

I'll be loading it this afternoon or first thing in the morning.

Thanks

[lvaillant]

Do you have any results after analysis ?
Thank you.

[gsmith]

Hi,

I need to back farter in time so please do the following in the CLI (Command Line Interface):

Code: Select all

cd /tmp
tar cvzf /tmp/supportfiles.tar.gz /var/log/mess* /var/log/mysql/*

Save the /tmp/supportfiles.tar.gz file and share the file in a private message and
then reply to this post to bring it up in the queue.

Thanks

[lvaillant]

Tarballs are > 20MB even if I split them. So I can't upload.

[gsmith]

Hi

Please try this:

Code: Select all

cd /tmp
tar cvzf /tmp/supportfiles.tar.gz /var/log/mess* /var/log/mysql/*
split -b 18M /tmp/supportfiles.tar.gz

That will split the original /tmp/supportfiles.tar.gz file into 18 Mb chunks named xaa, xab, xac, ....

Here is an example of using the split command on the file xi-5.6.5.tar.gz

Code: Select all

[root@gs-cent7-23-94 tmp]#ll
total 56692
-rwxrwxr-x. 1 root root 58048805 Sep 27 14:26 xi-5.6.5.tar.gz
[root@gs-cent7-23-94 tmp]# split -b 18M xi-5.6.5.tar.gz
[root@gs-cent7-23-94 tmp]# ll
total 113384
-rw-r--r--. 1 root root 18874368 Oct  1 15:54 xaa
-rw-r--r--. 1 root root 18874368 Oct  1 15:54 xab
-rw-r--r--. 1 root root 18874368 Oct  1 15:54 xac
-rw-r--r--. 1 root root  1425701 Oct  1 15:54 xad
-rwxrwxr-x. 1 root root 58048805 Sep 27 14:26 xi-5.6.5.tar.gz
[root@gs-cent7-23-94 tmp]#

[lvaillant]

Hello.

I'll wait next sunday to check if issue occurs again and send the related logs.

Last week-end, it occured and I had to restart mariaDB to allow Nagios to work as expected.
Before the restart, the SHOW PROCESSLIST displayed lots of "Waiting for table metadata lock" states.
The running/blocking jobs were :

Code: Select all

| 1318833 | ndoutils | localhost | nagios   | Query   | 1411 | Repair by sorting               | OPTIMIZE TABLE nagios_logentries                                                                     |    0.000 |
| 1320750 | nagiosxi | localhost | nagiosxi | Query   |   68 | Sending data                    | SELECT * FROM xi_meta WHERE metatype_id='1' AND metaobj_id='14462683'         |    0.000 |
| 1320750 | nagiosxi | localhost | nagiosxi | Query   |   68 | Sending data                    | SELECT * FROM xi_meta WHERE metatype_id='1' AND metaobj_id='14462683'         |    0.000 |

I had to use the repair_databases script as nagios_logentries was considered as crashed.
After the recovery:

Code: Select all

MariaDB [(none)]> SELECT table_name AS table_name, engine, table_rows, round(((data_length + index_length) / 1024 / 1024), 2) Size_in_MB FROM information_schema.TABLES WHERE table_rows > 1000000;
+-----------------------------+--------+------------+------------+
| table_name                  | engine | table_rows | Size_in_MB |
+-----------------------------+--------+------------+------------+
| nagios_contactnotifications | MyISAM |   12482612 |    1127.14 |
| nagios_logentries           | MyISAM |   21240397 |    5402.61 |
| nagios_notifications        | MyISAM |    1030111 |     204.83 |
| nagios_statehistory         | MyISAM |    7226701 |     901.23 |
| xi_auditlog                 | InnoDB |   15923637 |    4785.95 |
| xi_events                   | InnoDB |   20202333 |    1607.78 |
| xi_meta                     | InnoDB |   19892871 |   26778.95 |
+-----------------------------+--------+------------+------------+
7 rows in set (1.55 sec)

* Max Log Entries Age : 90d
* Max Notifications Age: 60d
* Max State History Age: 365d
* Max Audit Log Age: 180d
* Max Events Age: 480min

[gsmith]

Hi

That sounds good. Thanks for all the information.

Good luck!