Community contribution | how to understand RT correctly and monitor MySQL response time

Time:2020-5-21

Author: Yang Qilong
The network name “North in the South”, a 7-year veteran of DBA, currently works in Hangzhou youzan technology DBA, mainly responsible for database architecture design and operation and maintenance platform development, and is good at database performance optimization and fault diagnosis.

1、 Foreword

Response time (RT for short) is the time span between receiving request and returning response, which is an extremely important performance index. It can reflect the overall throughput of the system from the side, and it is also the basis for judging the performance of business requests (such as SQL requests).

For example, a to fly from Hangzhou to Beijing airport, the experience is as follows:

40min from the company to Xiaoshan Airport
Airport security check, waiting, boarding 40min
The flight time is 100min
It takes 40 minutes for the plane to land in Wangjing
RT= 40 + 40 + 100 + 40 =220min

The real “execution” time is the flight time of the aircraft (100min + 40 + 40). Other security checks, waiting times and traffic jams are waiting times.

RT = waiting time + execution time

If there is a traffic jam in the process of arriving at the airport, or air control causes the waiting time to be extended, the overall RT will also be longer, but the flight time of the aircraft is relatively certain. SQL request path from a technical point of view

App < — > proxy < — > MySQL (execute)

Because of network problems such as packet loss and retransmission, the data transmission time increases, which leads to the increase of the overall RT time. There are also common cases where the high CPU of app server leads to the slow speed of program execution, and the factors such as Java program GC also lead to the increase of RT. So SQL is slow, but RT is high. But vice versaHigh RT is not necessarily the reason why SQL is slow。 If the development students encounter monitoring, especially the trace system finds that an interface is slow, it is not necessarily SQL slow.

a key:Do not regard the monitoring RT in trace system as the execution time of DB directly

Reference case strace case

2、 How to monitor

The definition of RT and what it stands for are mentioned above. Next let’s see how to monitor the RT of the database. There are two main ways.

2.1 tcprstat

tcprstatIt is a tool developed by percona based on Libpcap. It measures the time interval required by the request and response of TCP and is applicable to the processing of one question one answer protocol type. Usually used to monitor the response time of MySQL, or the processing time of the request on the server side. The output includes the response time related statistics, which is used to diagnose the performance of the server side.
for instance:

The output includes time stamp, maximum value, mean value, variance and other information of response time. The output information can be customized by – f parameter, and the unit of response time is subtle. What is more important to us is:

Count: the number of requests processed in this interval.
AVG: the average time of all completed requests and responses in this interval.
95_avg: in this interval, the average response time of 95% of the requests, with subtle units, is better to reflect the average response time of MySQL server queries.
If we only need to output count, the average time,95_avg,99_avgYou can use the following command.
tcprstat -p 3312 -t 1 -n 0 -l ip_address -f ‘%Tt%nt%at%95at%99an’


The parameters of – F are explained as follows. Readers can adjust the output according to their needs

If tcprstat is executed, the following problems are encountered

# tcprstat -p 3312 -t 1 -n 5
pcap: SIOCGIFFLAGS: bonding_masters: No such device

You can specify the localip -l local_ipTo solve it.

2.2 MySQL plug in

Percona server provides a function called response time interval, which records the number of requests and the total execution time of SQL in the specified interval into the table. The time interval span is determined byquery_response_time_range_baseControl.

The commonly used ranges are: (0, 0.000001], (0.000001, 0.000010], (0.000010, 0.000100], (0.000100, 0.001000], (0.001000, 0.010000], (0.010000, 0.100000], (0.100000, 1.000000], (1,10).

Install MySQL 5.6 as a plug-in:

INSTALL PLUGIN QUERY_RESPONSE_TIME_AUDIT SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME_READ SONAME 'query_response_time.so';
INSTALL PLUGIN QUERY_RESPONSE_TIME_WRITE SONAME 'query_response_time.so';

Then check whether the plug-in is installed successfully through the show plugins command.

> SHOW PLUGINS;
......
| QUERY_RESPONSE_TIME         | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
| QUERY_RESPONSE_TIME_AUDIT   | ACTIVE   | AUDIT              | query_response_time.so | GPL     |
| QUERY_RESPONSE_TIME_READ    | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
| QUERY_RESPONSE_TIME_WRITE   | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
+-----------------------------+----------+--------------------+------------------------+---------

After installationINFORMATION_SCHEMAGenerate three tables

QUERY_ RESPONSE_ TIME_ Write records the response time distribution of all write requests
QUERY_ RESPONSE_ TIME_ Read records the response time distribution of all read requests
QUERY_ RESPONSE_ Time can be considered as the response time distribution of all requests.

seeQUERY_RESPONSE_TIMEContent of
Community contribution | how to understand RT correctly and monitor MySQL response time
717 SQL requests in the query results took between (0, 0.000001]. 47898 SQL requests take (0.000001, 0.000010), the total time is 0.29 seconds, and so on.It should be noted that count and total are cumulative values. During monitoring, you need to take the post value minus the pre value divided by the sampling interval
How to turn on response time statistics
Execute on the command line

SET GLOBAL query_response_time_stats = 1 ;

At my.cnf in

query_response_time_stats = 1

Reset (clear data) the statistics of the three tables

SET GLOBAL query_response_time_flush=’ON’;

Common SQL

INFORMATION_SCHEMA [RW][TEST:qa_single_0:3312] 11:50:44 
>SELECT c.count, c.time, (SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a WHERE a.count != 0) as query_count, (SELECT COUNT(*)     FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as b WHERE b.count != 0) as not_zero_region_count, (SELECT COUNT(*)     FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME) as region_count FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c WHERE c.count > 0;
+-------+----------------+-------------+-----------------------+--------------+
| count | time           | query_count | not_zero_region_count | region_count |
+-------+----------------+-------------+-----------------------+--------------+
|     1 |       0.000001 |       71370 |                     7 |           14 |
|    86 |       0.000010 |       71370 |                     7 |           14 |
| 47375 |       0.000100 |       71370 |                     7 |           14 |
| 23404 |       0.001000 |       71370 |                     7 |           14 |
|   423 |       0.010000 |       71370 |                     7 |           14 |
|    79 |       0.100000 |       71370 |                     7 |           14 |
|     2 |       1.000000 |       71370 |                     7 |           14 |
+-------+----------------+-------------+-----------------------+--------------+
7 rows in set (0.00 sec)

The data of response time obtained by monitoring script is shown in grafna as follows:
Community contribution | how to understand RT correctly and monitor MySQL response time
Community contribution | how to understand RT correctly and monitor MySQL response time
Other more detailed introductions can be found in percona’s official documents.

3、 Summary

This paper summarizes the meaning of RT in the technical system, and introduces two methods of monitoring MySQL response time. If there are other better ways, readers are welcome to discuss.

Reference article

  1. https://www.percona.com/doc/p…
  2. https://jin-yang.github.io/po…
  3. https://www.percona.com/doc/p…
  4. https://www.aikaiyuan.com/546…

Recommended Today

Python basics Chinese series tutorial · translation completed

Original: Python basics Python tutorial Protocol: CC by-nc-sa 4.0 Welcome anyone to participate and improve: a person can go very fast, but a group of people can go further. Online reading Apache CN learning resources catalog introduce Seven reasons to learn Python Why Python is great Learn Python introduction Executing Python scripts variable character string […]