mysql

#!/usr/bin/perl

=head1 NAME

mysql - Munin plugin to display misc MySQL server status 

=head1 APPLICABLE SYSTEMS

Should work on most MySQL platforms.

=head1 CONFIGURATION

You need to configure connection parameters to override the
defaults. These are the defaults:

  [mysql]
    env.mysqlconnection DBI:mysql:mysql
    env.mysqluser root

Non-default example:

  [mysql]
    env.mysqlconnection DBI:mysql:mysql;host=127.0.0.1;port=3306
    env.mysqluser root
    env.mysqlpassword geheim


Warning and critical values can be set via the environment in the usual way. 
For example: 
 
  [mysql_replication] 
    env.seconds_behind_master_warning 300 
    env.seconds_behind_master_critical 600

=head2 Multiple instances

This plugin can monitor many instances of MySQL. See 
L<http://github.com/kjellm/munin-mysql/issues#issue/22> for a hint on
how this can be configured.

=head2 Slave lag (mk-heartbeat)

You can use maatkit's mk-heartbeat L<http://www.maatkit.org> to better
measure slave lag. Add this to the configuration:

  env.maatkit_heartbeat 1

=head1 DEPENDENCIES

=over

=item DBD::mysql

=back

=head1 INTERPRETATION

=head2 InnoDB

The statistics from innodb are mainly collected from the command 

  SHOW ENGINE INNODB STATUS

A nice walk through is found at
L<http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/>

=head2 The graphs

FIX point to relevant sections in the MySQL manual and other www
resources for each graph

=over

=item mysql_replication

slave_running and slave_stopped creates an alterning color under the
seconds_behind_master line. It will have the color of slave_running
when the SQL thread runs and the color of slave_stopped otherwise.

=back

=head1 LICENSE

Copyright (C) 2008-2011 Kjell-Magne Øierud

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 dated June, 1991.

This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
USA.

=head1 VERSION

git-master

=head1 MAGICK MARKERS

  #%# family=auto
  #%# capabilities=autoconf

=cut

use warnings;
use strict;
use utf8;

use DBI;
use File::Basename;
use Math::BigInt; # Used to append "=> lib 'GMP'" here, but GMP caused
                  # segfault on some occasions. Removed as I don't
                  # think the tiny performance boost is worth the
                  # debugging effort.
use Module::Pluggable search_path => ['Munin::MySQL::Graph'], require => 1, inner => 0;
use Munin::Plugin;

# Check that multigraph is supported
need_multigraph();

#
# Global hash holding the data collected from mysql.
#
our $data; # Was 'my'. Changed to 'our' to facilitate testing.

#
# FIX
#
my $instance;



#---------------------------------------------------------------------
#  C O N F I G
#---------------------------------------------------------------------

my %config = (
    'dsn'               => $ENV{'mysqlconnection'}   || 'DBI:mysql:mysql',
    'user'              => $ENV{'mysqluser'}         || 'root',
    'password'          => $ENV{'mysqlpassword'}     || '',
    'maatkit_heartbeat' => $ENV{'maatkit_heartbeat'} || 0,
);


#---------------------------------------------------------------------
#  G R A P H   D E F I N I T I O N S
#---------------------------------------------------------------------

# These are defaults to save typing in the graph definitions
my %defaults = (
    global_attrs => {
        args   => "--base 1000",
    },
    data_source_attrs => {
        min   => '0',
        type  => 'DERIVE',
        draw  => 'AREASTACK',
    },
);

# %graphs contains the graph definitions, it is indexed on the graph
# name. The information stored for each graph is used for both showing
# data source values and for printing the graph configuration. Each
# graph follows the followingformat:
#
# $graph{NAME} => {
#     config => {
#         # The global attributes for this graph
#         global_attrs => {} 
#         # Attributes common to all data sources in this graph
#         data_source_attrs => {}
#     },
#     data_sources => [
#         # NAME - The name of the data source (e.g. variable names 
#         #        from SHOW STATUS)
#         # DATA_SOURCE_ATTRS - key-value pairs with data source 
#         #                     attributes
#         {name => 'NAME', (DATA_SOURCE_ATTRS)},
#         {...},
#     ],

my %graphs = ();


#---------------------------------------------------------------------
#  M A I N
#---------------------------------------------------------------------


sub main {
    my $command = $ARGV[0] || 'show';

    for my $graph_package (__PACKAGE__->plugins) {
        %graphs = (%graphs, %{$graph_package->graphs});
    }

    my %command_map = (
        'autoconf' => \&autoconf,
        'config'   => \&config,
        'show'     => \&show,
    );
    
    die "Unknown command: $command" 
        unless exists $command_map{$command};

    build_instance();

    if ($command eq 'autoconf') {
        return $command_map{$command}->();
    }
    else {
        if ($command eq 'show') {
            collect_data();
            if ($data->{_master_insufficient_privileges}) {
                print STDERR "Can't show data about replication master: $data->{_master_insufficient_privileges}.\n";
            }
            if ($data->{_slave_insufficient_privileges}) {
                print STDERR "Can't show data about replication slave: $data->{_slave_insufficient_privileges}.\n";
            }
        }
        for my $graph (sort keys %graphs) {
            print "multigraph $graph$instance\n";
            $command_map{$command}->($graph);
        }
    }
    return 0;
}


#---------------------------------------------------------------------
#  C O M M A N D   H A N D L E R S
#---------------------------------------------------------------------

# Each command handler should return an appropriate exit code


# http://munin.projects.linpro.no/wiki/ConcisePlugins#autoconf
sub autoconf {
    eval {
        db_connect();
    };
    if ($@) {
        my $err = $@;
        $err =~ s{\s at \s \S+ \s line .*}{}xms;
        print "no ($err)\n"; 
        return 0;
    }
    print "yes\n";
    return 0;
}


sub config {
    my $graph_name = shift;

    my $graph = $graphs{$graph_name};

    my %conf = (%{$defaults{global_attrs}}, %{$graph->{config}{global_attrs}});
    $conf{args} .=  ' --no-gridfit --slope-mode';

    while (my ($k, $v) = each %conf) {
        print "graph_$k $v\n";
    }
    print "graph_category mysql$instance\n";

    for my $ds (@{$graph->{data_sources}}) {
        my %ds_spec = (
            %{$defaults{data_source_attrs}},
            %{$graph->{config}{data_source_attrs}},
            %$ds,
        );
        while (my ($k, $v) = each %ds_spec) {
            # 'name' is only used internally in this script, not
            # understood by munin.
            next if ($k eq 'name');

            # 'value' should not be used in config context.
            next if ($k eq 'value');

            next if ($v eq '');

            printf("%s.%s %s\n", clean_fieldname($ds->{name}), $k, $v);
        }
        print_thresholds(clean_fieldname($ds->{name}));
    }
}

sub show {
    my $graph_name = shift;

    my $graph = $graphs{$graph_name};
    
    die "Can't show data for '$graph_name' because InnoDB is disabled."
        if $graph_name =~ /innodb_/ && $data->{_innodb_disabled};

    for my $ds (@{$graph->{data_sources}}) {
        my $value = exists $ds->{value} 
            ? $ds->{value}($data) 
                : $data->{$ds->{name}};

        printf "%s.value %s\n", clean_fieldname($ds->{name}), $value;
    }
}



#---------------------------------------------------------------------
#  U T I L I T Y   S U B S
#---------------------------------------------------------------------


sub build_instance {
    $instance = basename($0);
    if($instance =~ /^mysql_([0-9]+)/) {
        $instance = "-$1";
    }
    else {
        $instance = "";
    }
}


sub db_connect {
    my $dsn = "$config{dsn};mysql_connect_timeout=5";

    return DBI->connect($dsn, $config{user}, $config{password}, {
        RaiseError       => 1,
        PrintError       => 0,
        FetchHashKeyName => 'NAME_lc',
    });
}


sub collect_data {
    $data = {};

    my $dbh = db_connect();

    # Set up defaults in case the server is not a slave
    $data->{relay_log_space}       = 0;
    $data->{slave_running}         = 0;
    $data->{slave_stopped}         = 0;
    $data->{seconds_behind_master} = 0;
    
    # Set up defaults in case binlog is not enabled
    $data->{ma_binlog_size} = 0;

    update_variables($dbh);
    update_innodb($dbh);
    update_master($dbh);
    update_slave($dbh);
    update_process_list($dbh);
}


sub update_variables {
    my ($dbh) = @_;
    my @queries = (
        'SHOW /*!50002 GLOBAL*/ STATUS',
        'SHOW /*!40003 GLOBAL*/ VARIABLES',
    );

    my %variable_name_map = (
        table_cache => 'table_open_cache', # table_open_cache was
                                           # previously known as
                                           # table_cache in MySQL
                                           # 5.1.2 and earlier.
    );

    for my $query (@queries) {
        $data->{$query} = {};

        my $sth = $dbh->prepare($query);
        $sth->execute();
        while (my $row = $sth->fetch) {
            my $var = $variable_name_map{$row->[0]} || $row->[0];
            $data->{$var} = $row->[1];
        }
        $sth->finish();
    }
}


sub update_innodb {
    my ($dbh) = @_;

    my $sth = $dbh->prepare('SHOW /*!50000 ENGINE*/ INNODB STATUS');
    eval {
        $sth->execute();
    };
    if ($@) {
        if ($@ =~ /Cannot call SHOW INNODB STATUS because skip-innodb is defined/) {
            $data->{_innodb_disabled} = 1;
            return;
        }
        die $@;
    }
    my $row = $sth->fetchrow_hashref();
    my $status = $row->{'status'};
    $sth->finish();

    parse_innodb_status($status);
}


sub update_master {
    my ($dbh) = @_;

    return if $data->{log_bin} eq 'OFF';

    my $sth = $dbh->prepare('SHOW MASTER LOGS');
    eval {
      $sth->execute();
    };
    if ($@) {
      if ($@ =~ /Access denied/) {
        $data->{_master_insufficient_privileges} = $@;
        return 1;
      }
      die $@;
    }
    while (my $row = $sth->fetch) {
        $data->{ma_binlog_size} += $row->[1];
    }
    
    $sth->finish();
}


sub update_slave {
    my ($dbh) = @_;

    my $sth = $dbh->prepare('SHOW SLAVE STATUS');
    eval {
      $sth->execute();
    };
    if ($@) {
      if ($@ =~ /Access denied/) {
        $data->{_slave_insufficient_privileges} = $@;
        return 1;
      }
      die $@;
    }
    my $row = $sth->fetchrow_hashref();
    return unless $row;
    while (my ($k, $v) = each %$row) {
        $data->{$k} = $v;
    }
    $sth->finish();

    if ($config{maatkit_heartbeat}) {
        update_slave_maatkit_heartbeat($dbh);
    }

    # undef when slave is stopped, or when MySQL fails to calculate
    # the lag (which happens depressingly often). (mk-heartbeat fixes
    # this problem.)
    $data->{seconds_behind_master} ||= 0;

    # Scale slave_running and slave_stopped relative to the slave lag.
    $data->{slave_running} = ($data->{slave_sql_running} eq 'Yes')
            ? $data->{seconds_behind_master} : 0;
    $data->{slave_stopped} = ($data->{slave_sql_running} eq 'Yes')
            ? 0 : $data->{seconds_behind_master};
}

# Overwrites seconds_behind_master collected from SHOW SLAVE STATUS
sub update_slave_maatkit_heartbeat {
    my ($dbh) = @_;

    # The TIME_TO_SEC(TIMEDIFF in the query is necessary as mysql
    # otherwise calculates the wrong difference (simply substracting
    # won't work)
    my $sth = $dbh->prepare(q{
        SELECT TIME_TO_SEC(TIMEDIFF(NOW(), ts)) AS seconds_behind_master
          FROM maatkit.heartbeat
         LIMIT 1
    });
    $sth->execute();
    my $row = $sth->fetchrow_hashref();
    return unless $row;
    while (my ($k, $v) = each %$row) {
        $data->{$k} = $v;
    }
    $sth->finish();
}


sub update_process_list {
    my ($dbh) = @_;

    $data->{State_closing_tables}       = 0;
    $data->{State_copying_to_tmp_table} = 0;
    $data->{State_end}                  = 0;
    $data->{State_freeing_items}        = 0;
    $data->{State_init}                 = 0;
    $data->{State_locked}               = 0;
    $data->{State_login}                = 0;
    $data->{State_preparing}            = 0;
    $data->{State_reading_from_net}     = 0;
    $data->{State_sending_data}         = 0;
    $data->{State_sorting_result}       = 0;
    $data->{State_statistics}           = 0;
    $data->{State_updating}             = 0;
    $data->{State_writing_to_net}       = 0;
    $data->{State_none}                 = 0;
    $data->{State_other}                = 0;

    my $sth = $dbh->prepare('SHOW PROCESSLIST');
    $sth->execute();
    while (my $row = $sth->fetchrow_hashref()) {
        my $state = $row->{state};
        if (! defined $state) {
            $state = 'NULL';
        }
        elsif ($state eq '') {
            $state = 'none';
        }
        $state = lc $state;
        $state =~ tr/ /_/;
        my $state_key = exists($data->{"State_$state"}) ? "State_$state" : 'State_other';
        $data->{$state_key}++;
    }
    $sth->finish();
}


#
# In 'SHOW ENGINE INNODB STATUS' 64 bit integers are not formated as
# plain integers. They are either:
#
#   - split in two and needs to be shifted together,
#   - or hexadecimal
#
sub innodb_bigint {
    my ($x, $y) = @_;

    return defined $y
        ? Math::BigInt->new($x)->blsft(32) + $y
        : Math::BigInt->new("0x$x");
}


#---------------------------------------------------------------------
#  P A R S E   'SHOW ENGINE INNODB STATUS'   O U T P U T
#---------------------------------------------------------------------


# A nice walk through
# http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

# The parsing is split in one subrutine per section. Each subroutine
# should parse a block with the following structure
#
# block body ...
# more lines ....
# ----------

sub parse_innodb_status {
    local $_ = shift;

    # Add a dummy section to the end in case the innodb status output
    # has been truncated (Happens for status > 64K characters)
    $_ .= "\n----------\nDUMMY\n----------\n"; 

    my %section_map = (

        'BUFFER POOL AND MEMORY'      => \&parse_buffer_pool_and_memory,
        'INDIVIDUAL BUFFER POOL INFO' => \&skip,
        'FILE I/O'                    => \&parse_file_io,
        'INSERT BUFFER AND ADAPTIVE HASH INDEX' 
            => \&parse_insert_buffer_and_adaptive_hash_index,
        'LATEST DETECTED DEADLOCK'    => \&skip,
        'LATEST FOREIGN KEY ERROR'    => \&skip,
        'LOG'                         => \&parse_log,
        'ROW OPERATIONS'              => \&skip,
        'SEMAPHORES'                  => \&parse_semaphores,
        'TRANSACTIONS'                => \&parse_transactions,
        'BACKGROUND THREAD'           => \&skip,
    );

    skip_heading();
    for (;;) {
        m/\G(.*)\n/gc;
        my $sec = $1;

        last if $sec eq 'END OF INNODB MONITOR OUTPUT';
        if ($sec eq 'DUMMY') {
            handle_incomplete_innodb_status();
            last;
        }

        die "Unknown section: $1" unless exists $section_map{$sec};
        die "Parse error. Expected a section separator" unless m/\G-+\n/gc;

        $section_map{$sec}->();
    }
}


# This regular expression handles the different formating of 64-bit
# integers in different versions of the innodb engine. Either two
# decimal 32-bit integers seperated by a space, or a single
# hexadecimal 64-bit integer.
my $innodb_bigint_rx = qr{([[a-fA-F\d]+)(?: (\d+))?};


# Need the negative lookahead in match dashes because some times the
# TRANSACTION section contains a line of only dashes (see
# http://munin-monitoring.org/ticket/956):
#
# ------- TRX HAS BEEN WAITING 808 SEC FOR THIS LOCK TO BE GRANTED:
# [...]
# ------------------
# ---TRANSACTION 1 2450838625, ACTIVE 831 sec, OS thread id 27 fetching rows, thread declared inside InnoDB 104

sub match_new_section { 
    return m/\G
             -+\n                 # --------------------------- 
             (?= [A-Z\/ ]+\n      # SECTION NAME
                 [=-]+\n)/gcx;    # --------------------------- ('=' on end of output)
}


sub skip_line    { return m/\G.*\n/gc; }


sub skip_heading {
    # Heading is 6 lines
    for my $foo (1...6) {
        skip_line or die('Parse error'); 
    }
}


sub parse_section {
    my ($parser) = @_;

    #warn substr($_, pos(), 10);
    for (;;) {
        return if match_new_section;
        next if $parser->();
        skip_line();
    }
}


sub skip { parse_section(sub {}); }


sub parse_semaphores {
    parse_section(
        sub {
            m/\GMutex spin waits (\d+), rounds (\d+), OS waits (\d+)\n/gc && do {
                $data->{ib_spin_waits}  = $1;
                $data->{ib_spin_rounds} = $2;
                $data->{ib_os_waits}    = $3;
                return 1;
            };
        }
    );
}


sub parse_transactions {
    parse_section(
        sub {
            m/\GTrx id counter $innodb_bigint_rx\n/gc && do {
                $data->{ib_tnx} = innodb_bigint($1, $2);
                return 1;
            };
            m/\GPurge done for trx's n:o < $innodb_bigint_rx undo n:o < $innodb_bigint_rx\n/gc && do {
                if (defined $3) {
                    # old format
                    $data->{ib_tnx_prg} = innodb_bigint($1, $2);
                    # FIX add to data? innodb_bigint($3, $4);
                }
                else {
                    # new format
                    $data->{ib_tnx_prg} = innodb_bigint($1);
                    # FIX add to data? innodb_bigint($2);
                }
                return 1;
            };
            m/\GHistory list length (\d+)\n/gc && do {
                $data->{ib_tnx_hist} = $1;
                return 1;
            };
        }
    );

}


sub parse_file_io {
    parse_section(
        sub {
            m/\GPending normal aio reads: (\d+)(?: \[(?:\d+, )*\d+\] )?, aio writes: (\d+)(?: \[(?:\d+, )*\d+\] )?,\n\s*ibuf aio reads: (\d+), log i\/o's: (\d+), sync i\/o's: (\d+)\n/gc && do {
                $data->{ib_iop_aioread}  = $1;
                $data->{ib_iop_aiowrite} = $2;
                $data->{ib_iop_ibuf_aio} = $3;
                $data->{ib_iop_log}      = $4;
                $data->{ib_iop_sync}     = $5;
                return 1;
            };
            m/\GPending flushes \(fsync\) log: (\d+); buffer pool: (\d+)\n/gc && do {
                $data->{ib_iop_flush_log}   = $1;
                $data->{ib_iop_flush_bpool} = $2;
                return 1;
            };
            m/\G(\d+) OS file reads, (\d+) OS file writes, (\d+) OS fsyncs\n/gc && do {
                $data->{ib_io_read}  = $1;
                $data->{ib_io_write} = $2;
                $data->{ib_io_fsync} = $3;
                return 1;
            };
        }
    );
}


sub parse_insert_buffer_and_adaptive_hash_index {
    parse_section(
        sub {
            # MySQL < 5.5
            m/\G(\d+) inserts, (\d+) merged recs, (\d+) merges\n/gc && do {
                $data->{ib_ibuf_inserts}    = $1;
                $data->{ib_ibuf_merged_rec} = $2;
                $data->{ib_ibuf_merges}     = $3;
                return 1;
            };
            # MySQL >= 5.5
            m/\Gmerged operations:\n insert (\d+), delete mark \d+, delete \d+\ndiscarded operations:\n insert (\d+), delete mark \d+, delete \d+\n/gc && do {
                $data->{ib_ibuf_inserts} = $1;
                $data->{ib_ibuf_merged_rec} = $1 + $2;
                return 1;
            };
            m/\GIbuf: size (\d+), free list len (\d+), seg size (\d+),(?: (\d+) merges)?\n/gc && do {
                $data->{ib_ibuf_size}     = $1;
                $data->{ib_ibuf_free_len} = $2;
                $data->{ib_ibuf_seg_size} = $3;
                $data->{ib_ibuf_merges}   = $4 if defined $4; # MySQL >= 5.5
                return 1;
            };
        }
    );
}


sub parse_log {
    parse_section(
        sub {
            m/\GLog sequence number $innodb_bigint_rx\n/gc && do {
                $data->{ib_log_written} = innodb_bigint($1, $2);
                return 1;
            };
            m/\GLog flushed up to\s+$innodb_bigint_rx\n/gc && do {
                $data->{ib_log_flush} = innodb_bigint($1, $2);
                return 1;
            };
            m/\GLast checkpoint at\s+$innodb_bigint_rx\n/gc && do {
                $data->{ib_log_checkpoint} = innodb_bigint($1, $2);
                return 1;
            };
            m/\G(\d+) log i\/o's done.*\n/gc && do {
                $data->{ib_io_log} = $1;
                return 1;
            };
        }
    );
}


sub parse_buffer_pool_and_memory {
    parse_section(
        sub {
            m/\GBuffer pool size\s+(\d+)\n/gc && do {
                $data->{ib_bpool_size} = $1;
                return 1;
            };
            m/\GFree buffers\s+(\d+)\n/gc && do {
                $data->{ib_bpool_free} = $1;
                return 1;
            };
            m/\GDatabase pages\s+(\d+)\n/gc && do {
                $data->{ib_bpool_dbpages} = $1;
                return 1;
            };
            m/\GModified db pages\s+(\d+)\n/gc && do {
                $data->{ib_bpool_modpages} = $1;
                return 1;
            };
            m/\GPages read (\d+), created (\d+), written (\d+)\n/gc && do {
                $data->{ib_bpool_read}    = $1;
                $data->{ib_bpool_created} = $2;
                $data->{ib_bpool_written} = $3;
                return 1;
            };
        }
    );
}


sub handle_incomplete_innodb_status {

    warn "Output from SHOW ENGINE INNDOB STATUS was truncated. "
        . "This happens if the output of SEIS exceeds 64KB. "
        . "Several of the InnoDB graphs might be affected by this.";
    
    # FIX Is it possible to find some of the missing values from SHOW
    # STATUS?
}


exit main() unless caller;


1;