QPS (query per second) number of queries per second
TPS (Transaction per second) transaction volume per second
This is the two important performance indicators of MySQL, which need to be viewed frequently, compared with the results of MySQL benchmark test, if the value is too high, it should be processed as soon as possible
Calculation method
01
QPS
QPS = Queries/seconds
Queries is the system state value-the total number of queries, which can be obtained through the show status query
Seconds is the time interval for monitoring, in seconds
For example, sampling the number of queries within 10 seconds, then first query the queries value (Q1), wait 10 seconds, and then query the queries value (Q2)
QPS = (q2-q1)/10
02
Tps
There is no direct transaction counter in MySQL, which needs to be calculated by the number of transaction commits and the number of transaction rollbacks
TPS = (com_commit + com_rollback)/Seconds
The value of Com_commit and Com_rollback is obtained through the show status query
The calculation idea is similar to the QPS
Statistics script
Above is the statistical idea of QPS/TPS, practical operation if it is inconvenient to calculate by manual method, it is best to use the program to achieve
Mysqladmin's extended-status directive is similar to show status, and you can view MySQL's status values, such as
# mysqladmin-uroot-p ' Password ' extended-status
which contains the status values we need, then we can analyze the results of extended-status, then calculate, and finally show
Statistical goals
Every second counts the QPS, TPS
MySQL has two more important information:
threads_connected The number of threads currently connected
threads_running The number of threads running state
These two values are also in the extended-status result, so you can show them together.
The results of the final statistical information include:
QPS, TPS, threads_connected, threads_running
Look at the presentation of the statistical results first.
Basic ideas
Run Extended-status once per second, using awk to parse the result values to filter out the values we need:
Queries, Com_commit, Com_rollback, threads_connected, threads_running
QPS = queries value-Last value
TPS = (com_commit value-last value) + (com_rollback value-last value)
Because it's 1 seconds to count, you don't have to divide the number of seconds to calculate Qps/tps.
Script code
Code Analysis
Awk is the focus of the code, and Mysqladmin's execution results are passed to awk for analysis by pipeline
' Begin{flag=0;
Print "";
Print "QPS TPS threads_con Threads_run";
Print "-------------------------------------"}
This part is the initial setting, print out the table header
Flag=0 is to set an identity bit, followed by
$/queries$/{q=$4-lq;lq=$4;}
Where $ $4 represents the contents of a column
Awk is parsed by rows and separated by spaces, for example, the line information is:
| Queries | 213263713 |
Divide by space to get 5 columns:
' | ', ' Queries ', ' | ', ' 213263713 ', ' | '
$2:queries
$4:213263713
So the meaning of this sentence is:
When the value of the 2nd column matches ' Queries ',
Variable q = value of the 4th column-the value of the variable LQ,
Variable LQ = value of column 4th
The variable q is the QPS value, minus the last value with the queries value.
$/com_commit$/{c=$4-lc;lc=$4;}
$/com_rollback$/{r=$4-lr;lr=$4;}
$/threads_connected$/{tc=$4;}
$/threads_running$/{tr=$4;
The meaning of these sentences is similar to the previous sentence
if (flag==0) {
flag=1;
}
The flag is used here, which means nothing is done for the first analysis result, because the sentence
{q=$4-lq;lq=$4;}
Q=$4-LQ; The LQ in the first analysis has no value
else {
printf "%-6d%-8d%-10d%d \ n", q,c+r,tc,tr;
}
This is part of printing the statistical results information
Here is the script code, if you are interested, you can come down and experiment.
Http://devdd.oss-cn-beijing.aliyuncs.com/mysql_QPS_TPS.txt
#!/bin/Bashmysqladmin-uroot-p'hides Bang Home'extended-status-i1|awk 'begin{flag=0;Print"";p rint"QPS TPS Threads_con threads_run";p rint"------------------------------------- "}$2~/queries$/{q=$4-lq;lq=$4;} $2~/com_commit$/{c=$4-lc;lc=$4;} $2~/com_rollback$/{r=$4-lr;lr=$4;} $2~/threads_connected$/{tc=$4;} $2~/threads_running$/{TR=$4;if(flag==0) {flag=1; Count=0}Else{printf"%-6d%-8d%-10d%d \ n", Q,C+R,TC,TR;}}'
Mysql Monitor performance status Qps/tps "Go"