#!/usr/bin/perl # $Id: mysql-status,v 1.9 2006/11/07 23:57:54 kylev Exp $ # Copyright (C) 2006 Kyle VanderBeek # # 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; either version 2 of the License, or (at your option) any later # version. # # 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., 51 # Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. =head1 NAME mysql-status - Cricket information gatherer for MySQL servers. =head1 SYNOPSIS mysql-status [options] [host user password] Options: -c,--config Generate a Defaults config file to STDOUT. -C,--config-long Same as --config, but includes several useful graph definitions as well. -l,--list Generate the internal @MYSQL_VARS variable (useful if you wish to modify this script). Require DB connection information. -h,--help Print help/usage information. Running it without any flags but with the host/user/password will generate the Cricket-worthy output. =head1 FUNCTIONS Internal functions: =over =cut use strict; use DBI; use Getopt::Long; use Pod::Usage; # RRD type exceptions, else it is DERIVE my %TYPE_EXCEPT = qw(Key_blocks_not_flushed GAUGE Key_blocks_unused GAUGE Key_blocks_used GAUGE Open_files GAUGE Open_streams GAUGE Open_tables GAUGE Qcache_free_blocks GAUGE Qcache_free_memory GAUGE Qcache_queries_in_cache GAUGE Qcache_total_blocks GAUGE Slave_open_temp_tables GAUGE Threads_cached GAUGE Threads_connected GAUGE Threads_running GAUGE ); # Non RRD-trackable items we can overlook my %SKIP_VARS = qw(Rpl_status 1 Slave_running 1 Ssl_cipher 1 Ssl_cipher_list 1 Ssl_session_cache_mode 1 Ssl_version 1 ); # List of the numerical values from SHOW STATUS. You can use "-l" to # regenerate this list against your own host, but don't do so after you've # started tracking stats... you'll have to delete your RRDs if the order or # count of variables change. # # This is MySQL 4.1.20 with SSL enabled. my @MYSQL_VARS = qw(Aborted_clients Aborted_connects Binlog_cache_disk_use Binlog_cache_use Bytes_received Bytes_sent Com_admin_commands Com_alter_db Com_alter_table Com_analyze Com_backup_table Com_begin Com_change_db Com_change_master Com_check Com_checksum Com_commit Com_create_db Com_create_function Com_create_index Com_create_table Com_dealloc_sql Com_delete Com_delete_multi Com_do Com_drop_db Com_drop_function Com_drop_index Com_drop_table Com_drop_user Com_execute_sql Com_flush Com_grant Com_ha_close Com_ha_open Com_ha_read Com_help Com_insert Com_insert_select Com_kill Com_load Com_load_master_data Com_load_master_table Com_lock_tables Com_optimize Com_preload_keys Com_prepare_sql Com_purge Com_purge_before_date Com_rename_table Com_repair Com_replace Com_replace_select Com_reset Com_restore_table Com_revoke Com_revoke_all Com_rollback Com_savepoint Com_select Com_set_option Com_show_binlog_events Com_show_binlogs Com_show_charsets Com_show_collations Com_show_column_types Com_show_create_db Com_show_create_table Com_show_databases Com_show_errors Com_show_fields Com_show_grants Com_show_innodb_status Com_show_keys Com_show_logs Com_show_master_status Com_show_ndb_status Com_show_new_master Com_show_open_tables Com_show_privileges Com_show_processlist Com_show_slave_hosts Com_show_slave_status Com_show_status Com_show_storage_engines Com_show_tables Com_show_variables Com_show_warnings Com_slave_start Com_slave_stop Com_stmt_close Com_stmt_execute Com_stmt_prepare Com_stmt_reset Com_stmt_send_long_data Com_truncate Com_unlock_tables Com_update Com_update_multi Connections Created_tmp_disk_tables Created_tmp_files Created_tmp_tables Delayed_errors Delayed_insert_threads Delayed_writes Flush_commands Handler_commit Handler_delete Handler_discover Handler_read_first Handler_read_key Handler_read_next Handler_read_prev Handler_read_rnd Handler_read_rnd_next Handler_rollback Handler_update Handler_write Key_blocks_not_flushed Key_blocks_unused Key_blocks_used Key_read_requests Key_reads Key_write_requests Key_writes Max_used_connections Not_flushed_delayed_rows Open_files Open_streams Open_tables Opened_tables Qcache_free_blocks Qcache_free_memory Qcache_hits Qcache_inserts Qcache_lowmem_prunes Qcache_not_cached Qcache_queries_in_cache Qcache_total_blocks Questions Select_full_join Select_full_range_join Select_range Select_range_check Select_scan Slave_open_temp_tables Slave_retried_transactions Slow_launch_threads Slow_queries Sort_merge_passes Sort_range Sort_rows Sort_scan Ssl_accept_renegotiates Ssl_accepts Ssl_callback_cache_hits Ssl_client_connects Ssl_connect_renegotiates Ssl_ctx_verify_depth Ssl_ctx_verify_mode Ssl_default_timeout Ssl_finished_accepts Ssl_finished_connects Ssl_session_cache_hits Ssl_session_cache_misses Ssl_session_cache_overflows Ssl_session_cache_size Ssl_session_cache_timeouts Ssl_sessions_reused Ssl_used_session_cache_entries Ssl_verify_depth Ssl_verify_mode Table_locks_immediate Table_locks_waited Threads_cached Threads_connected Threads_created Threads_running Uptime ); =item genconfig() Generate a "Defaults" config file. =cut sub genconfig { print <<'END'; Target --default-- directory-desc = "MySQL Server Status" long-desc = "Counters for the essential SQL statements." mysql-stat-cmd = "%auto-base%/../cricket/util/mysql-status" mysql-host = "%snmp-host%" mysql-user = '' mysql-pass = '' END my $count = 0; foreach my $sname (@MYSQL_VARS) { print "datasource $sname\n"; print " ds-source = \"exec:$count:%mysql-stat-cmd% %mysql-host% %mysql-user% %mysql-pass%\"\n"; print " rrd-min = 0\n"; if (exists $TYPE_EXCEPT{$sname}) { print " rrd-ds-type = $TYPE_EXCEPT{$sname}\n" } else { print " rrd-ds-type = DERIVE\n" } print "\n"; $count++; } print "\ntargettype mysql-basic\n"; print ' ds = "' . join(", ", @MYSQL_VARS) . "\"\n\n"; } =item gengraphs() Generate the "view" section of a Defaults file with some of my favorite graphs. =cut sub gengraphs { print <<'END'; view = "QueryStats: Com_select Com_insert Com_update Com_delete Com_replace, Temps: Created_tmp_tables Created_tmp_disk_tables Created_tmp_files, SelectHandler: Handler_read_key Handler_read_rnd_next Handler_read_next Handler_read_rnd Handler_read_first Handler_read_prev, SelectQueries: Com_select Select_range Select_full_join Select_full_range_join Select_range_check Select_scan, Sorts: Sort_range Sort_merge_passes Sort_scan, LockingAndSlow: Slow_launch_threads Slow_queries Table_locks_waited, QueryCache: Qcache_not_cached Qcache_hits Qcache_inserts Qcache_lowmem_prunes" graph Com_select color = D8ACE0 legend = "Select" draw-as = AREA graph Com_insert color = FF7D00 legend = "Insert" draw-as = LINE1 graph Com_update color = 4444FF legend = "Update" draw-as = LINE1 graph Com_delete color = FF0000 legend = "Delete" draw-as = LINE1 graph Com_replace color = 000000 legend = "Replace" draw-as = LINE1 graph Created_tmp_tables color = FFAB00 legend = "Temp Tables" draw-as = AREA graph Created_tmp_disk_tables color = 0000FF legend = "Temp Disk Tables" draw-as = LINE2 graph Created_tmp_files color = 00FF00 legend = "Temp Files" draw-as = LINE1 graph Handler_read_key color = 00A0C1 legend = "Read Key" draw-as = AREA graph Handler_read_rnd color = 942D0C legend = "Read Rnd" draw-as = LINE1 graph Handler_read_rnd_next color = FF0000 legend = "Read Rnd Next" draw-as = LINE1 graph Handler_read_first color = 35962B legend = "Read First" draw-as = LINE1 graph Handler_read_next color = EA8F00 legend = "Read Next" draw-as = LINE1 graph Handler_read_prev color = 8D00BA legend = "Read Previous" draw-as = LINE2 graph Sort_merge_passes color = DA4725 legend = "Merge Passes" draw-as = LINE2 graph Sort_range color = 157419 legend = "Range" draw-as = AREA graph Sort_scan color = 4444FF legend = "Scan" draw-as = LINE1 graph Select_full_join color = FF7D00 legend = "Full Join" draw-as = LINE1 graph Select_full_range_join color = 4444FF legend = "Full Range Join" draw-as = LINE1 graph Select_range color = 00A0C1 legend = "Range" draw-as = LINE1 graph Select_range_check color = F51D30 legend = "Range Check" draw-as = LINE1 graph Select_scan color = 000000 legend = "Scan" draw-as = LINE1 graph Table_locks_waited color = EA8F00 legend = "Table Locks Waited" draw-as = LINE2 graph Slow_launch_threads color = DA4725 legend = "Slow Launch Threads" draw-as = LINE1 graph Slow_queries color = 35962B legend = "Slow Queries" draw-as = LINE1 graph Qcache_hits color = EAAF00 legend = "Hits" draw-as = LINE1 graph Qcache_inserts color = 0000FF legend = "Inserts" draw-as = LINE1 graph Qcache_lowmem_prunes color = FF0000 legend = "Prunes" draw-as = LINE1 graph Qcache_not_cached color = D8ACE0 legend = "Not Cached" draw-as = AREA END } =item genlist(DBH) Generate a @MYSQL_VARS array declaration by connecting to a host and figuring out what variables it hands back. You can use this to quickly modify this script (cut 'n paste the result) as new variables come show up in new versions of MySQL. =cut sub genlist { my ($dbh) = @_; my $status = $dbh->selectall_arrayref("SHOW STATUS"); print "my \@MYSQL_VARS =\n qw("; foreach my $r (@$status) { next if (exists $SKIP_VARS{$r->[0]}); print $r->[0] . "\n "; } print ");\n" } =item showstats() Show the statistics from SHOW STATUS in the order specified by the @MYSQL_VARS array. =cut sub showstats { my ($dbh) = @_; my %stats = (); my $status = $dbh->selectall_arrayref("SHOW STATUS"); foreach my $r (@$status) { $stats{$r->[0]} = $r->[1]; } # Iterate using MYSQL_VARS to ensure consistent ordering and content foreach my $s (@MYSQL_VARS) { my $thisstat = 0; if (defined $stats{$s} && $stats{$s} =~ m/^\d+$/) { # Looks like a RRD-worthy item! $thisstat = $stats{$s}; } print "$thisstat\t$s\n"; } } sub getConnection { my ($dbhost, $dbuser, $dbpass) = @_; my $dsn = "DBI:mysql:host=$dbhost\n"; return DBI->connect($dsn, $dbuser, $dbpass, {'RaiseError' => 1}); } =back =cut ## MAIN my $help = 0; my $do_cf = 0; # Spit out a Cricket config stub? my $do_longcf = 0; # Also include the graph definitions? my $do_list = 0; # Generate the list of variables? GetOptions('c|config' => \$do_cf, 'C|config-long' => \$do_longcf, 'l|list' => \$do_list, 'h|help' => \$help) or pod2usage(1); pod2usage(2) if ($help); if ($do_cf or $do_longcf) { genconfig(); if ($do_longcf) { gengraphs(); } } else { my ($dbhost, $dbuser, $dbpass) = @ARGV; my $dbh = getConnection($dbhost, $dbuser, $dbpass); if ($do_list) { genlist($dbh); } else { showstats($dbh); } $dbh->disconnect(); }