mysql filling /var

[ks6764]

I noticed some others on the forum have had this issue, but I didn't spot anything that would help in my particular instance.
I am looking for some tuning assistance for MySQL which is filling up /var on our system.
Below are the specifics I have.
Nagios XI 5.4.4 running on Red Hat Enterprise 6.8

/var was at 100% and I managed to remove some zipped logs, etc. to get it down to 99% which was just enough to allow the db repair script to run and optimize which brought /var down to 60%.

The largest directory based on size under /var is /var/lib/mysql and within that directory I see the following (note the 9.9G for the nagios dir)

Code: Select all

# du -sh *
10M     ibdata1
5.0M    ib_logfile0
5.0M    ib_logfile1
988K    mysql
9.9G    nagios
8.6M    nagiosql
43M     nagiosxi
4.0K    test

Within /var/lib/mysql/nagios there are 187 files. The largest files are:

Code: Select all

2.5G    nagios_logentries.MYD
945M    nagios_logentries.MYI
484M    nagios_statehistory.MYD
190M    nagios_statehistory.MYI

The /etc/my.cnf is as follows:

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Inserting new line below to resolve /tmp filling up - Kerry Dec. 2017
tmpdir=/var/tmp

Lastly, here is the FS layout:

Code: Select all

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             9.8G  713M  8.6G   8% /
tmpfs                  32G     0   32G   0% /dev/shm
/dev/sda1             240M   96M  132M  43% /boot
/dev/sda8              15G   65M   14G   1% /home
/dev/sda7             4.8G   12M  4.6G   1% /tmp
/dev/sda6             9.8G  1.9G  7.4G  21% /usr
/dev/sda5              16G  8.9G  6.0G  60% /var
/dev/mapper/VG01-Vmysql
                       40G   48M   38G   1% /opt/app/mysql
/dev/mapper/VG02-Vworkload
                       79G  7.8G   67G  11% /opt/app/workload
/dev/mapper/VG03-Vlogs
                       40G   48M   38G   1% /opt/logs
/dev/mapper/VG04-Vdata
                       40G   48M   38G   1% /opt/data
/dev/mapper/VG05-Vtools
                       40G   48M   38G   1% /opt/tools

Please advise if there is something I should add to the /etc/my.cnf or a Nagios setting somewhere to keep the size of the db reasonable.
Thanks much.

[cdienger]

You can control how long the log entries and state history stay in the database by editing the options under Admin > System Config > Performance Settings > Databases > NDOUtils Database.

[ks6764]

Yes, we had lowered those and I lowered them again. Below is what I currently have.

Max Commands Age: 480
Max Events Age: 480
Optimize Interval: 60
Max External Commands Age: 7
Max Log Entries Age: 60
Max Notifications Age: 60
Max State History Age: 60
......
Optimize Interval: 40

NagiosQL Database:
Max Logbook Age: 480
Optimize Interval: 60

[eloyd]

I'd say something is wrong somewhere. We have machines with 8GB total disk space (just a / partition) that have been collecting Nagios data for years and no issues with space filling up. In fact, most of them are less than 50% used. Are you obsessing over checks? Are you doing passive check freshness? Are you doing something that would cause any extra work to be done by Nagios (and thus, logged)?

[dwhitfield]

There are a lot of things that could be causing this. It might be easiest to resolve if we take a look at your profile.

Can you PM me your Profile? You can download it by going to Admin > System Config > System Profile and click the ***Download Profile*** button towards the top. If for whatever reason you *cannot* download the profile, please put the output of View System Info (5.3.4+, Show Profile if older) in the thread (that will at least get us some info). This will give us access to many of the logs we would otherwise ask for individually. If security is a concern, you can unzip the profile take out what you like, and then zip it up again. We may end up needing something you remove, but we can ask for that specifically.

You can also generate a profile manually using the script at /usr/local/nagiosxi/html/includes/components/profile/getprofile.sh

That should generate a profile in /usr/local/nagiosxi/var/components/ which you can get off the server with an application such as FileZilla.

After you PM the profile, please update this thread. Updating this thread is the only way for it to show back up on our dashboard.

If you get an error that PROFILE BUILD FAILED, please see https://support.nagios.com/kb/article.p ... ategory=44

[ks6764]

Thanks, I PM'd you the profile.zip.

[dwhitfield]

You may have two nagios setups on this system. Certainly you have one file pointing to /opt/app/nagios/var/spool/xidpe/ and another pointing to /usr/local/nagios/var/spool/xidpe/ . This could be symlinks, but it's a bit odd that some things are picking up the symlink and others are not.

At this point, I would suggest submitting a ticket at https://support.nagios.com/tickets/ and referencing this thread.

Alternatively, can you tell us a bit more about this setup?

[ks6764]

We have an in house utility that uses yum in part to install and setup Nagios Xi on our servers.
This is being done because our company forbids us from using /usr/local and instead wants us to use /opt/app as a location instead so there is linking setup.

[dwhitfield]

You've got something going on with your perfdata. I think it's probably an issue with symlinks, but considering you also have a TON of npcd errors, I would highly suggest you set up a ramdisk: https://assets.nagios.com/downloads/nag ... giosXI.pdf

Alternatively, in your npcd.cfg, you can change the load threshold to 60 (it's 10 now) and then send a new profile. I have a feeling that will clear up some of the errors, but not all of them, whereas a properly set up ramdisk should resolve both the load issues and the other perfdata issues. It remains to be seen if the perfdata issues are causing your db issues, but the checks not flowing properly could certainly cause db issues.