#!/bin/bash #------------------------------------------------------------------------------ # Name : ~hobbit/client/ext/xymon_mySQLtests.sh # # Purpose: Hobbit (Xymon) Monitor extension script to check 'mySQL' # server status. # # Updates the 'mysql' columns in the Xymon (Hobbit) monitor. # Creates 'mysql' page with information similar to: # # ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # Wed Jun 3 13:57:11 EDT 2009 - # # mySQL - Buffer pool is undefined % # mySQL - Unresolved deadlocks since Wed Jun 3 12:16:14 EDT 2009 # # Threads : 22 # Slow Queries : 26 # Open Tables : 95 # Flush Tables : 1 # Questions : 306630 # Opens : 100 # AvgQuerysPerSecond : 6.409 # Uptime : 47844 # Free Buffer Pool : undefined # # [ graph - MySWQL Statistics ] # # ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # # # # Version: 1.0 # Written by K. Medeiros and T. Brand, May 29, 2009 # # Version: 1.1 # Updated by T. Brand; cleaned up and added comments for release # to the Hobbit Users group. # # Notes : Currently requires the mySQL passwords to be hardcoded in this # script... there's gotta be a better way. # # This version has been tested on SUSE SLES 10 SP1 Linux; it may # work in your environment; # # # #------------------------------------------------------------------------------ # License: # # 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, either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # Installation: # # * Ensure this file was saved with UNIX line feeds, not DOS/Windows. # # * Copy this file to ~hobbit/client/ext/xymon_mySQLtests.sh on the # Hobbit (Xymon) client # # * Check the PATHs for 'mysqladmin' # # * Update ~hobbit/server/etc/hobbitserver.cfg: # # 1. Add ",mysql=ncv" to the TEST2RRD= line, eg: # TEST2RRD="cpu=la,disk, ...,clock,lines,mysql=ncv" # # 2. Add new line for NCV_mysql (all on one line, do NOT wrap lines # remove '\' at end of each example line below!) # NCV_mysql="Threads:GAUGE,SlowQueries:GAUGE,OpenTables:GAUGE,\ # FlushTables:NONE,Questions:NONE,Opens:NONE,\ # AvgQuerysPerSecond:NONE,Uptime:NONE,\ # FreeBufferPool:NONE" # # 3. Add ",mysql" to the GRAPHS= line, eg: # GRAPHS="la,disk,...,lines,mysql" # # # # # * Add this stanza to hobbitgraph.cfg for the mysql graphs: # # [mysql] # TITLE MySQL Statistics # YAXIS Number of # DEF:threads=mysql.rrd:Threads:AVERAGE # DEF:slow=mysql.rrd:SlowQueries:AVERAGE # DEF:opentables=mysql.rrd:OpenTables:AVERAGE # LINE2:threads#FF0000:Threads # LINE2:slow#00CCCC:Slow Queries # LINE2:opentables#FF00FF:Open Tables # COMMENT:\n # GPRINT:threads:LAST:Threads \: %5.1lf (cur) # GPRINT:threads:MAX: \: %5.1lf (max) # GPRINT:threads:MIN: \: %5.1lf (min) # GPRINT:threads:AVERAGE: \: %5.1lf (avg)\n # GPRINT:slow:LAST:Slow Queries\: %5.1lf (cur) # GPRINT:slow:MAX: \: %5.1lf (max) # GPRINT:slow:MIN: \: %5.1lf (min) # GPRINT:slow:AVERAGE: \: %5.1lf (avg)\n # GPRINT:opentables:LAST:Open Tables \: %5.1lf (cur) # GPRINT:opentables:MAX: \: %5.1lf (max) # GPRINT:opentables:MIN: \: %5.1lf (min) # GPRINT:opentables:AVERAGE: \: %5.1lf (avg)\n # # # #------------------------------------------------------------------------------ # Modifications: # 2009-03-31 TRB Updated to remove non-public code/info prior to releasing # to Xymon (aka Hobbit) list. # # #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # Uncomment to get debug output #exec 2>/tmp/bb-mysqlstat.sh.log && set -x # BBPROG should just contain the name of this file. It is useful when you # get environment dumps to locate the offending script export BBPROG=xymon_mySQLtests.sh # TEST NAME: This will become a column on the hobbit web display. # It should be as short as possible to save space. # Note: You can also create a HELP file for your test which should be put # in www/help/$TEST.html on the hobbit. It will automatically be linked # into the display. TEST="mysql" # BBHOME can be set manually when testing; otherwise it should be set from # the BB environment if test "$BBHOME" = "" then echo "BBHOME is not set... exiting" exit 1 fi if test ! "${BBTMP}" # GET DEFINITIONS IF NEEDED then # echo "*** LOADING BBDEF ***" . $BBHOME/etc/bbdef.sh # INCLUDE STANDARD DEFINITIONS fi # Set usernames and passwords as needed sqlUsr="root" sqlPwd="yyyyyyyyy" sqlAdminUsr="root" sqlAdminPwd="xxxxxxxx" #---------------------------------------------------------- # Start collecting data declare -i mySQL_UpTime declare -i mySQL_Threads declare -i mySQL_SlowQ declare -i mySQL_QPS # queries per second declare -i mySQL_OldUpTime declare -i mySQL_OldSlowQ # slow queries declare -i mySQL_DSlowQ COLOR="green" STATUS="" # Get current values mySQL_status="$( /usr/bin/mysqladmin --user=${sqlAdminUsr} \ --password=${sqlAdminPwd} \ status 2>&1 )" if [[ ${mySQL_status} =~ "connect to server.*failed" ]] then COLOR="red" STATUS="Can not connect to mySQL " $BB $BBDISP "status $MACHINE.$TEST $COLOR $(date) - $STATUS $MYSQLSTATUS" exit 0 fi #----------------------------------------- # Get current statistics mySQL_UpTime=$( echo ${mySQL_status} | awk '{ printf "%d", $2 }' ) mySQL_Threads=$( echo ${mySQL_status} | awk '{ printf "%d", $4 }' ) mySQL_SlowQ=$( echo ${mySQL_status} | awk '{ printf "%d", $9 }' ) mySQL_QPS=$( echo ${mySQL_status} | awk '{ printf "%d", $22 }' ) sqlStatusFile=${BBTMP}/bb-mysql.status.tmp mysql -u ${sqlUsr} -p${sqlAdminPwd} \ -e "show innodb status\G" >${sqlStatusFile} totBuffers=$( awk '/Buffer pool size/ {print $4}' ${sqlStatusFile} ) freeBuffers=$( awk '/Free buffers/ {print $3}' ${sqlStatusFile} ) if [[ -n ${totBuffers} && -n ${freeBuffers} ]] then mySQL_Buffers_Pct_Free=$(( ${freeBuffers} / ( ${totBuffers} / 100 ) )) else mySQL_Buffers_Pct_Free="undefined" fi # Get old values mySQL_OldUpTime=$( $CAT ${BBTMP}/$MACHINE.$TEST.mysqluptime.log ) mySQL_OldSlowQ=$( $CAT ${BBTMP}/$MACHINE.$TEST.mysqlslowq.log ) # Write new values to logfiles echo $mySQL_UpTime > ${BBTMP}/$MACHINE.$TEST.mysqluptime.log echo $mySQL_SlowQ > ${BBTMP}/$MACHINE.$TEST.mysqlslowq.log # Compute the differences mySQL_DSlowQ=$(( mySQL_SlowQ - mySQL_OldSlowQ )) #---------------------------------------- # Handle YELLOW conditions # Note: The $STATUS is cumulative -- info for each condition (if any) # is appended to any previous STATUS. if [[ $mySQL_UpTime -le $mySQL_OldUpTime ]] then COLOR="yellow" STATUS="${STATUS} mySQL - Recently Restarted" fi # 2 threads/client max clients 25 if [[ $mySQL_Threads -ge 50 ]] then COLOR="yellow" STATUS="${STATUS} mySQL - High threads" fi # Any slow query taking longer than 1 sec should be alerted. if [[ $mySQL_DSlowQ -ge 1 ]] then COLOR="yellow" STATUS="${STATUS} mySQL - Slow queries" fi if [[ ${mySQL_Buffers_Pct_Free} =~ "undefined" || ${mySQL_Buffers_Pct_Free} -lt 20 ]] then COLOR="yellow" STATUS="${STATUS} mySQL - Buffer pool is ${mySQL_Buffers_Pct_Free} %" fi #------------------------------------------ # Handel RED conditions # Using 20% over the warning if [[ $mySQL_Threads -ge 60 ]] then COLOR="red" STATUS="${STATUS} mySQL - Very high threads" fi # if more than 5 something bad is going on. if [[ $mySQL_DSlowQ -ge 5 ]] then COLOR="red" STATUS="${STATUS} mySQL - Very slow queries" fi #------------------------------------------------------------ # Look for Deadlocks # Warns if deadlocks continue to exist longer than $warnOnDeadlocks minutes # Notifies if deadlocks existed and were cleared via RollBack operation warnOnDeadlocks=10 # 10 minutes DL_Detected=$( grep -i "latest detected deadlock" ${sqlStatusFile} ) RB_Detected=$( grep -i "*** WE ROLL BACK TRANSACTION" ${sqlStatusFile} ) if [[ -n ${DL_Detected} && ${RB_Detected} ]] then [[ "${COLOR}" =~ "GREEN" ]] && COLOR=YELLOW lastDL_Time=$( grep -A2 -i "latest detected deadlock" ${sqlStatusFile} |\ tail -1 ) ldlDate=$( date -d "${lastDL_Time}" ) STATUS="${STATUS} mySQL - Last resolved a deadlock at: ${ldlDate}" elif [[ -n ${DL_Detected} ]] then # Check for unresolved deadlocks lastDL_Time=$( grep -A2 -i "latest detected deadlock" ${sqlStatusFile} |\ tail -1 ) LastDL_secs=$( date -d "${lastDL_Time}" +%s ) now_Seconds=$( date +%s ) deadLockTime=$(( (now_Seconds - LastDL_secs) / 60 )) lockedTime=$( date -d "${lastDL_Time}" "+%Y-%m-%d %H-%M" ) if (( ${deadLockTime} > ${warnOnDeadlocks} )) then COLOR="red" STATUS="${STATUS} mySQL - Unresolved deadlocks since ${lockedTime} (${deadLockTime} minutes.)" fi fi # Delete the temporary status file rm -f ${sqlStatusFile} # --------------------------------------------------- # Format the results on muliple lines for graphing.... # Only use alpha-numeric comments; max length=19 characters. # Also note, the 'status' can NOT have any ":" characters other than # those needed by the NCV graphs! # Example: # --Some status text here # --can be over multiple lines (':' will be replaced with ' ') # # Threads : 3 # Slow Queries : 0 # Open Tables : 25 # Flush Tables : 1 # Questions : 97080 # Opens : 30 # AvgQuerysPerSecond : 1.940 # Uptime : 50050 # Free Buffer Pool : 90 MYSQLSTATUS=$( echo "${mySQL_status//:/ }}" | \ awk ' {printf "%-19s: %s \n", "Threads", $4 } {printf "%-19s: %s \n", "Slow Queries", $9 } {printf "%-19s: %s \n", "Open Tables", $17 } {printf "%-19s: %s \n", "Flush Tables", $14 } {printf "%-19s: %s \n", "Questions", $6 } {printf "%-19s: %s \n", "Opens", $11 } {printf "%-19s: %s \n", "AvgQuerysPerSecond", $22 } {printf "%-19s: %s \n", "Uptime", $2 } ') #----------------------------------------- # Send info up to Hobbit server [[ ${COLOR} =~ "GREEN|green" ]] && STATUS="mySQL is OK ${STATUS}" $BB $BBDISP \ "status $MACHINE.$TEST $COLOR $(date) - $STATUS $MYSQLSTATUS $( printf "%-19s: %s \n" "Free Buffer Pool" ${mySQL_Buffers_Pct_Free} ) " exit 0 #----------------------------------------------------------------------------- #----- End of Script #-----------------------------------------------------------------------------