Monitor number of active connections to MySQL using Nagios

I need to monitor whether the number of active connections against a MySQL server is within a “reasonable” range.

For all my monitoring needs, I use Nagios. Of course, Nagios offers a MySQL monitoring plugin, however, it does not suit my needs. It only allows you to perform queries on tables, which doesn’t allow me to retrieve the number of current connections to the database server (or does it?!).

Therefore, I rolled my own plugin. Here it is:

#!/usr/bin/perl -w

use strict;
use Getopt::Std;
use lib "/usr/lib/nagios/plugins";
use utils qw(%ERRORS);

use vars qw/ %opt /;
sub debug($);

getopts('c:dhp:u:w:', \%opt);

if (exists $opt{h}) {
    usage();
    exit(0);
}

my $debug = 0;
if (exists $opt{d}) {
    print "Enabling debug mode...\n";
    $debug = 1;
}

my $critical_threshold = 10;
if (exists $opt{c}) {
    $critical_threshold = $opt{c};
}
debug("\$critical_threshold=$critical_threshold\n");

my $warn_threshold = 5;
if (exists $opt{w}) {
    $warn_threshold = $opt{w};
}
debug("\$warn_threshold=$warn_threshold\n");

my $username = "monitoring";
if (exists $opt{u}) {
    $username = $opt{u};
}

my $password = "monitoring";
if (exists $opt{p}) {
    $username = $opt{p};
}

my $query_output = `/bin/echo "SHOW GLOBAL STATUS LIKE 'Threads_connected';" \| /usr/bin/mysql -u $username -p$password \| /bin/grep "Threads_connected"`;
debug("\$query_output=\"$query_output\"\n");
$query_output =~ /^Threads_connected\s+(\d+)\s+$/;
my $threads_connected = $1;
debug("\$threads_connected=$threads_connected\n");

if ($threads_connected > $critical_threshold) {
    print "Critical: $threads_connected active connections\n";
    exit $ERRORS{'CRITICAL'}
} elsif ($threads_connected > $warn_threshold) {
    print "Warning: $threads_connected active connections\n";
    exit $ERRORS{'WARNING'}
} else {
    print "OK: $threads_connected active connections\n";
    exit $ERRORS{'OK'}
}

###########################################################################

sub usage {
    if (@_ == 1) {
	print "$0: $_[0].\n";
    }
    print < < "EOF";
Usage: $0 [options]
  -c THRESHOLD
     critical threshold for number of active connections (default: 10)
  -d
     enable debug mode (mutually exclusive to -q)
  -h
     display usage information
  -p PASSWORD
     The password to use when connecting to the server.
  -u USERNAME
     The MySQL username to use when connecting to the server.
  -w THRESHOLD
     Warning threshold for number of active connections (default: 5)
EOF
}

sub debug($) {
    if ($debug) {
	print STDERR $_[0];
    }
}

Admittedly it’s very simple, but it works. 🙂

Update: There seems to be a bug in WordPress that “gobbles” characters from out of the “pre” section above. I don’t know how to make WordPress keep all characters I paste into the “pre” section and render them correctly. Therefore I have placed the script in a static section of my homepage. Download the file here.

18 thoughts on “Monitor number of active connections to MySQL using Nagios”

  1. ./check_mysql_query -H -u nagios -p uCie9qua -q “show status like ‘Threads_connected’;”
    QUERY CRITICAL: Is not a numeric – ‘Threads_connected’

    That’s super close but I can’t figure out the “not a numeric” part.

    Any ideas?

  2. @Nathan: The Problem is that there is a bug in WordPress which ruins the script quoted in “pre” tags — thus certain characters are lost.

    I’ve linked the script to my original post, so that you can download it. Should you make any improvements, I’d like to hear from you. Thanks!

  3. Nice work. One thing, though:

    $ ./check_mysql_conn.pl
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
    Use of uninitialized value in concatenation (.) or string at ./check_mysql_conn.pl line 50.
    Use of uninitialized value in numeric gt (>) at ./check_mysql_conn.pl line 52.
    Use of uninitialized value in numeric gt (>) at ./check_mysql_conn.pl line 52.
    Use of uninitialized value in concatenation (.) or string at ./check_mysql_conn.pl line 59.
    OK: active connections
    $ echo $?
    0

    (Obviously there’s no SQL server running on that box, but the plugin still returns OK.

    Inability to connect should cause the plugin to return unknown or critical or warning.. something other than OK :).

    The problem is that on line 48, when you capture the \d+ from the line of output, there is no check to make sure that the regex matched and $1 actually exists. Something like this might help:

    unless ($query_output =~ /^Threads_connected\s+(\d+)\s+$/) {
    print “Unable to read output from MySQL\n”;
    exit $ERRORS{‘CRITICAL’};
    }
    my $threads_connected = $1;

    $ ./check_mysqlconnections
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
    Unable to read output from MySQL
    $ echo $?

  4. @ian: Thanks for your contribution.

    I’ve indeed overlooked the fact that the client might be unable to connect to the server and handle it apropriately. I’ve included your code into my little script and uploaded it to the webserver, so that the above link contains the newest version.

  5. Hi,

    I corrected an error in use of parameter password and and added an option to tell the plugin on which host the mysql is running (if not localhost).
    Please contact me by eMail, if You are interested in these changes.

    Berny

  6. Re: check_mysql doesn’t support tracking the currently running threads.

    check_mysql does show that, if you pass -S (or –check-slave). Sure, it isn’t completely desirable to show the complete load of information if you just wish to parse the amount of current running threads.

    Also, if you want a completed version of the plugin (like what Berny added $host and perfdata, and $port and $socket), you’ll be finding it here: http://chrischie.users.barfoo.org/check_mysql_connections.pl

  7. Below find an updated version of my script with Berny’s extensions. It also includes a proper license (the GPL). You can download the updated version from this URL.

    #!/usr/bin/perl -w
    
    use strict;
    use Getopt::Std;
    use lib "/usr/lib/nagios/plugins";
    use utils qw(%ERRORS);
    
    use vars qw/ %opt /;
    sub debug($);
    
    getopts('c:dhH:Lp:P:S:u:w:V', \%opt);
    
    if (exists $opt{h}) {
        usage();
        exit(0);
    }
    
    if (exists $opt{V}) {
        print '$Id: check_mysql_conn.pl 27 2009-06-13 19:14:27Z rabe $', "\n";
        exit(0);
    }
    
    if (exists $opt{L}) {
        print < < "EOF";
    Copyright (C) 2008-2009 Ralf G. R. Bergs
    
    Thanks for bugfixes and feature updates to Ian K. and Bernd
    Stroessenreuther.
    
    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 3 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, see .
    EOF
        exit(0);
    }
    
    my $debug = 0;
    if (exists $opt{d}) {
        print "Enabling debug mode...\n";
        $debug = 1;
    }
    
    my $warn_threshold = 5;
    if (exists $opt{w}) {
        $warn_threshold = $opt{w};
    }
    debug("\$warn_threshold=$warn_threshold\n");
    
    my $critical_threshold = 10;
    if (exists $opt{c}) {
        $critical_threshold = $opt{c};
    }
    debug("\$critical_threshold=$critical_threshold\n");
    
    my $username = "monitoring";
    if (exists $opt{u}) {
        $username = $opt{u};
    }
    
    my $password = "monitoring";
    if (exists $opt{p}) {
        $password = $opt{p};
    }
    
    my $host = "127.0.0.1";
    if (exists $opt{H}) {
    	$host = $opt{H};
    }
    
    my $port = 3306;
    if (exists $opt{P}) {
    	$port = $opt{P};
    }
    
    my $socket = "";
    if (exists $opt{S}) {
    	$socket = $opt{S};
    }
    
    my $cmdline = "/usr/bin/mysql -u $username -p$password";
    if ($socket eq "") {
    	$cmdline = "$cmdline -h $host -P $port";
    } else {
    	$cmdline = "$cmdline -S $socket";
    }
    
    debug("\$cmdline=\"$cmdline\"\n");
    my $query_output = `/bin/echo "SHOW GLOBAL STATUS LIKE 'Threads_connected';" \| $cmdline 2>/dev/null \| /bin/grep "Threads_connected"`;
    debug("\$query_output=\"$query_output\"\n");
    unless ($query_output =~ /^Threads_connected\s+(\d+)\s+$/) {
        print "Unknown: Unable to read output from MySQL\n";
        exit $ERRORS{'UNKNOWN'};
    }
    
    my $threads_connected = $1;
    debug("\$threads_connected=$threads_connected\n");
    
    my $perfdata = "mysql_connections=$threads_connected;$warn_threshold;$critical_threshold;0;0;";
    
    if ($threads_connected > $critical_threshold) {
        print "Critical: $threads_connected active connections|$perfdata\n";
        exit $ERRORS{'CRITICAL'}
    } elsif ($threads_connected > $warn_threshold) {
        print "Warning: $threads_connected active connections|$perfdata\n";
        exit $ERRORS{'WARNING'}
    } else {
        print "OK: $threads_connected active connections|$perfdata\n";
        exit $ERRORS{'OK'}
    }
    
    
    
    ###########################################################################
    
    sub usage {
        if (@_ == 1) {
    	print "$0: $_[0].\n";
        }
        print < < "EOF";
    Usage: $0 [options]
      -w THRESHOLD
         Warning threshold for number of active connections (default: 5)
      -c THRESHOLD
         critical threshold for number of active connections (default: 10)
      -H HOST
      	 Connect to TCP address/DNS name (default: 127.0.0.1).
      -P PORT
      	 Use this alternate TCP port (default: 3306).
      -S SOCKET
      	 Use this MySQL socket instead of the default/or TCPIP.
      -u USERNAME
         The MySQL username to use when connecting to the server.
      -p PASSWORD
         The password to use when connecting to the server.
      -d
         enable debug mode (mutually exclusive to -q)
      -h
         display usage information
      -L
         display license
      -V
         display version number      
    EOF
    }
    
    sub debug($) {
        if ($debug) {
    	print STDERR $_[0];
        }
    }
    
  8. Hi

    I wanted to be able to take the password from .my.cnf to avoid exposing it in the command line. So I made a few quick changes to your code to do that.

    Are you interested in me feeding them back to you?

  9. @Paul: Yes, I am. Please send me your update and I will include it. Thanks!

  10. need HowTo to Use.

    ./check_mysql_conn.pl -H -u root -p MyPassw -q

    Can’t locate utils.pm in @INC (@INC contains: /usr/lib/nagios/plugins /usr/lib/perl5/5.8.8/i586-linux-thread-multi /usr/lib/perl5/5.8.8 /usr/lib/perl5/site_perl/5.8.8/i586-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i586-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl .) at ./check_mysql_conn.pl line 6.
    BEGIN failed–compilation aborted at ./check_mysql_conn.pl line 6.

  11. @Andy: You need nagios-plugins-basic installed (this is how the package is named in Debian, it should give you an idea what to install for other distros).

  12. can u please explain me how to configure this plugin in icinga machine(ubuntu 10.04lts) in detail from starting to end step by step

  13. @gayatri Sorry, but before I read your reply I didn’t even know what “icinga” is — now I know, but I can’t help you with how to configure my plugin in Icinga…

    As Icinga seems to be a fork of Nagios or otherwise closely related the instructions given above for Nagios might also work in a similar fashion for Icinga. Try to download the script and run it on your machine. If you can get it to execute properly when launching it manually from a shell prompt without any missing modules then you’re almost there. The “only” thing you then need to do is integrated it into Icinga, but that’s definitely something I cannot help you with. Sorry!

  14. can u please tell me how to run the script in my p.c.. i copied the script into the note pad and saved with .pl extension….after that can u guide me how to check or run the script in my p.c

  15. @gayatri I’m afraid I can’t give you extensive guidance for how to run the script on your PC. You need to have Perl installed, plus the Nagios plugins which are required by my script. Since you seem to be fairly new to Perl, I suggest you either take a tutorial first or ask someone who is a little bit more experienced. Sorry that I can’t help you further.

Comments are closed.