1#!/bin/sh 2 3# ######################################################################## 4# This program is part of $PROJECT_NAME$ 5# License: GPL License (see COPYING) 6# Authors: 7# Baron Schwartz, Roman Vynar 8# ######################################################################## 9 10# ######################################################################## 11# Redirect STDERR to STDOUT; Nagios doesn't handle STDERR. 12# ######################################################################## 13exec 2>&1 14 15# ######################################################################## 16# Set up constants, etc. 17# ######################################################################## 18STATE_OK=0 19STATE_WARNING=1 20STATE_CRITICAL=2 21STATE_UNKNOWN=3 22STATE_DEPENDENT=4 23 24# ######################################################################## 25# Run the program. 26# ######################################################################## 27main() { 28 # Get options 29 for o; do 30 case "${o}" in 31 -C) shift; OPT_CHEK="${1}"; shift; ;; 32 -c) shift; OPT_CRIT="${1}"; shift; ;; 33 --defaults-file) shift; OPT_DEFT="${1}"; shift; ;; 34 -H) shift; OPT_HOST="${1}"; shift; ;; 35 -l) shift; OPT_USER="${1}"; shift; ;; 36 -L) shift; OPT_LOPA="${1}"; shift; ;; 37 -p) shift; OPT_PASS="${1}"; shift; ;; 38 -P) shift; OPT_PORT="${1}"; shift; ;; 39 -S) shift; OPT_SOCK="${1}"; shift; ;; 40 -w) shift; OPT_WARN="${1}"; shift; ;; 41 --version) grep -A2 '^=head1 VERSION' "$0" | tail -n1; exit 0 ;; 42 --help) perl -00 -ne 'm/^ Usage:/ && print' "$0"; exit 0 ;; 43 -*) echo "Unknown option ${o}. Try --help."; exit 1; ;; 44 esac 45 done 46 OPT_CHEK="${OPT_CHEK:-states_count}" 47 if [ -e '/usr/local/etc/nagios/mysql.cnf' ]; then 48 OPT_DEFT="${OPT_DEFT:-/usr/local/etc/nagios/mysql.cnf}" 49 fi 50 if is_not_sourced; then 51 if [ -n "$1" ]; then 52 echo "WARN spurious command-line options: $@" 53 exit 1 54 fi 55 fi 56 57 # Get processlist into a temp file. 58 local TEMP=$(mktemp -t "${0##*/}.XXXXXX") || exit $? 59 trap "rm -f '${TEMP}' >/dev/null 2>&1" EXIT 60 61 case "${OPT_CHEK}" in 62 states_count) 63 OPT_WARN=${OPT_WARN:-16} 64 OPT_CRIT=${OPT_CRIT:-32} 65 66 # Capture a number of types of states, add some together, take the max, 67 # and compare to the threshold. 68 mysql_exec 'SHOW PROCESSLIST\G' > "${TEMP}" 69 if [ $? = 0 ]; then 70 UNAUTH=$(count_mysql_processlist "${TEMP}" "User" "unauthenticated user") 71 LOCKED1=$(count_mysql_processlist "${TEMP}" "State" "Locked") 72 LOCKED2=$(count_mysql_processlist "${TEMP}" "State" "Waiting for .* lock") 73 LOCKED3=$(count_mysql_processlist "${TEMP}" "State" "Table lock") 74 LOCKED4=$(count_mysql_processlist "${TEMP}" "State" "Waiting for table flush") 75 LOCKED5=$(count_mysql_processlist "${TEMP}" "State" "Waiting for tables") 76 COPYIN=$(count_mysql_processlist "${TEMP}" "State" ".*opy.* to.* table.*") 77 STATIS=$(count_mysql_processlist "${TEMP}" "State" "statistics") 78 LOCKED=$((${LOCKED1:-0} + ${LOCKED2:-0} + ${LOCKED3:-0} + ${LOCKED4:-0} + ${LOCKED5:-0})) 79 NOTE="${UNAUTH} unauthenticated, ${LOCKED} locked," 80 NOTE="${NOTE} ${COPYIN} copy to table, ${STATIS} statistics" 81 MAX="$(max "${UNAUTH:-0}" "${LOCKED:-0}" "${COPYIN:-0}" "${STATIS:-0}")" 82 if [ "${MAX:-0}" -gt "${OPT_CRIT}" ]; then 83 NOTE="CRIT $NOTE" 84 elif [ "${MAX:-0}" -gt "${OPT_WARN}" ]; then 85 NOTE="WARN $NOTE" 86 else 87 NOTE="OK $NOTE" 88 fi 89 90 # Build the common perf data output for graph trending 91 PERFDATA="processes=${MAX:-0};${OPT_WARN};${OPT_CRIT};0;" 92 NOTE="$NOTE | $PERFDATA" 93 else 94 NOTE="UNK could not retrieve MySQL processlist" 95 fi 96 ;; 97 max_user_conn) 98 OPT_WARN=${OPT_WARN:-90} 99 OPT_CRIT=${OPT_CRIT:-95} 100 101 # Check if @@max_user_connections is set on MySQL 102 MAX_USER_CONN=$(mysql_exec 'SELECT @@max_user_connections') 103 if [ $? = 0 ]; then 104 if [ ${MAX_USER_CONN:-0} -gt 0 ]; then 105 # Capture a number of connections per user from the processlist, take the max, 106 # and compare to the threshold. 107 mysql_exec 'SHOW PROCESSLIST\G' > "${TEMP}" 108 if [ $? = 0 ]; then 109 MAX_USER=$(cat ${TEMP}|grep User|awk '{print $2}'|sort|uniq -c|sort -n|tail -1) 110 CNT=$(echo ${MAX_USER} | awk '{print $1}') 111 USER=$(echo ${MAX_USER} | awk '{print $2}') 112 MAX=$(expr ${CNT} \* 100 / ${MAX_USER_CONN}) 113 NOTE="User with max connections: ${USER} (${CNT}) = ${MAX}%" 114 if [ "${MAX:-0}" -gt "${OPT_CRIT}" ]; then 115 NOTE="CRIT $NOTE" 116 elif [ "${MAX:-0}" -gt "${OPT_WARN}" ]; then 117 NOTE="WARN $NOTE" 118 else 119 NOTE="OK $NOTE" 120 fi 121 122 # Build the common perf data output for graph trending 123 PERFDATA="max_user_conn=${MAX:-0};${OPT_WARN};${OPT_CRIT};0;100" 124 NOTE="$NOTE | $PERFDATA" 125 else 126 NOTE="UNK could not retrieve MySQL processlist" 127 fi 128 else 129 NOTE="OK @@max_user_connections is not configured." 130 fi 131 else 132 NOTE="UNK could not retrieve @@max_user_connections" 133 fi 134 ;; 135 *) 136 echo "Unknown value for -C: '${OPT_CHEK}'. Consult the documentation."; 137 exit 1; 138 ;; 139 esac 140 141 echo $NOTE 142} 143 144# ######################################################################## 145# Extract a count from MySQL processlist. The arguments are: 146# $1 - file with the processlist. 147# $2 - the column to examine. 148# $3 - the value to count. 149# ######################################################################## 150count_mysql_processlist() { 151 local FILE="${1}" 152 local COL="${2}" 153 local MATCH="${3}" 154 grep -c "^ *${COL}: ${MATCH}" "${FILE}" 155} 156 157# ######################################################################## 158# Find the maximum argument, assuming nonnegative integers. 159# ######################################################################## 160max() { 161 local MAX=0 162 for val; do 163 if [ "${val:-0}" -gt "${MAX}" ]; then 164 MAX="${val}" 165 fi 166 done 167 echo "${MAX:-0}" 168} 169 170# ######################################################################## 171# Execute a MySQL command. 172# ######################################################################## 173mysql_exec() { 174 mysql ${OPT_DEFT:+--defaults-file="${OPT_DEFT}"} \ 175 ${OPT_LOPA:+--login-path="${OPT_LOPA}"} \ 176 ${OPT_HOST:+-h"${OPT_HOST}"} ${OPT_PORT:+-P"${OPT_PORT}"} \ 177 ${OPT_USER:+-u"${OPT_USER}"} ${OPT_PASS:+-p"${OPT_PASS}"} \ 178 ${OPT_SOCK:+-S"${OPT_SOCK}"} -ss -e "$1" 179} 180 181# ######################################################################## 182# Determine whether this program is being executed directly, or sourced/included 183# from another file. 184# ######################################################################## 185is_not_sourced() { 186 [ "${0##*/}" = "pmp-check-mysql-processlist" ] || [ "${0##*/}" = "bash" -a "$_" = "$0" ] 187} 188 189# ######################################################################## 190# Execute the program if it was not included from another file. 191# This makes it possible to include without executing, and thus test. 192# ######################################################################## 193if is_not_sourced; then 194 OUTPUT=$(main "$@") 195 EXITSTATUS=$STATE_UNKNOWN 196 case "${OUTPUT}" in 197 UNK*) EXITSTATUS=$STATE_UNKNOWN; ;; 198 OK*) EXITSTATUS=$STATE_OK; ;; 199 WARN*) EXITSTATUS=$STATE_WARNING; ;; 200 CRIT*) EXITSTATUS=$STATE_CRITICAL; ;; 201 esac 202 echo "${OUTPUT}" 203 exit $EXITSTATUS 204fi 205 206# ############################################################################ 207# Documentation 208# ############################################################################ 209: <<'DOCUMENTATION' 210=pod 211 212=head1 NAME 213 214pmp-check-mysql-processlist - Alert when MySQL processlist has dangerous patterns. 215 216=head1 SYNOPSIS 217 218 Usage: pmp-check-mysql-processlist [OPTIONS] 219 Options: 220 -C CHECK What to alert on; default states_count. 221 Other options: max_user_conn. 222 -c CRIT Critical threshold; default varies. 223 --defaults-file FILE Only read mysql options from the given file. 224 Defaults to /usr/local/etc/nagios/mysql.cnf if it exists. 225 -H HOST MySQL hostname. 226 -l USER MySQL username. 227 -L LOGIN-PATH Use login-path to access MySQL (with MySQL client 5.6). 228 -p PASS MySQL password. 229 -P PORT MySQL port. 230 -S SOCKET MySQL socket file. 231 -w WARN Warning threshold; default varies. 232 --help Print help and exit. 233 --version Print version and exit. 234 Options must be given as --option value, not --option=value or -Ovalue. 235 Use perldoc to read embedded documentation with more details. 236 237=head1 DESCRIPTION 238 239This Nagios plugin examines MySQL processlist in several ways, 240depending on the value of the -C option: 241 242=over 243 244=item states_count 245 246Alerts when there are too many processes in various states. 247The list of checks is as follows: 248 249Unauthenticated users appear when DNS resolution is slow, and can be a warning 250sign of DNS performance problems that could cause a sudden denial of service to 251the server. 252 253Locked processes are the signature of MyISAM tables, but can also appear for 254other reasons. 255 256Too many processes copying to various kinds of temporary tables at one time is a 257typical symptom of a storm of poorly optimized queries. 258 259Too many processes in the "statistics" state is a signature of InnoDB 260concurrency problems causing query execution plan generation to take too long. 261 262The thresholds should be given as count. The default critical level is 32, 263and warning is 16. 264 265=item max_user_conn 266 267Alerts when C<@@max_user_connections> is configured on MySQL and any user reaches 268this limit. The output of this check will display the user with maximum 269connections consumed, its count and percentage of the actual limit. 270 271The thresholds should be given as percentage. The default critical level is 95, 272and warning is 90. 273 274=back 275 276Examples: 277 278 # /usr/lib64/nagios/plugins/pmp-check-mysql-processlist 279 OK 0 unauthenticated, 0 locked, 0 copy to table, 0 statistics | processes=0;16;32;0; 280 281 # /usr/lib64/nagios/plugins/pmp-check-mysql-processlist -C max_user_conn 282 OK User with max connections: myappuser (70) = 2% | max_user_conn=2;90;95;0;100 283 284=head1 PRIVILEGES 285 286This plugin executes the following commands against MySQL: 287 288=over 289 290=item * 291 292C<SHOW PROCESSLIST;> 293 294=item * 295 296C<SELECT @@max_user_connections;> 297 298=back 299 300This plugin executes no UNIX commands that may need special privileges. 301 302=head1 COPYRIGHT, LICENSE, AND WARRANTY 303 304This program is copyright 2012-$CURRENT_YEAR$ Baron Schwartz, 2012-$CURRENT_YEAR$ Percona Inc. 305Feedback and improvements are welcome. 306 307THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED 308WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF 309MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. 310 311This program is free software; you can redistribute it and/or modify it under 312the terms of the GNU General Public License as published by the Free Software 313Foundation, version 2. You should have received a copy of the GNU General 314Public License along with this program; if not, write to the Free Software 315Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. 316 317=head1 VERSION 318 319$PROJECT_NAME$ pmp-check-mysql-processlist $VERSION$ 320 321=cut 322 323DOCUMENTATION 324