Mysql Monitor performance status Qps/tps "Go"

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"