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.
# 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
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
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