Ian Rogers

Originally written around 2002

The mysql command can do quite a lot in batch mode. Here I’ll show how to graph the size of a MySQL table (the number of rows it contains) over time with MRTG.

I’ll assume you have a correct MRTG and MySQL installation.

To get the number of rows in a table we can use the COUNT function in a SELECT. To see the number of orders in an example Customer Relationship Management database:

      SELECT COUNT(*) FROM order

Now let’s assume we have a safe MySQL user “bill” with the password “ben” that can read the order table from database “crm” on localhost. In a Linux shell file we can write:

      mysql -ubill -pben -e "SELECT COUNT(*) FROM order;" crm | tail -1

Now we can write a script to be used by mrtg. The output format is

    * Line 1: “In” count
    * Line 2: “Out” count
    * Line 3: uptime string
    * Line 4: Title string

We only need the “Out” value and the title string:

      #!/bin/sh

      echo 0
      mysql -ubill -pben -e "SELECT COUNT(*) FROM order;" crm | tail -1
      echo 0
      echo “Table Size”

If we call this script table-size and put it in the same directory as the mrtg config files, then we can add an mrtg target like this:

      Target[order]: `/etc/mrtg/table-size`
      Options[order]: nopercent,growright,nobanner,nolegend,noinfo,gauge,
       integer,noi,transparent
      Title[order]: CRM order queue
      PageTop[order]: <h3>Number of outstanding orders</h3>
      YLegend[order]: orders
      ShortLegend[order]:  
      LegendI[order]:  
      LegendO[order]: orders 

By using the transparent option mrtg generates images that can be embedded in web pages with a background graphic.

By replacing the first “echo 0” in table-size with another mysql statement, and removing the “noi” option from the mrtg target, you can compare the sizes of two tables in one graph.

Originally written around 2002

MRTG was initially designed to monitor network traffic (hence the name Multi Router Traffic Grapher) - but it is so extensible it can be used to monitor nearly anything!

Here I show how to use mrtg to monitor disk usage on a Unix/Linux box with the df command.
The quick way

I assume you have mrtg installed with the config files in /etc/mrtg

      cd /etc/mrtg
      wget http://www.ianrogers.net/downloads/df-mrtg.tgz
      tar xvfz df-mrtg.tgz
      rm df-mrtg.tgz

Edit /etc/mrtg/df.cfg and change the “WorkDir” line to an appropriate directory within your website. You’ll have to create the directory as mrtg won’t do it for you!

Then edit /etc/crontab to include the line

      0-59/5 * * * * root /usr/local/mrtg-2/bin/mrtg /etc/mrtg/df.cfg

Wait for two 5 minute cycles to pass. Cron will send two warning messages to the root user containing lines like:

      Rateup WARNING: /usr/local/mrtg-2/bin/rateup could not read the primary log file for df-root
      Rateup WARNING: /home/local/mrtg-2/bin/rateup Can't remove df-root.old updating log file

etc. one each for the first two cycles, and then everything should be fine.
Configuration

The tar file contains only two files:

      -rwxr--r--   1 root    root         659 May  7 13:58 df-mrtg
      -rw-r--r--   1 root    root         3561 Jun 18 12:41 df.cfg

df.cfg controls the mrtg output

df-mrtg takes one argument: a directory in a disk partition, reads the df info and formats it for mrtg. It reads the disk usage in 1k blocks as mrtg seems to use 32 bit integers internally - i.e. it can’t deal with big enough numbers if you try to report gigabyte disks in bytes!

If you want one of the partitions to be displayed as the default page then edit df.cfg. For example, to display the /home partition by default change the 9 occurrences of df-home in df.cfg to index

This has been tested on a Sun Cobalt RaQ3, but should work well with only minor changes, if any, on other Unix systems.