Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== 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:06by 127.0.0.1