Weekly issue - DB connection threads peaks

[gsmith]

Hi


From a CLI please run:

Code: Select all

echo " SELECT logentry_time FROM nagios_logentries ORDER BY logentry_time ASC LIMIT 1;" | mysql -u root -pnagiosxi nagios

This will give you the date of the oldest log entry.


By default the settings in NagiosXI are:
Max Audit Log Age = 180 days

So if you are ok with keeping 180 days of log entries you can run the following on the CLI:

Code: Select all

mysqldump -u root -pnagiosxi nagios nagios_logentries | gzip > /tmp/nagios_logentries.sql.gz
echo "DELETE FROM nagios_logentries WHERE logentry_time  < NOW() - INTERVAL 180 DAY" | mysql -u root -pnagiosxi nagios

The first command will backup the nagios.nagios_logentries table and gzip it, and put it in /tmp so make sure you have
room on the filesystem.

The second command will remove entries older than 180 days from the nagios.nagios_logentries table.

Please let me know the results.

Thanks

[lvaillant]

Hi

Performance Settings -> Max Log Entries Age: 60d

Code: Select all

MariaDB [(none)]> SELECT logentry_time FROM nagios.nagios_logentries ORDER BY logentry_time ASC LIMIT 1;
+---------------------+
| logentry_time       |
+---------------------+
| 2021-08-22 15:35:10 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> DELETE FROM nagios.nagios_logentries WHERE logentry_time < NOW() - INTERVAL 180 DAY;
Query OK, 0 rows affected (0.00 sec)

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_name ='nagios_logentries';
+-------------------+--------+------------+------------+
| table_name        | engine | table_rows | Size_in_MB |
+-------------------+--------+------------+------------+
| nagios_logentries | MyISAM |   14650822 |    3755.66 |
+-------------------+--------+------------+------------+
1 row in set (0.00 sec)

[gsmith]

Hi

Now that the disk and the db tables have been cleaned up please send me a new System Profile.

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]

Hello,

Profile uploaded.
Regards

[gsmith]

Hi

It looks like your performance data is not getting collected.

1.Please run:

Code: Select all

ls -lR /usr/local/nagios/share/perfdata >> /tmp/perfinfo.txt
tar czf /tmp/perfinfo.tar.gz  /tmp/perfinfo.txt

and send me the /tmp/perfinfo.tar.gz file.

2. Please send me the output of:

Code: Select all

echo " SHOW PROCESSLIST;" | mysql -u root -pnagiosxi nagios

3. Please send me the /usr/local/nagios/etc/pnp/npcd.cfg file

4. Is dbmaint running successfully ?

5. Did the db connections spike this past Sunday?

Thanks

[lvaillant]

Hi

Sorry for the delay.
I was waiting to see if the issue recurs after a few weeks, because another phenomenon occurs randomly.
I had to truncate the xi_events, xi_meta, xi_eventqueue several times as they are growing and reaching several million rows each.
The backups are now of a reasonable size.

Code: Select all

~# du -sh /store/backups/*
14G     /store/backups/mysql
24G     /store/backups/nagiosxi

Perf data are collecting successfully, despite the other issue I mentionned: dbmaint sometimes fails and "optimize" phase blocks gearmand every 30min.
I will open another ticket for this one.

So I consider we can close this case.
Thank you for your help.
Regards.