Cleanup Nagios XI Database

bennyboy · Post by **bennyboy** » Wed Dec 02, 2020 8:56 am

Can you run some cleanup on this db ?

[root@NagiosServer mysql]# du -h
1012K   ./mysql
0       ./test
212K    ./performance_schema
6.5G    ./nagios
7.0M    ./nagiosql
14G     ./nagiosxi
21G     .

dchurch · Post by **dchurch** » Wed Dec 02, 2020 1:38 pm

How many hosts and services are you monitoring?

Any deletions from the database will result in data loss. Data loss such as historical data being used to draw uptime graphs. If disk space is more important to you than historical data, you can delete some of the entries in those tables.

If you're really concerned about which tables are contributing the most to the size-on-disk of your database, you can run this SQL:

Code: Select all

select
    round(((data_length + index_length) / 1024 / 1024), 2) "size_in_mb",
    concat(table_schema, '.', table_name) as "table"
from information_schema.tables
where data_length is not null
order by data_length + index_length;

Here Be Dragons

Again, THIS WILL RESULT IN DATA LOSS.

To delete entries in nagios_logentries older than 90 days, connect to the ndoutils database and run the following command:

Code: Select all

select count(*) from nagios_logentries where entry_time < date_sub(now(), interval 90 day);
-- only do this if you're sure: delete from nagios_logentries where entry_time < date_sub(now(), interval 90 day);

So, from the shell it would be:

Code: Select all

mysql -undoutils -pn@gweb nagios -e 'select count(*) from nagios_logentries where entry_time < date_sub(now(), interval 90 day);'
# only do this if you're sure: mysql -undoutils -pn@gweb nagios -e 'delete from nagios_logentries where entry_time < date_sub(now(), interval 90 day);'

Replace "90 day" with an appropriate value to delete more or less data. "30 day" would delete more data, "120 day" would delete less.

Nagios Support Forum

Cleanup Nagios XI Database

Cleanup Nagios XI Database

Re: Cleanup Nagios XI Database