Page 1 of 1
NagiosXI Postgresql database is filling up disk space
Posted: Fri Jun 30, 2017 5:02 am
by ddaluka
Hi All,
Nagiosxi postgresql data directory has taken upto 17GB space in my filesystem. I need to do cleanup and reduce the size of this data directory.
My data directory was originally placed in /var/lib/pgsql/data but because I ran out of disk space, I have moved it to /usr/local/pgsql/data where I had enough space available. But it will fill up filesystem in new directory as well.
I recently then decided to change the performance settings and the major change I have done in those settings is changed the max state history age from 730 days to 6 months.
I checked dbmaint.php cron job and I could see that logs were stuck at below query and taking lot of time to get response from postgresql database.
SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
I checked that there is total 42 million rows which comes in to this catagory and are ready to be deleted. So I decided to delete this data in batch of 40000 rows and it was successfully getting deleted but instead of reducing now the count has increased upto 74 million.
Can someone please help me to understand what data is stored in xi_meta table and how can I reduce the size of data directory? Can this table be truncated?
I tried Vaccum as well but there was no significant change. On top of that it looks like it creates temporary stats which is increasing my data directory size.
Thanks in advance for your help.
Re: NagiosXI Postgresql database is filling up disk space
Posted: Fri Jun 30, 2017 12:43 pm
by dwhitfield
Yes, that table can be truncated.
There was a bug in earlier versions. I would highly suggest an upgrade.
With Internet instructions:
https://assets.nagios.com/downloads/nag ... nstall.pdf
Without Internet instructions: repo.nagios.com
Repos, but also with Internet Instructions (it's just a matter of which repos you can access): repo.nagios.com
Re: NagiosXI Postgresql database is filling up disk space
Posted: Fri Jun 30, 2017 2:12 pm
by ddaluka
Hi,
Thanks for your reply. I have already upgraded nagiosxi to 5.4.4 version recently. Do you know what can be the possible reason now if that bug was already fixed?
Also, will truncating this table going to affect any of my existing monitoring setup?
Can you suggest what should be the necessary action taken to avoid this happening again? Is there any performance settings which controls how much data will be kept in this table?
Re: NagiosXI Postgresql database is filling up disk space
Posted: Fri Jun 30, 2017 2:44 pm
by tgriep
The xi_meta table is a intermediate table that Nagios temporarily uses when it is running so it should be cleaned out automatically and not get very large over time.
What could of happened it when the upgrade was run, it did not update the Postgres table so that could be the reason it is getting filled up.
If you still have the Nagios XI upgrade files on the server, lets run the database upgrade manually and see if it helps.
If you do not have the source files, run the following as root to download and extract them.
Code: Select all
cd /tmp
wget https://assets.nagios.com/downloads/nagiosxi/xi-latest.tar.gz
tar xzf xi-latest.tar.gz
Then run the following as root to update the Postgres table.
Code: Select all
service nagios stop
psql nagiosxi nagiosxi -f /tmp/nagiosxi/nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql
service postgresql restart
service nagios start
If you want, you can run the following to truncate all of the temporary tables to clean them out by running the following
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
Let us know if this fixes the issue for you.
Re: NagiosXI Postgresql database is filling up disk space
Posted: Mon Jul 03, 2017 4:39 am
by ddaluka
Hi ,
Thanks for your detailed response. I will try these steps today and will let you know how it goes.
Re: NagiosXI Postgresql database is filling up disk space
Posted: Mon Jul 03, 2017 5:03 am
by ddaluka
Hi,
I have tried these mentioned steps. Database upgrade was already done as part of Nagiosxi upgrade hence didn't make any changes. But truncating these tables have put significant difference and has released lot of space and Database maintenance is also running fine now.
But How do I make sure that it gets cleared up automatically ? is dbmaint.php supposed to do this? Because this has been running properly since quite a long time but still I came to this situation.
Also, event_handler.log is also keep on filling up. This cron job is running every min.
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/local/nagiosxi/cron/recurringdowntime.pl >> /usr/local/nagiosxi/var/recurringdowntime.log 2>&1
* * * * * nagios /usr/bin/php -q /usr/local/nagiosxi/cron/deadpool.php >> /usr/local/nagiosxi/var/deadpool.log 2>&1
Re: NagiosXI Postgresql database is filling up disk space
Posted: Wed Jul 05, 2017 10:58 am
by tgriep
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.
If the Postgres tables are still increasing, run the following as root and post the /tmp/info.txt file so I can verify that the tables have been upgraded.
Code: Select all
echo '\d xi_events;' | psql nagiosxi nagiosxi >/tmp/info.txt
echo '\d xi_meta;' | psql nagiosxi nagiosxi >>/tmp/info.txt
echo '\d xi_eventqueue;' | psql nagiosxi nagiosxi >>/tmp/info.txt
The eventhandler.log file should be rotated using the logrotate command.
To check as see it is in the list to be rotated, look as the nagiosxi file in ghr following folder and you should see the files in the /usr/local/nagiosxi/var folder should be rotated.
Re: NagiosXI Postgresql database is filling up disk space
Posted: Mon Jul 17, 2017 5:21 pm
by ddaluka
Hi,
My postgresql database is quite stable now and getting purged on timely basis. Thanks for all your help and suggestions to make it work
Re: NagiosXI Postgresql database is filling up disk space
Posted: Tue Jul 18, 2017 11:36 am
by bolson
May we close this topic?