mysql 计算时间内_mysql计算指定时间内TPS

有朋友留言,需要监控指定时间内如早上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

原文：http://blog.csdn.net/yangzhawen/article/details/46471595