Percona-Server/MySQL响应时间统计

原文地址：

在Percona的5.1.53和5.5.8版本，开始将RT的统计内置到MySQL Server端。Thanks, Percona.

Percona在提供了tcprstat工具统计RT时间之后，很快就在Percona Server中集成了响应时间统计的功能。这里介绍一下该功能，各位看官如果在犹豫选择还是MySQL Community Server，这里给Percona Server加一个筹码。

"响应时间"（Response time，后面简称RT）在数据库应用中，特别是OLTP的场景，可以说非常重要，不过，MySQL官方版本中一直没有加上这个统计功能。最早，社区开始尝试tcmdump+perl脚本的方式查看RT，后来告警一点用tcpdump+mk-query-digest，再后来Ignacio Nin和Baron Schwartz完成了tcprstat。

在Percona的5.1.53和5.5.8版本，开始将RT的统计内置到MySQL Server端。

1. 配置该功能

这个是功能默认是关闭的，可以通过设置参数query_response_time_stats=1打开这个功能，这是一个动态参数，所以在服务器上可以很方便的打开或者关闭这个功能。可以通过命令SHOW QUERY_RESPONSE_TIME查看响应时间统计。

默认关闭状态：

	SHOW QUERY_RESPONSE_TIME; 
+----------------+---+----------------+
|                |   |                |
+----------------+---+----------------+
|       0.000001 | 0 |       0.000000 |
|       0.000010 | 0 |       0.000000 |
|       0.000100 | 0 |       0.000000 |
|       0.001000 | 0 |       0.000000 |
......
|  1000000.00000 | 0 |       0.000000 |
| TOO LONG       | 0 | TOO LONG       |
+----------------+---+----------------+

打开参数后：

	set global query_response_time_stats=1;
Query OK, 0 rows affected (0.00 sec)

08:23:13>SHOW QUERY_RESPONSE_TIME;
+----------------+-------+----------------+
|                |       |                |
+----------------+-------+----------------+
|       0.000001 |  7982 |       0.000000 |
|       0.000010 | 13927 |       0.014511 |
|       0.000100 | 43811 |       1.553811 |
|       0.001000 |    29 |       0.007936 |
|       0.010000 |     1 |       0.001711 |
|       0.100000 |     0 |       0.000000 |

这里可以看到，响应时间小于0.000001秒的SQL有7982个，0.000001 < RT < 0.000010有13927个， 0.000010 < RT < 0.000100有43811 ...... 。这样整个系统的响应时间就清晰了。

2. INFORMATION_SCHEMA.QUERY_RESPONSE_TIME

除了用命令SHOW QUERY_RESPONSE_TIME，还可以通过INFORMATION_SCHEMA里面的表QUERY_RESPONSE_TIME来查看MySQL的响应时间。

	08:25:16>SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;
+----------------+--------+----------------+
| time           | count  | total          |
+----------------+--------+----------------+
|       0.000001 |  33824 |       0.000000 |
|       0.000010 |  59222 |       0.060457 |
|       0.000100 | 186003 |       6.659908 |
|       0.001000 |    117 |       0.025163 |
|       0.010000 |      8 |       0.014534 |

使用INFORMATION_SCHEMA.QUERY_RESPONSE_TIME，就可以稍微定制一下输出结果了：

	SELECT time, CONCAT(ROUND(100*count/query_count,2),"%") as percent,count
FROM(
    SELECT 
    	c.count, 
    	c.time,
    	(
    	    SELECT SUM(a.count) 
    	    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a 
    	    WHERE a.count != 0
    	) as query_count
    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c 
    WHERE c.count > 0
) d;
+----------------+---------+--------+
| time           | percent | count  |
+----------------+---------+--------+
|       0.000001 | 12.64%  |  81557 |
|       0.000010 | 20.69%  | 133440 |
|       0.000100 | 66.62%  | 429705 |
|       0.001000 | 0.05%   |    339 |
|       0.010000 | 0.00%   |     10 |
|    1000.000000 | 0.00%   |      1 |
+----------------+---------+--------+

3. 高端一点的功能：定制化时间区间

默认的时间区间是：

(0; 10 ^ -6], (10 ^ -6; 10 ^ -5], (10 ^ -5; 10 ^ -4], ..., (10 ^ -1; 10 ^1], (10^1; 10^2]...(10^

你可以通过修改参数query_response_time_range_base来缩小时间区间，默认该参数是10，时间区间如上。

例如我们修个set global query_response_time_range_base=2; 则区间如下：

(0; 2 ^ -19], (2 ^ -19; 2 ^ -18], (2 ^ -18; 2 ^ -17], ..., (2 ^ -1; 2 ^1], (2 ^ 1; 2 ^ 2]...(2 ^

第一个区间总是最接近0.000001区间开始(2^19次方最接近)；最后一个区间是到最接近且小于10000000处结束。

具体的：

set global query_response_time_range_base=2;
flush QUERY_RESPONSE_TIME;
SHOW QUERY_RESPONSE_TIME;
| 0.000001 | 711 | 0.000000 |
| 0.000003 | 1456 | 0.001456 |
......
| 0.125000 | 0 | 0.000000 |
| 0.250000 | 0 | 0.000000 |
| 0.500000 | 0 | 0.000000 |
| 1.000000 | 0 | 0.000000 |
| 2.000000 | 0 | 0.000000 |
| 4.000000 | 0 | 0.000000 |
| 8.000000 | 0 | 0.000000 |
| 16.000000 | 0 | 0.000000 |
......
| 8388608.00000 | 0 | 0.000000 |
| TOO LONG | 0 | TOO LONG |

一些注意事项

在备库上，只有打开了参数(这个参数也是Percona Server上特有的)时，slave sql线程的SQL才会被统计。

启动变量have_response_time_distribution，不能配置，该参数只表示该Server是否具有上述的RT统计功能。

设置了query_response_time_range_base，必须flush QUERY_RESPONSE_TIME后才生效。这里flush做两件事情，使得QUERY_RESPONSE_TIME生效，另外清空之前的统计结果。

既然上面读取INFORMATION_SCHEMA的SQL已经有些复杂了，那就不怕再复杂一些：

	

		SELECT 	
    case TRIM(time) 
    	when '0.000001' then '<  1us'
    	when '0.000010' then '< 10us'
    	when '0.000100' then '<100us'
    	when '0.001000' then '<  1ms'
    	when '0.010000' then '< 10ms'
    	when '0.100000' then '<100ms'
    	when '1.000000' then '<   1s'
    	when '10.000000' then '<  10s'
    	when '100.000000' then '<100s'
    	else '>100s'
    END as `RT area`,
    CONCAT(ROUND(100*count/query_count,2),"%") as percent,
    count
FROM(
    SELECT
    	c.count,
    	c.time,
    	(
    	    SELECT SUM(a.count)
    	    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a
    	    WHERE a.count != 0
    	) as query_count
    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c
    WHERE c.count > 0
) d;
+---------+---------+--------+
| RT area | percent | count  |
+---------+---------+--------+
| < 10us  | 41.92%  | 404409 |
| <100us  | 21.34%  | 205881 |
| <  1ms  | 35.20%  | 339574 |
| < 10ms  | 1.30%   |  12586 |
| <100ms  | 0.18%   |   1736 |
| <   1s  | 0.02%   |    160 |
| <  10s  | 0.04%   |    340 |
+---------+---------+--------+