程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql計算指定時間內TPS

mysql計算指定時間內TPS

編輯:MySQL綜合教程

mysql計算指定時間內TPS



<pre name="code" class="sql">有朋友留言,需要監控指定時間內如早上9定到18點的TPS,寫了一個10秒內TPS的方法.  
  
#!/bin/bash  
export 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 in  
    black)  
         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 in  
    black)  
        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 evn  
MYSQL_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=10  
tps_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} " red  
echo "                                                                           "  
echo "                                                                           "  
rm -rf ${tps_re021}  
rm -rf ${tps_re022}  
rm -rf ${tps_re023}  
  
  
  
  
  
############運行結果  
正在獲取TPS值:  
Within the last 10 seconds,TPS is: 0.9      

						

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved