Gale Force

At work last week I set up some MRTG reports to monitor our MySQL servers. I used the handy mrtg-mysql-load utility to get queries/slow queries information but I also needed to monitor the number of records in a particular table and graph it using MRTG. For those unfamiliar with MRTG, graphing “custom” data is very simple. All you need to do is write a script that outputs 4 lines:

Line 1 -current state of the first variable, normally ‘incoming bytes count’
Line 2 -current state of the second variable, normally ‘outgoing bytes count’
Line 3 -string (in any human readable format), telling the uptime of the target.
Line 4 -string, telling the name of the target.

So I created a simple script to get the record count from a particular table:

#!/bin/sh
echo 0
mysql -h $1 -uwww -e "SELECT COUNT(*) from injector" nextstat_injector | tail -1
echo 0
echo "Queued Records"

Which I would call passing the MySQL database host as the first argument. Once I had the script I added a new MRTG target like this:

Target[injector]: `/etc/mrtg/injector-stat 192.168.1.121` + `/etc/mrtg/injector-stat 192.168.1.122` + `/etc/mrtg/injector-stat 192.168.1.123`
MaxBytes[injector]: 500000
Options[injector]: nopercent,growright,nobanner,nolegend,noinfo,gauge,integer,transparent,noi
Title[injector]: Injector Records Queued
PageTop[injector]:<h3>Injector Records</h3>
YLegend[injector]: records
ShortLegend[injector]:
LegendI[injector]:
LegendO[injector]: records

You’ll notice that I’m adding the totals from 3 different servers to graph a single sum value in MRTG. Any mathematical expression can be used to generate your graph values. From the manual:

You can also combine several target definitions in a mathematical expression. Any syntactically correct expression that the Perl interpreter can evaluate to will work. An expression could be used, for example, to aggregate both B channels in an ISDN connection or to calculate the percentage hard disk utilization of a server from the absolute used space and total capacity.

For a graph like this you’ll want to use similar options to the ones I used above. nopercent disables percentage display, growright tells the graph to read from left to right instead of the default right to left, gauge tell MRTG that the data points are “current status” measurements rather than ever-increasing counters, noi tells MRTG there is no “input” data to compare against “output” data and a few cosmetic display options. The resulting graph looks something like this: