Xymon-MySQLPerf

Author	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!

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.

Show Code ⇲

Hide 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

Graphic all variables.

Melonfire - For MySQL explanation.