Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/bin/bash
- ##
- # Tart Monitoring
- #
- # Copyright (c) 2013, Tart İnternet Teknolojileri Ticaret AŞ
- #
- # Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby
- # granted, provided that the above copyright notice and this permission notice appear in all copies.
- #
- # The software is provided "as is" and the author disclaims all warranties with regard to the software including all
- # implied warranties of merchantability and fitness. In no event shall the author be liable for any special, direct,
- # indirect, or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether
- # in an action of contract, negligence or other tortious action, arising out of or in connection with the use or
- # performance of this software.
- ##
- #
- # Fetching the parameters
- #
- while getopts "H:P:u:p:s:c:w:qh" opt; do
- case $opt in
- H ) connectionString=$connectionString"--host=$OPTARG " ;;
- P ) connectionString=$connectionString"--port $OPTARG " ;;
- u ) connectionString=$connectionString"--user=$OPTARG " ;;
- p ) connectionString=$connectionString"--password=$OPTARG " ;;
- s ) secondsArray=(${secondsArray[*]} $OPTARG) ;;
- c ) criticalLimitsArray=(${criticalLimitsArray[*]} $OPTARG) ;;
- w ) warningLimitsArray=(${warningLimitsArray[*]} $OPTARG) ;;
- q ) queryMode=1 ;;
- h ) echo "a script to monitor MySQL processlist"
- echo "Usage:"
- echo "$0 -h"
- echo "$0 [-H hostname] [-P port] [-u username] [-p password] \\"
- echo " [-q] [-s seconds] [-w limits] [-c limits]"
- echo "Source:"
- echo "github.com/tart/CheckMySQLProcesslist"
- exit 3 ;;
- \? ) echo "UNKNOWN wrong parameter"
- exit 3
- esac
- done
- if [ ! "$secondsArray" ]; then
- secondsArray=(0)
- fi
- #
- # Querying the database
- #
- processlist=$(mysql $connectionString--execute="Show processlist" | sed 1d | sed "/^[^\t]*\tsystem user/d")
- if [ ! "$processlist" ]; then
- echo "UNKNOWN no processlist"
- exit 3
- fi
- globalVariables=$(mysql $connectionString--execute="Show global variables")
- if [ ! "$globalVariables" ]; then
- echo "UNKNOWN no global variables"
- exit 3
- fi
- #
- # Parsing the resources
- #
- maxConnections=$(echo "$globalVariables" | grep ^max_connections | cut -f 2)
- if [ $queryMode ]; then
- timeout=$(echo "$globalVariables" | grep ^interactive_timeout | cut -f 2)
- else
- timeout=$(echo "$globalVariables" | grep ^wait_timeout | cut -f 2)
- fi
- id=0
- for seconds in ${secondsArray[*]}; do
- for second in $(echo $seconds | sed "s/,/ /g"); do
- secondPercent=$(echo $second | grep % | sed "s/%//")
- if [ $secondPercent ]; then
- secondArray[$id]=$[$[$timeout*$secondPercent]/100]
- elif [ $second ]; then
- secondArray[$id]=$second
- fi
- id=$[$id+1]
- if [ ! $shortestSecond ] || [ $second -lt $shortestSecond ]; then
- shortestSecond=$second
- fi
- done
- done
- id=0
- for criticalLimits in ${criticalLimitsArray[*]}; do
- for criticalLimit in $(echo $criticalLimits | sed "s/,/ /g"); do
- criticalLimitPercent=$(echo $criticalLimit | grep % | sed "s/%//")
- if [ $criticalLimitPercent ]; then
- criticalLimitArray[$id]=$[$[$maxConnections*$criticalLimitPercent]/100]
- else
- criticalLimitArray[$id]=$criticalLimit
- fi
- id=$[$id+1]
- done
- done
- id=0
- for warningLimits in ${warningLimitsArray[*]}; do
- for warningLimit in $(echo $warningLimits | sed "s/,/ /g"); do
- warningLimitPercent=$(echo $warningLimit | grep % | sed "s/%//")
- if [ $warningLimitPercent ]; then
- warningLimitArray[$id]=$[$[$maxConnections*$warningLimitPercent]/100]
- else
- warningLimitArray[$id]=$warningLimit
- fi
- id=$[$id+1]
- done
- done
- for id in ${!secondArray[*]}; do
- countArray[$id]=0
- done
- if [ $queryMode ]; then
- processlist=$(echo "$processlist" | sed "/^[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\tNULL/d")
- fi
- for time in $(echo "$processlist" | cut -f 6); do
- for id in ${!secondArray[*]}; do
- if [ $time -ge ${secondArray[$id]} ]; then
- countArray[$id]=$[${countArray[$id]}+1]
- fi
- done
- if [ ! $longestTime ] || [ $time -gt $longestTime ]; then
- longestTime=$time
- fi
- done
- if [ ! $queryMode ]; then
- unauthenticatedConnections=0
- for user in $(echo "$processlist" | cut -f 2); do
- case $user in
- "unauthenticated user") unauthenticatedConnections=$[$unauthenticatedConnections+1] ;;
- esac
- done
- queringConnections=0
- connectingConnections=0
- fetchingConnections=0
- executingConnections=0
- sleepingConnections=0
- for command in $(echo "$processlist" | cut -f 5); do
- case $command in
- "Query") queringConnections=$[$queringConnections+1] ;;
- "Connect") connectingConnections=$[$connectingConnections+1] ;;
- "Fetch") fetchingConnections=$[$fetchingConnections+1] ;;
- "Execute") executingConnections=$[$executingConnections+1] ;;
- "Sleep") sleepingConnections=$[$sleepingConnections+1] ;;
- esac
- done
- fi
- if [ $queryMode ]; then
- tempTableUsingQueries=0
- preparingQueries=0
- sortingQueries=0
- lockedQueries=0
- returningQueries=0
- for state in $(echo "$processlist" | cut -f 7); do
- case $state in
- "Copying to group table") tempTableUsingQueries=$[$tempTableUsingQueries+1] ;;
- "Copying to tmp table") tempTableUsingQueries=$[$tempTableUsingQueries+1] ;;
- "Copying to tmp table on disk") tempTableUsingQueries=$[$tempTableUsingQueries+1] ;;
- "Creating tmp table") tempTableUsingQueries=$[$tempTableUsingQueries+1] ;;
- "optimizing") preparingQueries=$[$preparingQueries+1] ;;
- "preparing") preparingQueries=$[$preparingQueries+1] ;;
- "statistics") preparingQueries=$[$preparingQueries+1] ;;
- "Sorting for group") sortingQueries=$[$sortingQueries+1] ;;
- "Sorting for order") sortingQueries=$[$sortingQueries+1] ;;
- "Sorting index") sortingQueries=$[$sortingQueries+1] ;;
- "Sorting result") sortingQueries=$[$sortingQueries+1] ;;
- "Locked") lockedQueries=$[$lockedQueries+1] ;;
- "System lock") lockedQueries=$[$lockedQueries+1] ;;
- "Table lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for commit lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for global read lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for table level lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for event metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for schema metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for stored function metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for stored procedure metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for table metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Waiting for trigger metadata lock") lockedQueries=$[$lockedQueries+1] ;;
- "Sending data") returningQueries=$[$returningQueries+1] ;;
- "Writing to net") returningQueries=$[$returningQueries+1] ;;
- esac
- done
- fi
- #
- # Preparing the output
- #
- for id in ${!secondArray[*]}; do
- second=${secondArray[$id]}
- if [ $queryMode ]; then
- title="${countArray[$id]} queries"
- performanceData=$performanceData"queries"
- else
- title="${countArray[$id]} connections"
- performanceData=$performanceData"connections"
- fi
- if [ $second == 0 ]; then
- title=$title" of $maxConnections"
- else
- title=$title" active for"
- performanceData=$performanceData"ActiveFor$second"
- if [ $second == 3600 ]; then
- title=$title" an hour"
- elif [ $[$second%3600] == 0 ]; then
- title=$title" $[$second/3600] hours"
- elif [ $second == 60 ]; then
- title=$title" a minute"
- elif [ $[$second%60] == 0 ]; then
- title=$title" $[$second/60] minutes"
- elif [ second == 1 ]; then
- title=$title" a second"
- else
- title=$title" $second seconds"
- fi
- fi
- performanceData=$performanceData"=${countArray[$id]};${warningLimitArray[$id]};${criticalLimitArray[$id]};0;$maxConnections "
- if [ ${criticalLimitArray[$id]} ] && [ ${countArray[$id]} -ge ${criticalLimitArray[$id]} ]; then
- criticalString=$criticalString"$title reached ${criticalLimitArray[$id]}; "
- elif [ ${warningLimitArray[$id]} ] && [ ${countArray[$id]} -ge ${warningLimitArray[$id]} ]; then
- warningString=$warningString"$title reached ${warningLimitArray[$id]}; "
- elif [ $second == $shortestSecond ]; then
- okString="$title; "
- fi
- done
- if [ ! $queryMode ]; then
- performanceData=$performanceData"unauthenticatedConnections=$unauthenticatedConnections;;;0;$maxConnections "
- performanceData=$performanceData"queringConnections=$queringConnections;;;0;$maxConnections "
- performanceData=$performanceData"connectingConnections=$connectingConnections;;;0;$maxConnections "
- performanceData=$performanceData"fetchingConnections=$fetchingConnections;;;0;$maxConnections "
- performanceData=$performanceData"executingConnections=$executingConnections;;;0;$maxConnections "
- performanceData=$performanceData"sleepingConnections=$sleepingConnections;;;0;$maxConnections "
- fi
- if [ $queryMode ]; then
- performanceData=$performanceData"tempTableUsingQueries=$tempTableUsingQueries;;;0;$maxConnections "
- performanceData=$performanceData"preparingQueries=$preparingQueries;;;0;$maxConnections "
- performanceData=$performanceData"sortingQueries=$sortingQueries;;;0;$maxConnections "
- performanceData=$performanceData"lockedQueries=$lockedQueries;;;0;$maxConnections "
- performanceData=$performanceData"returningQueries=$returningQueries;;;0;$maxConnections "
- if [ $longestTime -gt 0 ]; then
- longestQuery=$(echo "$processlist" | grep -P "^[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\t[^\t]*\t$longestTime" | head -n 1)
- longestQueryString=$longestQueryString"time is $(echo "$longestQuery" | cut -f 6); "
- longestQueryString=$longestQueryString"id is $(echo "$longestQuery" | cut -f 1); "
- longestQueryString=$longestQueryString"user is $(echo "$longestQuery" | cut -f 2); "
- longestQueryHost=$(echo "$longestQuery" | cut -f 3)
- if [ $longestQueryHost != "localhost" ]; then
- longestQueryString=$longestQueryString"host is $(echo "$longestQuery" | cut -f 3); "
- fi
- longestQuerySchema=$(echo "$longestQuery" | cut -f 4)
- if [ $longestQuerySchema != "NULL" ]; then
- longestQueryString=$longestQueryString"schema is $(echo "$longestQuery" | cut -f 4); "
- fi
- longestQueryString=$longestQueryString"executing \"$(echo "$longestQuery" | cut -f 8)\"; "
- longestQueryString=$longestQueryString"state is $(echo "$longestQuery" | cut -f 7); "
- fi
- fi
- performanceData=$performanceData"longestTime=$longestTime;;;0;$timeout "
- #
- # Quiting
- #
- if [ "$criticalString" ]; then
- echo -n "CRITICAL CheckMySQLProcesslist $criticalString"
- fi
- if [ "$warningString" ]; then
- echo -n "WARNING CheckMySQLProcesslist $warningString"
- fi
- if [ ! "$criticalString" ] && [ ! "$warningString" ]; then
- echo -n "OK CheckMySQLProcesslist $okString"
- fi
- if [ "$longestQueryString" ]; then
- echo -n "longest query: $longestQueryString"
- fi
- echo "| $performanceData"
- if [ "$criticalString" ]; then
- exit 2
- fi
- if [ "$warningString" ]; then
- exit 1
- fi
- exit 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement