Squeezing the juice out of check_mysql

Posted on 01/04/

The check_mysql plugin from the Nagios Plugins project is useful, but at CAPSiDE we're quite obsessed with having performance data, and registering it, to later graph it. That way we have a better vision of the systems we're monitoring. Opsview will automatically detect performance data from the plugins and graph it. But in the case of check_mysql we're out of luck. It outputs useful data, but it's not performance data.

Luckily the rrdgraph tool that Opsview uses let's you do some tricks for checks that don't output performance data (like check_mysql). It's map file let's you specify a set of regular expressions to turn the output of plugins into graphable data (Opsview provides a standard set of mappings with it's base installation)

The output of check_mysql looks like

Uptime: 801963 Threads: 5 Questions: 55210201 Slow queries: 246 Opens: 25611 Flush tables: 1 Open tables: 55 Queries per second avg: 68.843

If you paste the line below into /usr/local/nagios/etc/map.local on your Opsview Master server.

/output:Uptime: \d+  Threads: (\d+)  Questions: (\d+)  Slow queries: (\d+)  Opens: (\d+)  Flush tables: (\d+)  Open tables: (\d+)  Queries per second avg: ([-.0-9]+)(?: Slave IO: (\w+) Slave SQL: (\w+) Seconds Behind Master: (\d+)|)/
and push @s, [ "mysql",
             [ "threads", GAUGE, $1 ],
             [ "questions", DERIVE, $2 ],
             [ "slow", DERIVE, $3 ],
             [ "opens", DERIVE, $4 ],
             [ "flush_tables", DERIVE, $5],
             [ "open_tables", GAUGE, $6],
             [ "avg_qps", GAUGE, $7],
             defined $8?[ 'slave_io', GAUGE, (lc($8) eq 'yes'?1:0) ]:(),
             defined $9?[ 'slave_running', GAUGE, (lc($9) eq 'yes'?1:0) ]:(),
             defined $10?[ 'sec_behind', GAUGE, $10 ]:()
             ];
Opsview will be able to generate graphs for all your configured check_mysql checks (note that passed a couple of checks, you will have to reload Opsview to see the icon that links to the graphs).

This map file takes into account if you execute check_mysql with the -S option to monitor MySQL slave status, and creates the slave_io, slave_running and sec_behind data channels.

You can get pretty graphs like this:

Try it out! and tell me if it works for you, and please correct and criticize

DISCLAIMER: Editing the map.local file can leave the rrdgraph recollection broken. Please pass

perl -c /usr/local/nagios/etc/map.local
to assure that it says: /usr/local/nagios/etc/map.local syntax OK and then pay special attention to see if the other RRDs are working correctly. Please read Opsview docs for more information