xi_meta table is too big and grows in Postgresql

This support forum board is for support questions relating to Nagios xi, our flagship commercial network monitoring solution.
danil.kim
Posts: 4
Joined: Wed Apr 21, 2021 2:41 am

xi_meta table is too big and grows in Postgresql

Post by danil.kim »

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?
Top
User avatar
vtrac
Posts: 903
Joined: Tue Oct 27, 2020 1:35 pm

Re: xi_meta table is too big and grows in Postgresql

Post by vtrac »

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
Top

Return to “Nagios xi”