plugin-mysql_query_cache: mysql_querycache

#!/usr/bin/perl
#
# mysql_querycache - written by Paul Dixon (paul@elphin.com)
# adapted from mysql_queries by Per Andreas Buer
#
# This munin plugin graphs the efficiency of the mysql query
# cache, showing the percentage of select queries which were
# served directly from the cache
#
# 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";

#status variables we want to fetch...
my %mysql_status = ( 
               "Com_select"  => 0, 
               "Qcache_hits" => 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);

#cache hits do not increment Com_select - figure out how many queries we've serviced...
my $total_queries = $mysql_status{'Com_select'} + $mysql_status{'Qcache_hits'};

#figure out efficiency in percent
my $efficiency=$total_queries?(($mysql_status{'Qcache_hits'}/$total_queries)*100):0;

print "efficiency.value $efficiency\n";

exit;



sub print_config {

    my $num = 0;

    print("graph_title MySQL Query Cache Efficiency
graph_args --upper-limit 100 -l 0
graph_vlabel Cache hit %
graph_category mysql
graph_info This graph shows the percentage of queries which were served from the query cache
efficiency.label Efficiency %
efficiency.type GAUGE
efficiency.draw LINE2
");

    
}


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;
}