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

#!/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:

MRTG Custom Stats

About these ads

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Subscribe to this blog now!

Top Clicks

  • None
April 2008
M T W T F S S
« Feb   May »
 123456
78910111213
14151617181920
21222324252627
282930  
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: