mysql計算指定時間內TPS，mysql計算指定tps

mysql計算指定時間內TPS，mysql計算指定tps

<pre name="code" class="sql">有朋友留言,需要監控指定時間內如早上9定到18點的TPS,寫了一個10秒內TPS的方法.#!/bin/bashexport black='\033[0m'export boldblack='\033[1;0m'export red='\033[31m'export boldred='\033[1;31m'export green='\033[32m'export boldgreen='\033[1;32m'export yellow='\033[33m'export boldyellow='\033[1;33m'export blue='\033[34m'export boldblue='\033[1;34m'export magenta='\033[35m'export boldmagenta='\033[1;35m'export cyan='\033[36m'export boldcyan='\033[1;36m'export white='\033[37m'export boldwhite='\033[1;37m'cecho ()## -- Function to easliy print colored text -- ### Color-echo.# 參數 $1 = message# 參數 $2 = color{local default_msg="No message passed."message=${1:-$default_msg}# 如果$1沒有輸入則為預設值default_msg.color=${2:-black}# 如果$1沒有輸入則為預設值black.case $color inblack) printf "$black" ;;boldblack) printf "$boldblack" ;;red) printf "$red" ;;boldred) printf "$boldred" ;;green) printf "$green" ;;boldgreen) printf "$boldgreen" ;;yellow) printf "$yellow" ;;boldyellow) printf "$boldyellow" ;;blue) printf "$blue" ;;boldblue) printf "$boldblue" ;;magenta) printf "$magenta" ;;boldmagenta) printf "$boldmagenta" ;;cyan) printf "$cyan" ;;boldcyan) printf "$boldcyan" ;;white) printf "$white" ;;boldwhite) printf "$boldwhite" ;;esac  printf "%s\n"  "$message"  tput sgr0# tput sgr0即恢複預設值  printf "$black"return}cechon ()# Color-echo.# 參數1 $1 = message# 參數2 $2 = color{local default_msg="No message passed."# Doesn't really need to be a local variable.message=${1:-$default_msg}# 如果$1沒有輸入則為預設值default_msg.color=${2:-black}# 如果$1沒有輸入則為預設值black.case $color inblack)printf "$black" ;;boldblack)printf "$boldblack" ;;red)printf "$red" ;;boldred)printf "$boldred" ;;green)printf "$green" ;;boldgreen)printf "$boldgreen" ;;yellow)printf "$yellow" ;;boldyellow)printf "$boldyellow" ;;blue)printf "$blue" ;;boldblue)printf "$boldblue" ;;magenta)printf "$magenta" ;;boldmagenta)printf "$boldmagenta" ;;cyan)printf "$cyan" ;;boldcyan)printf "$boldcyan" ;;white)printf "$white" ;;boldwhite)printf "$boldwhite" ;;esac  printf "%s"  "$message"  tput sgr0# tput sgr0即恢複預設值  printf "$black"return}#set mysql evnMYSQL_USER=root  #mysql的使用者名稱MYSQL_PASS='123'  #mysql的登入使用者密碼MYSQL_HOST=localhost#TPS01(間隔時間內事務量)####TPS = (Com_commit + Com_rollback) / seconds ########mysql > show global status like 'Com_insert'; ########mysql > show global status like 'Com_update'; ########mysql > show global status like 'Com_delete'; ####sleep_time=10tps_01="show  global status where Variable_name in('Com_insert'); "tps_02="show  global status where Variable_name in('Com_update'); "tps_03="show  global status where Variable_name in('Com_delete'); "tps_re01="tpsre01.`date +%Y%m%d%H%M%S`.txt"tps_re02="tpsre02.`date +%Y%m%d%H%M%S`.txt"tps_re03="tpsre03.`date +%Y%m%d%H%M%S`.txt"mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_01}" |grep -v Variable_name \|cut -f 2 >${tps_re01}mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_02}" |grep -v Variable_name \|cut -f 2 >${tps_re02}mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_03}" |grep -v Variable_name \|cut -f 2 >${tps_re03}tps_01_re=`cat ${tps_re01}`tps_02_re=`cat ${tps_re02}`tps_03_re=`cat ${tps_re03}`tps_sum_now=`awk 'BEGIN{print '${tps_01_re}' + '${tps_02_re}' + '${tps_03_re}'}' ` #shell預設不支援浮點運算rm -rf ${tps_re01}rm -rf ${tps_re02}rm -rf ${tps_re03}echo "正在擷取TPS值:"sleep ${sleep_time}tps_021="show  global status where Variable_name in('Com_insert'); "tps_022="show  global status where Variable_name in('Com_update'); "tps_023="show  global status where Variable_name in('Com_delete'); "tps_re021="tpsre021.`date +%Y%m%d%H%M%S`.txt"tps_re022="tpsre022.`date +%Y%m%d%H%M%S`.txt"tps_re023="tpsre023.`date +%Y%m%d%H%M%S`.txt"mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_021}" |grep -v Variable_name \|cut -f 2 >${tps_re021}mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_022}" |grep -v Variable_name \|cut -f 2 >${tps_re022}mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_023}" |grep -v Variable_name \|cut -f 2 >${tps_re023}tps_021_re=`cat ${tps_re021}`tps_022_re=`cat ${tps_re022}`tps_023_re=`cat ${tps_re023}`tps_sum_new=`awk 'BEGIN{print '${tps_021_re}' + '${tps_022_re}' + '${tps_023_re}' }'`tps_sum_diff=`awk 'BEGIN{print '${tps_sum_new}' - '${tps_sum_now}' }' `tps_avg=`awk 'BEGIN{print '${tps_sum_diff}' / '${sleep_time}'}'` #shell預設不支援浮點運算cechon "Within the last $sleep_time seconds,TPS is: ${tps_avg} " redecho "                                                                           "echo "                                                                           "rm -rf ${tps_re021}rm -rf ${tps_re022}rm -rf ${tps_re023}############運行結果正在擷取TPS值:Within the last 10 seconds,TPS is: 0.9