Nagios mysql connection count plugin.

Lastnight I switched apache from MPM-prefork to MPM-worker, all worked well.

It wasn’t actually all well, I discovered that some 3rd party php code was using mysql_pconnect which wasn’t disconnecting from the server, so a couple of tweaks to my.cnf and all was looking good again. This left me with a problem, was it really OK and will it remain so?

Thus I decided that I should monitor the connections via nagios. Great I found a perl script over here and gave it the once over. One surprise was that rather than using perl to connect to sql and get the connections it was actually dropping to shell and then parsing the output, had this being pure perl and not dropping to shell I might have left it there, however this is me and not having written a plugin I thought it was about time.

Another thing which was missing was performance data, so with the nagios plugin docs open on one screen and a console on the other I set about the plugin.

#!/bin/bash

# default options
debug=0
critical_threshold=10
warn_threshold=5
hostname='localhost';
username='sqluser'
password='sqlpass'

NAGIOS_PLUGIN_PATH=`dirname $0`

if [ -f $NAGIOS_PLUGIN_PATH/utils.sh ]; then
 . $NAGIOS_PLUGIN_PATH/utils.sh
else
 echo "Unable to load utils.sh"
 exit 3
fi

while getopts c:H:dhp:u:w: OPT $@; do

case $OPT in
 h)
  echo "Usage: $0 [options]
   -H hostname
       host to check the connections on.
   -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)"
  ;;
 d)
  echo "Enabling debug mode..."
  debug=1
  ;;
 c)
  critical_threshold=$OPTARG
  ;;
 w)
  warn_threshold=$OPTARG
  ;;
 u)
  username=$OPTARG
  ;;
 p)
  password=$OPTARG
  ;;
 H)
  hostname=$OPTARG
  ;;
 esac
done

if [ $debug -eq 1 ]; then
 echo critical_threshold=$critical_threshold
 echo warn_threshold=$warn_threshold
 echo username=$username
 echo password=$password
 echo hostname=$hostname
fi

sqlconns=`/bin/echo "SHOW GLOBAL STATUS LIKE 'Threads_connected';" | /usr/bin/mysql -h $hostname -u $username -p$password | /bin/grep "Threads_connected"| /usr/bin/awk '{print $2}'`

[ $debug -eq 1 ] && echo "sqlconns=\"$sqlconns\""
 if [ -n "$sqlconns" ]; then
  if [ $sqlconns -gt $critical_threshold ];then
  echo "Critical: $sqlconns active  connections|connections=$sqlconns;$warn_threshold;$critical_threshold"
  exit $STATE_CRITICAL
 elif [ $sqlconns -gt $warn_threshold ]; then
  echo "Warning: $sqlconns active  connections|connections=$sqlconns;$warn_threshold;$critical_threshold"
  exit $STATE_WARNING
 elif [ $sqlconns -ge 0 ]; then
  echo "OK: $sqlconns active  connections|connections=$sqlconns;$warn_threshold;$critical_threshold"
  exit $STATE_OK
 fi
fi

# failing that something went wrong;
echo "Unknown: Something went wrong"
exit $STATE_UNKNOWN

The bigest hurdle here was getopts, this was my first experience of using it and no problems that I can see.

This has now been running in production for a couple of hours and pnp4nagios is producing a nice graph with of the connection count, if it gets too high I’ll be getting messages to my phone and emails.