Page 1 of 2
MySQL Performance on Nagios Server
Posted: Mon Apr 24, 2017 3:50 am
by 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
Re: MySQL Performance on Nagios Server
Posted: Mon Apr 24, 2017 10:39 am
by 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
Re: MySQL Performance on Nagios Server
Posted: Thu Apr 27, 2017 7:52 pm
by 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?
Re: MySQL Performance on Nagios Server
Posted: Fri Apr 28, 2017 10:15 am
by 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.
Re: MySQL Performance on Nagios Server
Posted: Sun Apr 30, 2017 7:32 pm
by Fred Kroeger
Thanks - what metrics should I be capturing that would be useful to assist in performance improvement.
Re: MySQL Performance on Nagios Server
Posted: Mon May 01, 2017 1:05 pm
by 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
Re: MySQL Performance on Nagios Server
Posted: Tue May 02, 2017 3:48 am
by 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+
Re: MySQL Performance on Nagios Server
Posted: Tue May 02, 2017 11:23 am
by 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?
Re: MySQL Performance on Nagios Server
Posted: Tue May 02, 2017 10:26 pm
by 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
Re: MySQL Performance on Nagios Server
Posted: Wed May 03, 2017 9:20 am
by 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