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, reflecting overall throughput and serving as a key performance indicator for SQL queries.

An analogy compares RT to a flight from Hangzhou to Beijing, where the actual flight time is the execution time and all waiting periods (check‑in, security, traffic) constitute waiting time, leading to the formula RT = waiting time + execution time .

From a technical perspective, a SQL request traverses the application, network, proxy, and finally MySQL; network packet loss, retransmissions, high CPU usage, or Java GC can increase RT, but a high RT does not always mean a slow SQL statement.

Key point: Do not treat the RT shown in trace systems as the database execution time.

Two main ways to monitor MySQL RT are presented:

1. tcprstat

tcprstat, developed by Percona on top of libpcap, measures the time between TCP request and response, suitable for request‑response protocols like MySQL. It outputs timestamps, max, average, variance, and custom fields such as 95_avg (average response time of the fastest 95% of requests, in microseconds).

Example command to display count and 95th/99th percentile averages:

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

If the tool reports pcap: SIOCGIFFLAGS: bonding_masters: No such device , specify the local IP with -l local_ip to resolve the issue.

2. MySQL Query Response Time Plugin

Percona Server provides a plugin that records the number of queries and total execution time for each response‑time interval (e.g., (0,0.000001], (0.000001,0.000010], …). Installation commands:

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

After installation, verify with SHOW PLUGINS; and the plugin creates three tables in INFORMATION_SCHEMA : QUERY_RESPONSE_TIME_WRITE , QUERY_RESPONSE_TIME_READ , and QUERY_RESPONSE_TIME .

Enable statistics globally:

SET GLOBAL query_response_time_stats = 1;

Reset statistics:

SET GLOBAL query_response_time_flush = 'ON';

Sample query to retrieve counts, times, and totals from the tables is provided, and the result shows how many queries fall into each interval.

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

For more details, refer to the official Percona documentation.

Conclusion: The article summarizes the meaning of RT in a technical system and introduces two methods to monitor MySQL response time, inviting readers to share alternative approaches.