msyql TPS v1:計算指定時間內，msyqltps

msyql TPS v1:計算指定時間內，msyqltps

需求:計算每天業務高峰期9:00-18:00的TPS.

思路：1.每天早上9點定時記錄下TPS相關的曆史值  2.任意目前時間擷取TPS相關的值減去早上9點記錄的值 3.擷取早上9點到目前時間的秒數

/***********************

1.定時job

**********************

#!/bin/bash
#ocpyang@126.com
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




#每天9點產生TPS曆史值和時間值
last_exec_time="/root/tps_lastime.`date +%Y%m%d`.txt"
t01=`date "+%Y-%m-%d %H:%M:%S" >${last_exec_time}`
tps_everydat_9="tps_everydat_9.`date +%Y%m%d`.txt"




#TPS(每秒事務量)




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_everydat_9="/root/tps_everydat_9.`date +%Y%m%d`.txt"


tps_sum_now=`awk 'BEGIN{print '${tps_01_re}' + '${tps_02_re}' + '${tps_03_re}'}' >${tps_everydat_9}` #shell預設不支援浮點運算


rm -rf ${tps_re01}
rm -rf ${tps_re02}
rm -rf ${tps_re03}

/***********************

2.指令碼

**********************

#!/bin/bash
#ocpyang@126.com
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


tps_051="show  global status where Variable_name in('Com_insert'); "
tps_052="show  global status where Variable_name in('Com_update'); "
tps_053="show  global status where Variable_name in('Com_delete'); "
tps_re051="tpsre051.`date +%Y%m%d%H%M%S`.txt"
tps_re052="tpsre052.`date +%Y%m%d%H%M%S`.txt"
tps_re053="tpsre053.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_051}" |grep -v Variable_name \
|cut -f 2 >${tps_re051}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_052}" |grep -v Variable_name \
|cut -f 2 >${tps_re052}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_053}" |grep -v Variable_name \
|cut -f 2 >${tps_re053}


tps_051_re=`cat ${tps_re051}`
tps_052_re=`cat ${tps_re052}`
tps_053_re=`cat ${tps_re053}`


tps_sum_new=`awk 'BEGIN{print '${tps_051_re}' + '${tps_052_re}' + '${tps_053_re}' }'`


#擷取上一次的值(見TPS-JOB)
tps_everydat_9="/root/tps_everydat_9.`date +%Y%m%d`.txt"
tps_sum_old=`cat ${tps_everydat_9}`
tps_sum_diff=`awk 'BEGIN{print '${tps_sum_new}' - '${tps_sum_old}' }' `




#擷取時間差
last_exec_time="/root/tps_lastime.`date +%Y%m%d`.txt"
t02=`cat ${last_exec_time}`
start_time=`date +%s -d "$t02"`
end_time=`date +%s `
#echo $(($end_time-$start_time)) 
tps_uptime_gf=`awk 'BEGIN{ print '$end_time'-'$start_time'}'`




#計算早上9點到現在的TPS
tps_avg=`awk 'BEGIN{print '${tps_sum_diff}' / '${tps_uptime_gf}'}'|awk '{printf("%.f\n",$1)}'` #shell預設不支援浮點運算






cechon "From $t02 to now ,TPS: ${tps_avg} " red
echo "                                                                           "
tps_01_re=`cat ${tps_re051}`
tps_02_re=`cat ${tps_re052}`
tps_03_re=`cat ${tps_re053}`
echo




rm -rf ${tps_re051}
rm -rf ${tps_re052}
rm -rf ${tps_re053}