Monitoring MySQL Response Time (RT) with tcprstat and Query Response Time Plugin

Response time (RT) is the interval from when a system receives a request to when it returns a response. It reflects overall throughput and is a key metric for judging the performance of business requests such as SQL queries.

An analogy illustrates RT: a trip from Hangzhou to Beijing includes travel to the airport, security checks, flight time, and ground travel after landing. The total RT equals the sum of all these segments, while the true "execution" time is the flight duration. In technical terms, RT = waiting time + execution time, and a high RT does not necessarily mean the SQL itself is slow.

从公司到萧山机场 40min 机场安检，候机，登机 40min 飞机飞行 耗时 100min 飞机落地，打的到望京 耗时40min RT= 40 + 40 + 100 + 40 =220min

Two main ways to monitor MySQL RT are described:

2.1 tcprstat

tcprstat is a Percona tool built on libpcap that measures the time interval between TCP request and response. It is suitable for request‑response protocols and can monitor MySQL response time. The tool outputs statistics such as count, avg, and percentile values (e.g., 95_avg, 99_avg) in microseconds.

Example command:

tcprstat -p 3312 -t 1 -n 0 -l ip_address -f '%T\t%n\t%a\t%95a\t%99a\n'

Key fields:

count: number of completed requests in the interval. avg: average response time of all completed requests. 95_avg : average response time of the fastest 95% of requests (µs). To output only count and percentile averages: tcprstat -p 3312 -t 1 -n 0 -l ip_address -f '%T\t%n\t%a\t%95a\t%99a\n'

If tcprstat reports an error like:

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

Specify the local IP with the -l option to resolve it.

2.2 MySQL Query Response Time Plugin

Percona Server provides a query response time audit plugin that records the number of requests and total execution time for SQL statements falling into configurable latency ranges (controlled by query_response_time_range_base ).

Typical latency buckets: (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].

Installation commands (MySQL 5.6+):

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';

Verify installation:

SHOW PLUGINS;

Sample output shows the plugin is ACTIVE:

| 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 installation, three tables are created in INFORMATION_SCHEMA :

QUERY_RESPONSE_TIME_WRITE   – records write‑request latency distribution
QUERY_RESPONSE_TIME_READ    – records read‑request latency distribution
QUERY_RESPONSE_TIME         – records overall request latency distribution

Enable statistics collection:

SET GLOBAL query_response_time_stats = 1;

Or add to my.cnf :

query_response_time_stats = 1

Reset (clear) the tables:

SET GLOBAL query_response_time_flush = 'ON';

Typical query to retrieve statistics:

SELECT c.count, c.time,
       (SELECT SUM(a.count) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME a WHERE a.count != 0) AS query_count,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME 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 c
WHERE c.count > 0;

Result example (count, time in seconds):

| 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           |

Note that count and total are cumulative; to compute rates, subtract previous values and divide by the sampling interval.

Collected data can be visualized in Grafana, as shown by the screenshots in the original article.

In summary, the article explains the meaning of RT in a technical context and presents two practical methods—tcprstat and the Percona query response time plugin—for monitoring MySQL response time.