Index is introduced

  • QPS: Number of Queries Per Second. It is a measure of the number of Queries a server can process in a specified period of time.

  • TPS: Transactions Per Second is the number of Transactions processed by a database server Per unit of time.

QPS indicators

Let’s take a look at the following metrics for querying:

  1. Questions: Number of queries sent to MySQL from the client since MySQL was last started. Queries within stored procedures are not included. It does not count COM_PING,COM_STATISTICS,COM_STMT_PREPARE,COM_STMT_CLOSE, and COM_STMT_RESET, but it does count the show command.
  2. Queries: indicates the number of Queries sent by the MySQL client to MySQL from the last startup to the current startup. Queries include those within stored procedures. The COM_PING and COM_STATISTICS commands are not collected.
  3. Com_select: indicates the total number of query statements executed by MySQL since the last startup.

As can be seen from the above, the “show” command in the database will increase the value of questions by 1, while the “com_select” will not be recorded. In addition, for the monitoring of the database, the “show” command is often used, so the data is actually polluted when calculating by “questions”. In addition, the value of questions is always growing when setting environment variables, while the value of COM_SELECT is not growing during this process. Therefore, when using the question method to calculate, the QPS result is artificially raised. Relatively speaking, using the COM_SELECT method to calculate QPS is relatively close to the real situation, that is to say, under the same conditions, the QPS value is raised.

The following will introduce how to calculate QPS by Questions and Com_select

Questions method to calculate QPS

1. QPS formula is calculated by Questions

questions = show global status where variable_name='Questions';
uptime = show global status like 'Uptime';
qps=questions/uptime
Copy the code

QPS = > QPS = > QPS = > QPS = > QPS = > QPS = > QPS = > QPS = > QPS = > QPS = > QPS The questions value contains data contamination caused by the show command and environment variables.

Note: The following SQL is tested in MySQL8.0, there are some differences in 5.6 or 5.7.

select round(sum(if(variable_name='Questions',variable_value,0))/sum(if(variable_name='Uptime',variable_value,0)),1) as 'QPS' from performance_schema.global_status where variable_name in ('Questions'.'Uptime');
Copy the code

Calculate QPS in Com_select mode

QPS can be calculated by Com_select, and two Com_select indicators can be obtained. The new indicator is subtracted from the old indicator and then divided into the average value per second during the interval. This way is more realistic. 1.com_select calculates the QPS script

Com_Select = show global status where Variable_name='Com_select';
#! /usr/bin/env bash
OLD_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep The $1
NEW_QPS=`echo "show global status where Variable_name='Com_select';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
echo "($NEW_QPS-$OLD_QPS) / The $1" | bc
Copy the code

TPS indicators

There are also two ways to obtain TPS metrics:

  1. Based on thecom_commit,com_rollbackCalculate the TPS
  2. Based on thecom_insert,com_delete,com_updatethestatus, the TPS is calculated

Calculate TPS based on COM_COMMIT and COM_ROLLBACK

Related indicators:

  • Com_commit: Total number of commit statements executed by MySQL since last startup
  • Com_rollback: Total number of MySQL fallback statements executed since last startup

1. Calculate TPS based on com_COMMIT and COM_ROLLBACK. The calculated TPS is also the average TPS of MySQL per second since last startup

com_commit = show global status where variable_name='com_commit';
com_rollback = show global status where variable_name='com_rollback';
uptime = show global status where Variable_name='Uptime';
tps=(com_commit + com_rollback)/uptime
Copy the code

2. Calculate the script

#! /usr/bin/env bash
COM_COMMIT=`echo "show global status where Variable_name='Com_commit';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
COM_ROLLBACK=`echo "show global status where Variable_name='Com_rollback';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
UPTIME=`echo "show global status where Variable_name='Uptime';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`

echo "($COM_COMMIT + $COM_ROLLBACK) / $UPTIME" | bc
Copy the code

TPS is calculated based on com_INSERT, COM_DELETE, and COM_UPDATE status

Related indicators:

  • Com_update: Total number of update statements executed by MySQL since last startup
  • Com_delete: Total number of delete statements executed by MySQL since last startup
  • Com_insert: Total number of insert statements executed by MySQL since last startup

1. Calculate the value twice within the interval specified by the script. Subtract the old indicator from the new indicator and divide by the interval to obtain the average TPS per second during the interval

#/usr/bin/env bash
OLD_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
OLD_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
sleep The $1
NEW_COM_INSERT=`echo "show global status where Variable_name='Com_insert';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_UPDATE=`echo "show global status where Variable_name='Com_update';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`
NEW_COM_DELETE=`echo "show global status where Variable_name='Com_delete';"|mysql --defaults-file=./.my.cnf -N|awk '{print $2}'`

echo "(($NEW_COM_INSERT - $OLD_COM_INSERT) + ($NEW_COM_UPDATE - $OLD_COM_UPDATE) + ($NEW_COM_DELETE - $OLD_COM_DELETE)) / The $1" | bc
Copy the code

If there is any error or disagreement on the above contents, Welcome to point out and discuss the better method in the comments section does more articles and materials | click behind the text to the left left left 100 gpython self-study data package Ali cloud K8s practical manual guide] [ali cloud CDN row pit CDN ECS Hadoop large data of actual combat operations guide the conversation practice manual manual Knative Cloud Native Application Development Guide OSS Operation and Maintenance Practice Manual Cloud native Architecture white paper Zabbix Enterprise distributed Monitoring System source document Cloud Native Basics Guide 10G