Page 1 of 1
Database files large in size
Posted: Tue Nov 21, 2017 7:44 am
by sandeepatil
Hi,
Database xi_meta file at /var/lib/mysql/nagiosxi/, automatically increased very large in size. Status as show in below.
-rw-rw---- 1 mysql mysql 85G Nov 21 07:22 xi_meta.MYD
-rw-rw---- 1 mysql mysql 695M Nov 21 07:22 xi_meta.MYI
-rw-rw---- 1 mysql mysql 8.6K Nov 3 20:08 xi_meta.frm
Due to those files partition utilization was 100%.
Please share the cause and solution for this issue and steps for avoid in future.
Thanks,
Sandeep.
Re: Database files large in size
Posted: Tue Nov 21, 2017 4:03 pm
by lmiltchev
How long do you keep the data in the DB? Go to
Admin -> Performance Settings -> Database tab, and show us a screenshot of this page.
Do you see any errors in the dbmaint.log?
Code: Select all
tail /usr/local/nagiosxi/var/dbmaint.log
Is crond running?
Code: Select all
service crond restart
service crond status
ps -ef | grep cron
Any errors in the cron log?
Is nagios user account expired?
Re: Database files large in size
Posted: Tue Nov 21, 2017 4:16 pm
by npolovenko
Hello,
@sandeepatil. Yes, 85gb is a lot. There are two things you need to do to reduce the size of these files:
1)run this command to truncate MYSQL database:
Code: Select all
echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -u root -pnagiosxi nagiosxi
2)Only after step 1 is done, go to
and run:
(this might take a while)
3) Check the size of the files in /var/lib/mysql/nagiosxi/ one more time.
As far as the reason to why this happened. Could be a few things. If your Nagios server was shut down incorrectly at some point in the past, one of the database tables might've gotten corrupted. This often causes the database to blow with time.
If Nagios XI was upgraded recently I'd suspect that MySQL schema was not updated causing database issues. So if you did upgrade Nagios recently let me know, I'll walk you through replacing the schema.
Cron Jobs that perform the DB maintenance may not be working. Please run the following command and post the output:
Also, did you move the database from Postgres to Mysql recently by chance?
Re: Database files large in size
Posted: Wed Nov 22, 2017 10:58 am
by sandeepatil
We had not moved the database from Postgres to Mysql, we installed Nagios XI 5.4.2.
Code: Select all
[abc@abc123 ~]$ ps -ef --cols=300 |grep cron
nagios 1225 1218 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/perfdataproc.php > /usr/local/nagiosxi/var/perfdataproc.log 2>&1
nagios 1226 1225 0 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/perfdataproc.php
nagios 1229 1223 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/sysstat.php > /usr/local/nagiosxi/var/sysstat.log 2>&1
nagios 1230 1222 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/cmdsubsys.php > /usr/local/nagiosxi/var/cmdsubsys.log 2>&1
nagios 1231 1229 1 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/sysstat.php
nagios 1233 1219 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/feedproc.php > /usr/local/nagiosxi/var/feedproc.log 2>&1
nagios 1235 1230 0 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/cmdsubsys.php
nagios 1236 1221 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/eventman.php > /usr/local/nagiosxi/var/eventman.log 2>&1
nagios 1237 1233 0 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/feedproc.php
nagios 1239 1220 0 10:52 ? 00:00:00 /bin/sh -c /usr/bin/php -q /usr/local/nagiosxi/cron/event_handler.php > /usr/local/nagiosxi/var/event_handler.log 2>&1
nagios 1240 1239 0 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/event_handler.php
nagios 1241 1236 0 10:52 ? 00:00:00 /usr/bin/php -q /usr/local/nagiosxi/cron/eventman.php
We had removed dbmaint.lock and mysql service restart.
Now DB is stable.
Share monitoring and preventive steps to avoid this type of issue.
Re: Database files large in size
Posted: Wed Nov 22, 2017 11:43 am
by npolovenko
@sandeepatil, Ok, based on your output the cron jobs seem to be running as well. I would say the most important preventive step is not to do a hard reset on the Nagios Server. If you have to restart the server make sure to run
service nagios stop,
service httpd stop first, and only then perform a soft reboot on your vm/linux machine. You can also upgrade the XI to the latest version (5.4.11 as of right now and 5.5.0 is coming soon) with many fixes and improvements.
Other than that, if you performed step 1 and 2 from my previous post, I'd just keep an eye on the database size. It could be a one-time thing but if it grows to enormous size within a week or so, we'd have to do more troubleshooting at that point.
Re: Database files large in size
Posted: Wed Nov 22, 2017 2:07 pm
by sandeepatil
xi_meta.MYD increased due to below cron not running right ?
Code: Select all
/usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php > /usr/local/nagiosxi/var/dbmaint.log 2>&1
Can we create script, whenever found file in large size,
run below steps.
1) Remove dbnaint.lock
2) echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -u root -pnagiosxi nagiosxi
3) ./repair_databases.sh
4) run cron "/usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php > /usr/local/nagiosxi/var/dbmaint.log"
For avoiding issue.
Re: Database files large in size
Posted: Wed Nov 22, 2017 4:11 pm
by dwasswa
Hi
@sandeepatil,
In regards to:
Can we create script, whenever found file in large size,
run below steps.
1) Remove dbnaint.lock
2) echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -u root -pnagiosxi nagiosxi
3) ./repair_databases.sh
4) run cron "/usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php > /usr/local/nagiosxi/var/dbmaint.log"
Yes, you can create a
Event Handler for your use case. Here is a kb article on how you can implement that...
Introduction-To-Event-Handlers-in-Nagios-XI.
However, in your script, you will have to add a command to check the table size and then based on your
Warning or
Critical threshold, truncate the table.
There is no plugin in Nagios that can check the size of a specific table in the database, but here is a plugin at this link
check_tablespace_mysql that you can modify to suit your needs.
Please let me know if you have any questions.
Re: Database files large in size
Posted: Tue Nov 28, 2017 2:42 pm
by npolovenko
@sandeepatil xi_meta.MYD increased due to below cron not running right ?
/usr/bin/php -q /usr/local/nagiosxi/cron/dbmaint.php > /usr/local/nagiosxi/var/dbmaint.log 2>&1
Not necessarily. We actually don't recommend automating anything at this point. Truncating DB is not always the right solution and it wouldn't solve the cause of the problem. And the DBmaint cron is running every 5 minutes, not just after the DB maintenance. It pretty much deals with cleaning up the database on the go. The whole xi_meta.MYD is supposed to only have entries that are used by Nagios at the present moment and nothing else.
So let's keep an eye on the size of xi_meta.MYD and if it grows really large again we're gonna have to do more troubleshooting.