When using a database that are business critical it can be a good idea to monitor the internals. This article describe how-to monitor MySql with Nagios or op5 Monitor. In these case it runs a MythTV system but it could be almost anything.
Pre requirements
The pre requriements for this article is a working Nagios or op5 Installation and a working MySql database.
Plugin
The plugin used in this article is: http://www.consol.de/opensource/nagios/check-mysql-health
Unfortunatly the webpage is in German only.
Enable MySql to retrive information
mysql> grant usage on *.* to 'nagios'@'op5' identified by 'XXXX-';
op5 is the nagios hostname.
Download the plugin, install and test
Download the plugin from http://www.consol.de/fileadmin/opensource/Nagios/check_mysql_health-2.0.4.tar.gz
I put it into /opt/plugins/custom/check_mysql_health at the Nagios server.
Test the command by running at the Nagios server:
[root@op5 custom]# ./check_mysql_health -H mysqlhost --user nagios --password XXXX --mode threads-connected OK - 5 client connection threads | threads_connected=5;10;20
It works!!
To get a list of what the check_mysql_health plugin can monitor run:
[root@op5 custom]# ./check_mysql_health --help
Configure Nagios or op5 Monitor
I find the following parameters interesting to monitor:
- connection-time
- keycache-hitrate
- qcache-hitrate
- tablecache-hitrate
- threadcache-hitrate
- tmp-disk-tables
checkcommands.cfg
# command 'check_mysql_health'
define command{
command_name check_mysql_health
command_line $USER1$/custom/check_mysql_health -H $HOSTADDRESS$ --user $ARG1$ -password $ARG2$ --mode $ARG3$
}
# command 'check_mysql_health_tresholds'
define command{
command_name check_mysql_health_tresholds
command_line $USER1$/custom/check_mysql_health -H $HOSTADDRESS$ --user $ARG1$ -password $ARG2$ --mode $ARG3$ --warning $ARG4$ --critical $ARG5$
}
I created a servicegroup named mysql
servicegroups.cfg
# servicegroup 'mysql'
define servicegroup{
servicegroup_name mysql
alias MySQL
}
I use the default thresholds in my servicedefinitions, but use check_mysql_health_thresholds instead if thresholds are required. services.cfg
# service 'MySQL connection-time'
define service{
use default-service
host_name lala
service_description MySQL connection-time
check_command check_mysql_health!nagios!XXXX!connection-time
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
# service 'MySQL keycache-hitrate'
define service{
use default-service
host_name lala
service_description MySQL keycache-hitrate
check_command check_mysql_health!XXXX!keycache-hitrate
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
# service 'MySQL qcache-hitrate'
define service{
use default-service
host_name lala
service_description MySQL qcache-hitrate
check_command check_mysql_health!nagios!XXXX!qcache-hitrate
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
# service 'MySQL tablecache-hitrate'
define service{
use default-service
host_name lala
service_description MySQL tablecache-hitrate
check_command check_mysql_health!nagios!XXXX!tablecache-hitrate
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
# service 'MySQL threadcache-hitrate'
define service{
use default-service
host_name lala
service_description MySQL threadcache-hitrate
check_command check_mysql_health!nagios!XXXX!threadcache-hitrate
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
# service 'MySQL tmp-disk-tables'
define service{
use default-service
host_name lala
service_description MySQL tmp-disk-tables
check_command check_mysql_health!nagios!XXXX!tmp-disk-tables
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
The result
Conclusions and wrap-up
The default values are in many cases not sufficent, I have no database that do not get WARNING or CRITICAL when the default thresholds are used. If that is because the thresholds are not realistic or if it my applications that are badly implemented I do not know. So the databases needs tuning and the thresholds must be changed to get rid of constant errormessages.
Links
- op5 Monitor
- Nagios
- Tuning MySQL
- The plugin used in this article check_mysql_health
April 18th, 2009 at 9:20 am
Hi,
you forgot $ARG1$ in service “MySQL keycache-hitrate”.
I know, i know, i should write an english translation, but had been either too lazy or either too busy. Mostly the latter 🙁 I hope, the command line parameters and the examples are self-explaining enough. We have a website-redesign in summer and you will get an english version, promised.
Gerhard
April 18th, 2009 at 9:09 pm
You are right!
So it should look like this instead:
# service ‘MySQL keycache-hitrate’
define service{
use default-service
host_name lala
service_description MySQL keycache-hitrate
check_command check_mysql_health!nagios!XXXX!keycache-hitrate
servicegroups mysql
contact_groups it-slav_msn,it-slav_mail,call_it-slav
}
April 11th, 2011 at 10:43 am
Peter, your last comment to this blog post appears to say that your original post contains an error related to service MySQL keycache-hitrate. I suggest you update the blog post to fix this issue, because not everyone will be reading these comments below the blog post.
Also, I have a question. I’m using Nagios and it is unclear to me where exactly the files checkcommands.cfg, servicegroups.cfg and services.cfg should be added. Should they be in /usr/local/nagios/etc/ or /usr/local/nagios/etc/objects/? And if so, will Nagios autodetect them or will I need to call the .cfg files in another script somewhere?
April 11th, 2011 at 10:54 am
Okay, I found another blog post with information that worked for me:
http://kedar.nitty-witty.com/blog/10-steps-mysql-monitoring-nagios-installation-configuration
I could add what you add to services.cfg to mysqlmonitoring.cfg and that worked.
May 16th, 2011 at 11:28 am
this is great article, work fine for me!!!
btw any tool to automatic optimize table?
thanks
May 16th, 2011 at 11:36 am
I run the following script to optimize my tables:
#!/bin/sh
#Script that optimize all databases in mysql
for I in `mysql -s -B -e ‘show databases;’`
do
echo Will clean:$I
mysqlcheck -o $I
done
November 7th, 2016 at 1:38 pm
How to enable the warning and critical..can any body send the syntax for above all.
November 7th, 2016 at 1:46 pm
The syntax is there. Could you elaborate?
Use check_mysql_health_thresholds