mariadb error

[mbellerue]

That is quite a bit of growth. Can you PM me your system profile? You can get the system profile from Admin -> System Profile -> Download Profile. Also, about how many hosts and services are monitored by this instance of Nagios?

Let's also get the sizes of each of the tables in each of the databases.

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -uroot -pnagiosxi --table

[mbellerue]

Normally the profile includes more log files and such. That may be due to the age of your Nagios software. In any case, I couldn't get any information from the profile.

Let's see if the table is filling up with useful information, or if something has just spun out of control. Run this command, and PM the results to me, please.

Code: Select all

echo "select * from nagios_logentries order by logentry_id desc limit 100;" | mysql -uroot -pnagiosxi nagios > /tmp/output.txt

[exp-user1]

Hi mbellerue,

I sent you PM, would you please check.

[mbellerue]

Thank you for that. Were you able to expand the size of /tmp, like Scott had suggested?

[exp-user1]

This is the third time I expand /tmp.

The problem is that nagios_logentries table keeps increasing abnormally!

now it's :
nagios_logentries | 4899.71

have you noticed any thing in output file that I sent in PM?

[mbellerue]

I'm seeing a lot of services timed out messages. Let's do this, try these commands and let me know what the output is.

Code: Select all

systemctl stop nagios.service
ps aux | grep nagios

Once Nagios is shut down, we can clear out the nagios_logentries table to make space, just know that this does delete historical data.

Code: Select all

echo 'DELETE FROM nagios_logentries WHERE entry_time < (NOW() - INTERVAL 30 DAY);' |mysql -t -u root -pnagiosxi nagios

Check in on the size of nagios_logentries, give it a few minutes to make sure that with Nagios not running, it's not growing in size. Also check your system load with top, and make sure that the system load is not particularly high (not above 4 or 5). If everything on the system seems calm, go ahead and start Nagios again, and watch the nagios_logentries table to see if the database growth issue persists.

[exp-user1]

Hi,

nagios_logentries table is 3279.84 MB now. but it's still growing in slow pace(much better that before).
I deleted all down hosts to minimize logs. what else affects this table, should I do cleaning periodically, what is the safest way to keep the size normal?

thanks.

[mbellerue]

Here's a doc on database optimization settings in XI.
https://assets.nagios.com/downloads/nag ... zation.pdf

I'm wondering if your server was getting into a downward spiral, where it would take long enough to write or retrieve information to/from the database that service checks started failing. Then enough service checks started failing often enough to back log the database, which caused more service checks to time out, which caused more logs, yadda yadda yadda.

That may be the case. The log entries table is only supposed to keep 90 days worth of entries, and the command destroys all but the last 30 days, and your table is still 3.5GB in size. Which means that most of the original 4.something GB of data has been generated in the last 30 days. You can prune that data even further if it's not important to you.

Also definitely if you retire hosts or services, they should be removed from active monitoring. That way your system doesn't expend any resources trying to monitor systems that have gone away permanently.

[exp-user1]

Thank you I will keep monitoring it.

[ssax]

@mbellerue is likely correct about them spooling up.

If it occurs again, please PM one of us a fresh copy of your profile before remediation (if possible), you can download it from Admin > System Profile > Download Profile.