mysql_connections_per_user

#!/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;
}