mysql_

#!/usr/bin/perl

=head1 NAME

mysql_ - Munin plugin to display misc MySQL server status 

=head1 APPLICABLE SYSTEMS

Should work on most MySQL platforms, tested by the author on MySQL
versions 5.1.41, 5.0.87, and 4.1.22.

=head1 CONFIGURATION

This script is used to generate data for several graphs. To generate
data for one specific graph, you need to create a symbolic link with a
name like mysql_<GRAPH> to this script. 

To get a list of symlinks that can be created, run:

  ./mysql_ suggest

In addition you might need to specify connection parameters in the
plugin configuration 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
    env.mysqlhbdb maindb

=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.

=head1 DEPENDENCIES

=over

=item Cache::Cache

The plugin uses shared memory to cache the statistics gathered from
MySQL. This ensures minimal inpact on the MySQL server.

=item DBD::mysql

=item mk-heartbeat from Maatkit

If you chose to use the heartbeat graph, mk-heartbeat must be installed and
the database containing the heartbeat table must be configured.

=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,2009 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=suggest 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 POSIX qw(floor);

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


my ($has_cache, $has_mkhb);
my $instance;
my $shared_memory_cache;


BEGIN {
    eval 'require Cache::SharedMemoryCache';
    $has_cache = $@ ? 0 : 1;

    qx(which mk-heartbeat 2>&1 >/dev/null);
    $has_mkhb = $? ? 0: 1;
}


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

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


#---------------------------------------------------------------------
#  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 = ();

#---------------------------------------------------------------------

$graphs{bin_relay_log} = {
    config => {
        global_attrs => {
            title  => 'Binary/Relay Logs',
            vlabel => 'Log activity',
        },
        data_source_attrs => {
            draw  => 'LINE1',
        },
    },
    data_sources => [
        {name => 'Binlog_cache_disk_use', label => 'Binlog Cache Disk Use',
                                          info  => 'The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.'},
        {name => 'Binlog_cache_use',      label => 'Binlog Cache Use',
                                          info  => 'The number of transactions that used the temporary binary log cache.'},
        {name => 'ma_binlog_size',        label => 'Binary Log Space',
                                          info  => 'The total combined size of all existing binary logs.'},
        {name => 'relay_log_space',       label => 'Relay Log Space',
                                          info  => 'The total combined size of all existing relay logs.'},
    ],
};

#---------------------------------------------------------------------

$graphs{commands} = {
    config => {
        global_attrs => {
            title  => 'Command Counters',
            vlabel => 'Commands per ${graph_period}',
            total  => 'Questions',
        },
        data_source_attrs => {},
    },
    data_sources => [
        {name => 'Com_delete',         label => 'Delete',
                                       info  => 'The Com_xxx  statement counter variables indicate the number of times each xxx statement has been executed.'},
        {name => 'Com_insert',         label => 'Insert'},
        {name => 'Com_insert_select',  label => 'Insert select'},
        {name => 'Com_load',           label => 'Load Data'},
        {name => 'Com_replace',        label => 'Replace'},
        {name => 'Com_replace_select', label => 'Replace select'},
        {name => 'Com_select',         label => 'Select'},
        {name => 'Com_update',         label => 'Update'},
        {name => 'Com_update_multi',   label => 'Update multi'},
    ],
};

#---------------------------------------------------------------------

$graphs{connections} = {
    config => {
        global_attrs => {
            title  => 'Connections',
            vlabel => 'Connections per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE1',
        },
    },
    data_sources => [
        {name => 'max_connections',      label  => 'Max connections', 
                                         type   => 'GAUGE',
                                         draw   => 'AREA',
                                         colour => 'cdcfc4',
                                         info   => 'The number of simultaneous client connections allowed.'},
        {name => 'Max_used_connections', label  => 'Max used',
                                         type   => 'GAUGE',
                                         draw   => 'AREA',
                                         colour => 'ffd660',
                                         info   => 'The maximum number of connections that have been in use simultaneously since the server started.'},
        {name => 'Aborted_clients',      label  => 'Aborted clients',
                                         info   => 'The number of connections that were aborted because the client died without closing the connection properly.'},
        {name => 'Aborted_connects',     label  => 'Aborted connects',
                                         info   => 'The number of failed attempts to connect to the MySQL server.'},
        {name => 'Threads_connected',    label  => 'Threads connected',
                                         type   => 'GAUGE',
                                         info   => 'The number of currently open connections.'},
        {name => 'Connections',          label  => 'New connections',
                                         info   => 'The number of connection attempts (successful or not) to the MySQL server.'},
    ],
};

#---------------------------------------------------------------------

$graphs{files_tables} = {
    config => {
        global_attrs => {
            title  => 'Files and tables',
            vlabel => 'Tables',
        },
        data_source_attrs => {
            type  => 'GAUGE',
            draw  => 'LINE1',
        },
    },
    data_sources => [
        {name => 'table_open_cache', label  => 'Table cache',
                                     draw   => 'AREA',
                                     colour => 'cdcfc4',
                                     info   => 'The number of open tables for all threads.'},
        {name => 'Open_files',       label  => 'Open files',
                                     info   => 'The number of files that are open.'},
        {name => 'Open_tables',      label  => 'Open tables',
                                     info   => 'The number of tables that are open.'},
        {name => 'Opened_tables',    label  => 'Opened tables',
                                     type   => 'DERIVE',
                                     info   => 'The number of tables that have been opened.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_bpool} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Buffer Pool',
            vlabel => 'Pages',
            args   => '--base 1024',
        },
        data_source_attrs => {
            draw => 'LINE2',
            type => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'ib_bpool_size',     label  => 'Buffer pool size',
                                      draw   => 'AREA',
                                      colour => 'ffd660',
                                      info   => 'The total number of buffer pool pages'},
        {name => 'ib_bpool_dbpages',  label  => 'Database pages',
                                      draw   => 'AREA',
                                      colour => 'cdcfc4',
                                      info   => 'The number of used buffer pool pages'},
        {name => 'ib_bpool_free',     label => 'Free pages',
                                      info  => 'The number of unused buffer pool pages'},
        {name => 'ib_bpool_modpages', label => 'Modified pages',
                                      info  => 'The number of "dirty" database pages'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_bpool_act} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Buffer Pool Activity',
            vlabel => 'Activity per ${graph_period}',
            total  => 'Total',
        },
        data_source_attrs => {
            draw => 'LINE2',
        },
    },
    data_sources => [
        {name => 'ib_bpool_read',    label => 'Pages read',
                                     info  => 'Pages read into the buffer pool from disk.'},
        {name => 'ib_bpool_created', label => 'Pages created',
                                     info  => 'Pages created in the buffer pool without reading the corresponding disk page.'},
        {name => 'ib_bpool_written', label => 'Pages written',
                                     info  => 'Pages written to disk from the buffer pool.'},
    ],
};


#---------------------------------------------------------------------

$graphs{innodb_checkpoint_age} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Checkpoint Age',
            vlabel => 'Bytes',
            args   => '--base 1024 -l 0',
        },
        data_source_attrs => {
            draw  => 'AREA',
            type  => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'innodb_log_size',  label  => 'InnoDB log size',
                                     colour => 'cdcfc4',
                                     info   => 'The size in bytes of InnoDB log space.',
                                     value  => sub {
                                         $_[0]->{innodb_log_file_size} * $_[0]->{innodb_log_files_in_group}
                                     }},
        {name => 'ib_log_chkpt_age', label  => 'Uncheckpointed bytes',
                                     colour => 'ffd660',
                                     info   => 'The age in bytes of InnoDB checkpoint.',
                                     value  => sub {
                                         $_[0]->{ib_log_flush} - $_[0]->{ib_log_checkpoint}
                                     }},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_history_length} = {
    config => {
        global_attrs => {
            title  => 'InnoDB History List',
            vlabel => 'Transactions',
        },
        data_source_attrs => {
            draw  => 'LINE1',
            type  => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'ib_tnx_hist', label => 'History list length',
                                info  => 'Number of unpurged transactions in undo space.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_insert_buf} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Insert Buffer',
            vlabel => 'Activity per ${graph_period}',
        },
        data_source_attrs => {
            draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'ib_ibuf_inserts',    label => 'Inserts',
                                       info  => 'These values shows statistics about how many buffer operations InnoDB has done. The ratio of merges to inserts gives a good idea of how efficient the buffer is.'},
        {name => 'ib_ibuf_merged_rec', label => 'Merged Records'},
        {name => 'ib_ibuf_merges',     label => 'Merges'},
    ],
};    
  
#---------------------------------------------------------------------

$graphs{innodb_insert_buf_size} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Insert Buffer Size',
            vlabel => 'Pages',
            args   => '-l 0',
        },
        data_source_attrs => {
            draw  => 'LINE1',
            type  => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'ib_ibuf_seg_size', label  => 'Segment size',
                                     draw   => 'AREA',
                                     colour => 'cdcfc4',
                                     info   => 'Allocated size of insert buffer segment.'},
        {name => 'ib_ibuf_size',     label  => 'Unmerged pages',
                                     colour => '0022ff',
                                     info   => 'Number of pages containing unmerged records.'},
        {name => 'ib_ibuf_free_len', label  => 'Free pages',
                                     info   => 'Number of pages which are free.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_io} = {
    config => {
        global_attrs => {
            title  => 'InnoDB IO',
            vlabel => 'IO operations per ${graph_period}',
        },
        data_source_attrs => {
            draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'ib_io_read',  label => 'File reads',
                                info  => 'The number of calls to the OS read function.'},
        {name => 'ib_io_write', label => 'File writes',
                                info  => 'The number of calls to the OS write function.'},
        {name => 'ib_io_log',   label => 'Log writes',
                                info  => 'The number of calls to the OS write function that is caused by the log subsystem.'},
        {name => 'ib_io_fsync', label => 'File syncs',
                                info  => 'The number of calls to the OS fsync function.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_io_pend} = {
    config => {
        global_attrs => {
            title  => 'InnoDB IO Pending',
            vlabel => 'Pending operations',
        },
        data_source_attrs => {
            draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'ib_iop_log',         label => 'AIO Log'},
        {name => 'ib_iop_sync',        label => 'AIO Sync'},
        {name => 'ib_iop_flush_bpool', label => 'Buf Pool Flush'},
        {name => 'ib_iop_flush_log',   label => 'Log Flushes'},
        {name => 'ib_iop_ibuf_aio',    label => 'Insert Buf AIO Read'},
        {name => 'ib_iop_aioread',     label => 'Normal AIO Reads'},
        {name => 'ib_iop_aiowrite',    label => 'Normal AIO Writes'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_log} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Log',
            vlabel => 'Log activity per ${graph_period}',
        },
        data_source_attrs => {
            draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'innodb_log_buffer_size', label  => 'Buffer Size',
                                           type   => 'GAUGE',
                                           draw   => 'AREA',
                                           colour => 'fafd9e',
                                           info   => 'The size in bytes of the buffer that InnoDB  uses to write to the log files on disk.'},
        {name => 'ib_log_flush',           label => 'KB Flushed',
                                           info  => 'Number of bytes flushed to the transaction log file.'},
        {name => 'ib_log_written',         label => 'KB Written',
                                           info  => 'Number of bytes written to the transaction log buffer.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_rows} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Row Operations',
            vlabel => 'Operations per ${graph_period}',
            total  => 'Total',
        },
        data_source_attrs => {},
    },
    data_sources => [
        {name => 'Innodb_rows_deleted',  label => 'Deletes',
                                         info  => 'The number of rows deleted from InnoDB tables.'},
        {name => 'Innodb_rows_inserted', label => 'Inserts',
                                         info  => 'The number of rows inserted into InnoDB tables.'},
        {name => 'Innodb_rows_read',     label => 'Reads',
                                         info  => 'The number of rows read from InnoDB tables.'},
        {name => 'Innodb_rows_updated',  label => 'Updates',
                                         info  => 'The number of rows updated in InnoDB tables.'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_semaphores} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Semaphores',
            vlabel => 'Semaphores per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE1',
        },
    },
    data_sources => [
        {name => 'ib_spin_rounds', label => 'Spin Rounds'},
        {name => 'ib_spin_waits',  label => 'Spin Waits'},
        {name => 'ib_os_waits',    label => 'OS Waits'},
    ],
};

#---------------------------------------------------------------------

$graphs{innodb_tnx} = {
    config => {
        global_attrs => {
            title  => 'InnoDB Transactions',
            vlabel => 'Transactions per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE1',
        },
    },
    data_sources => [
        {name => 'ib_tnx', label => 'Transactions created',
                           info  => 'Number of transactions created.'},
    ],
};

#---------------------------------------------------------------------

$graphs{myisam_indexes} = {
    config => {
        global_attrs => {
            title  => 'MyISAM Indexes',
            vlabel => 'Requests per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Key_read_requests',  label => 'Key read requests',
                                       info  => 'The number of requests to read a key block from the cache.'},
        {name => 'Key_reads',          label => 'Key reads', 
                                       info  => 'The number of physical reads of a key block from disk.'},
        {name => 'Key_write_requests', label => 'Key write requests',
                                       info  => 'The number of requests to write a key block to the cache.'},
        {name => 'Key_writes',         label => 'Key writes',
                                       info  => 'The number of physical writes of a key block to disk.'},
   ],
};
  
#---------------------------------------------------------------------

$graphs{network_traffic} = {
    config => {
        global_attrs => {
            title  => 'Network Traffic',
            args   => '--base 1024',
            vlabel => 'Bytes received (-) / sent (+) per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Bytes_received', label => 'Bytes transfered',
                                   graph => 'no',
                                   info  => 'The number of bytes received from all clients.'},
        {name => 'Bytes_sent',     label    => 'Bytes transfered',
                                   negative => 'Bytes_received',
                                   info     => 'The number of bytes sent to all clients.'},
    ],
};
  
#---------------------------------------------------------------------

$graphs{processlist} = {
    config => {
        global_attrs => {
            title  => 'Processlist states',
            vlabel => 'State count per ${graph_period}',
        },
        data_source_attrs => {
        },
    },
    data_sources => [
        {name => 'State_closing_tables',       label => 'Closing tables',
                                               info  => 'The thread is flushing the changed table data to disk and closing the used tables.'},
        {name => 'State_copying_to_tmp_table', label => 'Copying to tmp table',
                                               info  => 'The thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.'},
        {name => 'State_end',                  label => 'End',
                                               info  => 'This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.'},
        {name => 'State_freeing_items',        label => 'Freeing items',
                                               info  => 'The thread has executed a command. This state is usually followed by cleaning up.'},
        {name => 'State_init',                 label => 'Init',
                                               info  => 'This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements.'},
        {name => 'State_locked',               label => 'Locked',
                                               info  => 'The query is locked by another query.'},
        {name => 'State_login',                label => 'Login',
                                               info  => 'The initial state for a connection thread until the client has been authenticated successfully.'},
        {name => 'State_preparing',            label => 'Preparing',
                                               info  => 'This state occurs during query optimization.'},
        {name => 'State_reading_from_net',     label => 'Reading from net',
                                               info  => 'The server is reading a packet from the network.'},
        {name => 'State_sending_data',         label => 'Sending data',
                                               info  => 'The thread is processing rows for a SELECT statement and also is sending data to the client.'},
        {name => 'State_sorting_result',       label => 'Sorting result',
                                               info  => 'For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.'},
        {name => 'State_statistics',           label => 'Statistics',
                                               info  => 'The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.'},
        {name => 'State_updating',             label => 'Updating',
                                               info  => 'The thread is searching for rows to update and is updating them.'},
        {name => 'State_writing_to_net',       label => 'Writing to net',
                                               info  => 'The server is writing a packet to the network.'},
        {name => 'State_none',                 label => 'None',
                                               info  => ''},
        {name => 'State_other',                label => 'Other',
                                               info  => ''},
    ],
};

#---------------------------------------------------------------------

$graphs{qcache} = {
    config => {
        global_attrs => {
            title  => 'Query Cache',
            vlabel => 'Commands per ${graph_period}',
        },
        data_source_attrs => {
             draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'Qcache_queries_in_cache', label => 'Queries in cache',
                                            info  => 'The number of queries registered in the query cache.'},
        {name => 'Qcache_hits',             label => 'Cache hits',
                                            info  => 'The number of query cache hits.'},
        {name => 'Qcache_inserts',          label => 'Inserts',
                                            info  => 'The number of queries added to the query cache.'},
        {name => 'Qcache_not_cached',       label => 'Not cached',
                                            info  => 'The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting).'},
        {name => 'Qcache_lowmem_prunes',    label => 'Low-memory prunes',
                                            info  => 'The number of queries that were deleted from the query cache because of low memory.'},
    ],
};

#---------------------------------------------------------------------

$graphs{qcache_mem} = {
    config => {
        global_attrs => {
            title  => 'Query Cache Memory',
            vlabel => 'Bytes',
            args   => '--base 1024 --lower-limit 0',
        },
        data_source_attrs => {
            draw => 'AREA',
            type => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'query_cache_size',    label => 'Cache size',
                                        info  => 'The amount of memory allocated for caching query results.'},
        {name => 'Qcache_free_memory',  label => 'Free mem',
                                        info  => 'The amount of free memory for the query cache.'},
    ],
};

#---------------------------------------------------------------------

$graphs{replication} = {
    config => {
        global_attrs => {
            title  => 'Replication',
            vlabel => 'Activity',
        },
        data_source_attrs => {
            draw => 'LINE1',
        },
    },
    data_sources => [
        {name => 'slave_running',              label => 'Slave Running',
                                               type  => 'GAUGE',
                                               draw  => 'AREA'},
        {name => 'slave_stopped',              label => 'Slave Stopped',
                                               type  => 'GAUGE',
                                               draw  => 'AREA'},
        {name => 'Slave_retried_transactions', label => 'Retried Transactions',
                                               info  => 'The total number of times since startup that the replication slave SQL thread has retried transactions.'},
        {name => 'Slave_open_temp_tables',     label => 'Open Temp Tables',
                                               info  => 'The number of temporary tables that the slave SQL thread currently has open.'},
        {name => 'seconds_behind_master',      label => 'Secs Behind Master', 
                                               type  => 'GAUGE',
                                               info  => 'http://dev.mysql.com/doc/refman/5.1/en/show-slave-status.html'},
    ],
};

#---------------------------------------------------------------------

$graphs{heartbeat} = {
    config => {
	global_attrs => {
	    title  => 'Maatkit Heartbeat',
	    vlabel => 'Seconds behind master',
	},
	data_source_attrs => {
	    draw => 'LINE1',
	},
    },
    data_sources => [
	{name => 'mk_heartbeat',   label => 'Slave Delay',
					       type  => 'GAUGE',
					       draw  => 'AREA'},
    ],
};

#---------------------------------------------------------------------

$graphs{select_types} = {
    config => {
        global_attrs => {
            title  => 'Select types',
            vlabel => 'Commands per ${graph_period}',
            total  => 'Total',
        },
        data_source_attrs => {},
    },
    data_sources => [
        {name => 'Select_full_join',       label => 'Full join',
                                           info  => 'The number of joins that perform table scans because they do not use indexes.'},
        {name => 'Select_full_range_join', label => 'Full range',
                                           info  => 'The number of joins that used a range search on a reference table.'},
        {name => 'Select_range',           label => 'Range',
                                           info  => 'The number of joins that used ranges on the first table.'},
        {name => 'Select_range_check',     label => 'Range check',
                                           info  => 'The number of joins without keys that check for key usage after each row.'},
        {name => 'Select_scan',            label => 'Scan',
                                           info  => 'The number of joins that did a full scan of the first table.'},
    ],
};

#---------------------------------------------------------------------

$graphs{slow} = {
    config => {
        global_attrs => {
            title  => 'Slow Queries',
            vlabel => 'Slow queries per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Slow_queries', label => 'Slow queries',
                                 info  => 'The number of queries that have taken more than long_query_time seconds.'},
    ],
};

#---------------------------------------------------------------------

$graphs{sorts} = {
    config => {
        global_attrs => {
            title  => 'Sorts',
            vlabel => 'Sorts / ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Sort_rows',         label => 'Rows sorted',
                                      info  => 'The number of sorted rows.'},
        {name => 'Sort_range',        label => 'Range',
                                      info  => 'The number of sorts that were done using ranges.'},
        {name => 'Sort_merge_passes', label => 'Merge passes',
                                      info  => 'The number of merge passes that the sort algorithm has had to do.'},
        {name => 'Sort_scan',         label => 'Scan',
                                      info  => 'The number of sorts that were done by scanning the table.'},
    ],
};

#---------------------------------------------------------------------

$graphs{table_locks} = {
    config => {
        global_attrs => {
            title  => 'Table locks',
            vlabel => 'locks per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Table_locks_immediate', label => 'Table locks immed',
                                          info  => 'The number of times that a request for a table lock could be granted immediately.'},
        {name => 'Table_locks_waited',    label => 'Table locks waited',
                                          info  => 'The number of times that a request for a table lock could not be granted immediately and a wait was needed.'},
    ],
};

#---------------------------------------------------------------------

$graphs{threads} = {
    config => {
        global_attrs => {
            title  => 'Threads',
            vlabel => 'Threads created per ${graph_period}',
        },
        data_source_attrs => {
        },
    },
    data_sources => [
        {name => 'thread_cache_size', label => 'Thread cache size',
                                      draw  => 'AREA',
                                      info  => 'How many threads the server should cache for reuse'},
        {name => 'Threads_created',   label => 'Threads created',
                                      draw  => 'LINE1',
                                      type  => 'GAUGE',
                                      info  => 'The number of threads created to handle connections'},
    ],
};

#---------------------------------------------------------------------

$graphs{tmp_tables} = {
    config => {
        global_attrs => {
            title  => 'Temporary objects',
            vlabel => 'Objects per ${graph_period}',
        },
        data_source_attrs => {
            draw  => 'LINE2',
        },
    },
    data_sources => [
        {name => 'Created_tmp_disk_tables', label => 'Temp disk tables',
                                            info  => 'The number of internal on-disk temporary tables created by the server while executing statements.'},
        {name => 'Created_tmp_tables',      label => 'Temp tables',
                                            info  => 'The number of internal temporary tables created by the server while executing statements.'},
        {name => 'Created_tmp_files',       label => 'Temp files',
                                            info  => 'How many temporary files mysqld has created.'},
    ],
};

#---------------------------------------------------------------------

$graphs{uptime} = {
    config => {
        global_attrs => {
            title  => 'Uptime',
            vlabel => 'Uptime in days',
        },
        data_source_attrs => {
            draw  => 'AREA',
            type  => 'GAUGE',
        },
    },
    data_sources => [
        {name => 'Uptime_days', label => 'Uptime',
                                info  => 'Server uptime in days.',
                                value => sub {
                                    floor($_[0]->{Uptime} / 86400); # seconds in a day
                                },
        },
    ],
};


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


sub main {
    my $graph = basename($0);
    # remove prefix and instance number from command name
    $graph =~ s/^mysql_([0-9]+_)?//;

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

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

    die "Missing dependency Cache::Cache"
        unless $has_cache || $command eq 'autoconf';
    
    build_instance();

    if ($has_cache) {
        build_shared_memory_cache();
    }

    return $command_map{$command}->($graph);
}


#---------------------------------------------------------------------
#  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 {
    unless ($has_cache) {
        print "no (Missing dependency Cache::Cache)\n";
        return 0;
    }

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


# http://munin.projects.linpro.no/wiki/ConcisePlugins#suggest
sub suggest {

    # What is the best way to decide which graphs is applicable to a
    # given system?
    #
    # Does the database use InnoDB? A zero count from:
    #
    #   SELECT COUNT(*) 
    #     FROM tables
    #    WHERE table_type = 'base table' 
    #      AND engine     = 'innodb'
    #
    # Does the database use binary logs? 'OFF' as the result from:
    #
    #   SHOW GLOBAL variables LIKE 'log_bin'
    #
    # Is the database setup as a slave? Empty result from:
    # 
    #   SHOW SLAVE STATUS

    local $_;
    delete $graphs{"heartbeat"} unless $has_mkhb;
    print "$_\n" for (sort keys(%graphs));

    return 0;
}


sub config {
    my $graph_name = shift;

    # In MySQL 5.1 (and probably erlier versions as well) status
    # variables are unique when looking at the last 19 characters.
    #
    #   SELECT RIGHT(variable_name, 19), COUNT(*) 
    #     FROM information_schema.global_status
    #    GROUP BY RIGHT(variable_name, 19)
    #   HAVING COUNT(*) > 1;
    #
    #   Empty set (0.06 sec)
    #
    # There is one duplicate when looking at server variables
    #   
    #   SELECT RIGHT(variable_name, 19), COUNT(*) 
    #     FROM information_schema.global_variables
    #    GROUP BY RIGHT(variable_name, 19)
    #   HAVING COUNT(*) > 1;
    #   
    #   +--------------------------+----------+
    #   | RIGHT(variable_name, 19) | COUNT(*) |
    #   +--------------------------+----------+
    #   | OW_PRIORITY_UPDATES      |        2 |
    #   +--------------------------+----------+
    #   1 row in set (0.05 sec)
    #
    #   show global variables like '%OW_PRIORITY_UPDATES';
    #   
    #   +--------------------------+-------+
    #   | Variable_name            | Value |
    #   +--------------------------+-------+
    #   | low_priority_updates     | OFF   |
    #   | sql_low_priority_updates | OFF   |
    #   +--------------------------+-------+
    #   2 rows in set (0.00 sec)
    #   
    # Not a problem since we don't graph these
        
    die 'Unknown graph ' . ($graph_name ? $graph_name : '')
        unless $graphs{$graph_name};

    my $graph = $graphs{$graph_name};
    
    my %conf = (%{$defaults{global_attrs}}, %{$graph->{config}{global_attrs}});
    while (my ($k, $v) = each %conf) {
        print "graph_$k $v\n";
    }
    print "graph_category mysql2$instance\n";

    my $i = 0;
    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');

            # AREASTACK is part of munin as of version 1.3.3 (not
            # released yet). Until then ...
            if ($k eq 'draw' && $v eq 'AREASTACK') {
                printf("%s.%s %s\n", 
                       clean_fieldname($ds->{name}), $k, ($i ? 'STACK' : 'AREA'));
                $i++;
            }
            else {
                printf("%s.%s %s\n", clean_fieldname($ds->{name}), $k, $v);
            }
        }
    }

    return 0;
}

sub show {
    my $graph_name = shift;

    die 'Unknown graph ' . ($graph_name ? $graph_name : '')
        unless $graphs{$graph_name};

    my $graph = $graphs{$graph_name};
    
    update_data();

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

    if ($graph_name =~ /heartbeat/) {
	die "Can't show data for '$graph_name' because mk-heartbeat is not present." if !$has_mkhb;
	die "Can't show data for '$graph_name' because option hbdb is not set." if !$config{hbdb};
    }

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

    return 0;
}



#---------------------------------------------------------------------
#  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 build_shared_memory_cache {
    my %cache_options = ( 
        'namespace'          => "munin_mysql$instance",
        'default_expires_in' => 60,
    );

    $shared_memory_cache = Cache::SharedMemoryCache->new(\%cache_options)
        or die("Couldn't instantiate SharedMemoryCache");
}


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 update_data {
    $data = $shared_memory_cache->get('data');
    return if $data;

    #warn "Need to update cache";

    $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_heartbeat() if ($has_mkhb && $config{hbdb});
    update_process_list($dbh);

    $shared_memory_cache->set('data', $data);
}


sub update_variables {
    my ($dbh) = @_;
    my @queries = (
        'SHOW /*!50000 GLOBAL*/ STATUS',
        'SHOW 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');
    $sth->execute();
    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');
    $sth->execute();
    my $row = $sth->fetchrow_hashref();
    return unless $row;
    while (my ($k, $v) = each %$row) {
        $data->{$k} = $v;
    }
    $sth->finish();

    # undef when slave is stopped, or when MySQL fails to calculate
    # the lag (which happens depresingly 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};


}

sub update_heartbeat {

    my $host = "127.0.0.1";
    if ($config{dsn} =~ m/.*host=([^;]*);/) {
	$host = $1;
    }
    my $cmd = "mk-heartbeat --check ";
    $cmd .= "-D $config{hbdb} ";
    $cmd .= "-h $host ";
    $cmd .= "-u $config{user} ";
    $cmd .= "-p $config{password} " if ($config{password});

    my $delay = qx($cmd 2>&1);
    $delay = -1 if ($? || $delay !~ /\d+/);

    $data->{mk_heartbeat} = $delay;
}

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

#
# Can't use variable names longer than 19 characters:
# http://munin.projects.linpro.no/wiki/notes_on_datasource_names
#
sub clean_fieldname {
    my $name = shift;

    # Replace a sequence of illegal leading chars with a single _
    $name =~ s/^[^A-Za-z_]+/_/;
    # Replace remaining illegals with _
    $name =~ s/[^A-Za-z0-9_]/_/g;

    # And use only the last 19 chars
    $name = substr($name,-19);

    return $name;
}


#---------------------------------------------------------------------
#  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"; 

    my %section_map = (

        'BUFFER POOL AND MEMORY'   => \&parse_buffer_pool_and_memory,
        '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+))?};


sub match_dashes { return m/\G-+\n/gc; }


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_dashes();
        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+), aio writes: (\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 {
            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;
            };
            m/\GIbuf: size (\d+), free list len (\d+), seg size (\d+),\n/gc && do {
                $data->{ib_ibuf_size}     = $1;
                $data->{ib_ibuf_free_len} = $2;
                $data->{ib_ibuf_seg_size} = $3;
                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;