Shane Duffy

Using MRTG to monitor records in a MySQL table

Posted on: April 21, 2008

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

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
YLegend[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:

MRTG Custom Stats


1 Response to "Using MRTG to monitor records in a MySQL table"

Thanks. Saved me hours of time getting MRTG graphs out of MySQL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Subscribe to this blog now!

Top Clicks

  • None
April 2008
« Feb   May »
%d bloggers like this: