Nagios Support Forum

Posted: **Thu May 06, 2021 12:55 am**

Dear all,

our Nagios XI is running and the table xi_meta grows 10GB per day.
I started dbmaint.php didn't help, VACUUM FULL won only a couple GB.

Code: Select all

nagiosxi=> SELECT pg_size_pretty( pg_total_relation_size('xi_meta') );
 pg_size_pretty
----------------
 147 GB
(1 row)

How can I to reduce this table?

In this folder /var/lib/pgsql/data/base/16385 there are a lot of files with names like

Code: Select all

-rw------- 1 postgres postgres 1.0G May  4 17:58 16424.32
-rw------- 1 postgres postgres 1.0G May  4 17:58 16424.33
-rw------- 1 postgres postgres 1.0G May  4 17:59 16424.34
-rw------- 1 postgres postgres 1.0G May  4 17:59 16424.35
-rw------- 1 postgres postgres 1.0G May  4 17:59 16424.36
-rw------- 1 postgres postgres 1.0G May  4 18:00 16424.37
-rw------- 1 postgres postgres 1.0G May  4 18:00 16424.38
-rw------- 1 postgres postgres 1.0G May  4 18:00 16424.39
-rw------- 1 postgres postgres 1.0G May  6 07:51 16424.4
-rw------- 1 postgres postgres 1.0G May  4 18:00 16424.40
-rw------- 1 postgres postgres 1.0G May  4 18:01 16424.41
-rw------- 1 postgres postgres 1.0G May  4 18:01 16424.42
-rw------- 1 postgres postgres 1.0G May  4 18:02 16424.43
-rw------- 1 postgres postgres 1.0G May  4 18:02 16424.44
-rw------- 1 postgres postgres 1.0G May  4 18:03 16424.45
-rw------- 1 postgres postgres 1.0G May  4 18:03 16424.46
-rw------- 1 postgres postgres 1.0G May  4 18:03 16424.47
-rw------- 1 postgres postgres 1.0G May  4 18:04 16424.48
-rw------- 1 postgres postgres 1.0G May  6 07:49 16424.49
-rw------- 1 postgres postgres 1.0G May  6 07:51 16424.5
-rw------- 1 postgres postgres 1.0G May  4 18:04 16424.50
-rw------- 1 postgres postgres 1.0G May  5 20:55 16424.51
-rw------- 1 postgres postgres 1.0G May  5 20:55 16424.52
-rw------- 1 postgres postgres 1.0G May  5 20:55 16424.53
-rw------- 1 postgres postgres 1.0G May  5 20:56 16424.54
-rw------- 1 postgres postgres 1.0G May  5 20:56 16424.55
-rw------- 1 postgres postgres 1.0G May  5 20:56 16424.56
-rw------- 1 postgres postgres 1.0G May  5 20:56 16424.57
-rw------- 1 postgres postgres 1.0G May  5 20:57 16424.58
-rw------- 1 postgres postgres 1.0G May  5 20:57 16424.59
-rw------- 1 postgres postgres 1.0G May  6 07:51 16424.6
-rw------- 1 postgres postgres 1.0G May  5 20:57 16424.60
-rw------- 1 postgres postgres 1.0G May  5 20:57 16424.61
-rw------- 1 postgres postgres 1.0G May  5 20:57 16424.62
-rw------- 1 postgres postgres 1.0G May  5 20:58 16424.63
-rw------- 1 postgres postgres 1.0G May  5 20:58 16424.64
-rw------- 1 postgres postgres 1.0G May  5 20:58 16424.65
-rw------- 1 postgres postgres 1.0G May  5 20:58 16424.66
-rw------- 1 postgres postgres 1.0G May  5 20:58 16424.67

any ideas?

Posted: **Thu May 06, 2021 3:50 pm**

Hi,
Hope you are having a good day!!

Please see forum ticket below, very helpful:
https://support.nagios.com/forum/viewto ... =6&t=44499

The dbmaint.php script that it run by the Cron Daemon should clear out the data in the tables as needed so that should free up the space unless there is an error.
Please check below log for any error belong to dbmaint.php:

Code: Select all

/usr/local/nagiosxi/var/dbmaint.log

Please make sure "dbmaint.php" does not commented out in the "/etc/cron.d/nagiosxi" file.

Example:
[root@vt-nagiosxi-62 cron.d]# cat /etc/crond/nagiosxi

Code: Select all

# /etc/cron.d/nagiosxi: crontab fragment for nagiosxi

# Backup MySQL & PostgreSQL Databases
0   7 * * * root   /root/scripts/automysqlbackup
0   7 * * * root   /root/scripts/autopostgresqlbackup > /dev/null 2>&1

*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/sysstat.php >> /usr/local/nagiosxi/var/sysstat.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/cmdsubsys.php >> /usr/local/nagiosxi/var/cmdsubsys.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/eventman.php >> /usr/local/nagiosxi/var/eventman.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/event_handler.php >> /usr/local/nagiosxi/var/event_handler.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/feedproc.php >> /usr/local/nagiosxi/var/feedproc.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/perfdataproc.php >> /usr/local/nagiosxi/var/perfdataproc.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/nom.php >> /usr/local/nagiosxi/var/nom.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/reportengine.php >> /usr/local/nagiosxi/var/reportengine.log 2>&1
*/5 * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php >> /usr/local/nagiosxi/var/dbmaint.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/cleaner.php >> /usr/local/nagiosxi/var/cleaner.log 2>&1
01  * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/recurring_downtime.php >> /usr/local/nagiosxi/var/recurringdowntime.log 2>&1
*       * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/snmptt_service_results.php >> /usr/local/nagiosxi/var/snmptt_service_results.log 2>&1
*   * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/deadpool.php >> /usr/local/nagiosxi/var/deadpool.log 2>&1

Here are the steps to truncate "xi_meta" (from ticket above):

Code: Select all

service nagios stop
service ndo2db stop
service crond stop
service postgresql restart
pkill -9 -u nagios
echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | psql nagiosxi nagiosxi
service crond start
service ndo2db start
service nagios start
service npcd restart

Best Regards,
Vinh

Nagios Support Forum

xi_meta table is too big and grows in Postgresql

xi_meta table is too big and grows in Postgresql

Re: xi_meta table is too big and grows in Postgresql