Mysql Performance Monitoring - Script

The following provides a linux shell script that provides some basic mysql performance monitoring.  It reports the following metrics every N seconds based on the parameter passed in:

• timestamp
• queries per second
• threads connected
• connections
• slow queries per second
• rows read per second
• rows inserted per second
• rows updated per second
• rows deleted per second

The results are written into a csv output file mysql-stats.csv.

Script

# mysql-stats.sh
# mysql stats sampled every $1 seconds until stopped
#

if [ "$1" == "" ] ; then
  echo "syntax= ./mysql-stats.sh <intervalSeconds>"
  exit
fi

time=$1
origqps=`mysql -u username -p password "show status" | awk '{if ($1 == "Queries") print $2}'`
origconn=`mysql -u username -p password "show status" | awk '{if ($1 == "Threads_connected") print $2}'`
origconnattempt=`mysql -u username -p password "show status" | awk '{if ($1 == "Connections") print $2}'`
origslowqry=`mysql -u username -p password "show status" | awk '{if ($1 == "Slow_queries") print $2}'`
origrowsread=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_read") print $2}'`
originserted=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_inserted") print $2}'`
origupdated=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_updated") print $2}'`
origdeleted=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_deleted") print $2}'`

echo "Stats (show status) sampled every $time seconds" > mysql-stats.csv
echo "Time , Queries , Threads_connected , Connections , Slow_queries , Innodb_rows_read , inserted , updated , deleted" >> mysql-stats.csv

x=1
while :
do

sleep $time
lastqps=`mysql -u username -p password "show status" | awk '{if ($1 == "Queries") print $2}'`
lastconn=`mysql -u username -p password "show status" | awk '{if ($1 == "Threads_connected") print $2}'`
lastconnattempt=`mysql -u username -p password "show status" | awk '{if ($1 == "Connections") print $2}'`
lastslowqry=`mysql -u username -p password "show status" | awk '{if ($1 == "Slow_queries") print $2}'`
lastrowsread=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_read") print $2}'`
lastinserted=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_inserted") print $2}'`
lastupdated=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_updated") print $2}'`
lastdeleted=`mysql -u username -p password "show status" | awk '{if ($1 == "Innodb_rows_deleted") print $2}'`

diffqps=`expr $lastqps - $origqps`
#diffconn=`expr $lastconn - $origconn`
diffconnattempt=`expr $lastconnattempt - $origconnattempt`
diffslowqry=`expr $lastslowqry - $origslowqry`
diffrowsread=`expr $lastrowsread - $origrowsread`
diffinserted=`expr $lastinserted - $originserted`
diffupdated=`expr $lastupdated - $origupdated`
diffdeleted=`expr $lastdeleted - $origdeleted`

avgqps=`expr $diffqps / $time`
#avgconn=`expr $diffconn / $time`
avgconnattempt=`expr $diffconnattempt / $time`
avgslowqry=`expr $diffslowqry / $time`
avgrowsread=`expr $diffrowsread / $time`
avginserted=`expr $diffinserted / $time`
avgupdated=`expr $diffupdated / $time`
avgdeleted=`expr $diffdeleted / $time`

#echo "$avgqps"
date=`date`
echo "$date , $avgqps , $lastconn , $avgconnattempt , $avgslowqry , $avgrowsread , $avginserted , $avgupdated , $avgdeleted" >> mysql-stats.csv

origqps=$lastqps
origconn=$lastconn
origconnattempt=$lastconnattempt
origslowqry=$lastslowqry
origrowsread=$lastrowsread
originserted=$lastinserted
origupdated=$lastupdated
origdeleted=$lastdeleted

done

Output

Stats (show status) sampled every 10 seconds
Time , Queries , Threads_connected , Connections , Slow_queries , Innodb_rows_read , inserted , updated , deleted
Tue Sep 18 14:32:37 PDT 2012 , 13 , 5 , 0 , 0 , 45 , 0 , 0 , 0
Tue Sep 18 14:32:47 PDT 2012 , 13 , 5 , 0 , 0 , 45 , 0 , 0 , 0
Tue Sep 18 14:32:57 PDT 2012 , 1374 , 5 , 0 , 0 , 258 , 352 , 0 , 153
Tue Sep 18 14:33:07 PDT 2012 , 4061 , 26 , 2 , 0 , 907 , 976 , 28 , 83
Tue Sep 18 14:33:17 PDT 2012 , 12122 , 26 , 0 , 0 , 374 , 3026 , 0 , 89
Tue Sep 18 14:33:27 PDT 2012 , 9714 , 26 , 0 , 0 , 45 , 2412 , 0 , 0
Tue Sep 18 14:33:37 PDT 2012 , 8694 , 26 , 0 , 0 , 45 , 2162 , 0 , 0
Tue Sep 18 14:33:47 PDT 2012 , 4659 , 26 , 0 , 0 , 45 , 1153 , 0 , 0
Tue Sep 18 14:33:57 PDT 2012 , 4237 , 26 , 0 , 0 , 45 , 1053 , 0 , 0
Tue Sep 18 14:34:07 PDT 2012 , 3668 , 26 , 0 , 0 , 332 , 921 , 0 , 108
Tue Sep 18 14:34:17 PDT 2012 , 3739 , 26 , 0 , 0 , 45 , 928 , 0 , 0
Tue Sep 18 14:34:27 PDT 2012 , 3636 , 26 , 0 , 0 , 45 , 902 , 0 , 0
Tue Sep 18 14:34:38 PDT 2012 , 4526 , 26 , 0 , 0 , 45 , 1125 , 0 , 0
Tue Sep 18 14:34:48 PDT 2012 , 3734 , 26 , 0 , 0 , 45 , 927 , 0 , 0
Tue Sep 18 14:34:58 PDT 2012 , 3678 , 26 , 0 , 0 , 45 , 912 , 0 , 0