From 9583889456e889763d27eed9d88582af6df7dd2e Mon Sep 17 00:00:00 2001 From: Micah Anderson Date: Mon, 1 Nov 2010 19:43:23 -0400 Subject: add mysql munin graphs and adjust the file distribution to match the right location of where they are --- files/munin/mysql_connections | 125 +++++++++++++++++++++++++++++ files/munin/mysql_qcache | 123 +++++++++++++++++++++++++++++ files/munin/mysql_qcache_mem | 129 ++++++++++++++++++++++++++++++ files/munin/mysql_size_all | 179 ++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 556 insertions(+) create mode 100644 files/munin/mysql_connections create mode 100644 files/munin/mysql_qcache create mode 100644 files/munin/mysql_qcache_mem create mode 100644 files/munin/mysql_size_all (limited to 'files') diff --git a/files/munin/mysql_connections b/files/munin/mysql_connections new file mode 100644 index 0000000..658b401 --- /dev/null +++ b/files/munin/mysql_connections @@ -0,0 +1,125 @@ +#!/usr/bin/perl +# +# This plugin is based off of the Connection Usage +# section of the MySQL Connection Health Page +# +# http://dev.mysql.com/doc/administrator/en/mysql-administrator-health-connection-health.html +# +# To enable, link mysql_connections to this file. E.g. +# +# ln -s /usr/share/node/node/plugins/mysql_connections /etc/munin/plugins/mysql_connections +# +# Revision 1.0 2007/08/03 +# Created by Justin Shepherd +# +# Parameters: +# +# config +# autoconf +# +# Configuration variables +# +# mysqlopts - Options to pass to mysql +# mysqladmin - Override location of mysqladmin +# warning - Override default warning limit +# critical - Override default critical limit +# +#%# family=auto +#%# capabilities=autoconf + +use strict; + +# Define the mysqladmin paths, and commands +my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin"; +my $TEST_COMMAND = "$MYSQLADMIN $ENV{mysqlopts} extended-status"; +my $MYSQL_VARIABLES = "$MYSQLADMIN $ENV{mysqlopts} extended-status variables"; +my $warning = $ENV{warning} || "80"; +my $critical = $ENV{critical} || "90"; + +# Pull in any arguments +my $arg = shift(); + +# Check to see how the script was called +if ($arg eq 'config') { + print_graph_information(); + exit(); +} elsif ($arg eq 'autoconf') { + if (test_service()) { print "yes\n"; } + else { print "no\n"; } + exit; +} else { + # Define the values that are returned to munin + my ($available, $current, $upper_limit) = (0,0,0); + + # Gather the values from mysqladmin + $current = poll_variables($MYSQL_VARIABLES,"Threads_connected"); + $upper_limit = poll_variables($MYSQL_VARIABLES,"max_connections"); + $available = $upper_limit - $current; + + # Return the values to Munin + print "current.value $current\n"; + print "available.value $available\n"; +} + + +sub poll_variables { + my $command = shift; + my $expression = shift; + my $ret = 0; + open(SERVICE, "$command |") + or die("Coult not execute '$command': $!"); + while () { + my ($field, $value) = (m/(\w+).*?(\d+(?:\.\d+)?)/); + next unless ($field); + if ($field eq $expression ) { + $ret = "$value"; + } + } + close(SERVICE); + return $ret; +} + + +sub print_graph_information { +print </dev/null 2>/dev/null"); + if ($? == 0) + { + system ("$TEST_COMMAND >/dev/null 2>/dev/null"); + if ($? == 0) + { + print "yes\n"; + $return = 0; + } + else + { + print "no (could not connect to mysql)\n"; + } + } + else + { + print "no (mysqladmin not found)\n"; + } + exit $return; +} diff --git a/files/munin/mysql_qcache b/files/munin/mysql_qcache new file mode 100644 index 0000000..b074436 --- /dev/null +++ b/files/munin/mysql_qcache @@ -0,0 +1,123 @@ +#!/usr/bin/perl +# +# Copyright (C) 2006 - Rodolphe Quiedeville +# Copyright (C) 2003-2004 - Andreas Buer +# +# 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. +# +# $Log$ +# Revision 1.0 2006/04/26 16:04:01 rodo +# Created by Rodolphe Quiedeville +# +# Parameters: +# +# config +# autoconf +# +# Configuration variables +# +# mysqlopts - Options to pass to mysql +# mysqladmin - Override location of mysqladmin +# +#%# family=auto +#%# capabilities=autoconf + +use strict; + +my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin"; +my $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} extended-status"; + +my %WANTED = ( "Qcache_queries_in_cache" => "queries"); + +my %WANTEDTYPE = ( "Qcache_queries_in_cache" => "GAUGE"); + +my $arg = shift(); + +if ($arg eq 'config') { + print_config(); + exit(); +} elsif ($arg eq 'autoconf') { + unless (test_service() ) { + print "yes\n"; + } else { + print "no\n"; + } + exit; +} + + +open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + +while () { + my ($k, $v) = (m/(\w+).*?(\d+(?:\.\d+)?)/); + next unless ($k); + if (exists $WANTED{$k} ) { + print("$WANTED{$k}.value $v\n"); + } +} + +close(SERVICE); + + +sub print_config { + + my $num = 0; + + print('graph_title MySQL Queries in cache +graph_args --base 1000 +graph_vlabel queries +graph_category mysql +graph_info Plugin available at http://rodolphe.quiedeville.org/hack/munin/ +'); + + for my $key (keys %WANTED) { + my $title = $WANTED{$key}; + print("$title.label ${title}\n", + "$title.min 0\n", + "$title.type ".$WANTEDTYPE{$key}."\n", + "$title.max 500000\n", + "$title.draw ", ($num) ? "STACK" : "AREA" , "\n", + ); + $num++; + } + +} + + +sub test_service { + + my $return = 1; + + system ("$MYSQLADMIN --version >/dev/null 2>/dev/null"); + if ($? == 0) + { + system ("$COMMAND >/dev/null 2>/dev/null"); + if ($? == 0) + { + print "yes\n"; + $return = 0; + } + else + { + print "no (could not connect to mysql)\n"; + } + } + else + { + print "no (mysqladmin not found)\n"; + } + exit $return; +} diff --git a/files/munin/mysql_qcache_mem b/files/munin/mysql_qcache_mem new file mode 100644 index 0000000..0fe06c3 --- /dev/null +++ b/files/munin/mysql_qcache_mem @@ -0,0 +1,129 @@ +#!/usr/bin/perl +# +# Copyright (C) 2006 - Rodolphe Quiedeville +# Copyright (C) 2003-2004 - Andreas Buer +# +# 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. +# +# $Log$ +# Revision 1.0 2006/04/28 09:04:01 rodo +# Add lower limit fixed to 0 +# +# Revision 1.0 2006/04/26 16:04:01 rodo +# Created by Rodolphe Quiedeville +# +# Parameters: +# +# config +# autoconf +# +# Configuration variables +# +# mysqlopts - Options to pass to mysql +# mysqladmin - Override location of mysqladmin +# +#%# family=auto +#%# capabilities=autoconf + +use strict; + +my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin"; +my $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} extended-status"; +my $COMMANDSIZE = "$MYSQLADMIN $ENV{mysqlopts} variables"; + +my %WANTED = ( "Qcache_free_memory" => "free" ); + +my $arg = shift(); + +if ($arg eq 'config') { + print_config(); + exit(); +} elsif ($arg eq 'autoconf') { + unless (test_service() ) { + print "yes\n"; + } else { + print "no\n"; + } + exit; +} + +my ($free, $used) = (0,0); + +open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + +while () { + my ($k, $v) = (m/(\w+).*?(\d+(?:\.\d+)?)/); + next unless ($k); + if (exists $WANTED{$k} ) { + $free = $v; + print("$WANTED{$k}.value $v\n"); + } +} +close(SERVICE); + +open(SERVICE, "$COMMANDSIZE |") + or die("Coult not execute '$COMMANDSIZE': $!"); + +while () { + my ($k, $v) = (m/(\w+).*?(\d+(?:\.\d+)?)/); + + next unless ($k); + if ($k eq "query_cache_size" ) { + print("used.value ",($v-$free),"\n"); + } +} +close(SERVICE); + +sub print_config { + + print('graph_title MySQL Queries Cache Size +graph_args --base 1024 -l 0 +graph_vlabel bytes +graph_category mysql +graph_order used free +graph_total Total +graph_info Plugin available at http://rodolphe.quiedeville.org/hack/munin/ +used.label Used +used.draw AREA +free.label Free +free.draw STACK +'); +} + +sub test_service { + + my $return = 1; + + system ("$MYSQLADMIN --version >/dev/null 2>/dev/null"); + if ($? == 0) + { + system ("$COMMAND >/dev/null 2>/dev/null"); + if ($? == 0) + { + print "yes\n"; + $return = 0; + } + else + { + print "no (could not connect to mysql)\n"; + } + } + else + { + print "no (mysqladmin not found)\n"; + } + exit $return; +} diff --git a/files/munin/mysql_size_all b/files/munin/mysql_size_all new file mode 100644 index 0000000..f5954ad --- /dev/null +++ b/files/munin/mysql_size_all @@ -0,0 +1,179 @@ +#!/usr/bin/perl +# +# Copyright (C) 2007 - Rodolphe Quiedeville +# Copyright (C) 2003-2004 - Andreas Buer +# +# 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. +# +# $Log$ +# Revision 1.1 2007/01/17 10:41:01 rodo +# Change incorrect family +# +# Revision 1.0 2007/01/16 15:57:01 rodo +# Created by Rodolphe Quiedeville +# +# Parameters: +# +# config +# autoconf +# +# Configuration variables +# +# mysqlopts - Options to pass to mysql +# mysqladmin - Override location of mysqladmin +# +#%# family=manual +#%# capabilities=autoconf + +use strict; + +# unless ($0 =~ /mysql_size(?:_([^_]+)|)_(.+)\s*$/) +# { +# die "Could not parse name $0.\n"; +# } +# my $db = $2; + +my $COMMAND; +my $MYSQLADMIN = $ENV{mysqladmin} || "mysql"; + +my %WANTED = ( "Index" => "index", + "Datas" => "datas", + ); + +my $arg = shift(); + +if ($arg eq 'config') { + print_config(); + exit(); +} elsif ($arg eq 'autoconf') { + unless (test_service() ) { + print "yes\n"; + } else { + print "no\n"; + } + exit; +} + +sub getDBList; +foreach my $db (getDBList()) { + + my $datas = 0; + my $indexes = 0; + my (@infos,$info,$i_data,$i_index); + + $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} $db -e 'show table status;' | head -n 1"; + + open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + + while () { + (@infos) = split; + } + close(SERVICE); + + my $i = 0; + foreach $info (@infos) { + $i++; + if ($info eq 'Data_length') { + $i_data = $i; + next; + } + if ($info eq 'Index_length') { + $i_index = $i; + last; + } + } + my $total_size = 0; + if ($i_data>0 && $i_index>0) { + $COMMAND = "$MYSQLADMIN $ENV{mysqlopts} $db -e 'show table status;' | cut -f $i_data,$i_index | grep -v leng"; + + open(SERVICE, "$COMMAND |") + or die("Coult not execute '$COMMAND': $!"); + + while () { + (m/(\d+).*?(\d+(?:\.\d+)?)/); + $datas += $1; + $indexes += $2; + } + close(SERVICE); + + $total_size = $datas+$indexes; + } + print("$db.value $total_size\n"); +# print("datas.value $datas\n"); +# print("index.value $indexes\n"); +} + + +sub print_config { + + my $num = 0; + + my @dbs = getDBList; + + print("graph_title MySQL databases size\n"); + print ('graph_args --base 1024 -l 0 +graph_vlabel bytes +graph_category mysql +graph_info Plugin available at http://rodolphe.quiedeville.org/hack/munin/ +'); + + for my $db (@dbs) { + my $title = "$db"; + print("$title.label ${title}\n", + "$title.min 0\n", + "$title.type GAUGE\n", + "$title.draw ", ($num) ? "STACK" : "AREA" , "\n", + ); + $num++; + } +} + + +sub test_service { + + my $return = 1; + + system ("$MYSQLADMIN --version >/dev/null 2>/dev/null"); + if ($? == 0) + { + system ("$COMMAND >/dev/null 2>/dev/null"); + if ($? == 0) + { + print "yes\n"; + $return = 0; + } + else + { + print "no (could not connect to mysql)\n"; + } + } + else + { + print "no (mysqladmin not found)\n"; + } + exit $return; +} + +sub getDBList { + my @dbs; + foreach my $f (glob("/var/lib/mysql/*")) { + if (-d $f) { + $f =~ s!.*/!!; + @dbs[$#dbs+1]=$f }; + } + return @dbs; +} + -- cgit v1.2.3