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_per_user /etc/munin/plugins/mysql_connections_per_user

#

# 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

#

# Revision 3.0 2014/06/11

# Fix now showing if not connected by transilvlad@gmail.com

# Other fixes and performance by transilvlad@gmail.com

#

# Parameters:

#

#   config

#   autoconf

#

# Configuration variables

#

#   mysqlopts     - Options to pass to mysql

#   mysqlcli      - Override location of mysql

#   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 $MYSQLCLI     = $ENV{mysqlcli} || "mysql";

my $TEST_COMMAND = "$MYSQLCLI $ENV{mysqlopts} -N -B -e \"SELECT NOW();\"";

my $MYSQL_QUERY  = "$MYSQLCLI $ENV{mysqlopts} -N -B -e \"SELECT SUM(NO) AS NO, USER FROM (SELECT 1 AS NO, USER FROM INFORMATION_SCHEMA.PROCESSLIST UNION ALL SELECT DISTINCT 0 AS NO, User AS USER FROM mysql.user WHERE User != '') AS Q GROUP BY USER ORDER BY NO DESC;\"";

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

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

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

my $numthreads   = 0;

# Pull in any arguments

my $arg = shift();

# Check to see how the script was called

if ($arg eq 'config') {

    print_graph_information();

} elsif ($arg eq 'autoconf') {

    if (test_service()) { print "yes\n"; }

    else { print "no\n"; }

} else {

    print_graph_data();

}

exit;

sub print_graph_data() {

    # Define the values that are returned to munin

    # Return the values to Munin

    my $counts = count_thread_users();

    my %counts = %{$counts};

    sub valsort {

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

    }

    my $i = 0;

    my $total = 0;

    my $print_user = "";

    foreach my $user (reverse sort { $counts{$a} <=> $counts{$b} } keys %counts) {

        last if $i++ >= $numusers;

        if ($user eq "system user") {

            #skip internal user that manages binlog operations for slave servers.

            next;

        }

        $total += $counts{$user};

        $print_user = $user;

        if($print_user eq "root") {

            $print_user = "root_";

        }

        if (  $print_user =~ /[^A-Za-z0-9]/ ) {

            $print_user =~ s/[^A-Za-z0-9]/_/;

        }

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

    }

    my $other = $numthreads - $total;

    if($other < 0) {

        $other = 0;

    }

    print "other.value $other\n";

}

sub print_graph_information {

    print <<EOM;

graph_title MySQL Connections per user

graph_args --base 1000 --lower-limit 0

graph_vlabel Connections

graph_info The number of current connexions per user.

graph_category db

graph_total Total

EOM

    my $counts = count_thread_users();

    my %counts = %{$counts};

    my $stacked = 0;

    sub valsort {

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

    }

    my $i = 0;

    foreach my $user (reverse sort { $counts{$a} <=> $counts{$b} } keys %counts) {

        last if $i++ >= $numusers;

        if ($user eq "system user") {

            #skip internal user that manages binlog operations for slave servers.

            next;

        }

        my $print_user = $user;

        if($print_user eq "root") {

            $print_user = "root_";

        }

        if (  $print_user =~ /[^A-Za-z0-9]/ ) {

            $print_user =~ s/[^A-Za-z0-9]/_/;

        }

        print <<EOM;

$print_user.label $user

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

EOM

        print "$print_user.draw ";

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

        if ($i > 1) {

            print "STACK\n";

        }

        else {

            print "AREA\n";

        }

    }

    print <<EOM;

other.label Others

other.draw STACK

other.info Other connected threads not in the top $numusers

EOM

}

sub count_thread_users {

    my %counts = ();

    open(SERVICE, "$MYSQL_QUERY |")

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

    while (<SERVICE>) {

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

        $user =~ s/^\s+|\s+$//g;

        $counts{$user} = $no;

        $numthreads += $no;

    }

    return \%counts;

}

sub test_service {

    my $return = 1;

    system ("$MYSQLCLI --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 (mysql not found)\n";

    }

    exit $return;

}