Shane Duffy

This week I was working on a new project and for the project I needed to keep an eye on the lenght of a queue which was stored in a MySQL table. I already have the very useful MRTG tool installed on the server so I decided to make use of it to create a simple a quick graphs.

For anyone who doesn’t already know what MRTG is its a very handy tool for graphing data. It is normally used to graph things such are network interface traffic stats and works with an RRD data store in the background storing 5 minute averages. MRTG makes it very easy for users to monitor custom data aswell and has a simple input format. All that is required is to write a script which will output 4 lines of data.

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.

In order to get started graphing that data that I wanted I created this small script below

#!/bin/sh
echo 0
mysql -h localhost -u USER --password=PASSWORD -e "$1" DATABASE | tail -1
echo 0
echo "Queued Items"

When this script is called it takes 1 argument which is in the form of the count query you want to preform. eg “Select count(*) from table_X”

Then in our MRTG cfg file we need to add an entry to call and display this data.

Target[Blackarrow_queue]: `/graphs/collector_links "Select count(*) from table_X"`
MaxBytes[Blackarrow_queue]: 10000
Options[Blackarrow_queue]: nopercent,growright,nobanner,nolegend,noinfo,gauge,integer,transparent,noi
Title[Blackarrow_queue]: Queue Lenght
PageTop[Blackarrow_queue]:<h3>Queue</h3>
YLegend[Blackarrow_queue]: records
ShortLegend[Blackarrow_queue]:
LegendI[Blackarrow_queue]:
LegendO[Blackarrow_queue]:records

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: