munin plugin: mysql query cache ratio

mysql_qcache_ratio

#!/usr/bin/perl --
# see also: mytop(1)

use strict;
use warnings;
use DBI;
use Cache::SharedMemoryCache;

my $config = {
	dsn => $ENV{'mysqlconnection'} || 'DBI:mysql:mysql',
	user => $ENV{'mysqluser'} || 'root',
	password => $ENV{'mysqlpassword'} || '',
	cache_namespace => $ENV{'cachenamespace'} || 'munin_mysql_qcache_ratio',
};

my $cmd = shift @ARGV // "";
if( $cmd eq "autoconf" ){
	print "no (not supported)\n";
	exit 1;
}
elsif( $cmd eq "config" ){
	print "graph_title Query Cache Ratio\n";
	print "graph_category mysql2\n";
	print "graph_vlabel %.\n";
	
	print "Qcache_hits_ratio_average.draw LINE1\n";
	print "Qcache_hits_ratio_average.min 0\n";
	print "Qcache_hits_ratio_average.max 100\n";
	print "Qcache_hits_ratio_average.label Cache hits ratio (avg)\n";
	print "Qcache_hits_ratio_average.info DERIVE\n";
	
	print "Qcache_hits_ratio.draw LINE1\n";
	print "Qcache_hits_ratio.min 0\n";
	print "Qcache_hits_ratio.max 100\n";
	print "Qcache_hits_ratio.label Cache hits ratio\n";
	print "Qcache_hits_ratio.info DERIVE\n";
	
	exit 0;
}

# setup cache
my $shared_memory_cache = Cache::SharedMemoryCache->new( {
	namespace => $config->{cache_namespace},
	default_expires_in => 60,
} );

# get
my $dbh = &get_dbh or die DBI->errstr;
my $sth = $dbh->prepare("SHOW GLOBAL STATUS");
$sth->execute;
my $data = {};
while( my $row = $sth->fetch ){
	next if not ( $row->[0] ~~ [qw(Com_select Qcache_hits)] );
	$data->{ $row->[0] } = $row->[1];
}
$sth->finish;
$dbh->disconnect;

# calc
my $ratio = 0;
if( my $data_old = $shared_memory_cache->get('data') ){
	$ratio = 100
		* ( $data->{Qcache_hits} - $data_old->{Qcache_hits} )
		/ ( ($data->{Com_select} + $data->{Qcache_hits} - ( $data_old->{Qcache_hits} + $data_old->{Com_select} ) ) || 1);
}

# output
printf "Qcache_hits_ratio.value %.3f\n", $ratio;
printf "Qcache_hits_ratio_average.value %.3f\n", 100 * ( $data->{Qcache_hits} ) / ( $data->{Qcache_hits} + $data->{Com_select} );

# store cache
$shared_memory_cache->set( data => $data );

exit 0;

sub get_dbh {
	my $dsn = sprintf "%s;mysql_connect_timeout=5", $config->{dsn};
	my $dbiconfig = {
		RaiseError => 1,
		PrintError => 0,
		FetchHashKeyName => 'NAME_lc',
	};
	return DBI->connect( $dsn, @{$config}{qw(user password)}, $dbiconfig );
}

__END__