#!/usr/bin/perl

# Program: mysql_ratios
# Author: James Briggs
# Env: Perl 5
# Purpose: Munin plugin to calculate and display MySQL read and write cache performance
# Date: 2011-10-03
# Note: test with:
# munin-run --debug mysql_ratios config

use strict;
use diagnostics;

use Munin::Plugin;

my %config = (
    'user'       => $ENV{'mysqluser'} || 'root',
    'password'   => $ENV{'mysqlpassword'} || '',
);

   my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin";
   my $COMMAND    = "$MYSQLADMIN $ENV{mysqlopts} -u $config{'user'} -p$config{'password'} extended-status";

   my $op = shift || '';

   my %info;

   if ($op eq 'config') {
      print <<EOD;
graph_title MySQL Hit % and Miss Ratios
graph_category mysql
graph_vlabel Hit % or Miss Ratio
graph_args --upper-limit 200 -l 0
EOD

        $info{'innodb_reads_hit_percent'}  = '100 * (1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests))';
        $info{'myisam_reads_hit_percent'}  = '100 * (1 - (Key_reads/Key_read_requests))';
        $info{'myisam_reads_miss_rate'}    = 'Key_reads/Uptime';
        $info{'myisam_writes_hit_percent'} = '100 * (1 - (Key_writes/Key_write_requests))';
        $info{'qcache_reads_hit_percent'}  = '100 * Qcache_hits / (Qcache_hits + Com_select)';
        $info{'qcache_updates_miss_rate'}  = '(Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits';

        for my $key ('innodb_reads_hit_percent', 'myisam_reads_hit_percent', 'myisam_reads_miss_rate', 'myisam_writes_hit_percent', 'qcache_reads_hit_percent', 'qcache_updates_miss_rate',) {
           my_info($key, 0, $key eq 'myisam_reads_miss_rate' ? 200 : 100);
        }

        exit 0;
   }

   my $r = my_status();

   my $innodb_reads = 0;
   if ($r->{Innodb_buffer_pool_read_requests}) {
      $innodb_reads = 1 - ($r->{Innodb_buffer_pool_reads} / $r->{Innodb_buffer_pool_read_requests});
   }

   my $myisam_reads = 0;
   if ($r->{Key_read_requests}) {
      $myisam_reads = 1 - ($r->{Key_reads} / $r->{Key_read_requests});
   }

   my $myisam_reads_miss = 0;
   if ($r->{Uptime}) {
      $myisam_reads_miss = $r->{Key_reads} / $r->{Uptime};
   }

   my $myisam_writes = 0;
   if ($r->{Key_write_requests}) {
      $myisam_writes = 1 - ($r->{Key_writes} / $r->{Key_write_requests});
   }

   my $qcache_reads_ratio = 0;
   if (($r->{Qcache_hits}) + $r->{Qcache_inserts}) {
      $qcache_reads_ratio = 100 * $r->{Qcache_hits} / ($r->{Qcache_hits} + $r->{Com_select});
   }

   my $qcache_updates_ratio = 0;
   if ($r->{Qcache_hits}) {
      $qcache_updates_ratio = ($r->{Com_insert}+$r->{Com_delete}+$r->{Com_update}+$r->{Com_replace})/$r->{Qcache_hits};
   }

   my $fmt = "%s %.2f\n";

   print sprintf($fmt, 'innodb_reads_hit_percent.value',  100 * $innodb_reads);
   print sprintf($fmt, 'myisam_reads_hit_percent.value',  100 * $myisam_reads);
   print sprintf($fmt, 'myisam_reads_miss_rate.value',  $myisam_reads_miss);
   print sprintf($fmt, 'myisam_writes_hit_percent.value', 100 * $myisam_writes);
   print sprintf($fmt, 'qcache_reads_hit_percent.value',  $qcache_reads_ratio);
   print sprintf($fmt, 'qcache_updates_miss_rate.value',  $qcache_updates_ratio);

   exit;

# +---------------------------------------+-------------+
# | Variable_name                         | Value       |
# +---------------------------------------+-------------+
# | Aborted_clients                       | 63          |
# | Aborted_connects                      | 8           |
# | Binlog_cache_disk_use                 | 0           |
# | Binlog_cache_use                      | 0           |

sub my_status {
   my %v;

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

   while (<SERVICE>) {
      if (/(\w+).*?(\w+)/) {
         next if $1 eq 'Variable_name';

         $v{$1} = $2;
      }
   }
   close SERVICE;

   return \%v;
}

sub my_info {
    my ($title, $min, $max) = @_;

   print <<EOD;
$title.label ${title}
$title.info $info{$title}
$title.min $min
$title.type GAUGE
$title.max $max
$title.draw LINE2
EOD
}

# end