check_mysql_perf is a plugin for Nagios which allows you to monitor various performance-related characteristics of a MySQL database.

You tell the plugin which values to retrieve and to check by providing keywords to the -m command line option.

nagios:~> check_mysql_perf -m qcache-hitrate 
OK - Query Cache Hitrate at 92.89%|qcache_hitrate=92.89%;90:;80:

nagios:~> check_mysql_perf -m qcache-hitrate -w 95: -c 90:
WARNING - Query Cache Hitrate at 92.89%|qcache_hitrate=92.89%;95:;90:

nagios:~> check_mysql_perf -m connection-time             
OK - Connection Time 0.008 seconds|connection_time=0.01;2;10

nagios:~> check_mysql_perf -m connection-time -w 0.001
WARNING - Connection Time 0.005 seconds|connection_time=0.01;0.001;10

nagios:~> check_mysql_perf -m threads-connected
OK - Threads_connected: 3|threads_connected=3;10;20

nagios:~> check_mysql_perf -m slave-io-running
CRITICAL - Slave io not running|slave_io_running=0

nagios:~> check_mysql_perf -m tmp-disk-tables
CRITICAL - 73.00% of 8985 temp tables were created on disk →
... →|pct_tmp_table_on_disk=73.00%;25;50

Measurements which depend on a time interval can be performed in different ways. To calculate the final result you need an initial value, a final value and the time elapsed between the two measurements. Without providing any further options the initial value is the measured final value of the last run. The time elapsed since the last run is aproximately the normal_check_interval.

With the option -i <interval> you tell the plugin to measure, sleep for <interval> seconds and measure again. In this case nothing will be saved for the next run.

If the result of the check should depend on the absolute difference of the two measurements instead of the rise per second, you tell it with the -a option. This makes sense for example with slow-queries, which may so rarely appear that the rate is nearly 0. Although you want an alarm if the counter increments.
Select the thresholds carefully so that they can be reached also within a retry_check_interval. Otherwise each SOFT;1 state could be followed by an OK-state immediately.

nagios:~> check_mysql_perf -m qcache-lowmem-prunes 
OK - 88 Qcache low memory prunes in 5101 seconds (0.0173/sec)|→...
    → lowmem_prunes_rate=0.0173;1;10 lowmem_prunes=88;1;10

nagios:~> check_mysql_perf -m qcache-lowmem-prunes 
WARNING - 274 Qcache low memory prunes in 61 seconds (4.5016/sec)|→...
    → lowmem_prunes_rate=4.5016;1;10 lowmem_prunes=274;1;10

nagios:~> check_mysql_perf -m qcache-lowmem-prunes -a -w :100 -c :500
WARNING - 315 Qcache low memory prunes in 60 seconds (5.2500/sec)|→...
    → lowmem_prunes_rate=5.2500;:100;:500 lowmem_prunes=315;:100;:500

nagios:~> check_mysql_perf -m qcache-lowmem-prunes -a -i 15
CRITICAL - 32 Qcache low memory prunes in 15 seconds (2.1333/sec)|→...
    → lowmem_prunes_rate=2.1333;1;10 lowmem_prunes=32;1;10

• After unpacking the archive please call ./configure. With ./configure--help you can you can show the options if you want to modify the default settings.
• --prefix=BASEDIRECTORY Specify here the directory where you want to install check_mysql_perf. (default: /usr/local/nagios)
• --with-nagios-user=SOMEUSER The user which will own the check_mysql_perf script. (default: nagios)
• --with-nagios-group=SOMEGROUP The group. (default: nagios)
• --with-mysql=DIR The path to your MySQL Installation. DIR/bin/mysql_config is expected.
• --with-officialplugins=DIR The directory where the "official" Nagios-Plugins were unpacked and configured. This directory must exist, because check_mysql_perf uses libraries provided by the nagios-plugins distribution.

$ tar zxvf nagios-plugins-1.4.11.tar.gz
$ cd nagios-plugins-1.4.11; ./configure; make
$ cd ..
$ tar zxvf check_mysql-perf-1.1.tar.gz
$ cd check_mysql-perf-1.1
$ ./configure \
  --with-officialplugins=/absolute/path/to/nagios-plugins-1.4.11
$ make

You can pass thresholds to the -c and -w parameters. Without these parameters, the plugin uses default values:

Please keep in mind, that the critical and warning thresholds are ranges conforming to the Nagios Plugin Developer Guidelines.

Hence
"10" means "Alert, if > 10" and
"90:" means "Alert, if < 90"

Here you can lookup how the values were calculated.

• The official Nagios-Plugins
• Nagios Homepage
• Nagios Plugins Exchange
• German Nagios Portal
• German Nagios Wiki
• MySQL Performance Blog

• 2008-05-06 1.3.1 bugfix for master-slave-config of MySQL < 4.1.1
• 2008-01-17 1.3 slow-queries, qcache-lowmem-prunes, bufferpool-wait-free, log-waits, -i, -a
• 2008-01-08 1.2 threadcache-hitrate,bufferpool-hitrate,tablecache-hitrate,table-lock-contention
• 2008-01-03 1.1 ./configure bugfix. New mode tmp-disk-tables
• 2008-01-01 1.0 first public release.

2008 Gerhard Lausser

Check_mysql_perf is released under the GNU General Public License. GPL

Gerhard Lausser (gerhard.lausser@consol.de) will gladly answer your questions.