Munin contributed plugins

#!/usr/bin/perl

#

# Copyright (C) 2008 Rackspace US, Inc. <http://www.rackspace.com>

#

# 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, see http://www.gnu.org/licenses/gpl.txt

#

#

# 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 <galstrom21@gmail.com>

#

# Revision 2.0 2013/01/02

# Per user support by anarcat@koumbit.org

#

# Parameters:

#

#   config

#   autoconf

#

# Configuration variables

#

#   mysqlopts     - Options to pass to mysql

#   mysqladmin    - Override location of mysqladmin

#   numusers      - Override maximum number of users to display

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

my $MYSQL_VARIABLES = "$MYSQLADMIN $ENV{mysqlopts} extended-status variables";

my $warning = $ENV{warning} || "80";

my $critical = $ENV{critical} || "90";

my $numusers = $ENV{numusers} || 10;

# 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 {

    print_graph_data();

    exit;

}

sub print_graph_data() {

	# Define the values that are returned to munin

	my ($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");

	# Return the values to Munin

        my $threads = get_thread_list();

        my %counts = ();

        my ($thread, $user, $count);

        while (($thread, $user) = each %$threads) {

            $counts{$user} = 0 unless defined($counts{$user});

            $counts{$user}++;

        }

        sub valsort {

            return $$threads{$a} <=> $$threads{$b};

        }

        my $i = 0;

        foreach my $user (sort valsort keys(%counts)) {

            last if $i++ >= $numusers;

            print "$user.value $counts{$user}\n";

        }

	print "current.value $current\n";

	print "limit.value $upper_limit\n";

}

sub poll_variables {

	my $command = shift;

	my $expression = shift;

	my $ret = 0;

	open(SERVICE, "$command |")

  		or die("Coult not execute '$command': $!");

	while (<SERVICE>) {

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

graph_title MySQL Connections

graph_args --base 1000 --lower-limit 0

graph_vlabel Connections

graph_info The number of current connexions per user.

graph_category mysql

graph_total Total

EOM

my $threads = get_thread_list();

my %seen = ();

my ($thread, $user);

while (($thread, $user) = each %$threads) {

    # display user only once

    if (defined($seen{$user})) {

        next;

    }

    else {

        print <<EOM;

$user.label Connexions for user $user

$user.info Number of connexions used by user $user

EOM

        print "$user.draw ";

        # if we already printed an entry, make the next ones stacked

        if (scalar %seen) {

            print "STACK\n";

        }

        else {

            print "AREA\n";

        }

        $seen{$user} = 1;

    }

}

print <<EOM;

current.label In Use

current.draw LINE1

current.info The number of current threads connected

current.warning $warning

current.critical $critical

limit.label Maximum

limit.draw LINE1

limit.info The current value of the "max_connections" variable

EOM

}

sub get_thread_list {

    my %threads = ();

    my $command = 'mysql -N -B -e "SHOW PROCESSLIST;"';

    open(SERVICE, "$command |")

        or die("Could not execute '$command': $!");

    while (<SERVICE>) {

        my ($threadid, $user) = split "\t";

        next unless ($threadid);

        $threads{$threadid} = $user;

    }

    return \%threads;

}

sub test_service {

    my $return = 1;

    system ("$MYSQLADMIN --version >/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;

}