MSYQL TPS v1: Calculate within a specified time

Requirements: Calculate the TPS at the peak of the business day.

Train of thought: 1. Record the TPS-related historical value 2 at 9 o ' clock every morning. Gets the TPS-related value at any current time minus the value of 9 points recorded in the morning 3. Get the number of seconds in the morning from 9 to the current time

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

1. Timed Job

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

#!/bin/bash
#[email protected]
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.
# parameter $ = message
# parameter = = Color
{
Local default_msg= "No message passed."


message=${1:-$default _msg} # The default value is default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.


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 Restores default values
printf "$black"


Return
}




Cechon ()


# Color-echo.
# parameter 1 = message
# parameter 2 = = Color
{
Local default_msg= "No message passed."
# doesn ' t really need to be a local variable.


message=${1:-$default _msg} # The default value is default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.


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 Restores default values
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 (transaction volume per second)




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 does not support floating-point arithmetic by default


RM-RF ${TPS_RE01}
RM-RF ${TPS_RE02}
RM-RF ${TPS_RE03}

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

2. Scripts

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

#!/bin/bash
#[email protected]
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.
# parameter $ = message
# parameter = = Color
{
Local default_msg= "No message passed."


message=${1:-$default _msg} # The default value is default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.


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 Restores default values
printf "$black"


Return
}




Cechon ()


# Color-echo.
# parameter 1 = message
# parameter 2 = = Color
{
Local default_msg= "No message passed."
# doesn ' t really need to be a local variable.


message=${1:-$default _msg} # The default value is default_msg if no input is entered.
Color=${2:-black} # The default value is black if no input is entered.


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 Restores default values
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} '} '


#获取上一次的值 (see 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 ' $t 02 "'
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", $)} ' #shell默认不支持浮点运算






Cechon "from $t-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}

MSYQL TPS v1: Calculate within a specified time