Page 3 of 3
Re: Weekly issue - DB connection threads peaks
Posted: Wed Oct 20, 2021 11:58 am
by 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
Re: Weekly issue - DB connection threads peaks
Posted: Thu Oct 21, 2021 9:06 am
by 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)
Re: Weekly issue - DB connection threads peaks
Posted: Fri Oct 22, 2021 9:35 am
by 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
Re: Weekly issue - DB connection threads peaks
Posted: Mon Oct 25, 2021 12:07 pm
by lvaillant
Hello,
Profile uploaded.
Regards
Re: Weekly issue - DB connection threads peaks
Posted: Tue Oct 26, 2021 12:24 pm
by 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
Re: Weekly issue - DB connection threads peaks
Posted: Tue Dec 07, 2021 4:58 am
by 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.