Msyql TPS v1: Calculate the msyqltps within the specified time

Msyql TPS v1: Calculate the msyqltps within the specified time

Requirement: Calculate the TPS between-during daily business peaks.

Idea: 1. records TPS-related historical values at every morning. obtain TPS-related values at any current time minus the value recorded at AM. obtains the number of seconds from AM to the current time.

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

1. timed 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.
# Parameter $1 = message
# Parameter $2 = color
{
Local default_msg = "No message passed ."


Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is 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 restores the default value
Printf "$ black"


Return
}




Cechon ()


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


Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is 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 restores the default value
Printf "$ black"


Return
}




# Set mysql evn
MYSQL_USER = root # mysql Username
MYSQL_PASS = '000000' # mysql logon User Password
MYSQL_HOST = localhost




# Generate TPS historical value and time value at every day
Last_exec_time = "/root/tps_lastime. 'date when policyuncm=d'.txt"
T01 = 'date "+ % Y-% m-% d % H: % M: % S" >$ {last_exec_time }'
Tps_everydat_9 = "tps_everydat_9. 'date when policyuncm=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 when policyymm1_d1_h1_m1_s'.txt"
Tps_re02 = "tpsre02. 'date when policyymm1_d1_h1_m1_s'.txt"
Tps_re03 = "tpsre03. 'date when policyymm1_d1_h1_m1_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 has been policypolicm=d'.txt"


Tps_sum_now = 'awk' BEGIN {print '$ {tps_01_re}' + '$ {tps_02_re}' + '$ {tps_03_re}' >$ {tps_everydat_9} '# shell by default supports floating point operations


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

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

2. Script

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

#! /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.
# Parameter $1 = message
# Parameter $2 = color
{
Local default_msg = "No message passed ."


Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is 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 restores the default value
Printf "$ black"


Return
}




Cechon ()


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


Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.
Color = $ {2:-black} # If $1 is not input, the default value is 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 restores the default value
Printf "$ black"


Return
}




# Set mysql evn
MYSQL_USER = root # mysql Username
MYSQL_PASS = '000000' # mysql logon User Password
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 too many yymm1_d1_h1_m1_s'.txt"
Tps_re052 = "tpsre052. 'date used policyuncm=d1_hsf-m=s'.txt"
Tps_re053 = "tpsre053. 'date used policyuncm=d1_hsf-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 }'}''


# Get the last value (see TPS-JOB)
Tps_everydat_9 = "/root/tps_everydat_9. 'date has been policypolicm=d'.txt"
Tps_sum_old = 'cat ${tps_everydat_9 }'
Tps_sum_diff = 'awk' BEGIN {print '$ {tps_sum_new}'-'$ {tps_sum_old }'}''




# Obtain Time Difference
Last_exec_time = "/root/tps_lastime. 'date when policyuncm=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 '}''




# Calculate the TPS from AM to the present
Tps_avg = 'awk' BEGIN {print '$ {tps_sum_diff}'/'$ {tps_uptime_gf}' | awk '{printf ("%. f \ n ", $1)}'' # The shell does not support floating point operations by default.






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}