plugin-mysql_cache_memory: mysql_cachememory

#!/usr/bin/perl
#
# mysql_cachememory - written by Paul Dixon (paul@elphin.com)
# adapted from mysql_queries by Per Andreas Buer
#
# This munin plugin graphs the total available memory for the
# mysql query cache, show the proportion that has been used
#
# Parameters:
#
#   config
#   autoconf
#
# Configuration variables
#
#   mysqlopts     - Options to pass to mysql
#   mysqladmin    - Override location of mysqladmin
#
#%# family=auto
#%# capabilities=autoconf

use strict;

my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin";
my $COMMAND    =      "$MYSQLADMIN $ENV{mysqlopts} extended-status variables";

#status variables we want to fetch...
my %mysql_status = ( 
               "Qcache_free_memory"  => 0, 
               "query_cache_size" => 0,
             );

my $arg = shift();

if ($arg eq 'config') {
    print_config();
    exit();
} elsif ($arg eq 'autoconf') {
    unless (test_service() ) {
        print "yes\n";
    } else {
        print "no\n";
    }
    exit;
}


#no special argument to process, let's gather our data!

open(SERVICE, "$COMMAND |")
  or die("Coult not execute '$COMMAND': $!");

while (<SERVICE>) {
    my ($k, $v) = (m/(\w+).*?(\d+(?:\.\d+)?)/);
    next unless ($k);
    if (exists $mysql_status{$k} ) {
         $mysql_status{$k}=$v;
    }
}

close(SERVICE);


my $mb_size = $mysql_status{'query_cache_size'}/(1024*1024);
my $mb_free = $mysql_status{'Qcache_free_memory'}/(1024*1024);
my $mb_used = $mb_size - $mb_free;

print "free.value $mb_free\n";
print "used.value $mb_used\n";

exit;



sub print_config {

    my $num = 0;

    print("graph_title MySQL Query Cache Memory
graph_args -l 0
graph_vlabel MB
graph_category mysql
graph_info This graph shows the size of the cache and the amount being used
used.label Used memory (MB)
used.type GAUGE
used.draw AREA
free.label Free memory (MB)
free.type GAUGE
free.draw STACK
");

    
}


sub test_service {

    my $return = 1;

    system ("$MYSQLADMIN --version >/dev/null 2>/dev/null");
    if ($? == 0)
    {
        system ("$COMMAND >/dev/null 2>/dev/null");
        if ($? == 0)
        {
            print "yes\n";
            $return = 0;
        }
        else
        {
            print "no (could not connect to mysql)\n";
        }
    }
    else
    {
        print "no (mysqadmin not found)\n";
    }
    exit $return;
}