Welcome to It-Slav.Net blog
Peter Andersson
peter@it-slav.net

I've already got a female to worry about. Her name is the Enterprise.
-- Kirk, "The Corbomite Maneuver", stardate 1514.0

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

mysql


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



8 Responses to “MySql Performance Monitoring with Nagios or op5 Monitor”

  1. Gerhard Lausser Says:

    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

  2. peter Says:

    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
    }

  3. Norbert Says:

    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?

  4. Norbert Says:

    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.

  5. mashary Says:

    this is great article, work fine for me!!!

    btw any tool to automatic optimize table?

    thanks

  6. peter Says:

    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

  7. Balaraju Says:

    How to enable the warning and critical..can any body send the syntax for above all.

  8. peter Says:

    The syntax is there. Could you elaborate?
    Use check_mysql_health_thresholds

Leave a Reply





Book reviews
FreePBX 2.5
Powerful Telephony Solutions






Asterisk 1.6
Build a feature rich telephony system with Asterisk






Learning NAGIOS 3.0





Cacti 0.8 Network Monitoring,
Monitor your network with ease!