mysql计算指定的时间TPS

转载

mob60475705c8db 2015-10-18 18:47:00

文章标签 mysql 默认值浮点运算 sed bash 文章分类 代码人生

<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的username
MYSQL_PASS='123'  #mysql的登录用户password
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