monitors:xymon-mysqlperf

no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


—monitors:xymon-mysqlperf [2012/12/13 19:06] (current) – created - external edit 127.0.0.1
Line 1: Line 1:
 +====== Xymon-MySQLPerf ======
  
 +^ Author | [[ contato@blackballti.com | LXTI ]] |
 +^ Compatibility | Xymon 4.3.10 |
 +^ Requirements | MySQL, Linux, etc |
 +^ Download | None |
 +^ Last Update | 2013-12-13 |
 +
 +===== Description =====
 +
 +This xymon plugin monitors some of the most important perform variables of MySQL for the Xymon environment. Enjoy!
 +
 +===== Installation =====
 +
 +=== Client side ===
 +
 +1. Copy xymon-mysqlperf.sh file to xymon extensions directory, usually “$XYMONCLIENTHOME/ext/”.
 +
 +2. Configure xymon client to make the test, add this content to clientlaunch.cfg:
 +
 +[mysqlperf]
 +        ENVFILE $XYMONCLIENTHOME/etc/xymonclient.cfg
 +        CMD $XYMONCLIENTHOME/ext/xymon-mysqlperf.sh
 +        LOGFILE $XYMONCLIENTLOGS/xymon-mysqlperf.log
 +        INTERVAL 5m
 +        
 +<note important>Don´t forget to set the right permissions to the files</note>       
 +        
 +=== Server side ===
 +
 +Nothing to do here, yet.
 +
 +===== Source =====
 +==== xymon-mysqlperf.sh ====
 +
 +<hidden onHidden="Show Code ⇲" onVisible="Hide Code ⇱">
 +<code>
 +#!/bin/bash
 +#
 +#
 +#USE IT AS YOU WANT. BY LXTI -- contato@blackballti.com - 23/11/2012.
 +#
 +#
 +#####################
 +#### EXPLANATION ####
 +#####################
 +#
 +#For explanation about the monitored mysql variables, please see http://www.techrepublic.com/blog/opensource/10-mysql-variables-that-you-should-monitor/56.
 +#
 +#ADDING NEW VARIABLE
 +#
 +#To add new monitorable value from mysql performance table, you just need to put the name of it on MONITORADOS.
 +#Create new measurement variables into the "CHECK" function.
 +#Still in the function "CHECK" add a new case, followed by the next value of the case. See case 8, copy it to be easyer.
 +#Voalá.
 +#
 +#
 +#Especial thanks to "Melonfire" for the contribution to explain the variables measures. All rights reserved.  
 +#http://www.techrepublic.com/blog/opensource/10-mysql-variables-that-you-should-monitor/56. Accessed 10/03/2012.
 +#
 +################################
 +#### VARIABLES DECLARATION #####
 +################################
 +
 +# User used to connect to MYSQL. It is not necessary have any privileges. Use "USAGE" to give the privileges.
 +MYSQLUSER=xymon
 +# Password used for the user connection.
 +MYSQLPASS=123
 +
 +# Variables pool monitored by the script. If you want to monitor others variables, this is the right place. Put them after all these.
 +MONITORADOS=(
 +"Threads_connected"
 +"Created_tmp_disk_tables"
 +"Handler_read_first"
 +"Innodb_buffer_pool_wait_free"
 +"Key_reads"
 +"Max_used_connections"
 +"Open_tables"
 +"Select_full_join"
 +"Slow_queries"
 +"Uptime"
 +#PUT IT HERE A NEW VARIABLE
 +)
 +
 +# Logs files
 +# Change them as you want, however set the correct permission for Xymon user.
 +
 +LOG1=/blackball/tmp/xymon-mysqlperf.tmp
 +LOG2=/blackball/tmp/xymon-mysqlperf2.tmp
 +STATUS=/blackball/tmp/xymon-mysqlperf-status.tmp
 +
 +#Initializing new variables.
 +
 +echo "" > $LOG1
 +echo "" > $LOG2
 +echo "" > $STATUS
 +
 +#########################################################################
 +
 +###################
 +#### FUNCTIONS ####
 +###################
 +
 +
 +# For Xymon getting the general status
 +
 +function general_status {
 +        if [  "$1" == "red" ]; then
 +                echo "red" > $STATUS
 +
 +        elif [ "`cat $STATUS`" == "green" ] && [ "$1" == "yellow" ]; then
 +                echo "yellow" > $STATUS
 +        fi
 +}
 +
 +
 +# Get the values of the Variables.
 +
 +function obtem_valores { 
 + #date > $LOG1
 + for (( i = 0 ; i < ${#MONITORADOS[@]} ; i++ ))
 + do
 + mysql -u $MYSQLUSER -p$MYSQLPASS -e "SHOW GLOBAL STATUS WHERE Variable_name LIKE '${MONITORADOS[$i]}';" | cut -f2 | tail -n1 >> $LOG1
 + check $(mysql -u $MYSQLUSER -p$MYSQLPASS -e "SHOW GLOBAL STATUS WHERE Variable_name LIKE '${MONITORADOS[$i]}';" | cut -f2 | tail -n1) $i
 + done
 +} 
 +
 +# Verify all Variables and set the threshold for Xymon.
 +
 +function check {
 +### SETTING MEASURES ###
 +#
 +## HOW TO USE ##
 +#
 +#By default the color yellow or red start when the definitions values get over of the values setted.
 +#I don't know how to know the best status value for your enviroment, so try them by yourself. 
 +#
 +####################
 +## LET'S STARTING ##
 +####################
 +#Threads_connected
 +#
 +TC_STARTING_YELLOW=100
 +TC_STARTING_RED=300
 +#
 +#Created_tmp_disk_tables
 +#
 +CT_STARTING_YELLOW=6000
 +CT_STARTING_RED=10000
 +#
 +#Handler_read_first
 +#
 +HF_STARTING_YELLOW=200000
 +HF_STARTING_RED=500000
 +#
 +#Innodb_buffer_pool_wait_free
 +#
 +IF_STARTING_YELLOW=10
 +IF_STARTING_RED=30
 +#
 +#Key_reads
 +#
 +KR_STARTING_YELLOW=5000
 +KR_STARTING_RED=10000
 +#
 +#Max_used_connections
 +#
 +MC_STARTING_YELLOW=100
 +MC_STARTING_RED=400
 +#
 +#Open_tables
 +#
 +OT_STARTING_YELLOW=100
 +OT_STARTING_RED=500
 +#
 +#Select_full_join
 +#
 +SJ_STARTING_YELLOW=150
 +SJ_STARTING_RED=500
 +#
 +#Slow_queries
 +#
 +SQ_STARTING_YELLOW=100
 +SQ_STARTING_RED=500
 +#
 +#Uptime. (PS. IN THIS CASE WE WANT TO KNOW WHEN THE MYSQL WAS RESTARTED AS SOON POSSIBLE, THEN WE NEED TO MONITORING SINCE WHEN IT IS UP. IN HOURS FEWER THAT YOU WHANT TO BE GREEN)
 +#
 +UP_STARTING_RED=1
 +#
 +
 + case "$2" in
 + 0) 
 + #Threads_connected
 + if [[ $1 -ge $TC_STARTING_YELLOW ]]; then
 + if [[ $1 -le $TC_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 1) 
 + #Created_tmp_disk_tables
 + if [[ $1 -ge $CT_STARTING_YELLOW ]]; then
 + if [[ $1 -le $CT_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 2) 
 + #Handler_read_first
 + if [[ $1 -ge $HF_STARTING_YELLOW ]]; then
 + if [[ $1 -le $HF_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 3) 
 + #Innodb_buffer_pool_wait_free
 + if [[ $1 -ge $IF_STARTING_YELLOW ]]; then
 + if [[ $1 -le $IF_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 4) 
 + #Key_reads
 + if [[ $1 -ge $KR_STARTING_YELLOW ]]; then
 + if [[ $1 -le $KR_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 5) 
 + #Max_used_connections
 + if [[ $1 -ge $MC_STARTING_YELLOW ]]; then
 + if [[ $1 -le $MC_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 6) 
 + #Open_tables
 + if [[ $1 -ge $OT_STARTING_YELLOW ]]; then
 + if [[ $1 -le $OT_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 7) 
 + #Select_full_join
 + if [[ $1 -ge $SJ_STARTING_YELLOW ]]; then
 + if [[ $1 -le $SJ_STARTING_RED ]]; then
 + # Vai pro yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 8) 
 + #Slow_queries
 + if [[ $1 -ge $SQ_STARTING_YELLOW ]]; then
 + if [[ $1 -le $SQ_STARTING_RED ]]; then
 + # Go to yellow
 + echo "&yellow ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status yellow
 + else
 + # Go to red
 + echo "&red ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status red
 + fi
 + else
 + #Go to green
 + echo "&green ${MONITORADOS[$2]} ---- $1" >> $LOG2
 + general_status green
 + fi
 + ;;
 + 9) 
 + #Uptime
 + if [[ $(($1/3600)) -le $UP_STARTING_RED ]]; then
 + # Vai pro red
 + echo "&red ${MONITORADOS[$2]} ---- $(($1/3600))" >> $LOG2
 + general_status red
 + else
 + #Vai pro green
 + echo "&green ${MONITORADOS[$2]} ---- $(($1/3600))" >> $LOG2
 + general_status green
 + fi
 + ;;
 + esac
 +}
 +
 +#######################
 +#### KICKING UP IT ####
 +#######################
 +
 +echo "green" > $STATUS
 +
 +obtem_valores
 +
 +################
 +#### XYMON  ####
 +################
 +
 +$BB $BBDISP "status $MACHINE.mysqlperf `cat $STATUS` `date`
 +
 +`cat $LOG2`"
 +
 +exit 0
 +</code>
 +</hidden>
 +
 +===== Known  Bugs and Issues =====
 +
 +===== To Do =====
 +
 +Graphic all variables.
 +
 +===== Credits =====
 +
 +Melonfire - For MySQL explanation.
 +
 +===== Changelog =====
 +
 +  * **2012-11-23**
 +    * Initial release
  • monitors/xymon-mysqlperf.txt
  • Last modified: 2012/12/13 19:06
  • by 127.0.0.1