MySql Performance Monitoring with Nagios or op5 Monitor

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.


Souce: http://www.it-slav.net/blogs/2009/04/06/mysql-performance-monitoring-with-nagios-or-op5-monitor/