Database files large in size

[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.

[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?

Code: Select all

tail -50 /var/log/cron

Is nagios user account expired?

Code: Select all

chage nagios -l

[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

Code: Select all

/usr/local/nagiosxi/scripts/

and run:

Code: Select all

./repair_databases.sh

(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:

Code: Select all

#  ps -ef --cols=300 |grep cron

Also, did you move the database from Postgres to Mysql recently by chance?

[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.

[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.

[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.

[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.

[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.