diff options
Diffstat (limited to 'nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-innodb')
| -rwxr-xr-x | nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-innodb | 368 |
1 files changed, 368 insertions, 0 deletions
diff --git a/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-innodb b/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-innodb new file mode 100755 index 0000000..038f564 --- /dev/null +++ b/nagios-plugins-contrib-24.20190301~bpo9+1/percona-nagios-plugins/nagios/bin/pmp-check-mysql-innodb @@ -0,0 +1,368 @@ +#!/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:-idle_blocker_duration}" + 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 + NOTE="UNK could not find information about transactions" + + local TEMP=$(mktemp -t "${0##*/}.XXXXXX") || exit $? + trap "rm -f '${TEMP}' >/dev/null 2>&1" EXIT + + case "${OPT_CHEK}" in + idle_blocker_duration) + OPT_WARN=${OPT_WARN:-60} + OPT_CRIT=${OPT_CRIT:-600} + OUTPUT=$(mysql_exec " + SELECT MAX(COALESCE(p.time, 0)) AS idle_in_trx, + p.id, + CONCAT(p.user, '@', p.host) AS userhost + FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w + INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id + INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id + LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id AND p.command = 'Sleep' + GROUP BY p.id, p.user, p.host + ORDER BY idle_in_trx DESC LIMIT 1" 2>"${TEMP}") + if [ "$?" != 0 ]; then + if grep "Unknown table" "${TEMP}" >/dev/null 2>&1; then + # The I_S tables don't exist. + NOTE="OK The INFORMATION_SCHEMA.INNODB_% tables don't exist." + else + cat "${TEMP}" + fi + elif [ -z "${OUTPUT}" ]; then + OUTPUT=0 + fi + ;; + waiter_count) + OPT_WARN=${OPT_WARN:-10} + OPT_CRIT=${OPT_CRIT:-25} + OUTPUT=$(mysql_exec " + SELECT COUNT(DISTINCT REQUESTING_TRX_ID) AS cnt + FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w" 2>"${TEMP}") + if [ "$?" != 0 ]; then + if grep "Unknown table" "${TEMP}" >/dev/null 2>&1; then + # The I_S tables don't exist. + mysql_exec "SHOW /*!50000 ENGINE*/ INNODB STATUS\G" > "${TEMP}" || exit $? + OUTPUT=$(get_waiter_count "${TEMP}") + else + cat "${TEMP}" + fi + fi + ;; + max_duration) + OPT_WARN=${OPT_WARN:-60} + OPT_CRIT=${OPT_CRIT:-600} + OUTPUT=$(mysql_exec " + SET @@time_zone='SYSTEM'; + SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(t.trx_started), + p.id, + CONCAT(p.user, '@', p.host) + FROM INFORMATION_SCHEMA.INNODB_TRX AS t + JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = t.trx_mysql_thread_id + ORDER BY t.trx_started LIMIT 1" 2>"${TEMP}") + if [ "$?" != 0 ]; then + if grep "Unknown table" "${TEMP}" >/dev/null 2>&1; then + # The I_S tables don't exist. + mysql_exec "SHOW /*!50000 ENGINE*/ INNODB STATUS\G" > "${TEMP}" || exit $? + OUTPUT=$(get_longest_trx "${TEMP}") + if [ -z "${OUTPUT}" ]; then + OUTPUT=0 + fi + else + cat "${TEMP}" + fi + elif [ -z "${OUTPUT}" ]; then + OUTPUT=0 + fi + ;; + *) + echo "Unknown value for -C: '${OPT_CHEK}'. Consult the documentation."; + exit 1; + ;; + esac + + # $OUTPUT now contains either an empty string or three words: 1) Age of + # oldest transaction, 2) thread ID of oldest transaction, 3) user info. + if [ -n "${OUTPUT}" ]; then + LEVEL="$(echo ${OUTPUT} | awk '{print $1}')" + INFO="$(echo ${OUTPUT} | awk '{print "(thread "$2" by "$3")"}')" + + case "${OPT_CHEK}" in + idle_blocker_duration) + NOTE="longest blocking idle transaction sleeps for ${LEVEL:-UNKNOWN} seconds" + ;; + waiter_count) + NOTE="${LEVEL:-UNKNOWN} transactions in LOCK WAIT status" + INFO="" + ;; + max_duration) + NOTE="longest transaction active for ${LEVEL:-UNKNOWN} seconds" + ;; + esac + if [ "${LEVEL:-0}" -gt "${OPT_CRIT}" ]; then + NOTE="CRIT $NOTE $INFO" + elif [ "${LEVEL:-0}" -gt "${OPT_WARN}" ]; then + NOTE="WARN $NOTE $INFO" + else + NOTE="OK $NOTE" + fi + fi + echo $NOTE +} + +# ######################################################################## +# 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" +} + +# ######################################################################## +# Gets max txn time in SHOW INNODB STATUS. File is $1. +# ######################################################################## +get_longest_trx() { + awk ' + BEGIN { + maxtime = 0; + thread = 0; + userinfo = "nobody"; + } + /^TRANSACTIONS$/ { + tseen = 1; + } + { + if ( tseen == 1 && $0 ~ /^---TRANSACTION.*[0-9] sec/ ) { + if ( $2 ~ /,/ ) { + time = $4; + } + else { + time = $5; + } + } + if ( tseen == 1 && $0 ~ /^MySQL thread id/ ) { + if ( time > maxtime ) { + maxtime = time; + thread = substr($4, 1, length($4) - 1); + match($0, /query id [0-9]+ .+/); + userinfo = substr($0, RSTART, RLENGTH); + split(userinfo, a, " "); + userinfo = a[5]"@"a[4]; + } + } + } + END { + print maxtime, thread, userinfo; + } + ' "${1}" +} + +# ######################################################################## +# Counts the number of LOCK WAIT in SHOW INNODB STATUS. File is $1. +# ######################################################################## +get_waiter_count() { + awk ' + BEGIN { + lock_waits = 0; + } + /^TRANSACTIONS$/ { + tseen = 1; + } + { + if ( tseen == 1 && $0 ~ /TRX HAS BEEN WAITING/ ) { + lock_waits++; + } + } + END { + print lock_waits; + } + ' "${1}" +} + +# ######################################################################## +# Determine whether this program is being executed directly, or sourced/included +# from another file. +# ######################################################################## +is_not_sourced() { + [ "${0##*/}" = "pmp-check-mysql-innodb" ] || [ "${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-innodb - Alert on problems inside InnoDB. + +=head1 SYNOPSIS + + Usage: pmp-check-mysql-innodb [OPTIONS] + Options: + -C CHECK What to alert on; default idle_blocker_duration. + Other options: waiter_count, max_duration. + -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 alerts on various aspects of InnoDB status in several ways, +depending on the value of the -C option: + +=over + +=item idle_blocker_duration + +This is the default behavior. It alerts when a long-running transaction is +blocking another, and the blocker is idle (Sleep). The threshold is based on +how long the transaction has been idle. Long-running idle transactions that +have acquired locks but not released them are a frequent cause of application +downtime due to lock wait timeouts and rollbacks, especially because +applications are often not designed to handle such errors correctly. The +problem is usually due to another error that causes a transaction not to be +committed, such as performing very long tasks in the application while holding +the transaction open. + +This check examines the INFORMATION_SCHEMA tables included with InnoDB version +1.0 and newer. The default critical level is 600, and warning is 60. If the +tables do not exist, the exit status is OK, with a note that the tables do not +exist. + +=item waiter_count + +Alerts if too many transactions are in LOCK WAIT status. Uses information from +SHOW ENGINE INNODB STATUS if the INFORMATION_SCHEMA tables are not available. +The default critical level is 25, and warning is 10. + +=item max_duration + +Alerts if any transaction is too old. Uses information from SHOW ENGINE INNODB +STATUS if the INFORMATION_SCHEMA tables are not available. The default critical +level is 600, and warning is 60. + +=back + +=head1 PRIVILEGES + +This plugin executes the following commands against MySQL: + +=over + +=item * + +C<SHOW ENGINE INNODB STATUS>. + +=item * + +C<SELECT> against the C<INFORMATION_SCHEMA> InnoDB transaction and lock tables. + +=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-innodb $VERSION$ + +=cut + +DOCUMENTATION |