Using Nagios as a MySQL Performance Profiler
Everybody knows than Nagios can be used as a service monitor to monitor things like Load Averages, MySQL Replication Status, RAID Array States, etc… Fewer know that there are plug-ins to monitor MySQL Performance Status, such as check_mysql_perf. Fewer still utilize Nagios’ built-in triggering mechanism to execute an additional script on the event of a critical alert.
It is not uncommon to experience a load spike in the middle of the night, only to discover that it immediately cleared itself. Many times, retroactive log analysis will not reveal anything out of the ordinary. In order to get an immediate snapshot of what is going on at the time of a Nagios alert, simply call a script via the event handler similar to this one:
#!/usr/local/bin/bash
Usage() {
echo 1>&2 "Usage: $0 -h <hostname> -u <username> -p <password> -s <state> -t <state_type>"
exit 1
}
MYSQL="/usr/local/bin/mysql"
MAIL="/usr/bin/mail"
RECIPS="dba@domain.com"
PERL="/usr/bin/perl"
[ $# -ne 10 ] && Usage
while getopts "h:u:p:s:t:" option; do
case $option in
h ) HOSTNAME="$OPTARG";;
u ) USERNAME="$OPTARG";;
p ) PASSWORD="$OPTARG";;
s ) STATE="$OPTARG";;
t ) STATE_TYPE="$OPTARG";;
* ) Usage;; # DEFAULT
esac
done
[ $STATE = "CRITICAL" -a $STATE_TYPE = "HARD" ] || exit 0
$MYSQL -h$HOSTNAME -u$USERNAME -p$PASSWORD --connect_timeout=10 -ss -e'SHOW FULL PROCESSLIST' | $PERL -pe 's/\\n/\n/g' | $MAIL -s"$HOSTNAME post-alert processlist" $RECIPS
exit 0
This will email dba@domain.com with the output of SHOW FULL PROCESSLIST;
This type of profiling is especially useful because it can easily be turned on and off without affecting your production site (and it is very inexpensive to execute). Don’t forget to grant your Nagios user the PROCESS privilege!
