[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: [hobbit] Problem with external script and RRD-Graphs (xymon_mySQLtests.sh)

Cayo,

Sorry it took so long to get back to you...

I had to 'cleanup' some of the non-public code in the script and
I added installation documentation...

The attached script was originally based on bb-mysql.sh 
	<http://www.deadcat.net/viewfile.php?fileid=29>
but has been heavily modified for my environment.

YMMV...

ALso, I noticed that I had missed the following in the earlier reply:

hobbitserver.cfg:
 Add ",mysql=ncv" to the TEST2RRD= line, eg:
    TEST2RRD="cpu=la,disk, ...,clock,lines,mysql=ncv"


Please let me know of any improvements to the code or to the
documentation.

Thanks,
Tom 

Tom Brand
CVS/pharmacy
IS Rx SysAdmin Store Support




#!/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
#-----------------------------------------------------------------------------