Msyql TPS v1: Calculate the msyqltps within the specified time

Source: Internet
Author: User

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}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.