diff options
Diffstat (limited to 'nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-processlist')
| -rwxr-xr-x | nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-processlist | 323 |
1 files changed, 323 insertions, 0 deletions
diff --git a/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-processlist b/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-processlist new file mode 100755 index 0000000..b2985f0 --- /dev/null +++ b/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-processlist @@ -0,0 +1,323 @@ +#!/bin/sh + +# ######################################################################## +# This program is part of $PROJECT_NAME$ +# License: GPL License (see COPYING) +# Authors: +# Baron Schwartz, Roman Vynar +# ######################################################################## + +# ######################################################################## +# Redirect STDERR to STDOUT; Nagios doesn't handle STDERR. +# ######################################################################## +exec 2>&1 + +# ######################################################################## +# Set up constants, etc. +# ######################################################################## +STATE_OK=0 +STATE_WARNING=1 +STATE_CRITICAL=2 +STATE_UNKNOWN=3 +STATE_DEPENDENT=4 + +# ######################################################################## +# Run the program. +# ######################################################################## +main() { + # Get options + for o; do + case "${o}" in + -C) shift; OPT_CHEK="${1}"; shift; ;; + -c) shift; OPT_CRIT="${1}"; shift; ;; + --defaults-file) shift; OPT_DEFT="${1}"; shift; ;; + -H) shift; OPT_HOST="${1}"; shift; ;; + -l) shift; OPT_USER="${1}"; shift; ;; + -L) shift; OPT_LOPA="${1}"; shift; ;; + -p) shift; OPT_PASS="${1}"; shift; ;; + -P) shift; OPT_PORT="${1}"; shift; ;; + -S) shift; OPT_SOCK="${1}"; shift; ;; + -w) shift; OPT_WARN="${1}"; shift; ;; + --version) grep -A2 '^=head1 VERSION' "$0" | tail -n1; exit 0 ;; + --help) perl -00 -ne 'm/^ Usage:/ && print' "$0"; exit 0 ;; + -*) echo "Unknown option ${o}. Try --help."; exit 1; ;; + esac + done + OPT_CHEK="${OPT_CHEK:-states_count}" + if [ -e '/etc/nagios/mysql.cnf' ]; then + OPT_DEFT="${OPT_DEFT:-/etc/nagios/mysql.cnf}" + fi + if is_not_sourced; then + if [ -n "$1" ]; then + echo "WARN spurious command-line options: $@" + exit 1 + fi + fi + + # Get processlist into a temp file. + local TEMP=$(mktemp -t "${0##*/}.XXXXXX") || exit $? + trap "rm -f '${TEMP}' >/dev/null 2>&1" EXIT + + case "${OPT_CHEK}" in + states_count) + OPT_WARN=${OPT_WARN:-16} + OPT_CRIT=${OPT_CRIT:-32} + + # Capture a number of types of states, add some together, take the max, + # and compare to the threshold. + mysql_exec 'SHOW PROCESSLIST\G' > "${TEMP}" + if [ $? = 0 ]; then + UNAUTH=$(count_mysql_processlist "${TEMP}" "User" "unauthenticated user") + LOCKED1=$(count_mysql_processlist "${TEMP}" "State" "Locked") + LOCKED2=$(count_mysql_processlist "${TEMP}" "State" "Waiting for .* lock") + LOCKED3=$(count_mysql_processlist "${TEMP}" "State" "Table lock") + LOCKED4=$(count_mysql_processlist "${TEMP}" "State" "Waiting for table flush") + LOCKED5=$(count_mysql_processlist "${TEMP}" "State" "Waiting for tables") + COPYIN=$(count_mysql_processlist "${TEMP}" "State" ".*opy.* to.* table.*") + STATIS=$(count_mysql_processlist "${TEMP}" "State" "statistics") + LOCKED=$((${LOCKED1:-0} + ${LOCKED2:-0} + ${LOCKED3:-0} + ${LOCKED4:-0} + ${LOCKED5:-0})) + NOTE="${UNAUTH} unauthenticated, ${LOCKED} locked," + NOTE="${NOTE} ${COPYIN} copy to table, ${STATIS} statistics" + MAX="$(max "${UNAUTH:-0}" "${LOCKED:-0}" "${COPYIN:-0}" "${STATIS:-0}")" + if [ "${MAX:-0}" -gt "${OPT_CRIT}" ]; then + NOTE="CRIT $NOTE" + elif [ "${MAX:-0}" -gt "${OPT_WARN}" ]; then + NOTE="WARN $NOTE" + else + NOTE="OK $NOTE" + fi + + # Build the common perf data output for graph trending + PERFDATA="processes=${MAX:-0};${OPT_WARN};${OPT_CRIT};0;" + NOTE="$NOTE | $PERFDATA" + else + NOTE="UNK could not retrieve MySQL processlist" + fi + ;; + max_user_conn) + OPT_WARN=${OPT_WARN:-90} + OPT_CRIT=${OPT_CRIT:-95} + + # Check if @@max_user_connections is set on MySQL + MAX_USER_CONN=$(mysql_exec 'SELECT @@max_user_connections') + if [ $? = 0 ]; then + if [ ${MAX_USER_CONN:-0} -gt 0 ]; then + # Capture a number of connections per user from the processlist, take the max, + # and compare to the threshold. + mysql_exec 'SHOW PROCESSLIST\G' > "${TEMP}" + if [ $? = 0 ]; then + MAX_USER=$(cat ${TEMP}|grep User|awk '{print $2}'|sort|uniq -c|sort -n|tail -1) + CNT=$(echo ${MAX_USER} | awk '{print $1}') + USER=$(echo ${MAX_USER} | awk '{print $2}') + MAX=$(expr ${CNT} \* 100 / ${MAX_USER_CONN}) + NOTE="User with max connections: ${USER} (${CNT}) = ${MAX}%" + if [ "${MAX:-0}" -gt "${OPT_CRIT}" ]; then + NOTE="CRIT $NOTE" + elif [ "${MAX:-0}" -gt "${OPT_WARN}" ]; then + NOTE="WARN $NOTE" + else + NOTE="OK $NOTE" + fi + + # Build the common perf data output for graph trending + PERFDATA="max_user_conn=${MAX:-0};${OPT_WARN};${OPT_CRIT};0;100" + NOTE="$NOTE | $PERFDATA" + else + NOTE="UNK could not retrieve MySQL processlist" + fi + else + NOTE="OK @@max_user_connections is not configured." + fi + else + NOTE="UNK could not retrieve @@max_user_connections" + fi + ;; + *) + echo "Unknown value for -C: '${OPT_CHEK}'. Consult the documentation."; + exit 1; + ;; + esac + + echo $NOTE +} + +# ######################################################################## +# Extract a count from MySQL processlist. The arguments are: +# $1 - file with the processlist. +# $2 - the column to examine. +# $3 - the value to count. +# ######################################################################## +count_mysql_processlist() { + local FILE="${1}" + local COL="${2}" + local MATCH="${3}" + grep -c "^ *${COL}: ${MATCH}" "${FILE}" +} + +# ######################################################################## +# Find the maximum argument, assuming nonnegative integers. +# ######################################################################## +max() { + local MAX=0 + for val; do + if [ "${val:-0}" -gt "${MAX}" ]; then + MAX="${val}" + fi + done + echo "${MAX:-0}" +} + +# ######################################################################## +# Execute a MySQL command. +# ######################################################################## +mysql_exec() { + mysql ${OPT_DEFT:+--defaults-file="${OPT_DEFT}"} \ + ${OPT_LOPA:+--login-path="${OPT_LOPA}"} \ + ${OPT_HOST:+-h"${OPT_HOST}"} ${OPT_PORT:+-P"${OPT_PORT}"} \ + ${OPT_USER:+-u"${OPT_USER}"} ${OPT_PASS:+-p"${OPT_PASS}"} \ + ${OPT_SOCK:+-S"${OPT_SOCK}"} -ss -e "$1" +} + +# ######################################################################## +# Determine whether this program is being executed directly, or sourced/included +# from another file. +# ######################################################################## +is_not_sourced() { + [ "${0##*/}" = "pmp-check-mysql-processlist" ] || [ "${0##*/}" = "bash" -a "$_" = "$0" ] +} + +# ######################################################################## +# Execute the program if it was not included from another file. +# This makes it possible to include without executing, and thus test. +# ######################################################################## +if is_not_sourced; then + OUTPUT=$(main "$@") + EXITSTATUS=$STATE_UNKNOWN + case "${OUTPUT}" in + UNK*) EXITSTATUS=$STATE_UNKNOWN; ;; + OK*) EXITSTATUS=$STATE_OK; ;; + WARN*) EXITSTATUS=$STATE_WARNING; ;; + CRIT*) EXITSTATUS=$STATE_CRITICAL; ;; + esac + echo "${OUTPUT}" + exit $EXITSTATUS +fi + +# ############################################################################ +# Documentation +# ############################################################################ +: <<'DOCUMENTATION' +=pod + +=head1 NAME + +pmp-check-mysql-processlist - Alert when MySQL processlist has dangerous patterns. + +=head1 SYNOPSIS + + Usage: pmp-check-mysql-processlist [OPTIONS] + Options: + -C CHECK What to alert on; default states_count. + Other options: max_user_conn. + -c CRIT Critical threshold; default varies. + --defaults-file FILE Only read mysql options from the given file. + Defaults to /etc/nagios/mysql.cnf if it exists. + -H HOST MySQL hostname. + -l USER MySQL username. + -L LOGIN-PATH Use login-path to access MySQL (with MySQL client 5.6). + -p PASS MySQL password. + -P PORT MySQL port. + -S SOCKET MySQL socket file. + -w WARN Warning threshold; default varies. + --help Print help and exit. + --version Print version and exit. + Options must be given as --option value, not --option=value or -Ovalue. + Use perldoc to read embedded documentation with more details. + +=head1 DESCRIPTION + +This Nagios plugin examines MySQL processlist in several ways, +depending on the value of the -C option: + +=over + +=item states_count + +Alerts when there are too many processes in various states. +The list of checks is as follows: + +Unauthenticated users appear when DNS resolution is slow, and can be a warning +sign of DNS performance problems that could cause a sudden denial of service to +the server. + +Locked processes are the signature of MyISAM tables, but can also appear for +other reasons. + +Too many processes copying to various kinds of temporary tables at one time is a +typical symptom of a storm of poorly optimized queries. + +Too many processes in the "statistics" state is a signature of InnoDB +concurrency problems causing query execution plan generation to take too long. + +The thresholds should be given as count. The default critical level is 32, +and warning is 16. + +=item max_user_conn + +Alerts when C<@@max_user_connections> is configured on MySQL and any user reaches +this limit. The output of this check will display the user with maximum +connections consumed, its count and percentage of the actual limit. + +The thresholds should be given as percentage. The default critical level is 95, +and warning is 90. + +=back + +Examples: + + # /usr/lib64/nagios/plugins/pmp-check-mysql-processlist + OK 0 unauthenticated, 0 locked, 0 copy to table, 0 statistics | processes=0;16;32;0; + + # /usr/lib64/nagios/plugins/pmp-check-mysql-processlist -C max_user_conn + OK User with max connections: myappuser (70) = 2% | max_user_conn=2;90;95;0;100 + +=head1 PRIVILEGES + +This plugin executes the following commands against MySQL: + +=over + +=item * + +C<SHOW PROCESSLIST;> + +=item * + +C<SELECT @@max_user_connections;> + +=back + +This plugin executes no UNIX commands that may need special privileges. + +=head1 COPYRIGHT, LICENSE, AND WARRANTY + +This program is copyright 2012-$CURRENT_YEAR$ Baron Schwartz, 2012-$CURRENT_YEAR$ Percona Inc. +Feedback and improvements are welcome. + +THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED +WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. + +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, version 2. You should have received a copy of the GNU General +Public License along with this program; if not, write to the Free Software +Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. + +=head1 VERSION + +$PROJECT_NAME$ pmp-check-mysql-processlist $VERSION$ + +=cut + +DOCUMENTATION |