Simple bash script for getting the current, average and maximum status value in MySQL

Here's a script to get the current, average and maximum value of a status counter in MySQL. This is useful when you need to monitor a certain counter which you will use as a threshold for running administrative scripts such as pt-online-schema-change, pt-table-checksum, pt-stalk, etc:

counters.sh

#!/bin/bash

INTERVAL=1

USER=root

PASSWORD=msandbox

HOST=127.0.0.1

VARIABLE=Threads_running

MAX=1

AVG=1

COUNT=0

SUM=0

while [ 1 ]

do

  CURRENT=`mysql -h$HOST -u$USER -p$PASSWORD -BNe "SHOW GLOBAL STATUS LIKE '$VARIABLE'"|tr "\t" " "|cut -d " " -f2`

  if [ $CURRENT -gt $MAX ]; then

    MAX=$CURRENT

  fi

  SUM=`expr $SUM + $CURRENT`

  COUNT=`expr $COUNT + 1`

  AVG=`expr $SUM / $COUNT`

  echo -e "$VARIABLE: CUR=$CURRENT MAX=$MAX AVG=$AVG" 

  sleep $INTERVAL

done

Example:

./counters.sh 

Threads_running: CUR=1 MAX=1 AVG=1

Threads_running: CUR=1 MAX=1 AVG=1

Threads_running: CUR=1 MAX=1 AVG=1

Threads_running: CUR=1 MAX=1 AVG=1

Threads_running: CUR=137 MAX=137 AVG=28

Threads_running: CUR=140 MAX=140 AVG=46

Threads_running: CUR=140 MAX=140 AVG=60

Threads_running: CUR=136 MAX=140 AVG=69

Threads_running: CUR=125 MAX=140 AVG=75

Threads_running: CUR=144 MAX=144 AVG=82