remove_historical_data.sh - nagios_logentries only

[gregbeyer]

I would like to clean up cruft in my nagios_logentries which I can tell has gotten huge and takes a long time to repair when I run repair-databases.sh. However remove_historical_data.sh, throws out not only logentries, but also performance history data, and other that I want to keep.

How can I clean up only logentries? Thanks.

[bbahn]

Hello @gregbeyer,

To clean up your nagios_logentries table without removing any historic data, I recommend using an SQL query.
I recommend creating backup before doing this so as to prevent accidental loss of data:

Code: Select all

 mysqldump -u <root/nagios> -p <mysqlrootpass/mysqlnagiospass> > nagiosdb_backup.sql

Then you can delete your nagios log entries by logging into mysql,

Code: Select all

mysql -u <root/nagios> -p

selecting your nagios table

Code: Select all

use nagios;

and deleting nagios log entries before a certain date

Code: Select all

DELETE FROM nagios_logentries WHERE entry_time < NOW() - INTERVAL 90 DAY;

This will delete any log entries from further than 90 days ago. You can update the interval as desired.

Since you are deleting a large number of table rows, it is recommended that you optimize your table afterwards using something like

Code: Select all

OPTIMIZE TABLE nagios_logentries

[gregbeyer]

Hello @bbahn, I've run your suggested commands, but it seems to make no difference in the sixe of my nagios_logentries.

First I ran repair_databases.sh, and it shows that nagios_logentries has Data records: 18,054,655.

Then I ran:

mysql> DELETE FROM nagios_logentries WHERE entry_time < NOW() - INTERVAL 30 DAY;
Query OK, 0 rows affected (24.60 sec)

mysql> OPTIMIZE TABLE nagios_logentries;
+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| nagios.nagios_logentries | optimize | status | OK |
+--------------------------+----------+----------+----------+
1 row in set (15.15 sec)

0 rows affected??

Then I ran repair_databases again: Data records: 18070647. (18,070,647 to make easier to read)

So in the few minutes between first repair, Delete and second repair the table grew in size.