MySQL Performance on Nagios Server

[Fred Kroeger]

I've setup the standard MySQL Monitors on the NagiosXI server using the MySQL Config Wizard and using the default thresholds.
I was surprised to see a lot of "red" ! Also surprised at the number of client connections.

Is there something that needs to be tweaked n the Nagios Database so that it performs better ?

MySQL Monitors.PNG

I'm running NagiosXI 5.3.4 with 24GB RAM - this is the my.cnf file

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
#Added for Performance Tuning
tmp_table_size=524288000
max_heap_table_size=524288000
table_cache=768
wait_timeout=7800
query_cache_size = 12582912
query_cache_limit=80000
thread_cache_size = 4
join_buffer_size = 128K

#
#Added to allow Remote SQL Connections - FK 11/01/2017
port=3306
bind-address=x.x.x.x
#Skip DNS resolution
skip_name_resolve

#Added to allow more connections to MySQL- FK 11/01/2017
max_connections=200



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

[mcapra]

All of those metrics are relative to the server being monitored. A high cache hit rate might be a good thing for some databases, and a bad thing for other databases. Our "MySQL Wizard" throws in some generic values for all the thresholds and may not be a good fit for all databases out-of-the-box.

If you're interested in tuning your MySQL database, this script can make some solid recommendations. Though it's not "officially" recommended for use against the XI databases, we often recommend it for those seeking to monitor other MySQL servers:
https://github.com/major/MySQLTuner-perl

Here's our official DB optimization docs:
https://assets.nagios.com/downloads/nag ... zation.pdf

[Fred Kroeger]

Thanks - I guess what I'm looking for then is what are the appropriate thresholds to set for the Nagios d/base to ensure it is performing well?

[tmcdonald]

That's hard to say, and largely depends on the size of your infrastructure, how often checks are being performed, how many users you have logged in at once, etc. My advice is always to get a baseline for a week or so of average usage, then adjust accordingly. If you want to post some numbers here we can look them over.

[Fred Kroeger]

Thanks - what metrics should I be capturing that would be useful to assist in performance improvement.

[ssax]

The defaults that are in the wizard should give you a general idea of your database performance, like tmcdonald bheden stated you'll need to let them run for a bit to get your thresholds defined, they will be entirely unique to your environments/system. Once you get the thresholds set when you have DB performance issues it should hit the thresholds you have defined and it should indicate an issue.

Tweaking mysql performance is technically outside the scope of our support and should generally be handled by your DBA but using mysqltuner (and others) gives you a good idea of what could be adjusted to give you better performance, officially we don't recommend that you change anything from the defaults unless required though.

Are you seeing MySQL performance issues on your server now?

Thank you

[Fred Kroeger]

Yes - performance of the Nagios server at times is poor.
At the start of this thread is a snapshot of the MySQL wizard monitors - using the default thresholds and there is a lot of red - hence my questions.
The VM is specced appropriately I believe - 4 x CPU + 24GB RAM. Plus I'm using a RAMDisk to improve performance.

I have also noticed that my events queue monitor drops right down during these times and that the MySQL process CPU utilisation is sustained at around 100% for a few minutes.
This is a snapshot from this morning - there were no MySQL backup processes running at that time.

Code: Select all

Tasks: 255 total,   2 running, 253 sleeping,   0 stopped,   0 zombie
Cpu(s): 49.5%us,  7.9%sy,  0.0%ni, 12.9%id, 28.5%wa,  0.2%hi,  0.9%si,  0.0%st
Mem:  24600024k total, 15719396k used,  8880628k free,   370404k buffers
Swap:  2359288k total,    62524k used,  2296764k free, 13469828k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2337 mysql     20   0 2230m 103m 3868 S 106.9  0.4   1823:17 mysqld
 9024 apache    20   0  442m  26m 5220 S 18.3  0.1   0:08.10 httpd
16695 apache    20   0  442m  25m 4828 R 10.3  0.1   0:01.21 httpd
  979 apache    20   0  449m  32m 5296 S  6.0  0.1   0:09.40 httpd
16485 apache    20   0  442m  26m 5020 S  5.6  0.1   0:01.10 httpd
12527 apache    20   0  449m  32m 5148 S  5.0  0.1   0:02.55 httpd

This is the event queue when things are normal

Event Queue - Normal.PNG

When it starts slowing down, the event queue has about 0-10 events for the whole 5min window instead of the 250+

[tgriep]

When the system is slow, do you see any errors in the /var/log/mysqld.log or the /var/log/messages log file?
How many hosts and services is the server monitoring?
Can you run the following commands as root and post the output?

Code: Select all

ipcs -q
ps -ef --cols=300

[Fred Kroeger]

Managed to catch it today when it started slowing down.
It appears that while the mysql process is running at 100%, the Active checks stop. They eventually start getting scheduled again when it returns to normal.

The attached file shows the Scheduled events graph as the problem started and then returned to normal plus the output of the commands you requested.
No extra messages found in the log files to explain what is happening,

Code: Select all

Active Host Checks - 5-min	1,030	
Passive Host Checks - 5-min	401	
Active Service Checks - 5-min	8,305	
Passive Service Checks - 5-min	4,588	

[tgriep]

It may be that the kernel message queue may be filling up causing the issue.
Try the fixes in this KB article to see if this helps.
https://support.nagios.com/kb/article.php?id=139