Ralf’s Blog

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

9 Responses to “Monitor number of active connections to MySQL using Nagios”

1. Nathan Says:
   May 16th, 2007 at 18:28

   ./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. Administrator Says:
   May 22nd, 2007 at 13:14

   @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. ian Says:
   August 2nd, 2007 at 14:46

   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. Administrator Says:
   August 2nd, 2007 at 15:25

   @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. Berny Says:
   June 10th, 2009 at 13:09

   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. Berny Says:
   June 10th, 2009 at 13:35

   Hi,

   also added perfdata now…

   Berny

7. Christian Says:
   June 12th, 2009 at 17:49

   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

8. Administrator Says:
   June 13th, 2009 at 21:12

   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];
       }
   }
   

9. Administrator Says:
   June 27th, 2009 at 21:47

   I have also submitted my plugin to MonitoringExchange.

Leave a Reply