{"id":3068,"date":"2011-09-19T21:02:57","date_gmt":"2011-09-19T13:02:57","guid":{"rendered":"http:\/\/www.orczhou.com\/?p=3068"},"modified":"2011-10-26T15:46:19","modified_gmt":"2011-10-26T07:46:19","slug":"thanks-percona-response-time-distribution","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2011\/09\/thanks-percona-response-time-distribution\/","title":{"rendered":"Percona-Server\/MySQL\u54cd\u5e94\u65f6\u95f4\u7edf\u8ba1"},"content":{"rendered":"<p>\u5728Percona\u76845.1.53\u548c5.5.8\u7248\u672c\uff0c\u5f00\u59cb\u5c06RT\u7684\u7edf\u8ba1\u5185\u7f6e\u5230MySQL Server\u7aef\u3002Thanks, Percona.<\/p>\n<p>Percona\u5728\u63d0\u4f9b\u4e86<a href=\"http:\/\/www.mysqlperformanceblog.com\/2010\/08\/31\/introducing-tcprstat-a-tcp-response-time-tool\/\">tcprstat\u5de5\u5177<\/a>\u7edf\u8ba1RT\u65f6\u95f4\u4e4b\u540e\uff0c\u5f88\u5feb\u5c31\u5728Percona Server\u4e2d\u96c6\u6210\u4e86\u54cd\u5e94\u65f6\u95f4\u7edf\u8ba1\u7684\u529f\u80fd\u3002\u8fd9\u91cc\u4ecb\u7ecd\u4e00\u4e0b\u8be5\u529f\u80fd\uff0c\u5404\u4f4d\u770b\u5b98\u5982\u679c\u5728\u72b9\u8c6b\u9009\u62e9<a href=\"http:\/\/www.percona.com\/software\/percona-server\/\">Percona Server<\/a>\u8fd8\u662f<a href=\"http:\/\/dev.mysql.com\/downloads\/mysql\/\">MySQL Community Server<\/a>\uff0c\u8fd9\u91cc\u7ed9Percona Server\u52a0\u4e00\u4e2a\u7b79\u7801\u3002<\/p>\n<p>&#8220;\u54cd\u5e94\u65f6\u95f4&#8221;\uff08Response time\uff0c\u540e\u9762\u7b80\u79f0RT\uff09\u5728\u6570\u636e\u5e93\u5e94\u7528\u4e2d\uff0c\u7279\u522b\u662fOLTP\u7684\u573a\u666f\uff0c\u53ef\u4ee5\u8bf4\u975e\u5e38\u91cd\u8981\uff0c\u4e0d\u8fc7\uff0cMySQL\u5b98\u65b9\u7248\u672c\u4e2d\u4e00\u76f4\u6ca1\u6709\u52a0\u4e0a\u8fd9\u4e2a\u7edf\u8ba1\u529f\u80fd\u3002\u6700\u65e9\uff0c\u793e\u533a\u5f00\u59cb\u5c1d\u8bd5tcmdump+perl\u811a\u672c\u7684\u65b9\u5f0f\u67e5\u770bRT\uff0c\u540e\u6765\u544a\u8b66\u4e00\u70b9\u7528<a href=\"http:\/\/ronaldbradford.com\/blog\/take-a-look-at-mk-query-digest-2009-10-08\/\">tcpdump+mk-query-digest<\/a>\uff0c\u518d\u540e\u6765Ignacio Nin\u548cBaron Schwartz\u5b8c\u6210\u4e86tcprstat\u3002<\/p>\n<p>\u5728Percona\u76845.1.53\u548c5.5.8\u7248\u672c\uff0c\u5f00\u59cb\u5c06RT\u7684\u7edf\u8ba1\u5185\u7f6e\u5230MySQL Server\u7aef\u3002<\/p>\n<div class=\"myt1\">1. \u914d\u7f6e\u8be5\u529f\u80fd<\/div>\n<p>\u8fd9\u4e2a\u662f\u529f\u80fd\u9ed8\u8ba4\u662f\u5173\u95ed\u7684\uff0c\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u53c2\u6570query_response_time_stats=1\u6253\u5f00\u8fd9\u4e2a\u529f\u80fd\uff0c\u8fd9\u662f\u4e00\u4e2a\u52a8\u6001\u53c2\u6570\uff0c\u6240\u4ee5\u5728\u670d\u52a1\u5668\u4e0a\u53ef\u4ee5\u5f88\u65b9\u4fbf\u7684\u6253\u5f00\u6216\u8005\u5173\u95ed\u8fd9\u4e2a\u529f\u80fd\u3002\u53ef\u4ee5\u901a\u8fc7\u547d\u4ee4SHOW QUERY_RESPONSE_TIME\u67e5\u770b\u54cd\u5e94\u65f6\u95f4\u7edf\u8ba1\u3002<!--more--><\/p>\n<div class=\"myt2\">\u9ed8\u8ba4\u5173\u95ed\u72b6\u6001\uff1a<\/div>\n<pre><div class=\"mycode\">SHOW QUERY_RESPONSE_TIME; \r\n+----------------+---+----------------+\r\n|                |   |                |\r\n+----------------+---+----------------+\r\n|       0.000001 | 0 |       0.000000 |\r\n|       0.000010 | 0 |       0.000000 |\r\n|       0.000100 | 0 |       0.000000 |\r\n|       0.001000 | 0 |       0.000000 |\r\n......\r\n|  1000000.00000 | 0 |       0.000000 |\r\n| TOO LONG       | 0 | TOO LONG       |\r\n+----------------+---+----------------+<\/div><\/pre>\n<div class=\"myt2\">\u6253\u5f00\u53c2\u6570\u540e\uff1a<\/div>\n<pre><div class=\"mycode\">set global query_response_time_stats=1;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n08:23:13>SHOW QUERY_RESPONSE_TIME;\r\n+----------------+-------+----------------+\r\n|                |       |                |\r\n+----------------+-------+----------------+\r\n|       0.000001 |  7982 |       0.000000 |\r\n|       0.000010 | 13927 |       0.014511 |\r\n|       0.000100 | 43811 |       1.553811 |\r\n|       0.001000 |    29 |       0.007936 |\r\n|       0.010000 |     1 |       0.001711 |\r\n|       0.100000 |     0 |       0.000000 |\r\n<\/div><\/pre>\n<p>\u8fd9\u91cc\u53ef\u4ee5\u770b\u5230\uff0c\u54cd\u5e94\u65f6\u95f4\u5c0f\u4e8e0.000001<strong>\u79d2<\/strong>\u7684SQL\u67097982\u4e2a\uff0c0.000001 &lt; RT &lt;  0.000010\u670913927\u4e2a\uff0c 0.000010 &lt; RT &lt; 0.000100\u670943811  &#8230;&#8230; \u3002\u8fd9\u6837\u6574\u4e2a\u7cfb\u7edf\u7684\u54cd\u5e94\u65f6\u95f4\u5c31\u6e05\u6670\u4e86\u3002<\/p>\n<div class=\"myt1\">2. INFORMATION_SCHEMA.QUERY_RESPONSE_TIME<\/div>\n<p>\u9664\u4e86\u7528\u547d\u4ee4SHOW QUERY_RESPONSE_TIME\uff0c\u8fd8\u53ef\u4ee5\u901a\u8fc7INFORMATION_SCHEMA\u91cc\u9762\u7684\u8868QUERY_RESPONSE_TIME\u6765\u67e5\u770bMySQL\u7684\u54cd\u5e94\u65f6\u95f4\u3002<\/p>\n<pre><div class=\"mycode\">08:25:16>SELECT * from INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;\r\n+----------------+--------+----------------+\r\n| time           | count  | total          |\r\n+----------------+--------+----------------+\r\n|       0.000001 |  33824 |       0.000000 |\r\n|       0.000010 |  59222 |       0.060457 |\r\n|       0.000100 | 186003 |       6.659908 |\r\n|       0.001000 |    117 |       0.025163 |\r\n|       0.010000 |      8 |       0.014534 |<\/div><\/pre>\n<p>\u4f7f\u7528INFORMATION_SCHEMA.QUERY_RESPONSE_TIME\uff0c\u5c31\u53ef\u4ee5\u7a0d\u5fae\u5b9a\u5236\u4e00\u4e0b\u8f93\u51fa\u7ed3\u679c\u4e86\uff1a<\/p>\n<pre><div class=\"mycode\">\r\nSELECT time, CONCAT(ROUND(100*count\/query_count,2),\"%\") as percent,count\r\nFROM(\r\n    SELECT \r\n    \tc.count, \r\n    \tc.time,\r\n    \t(\r\n    \t    SELECT SUM(a.count) \r\n    \t    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a \r\n    \t    WHERE a.count != 0\r\n    \t) as query_count\r\n    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c \r\n    WHERE c.count > 0\r\n) d;\r\n+----------------+---------+--------+\r\n| time           | percent | count  |\r\n+----------------+---------+--------+\r\n|       0.000001 | 12.64%  |  81557 |\r\n|       0.000010 | 20.69%  | 133440 |\r\n|       0.000100 | 66.62%  | 429705 |\r\n|       0.001000 | 0.05%   |    339 |\r\n|       0.010000 | 0.00%   |     10 |\r\n|    1000.000000 | 0.00%   |      1 |\r\n+----------------+---------+--------+\r\n<\/div><\/pre>\n<div class=\"myt1\">3. \u9ad8\u7aef\u4e00\u70b9\u7684\u529f\u80fd\uff1a\u5b9a\u5236\u5316\u65f6\u95f4\u533a\u95f4<\/div>\n<p>\u9ed8\u8ba4\u7684\u65f6\u95f4\u533a\u95f4\u662f\uff1a<\/p>\n<p>(0; 10 ^ -6], (10 ^ -6; 10 ^ -5], (10 ^ -5; 10 ^ -4], &#8230;, (10 ^ -1; 10 ^1], (10^1; 10^2]&#8230;(10^<\/p>\n<p>\u4f60\u53ef\u4ee5\u901a\u8fc7\u4fee\u6539\u53c2\u6570query_response_time_range_base\u6765\u7f29\u5c0f\u65f6\u95f4\u533a\u95f4\uff0c\u9ed8\u8ba4\u8be5\u53c2\u6570\u662f10\uff0c\u65f6\u95f4\u533a\u95f4\u5982\u4e0a\u3002<\/p>\n<p>\u4f8b\u5982\u6211\u4eec\u4fee\u4e2aset global query_response_time_range_base=2; \u5219\u533a\u95f4\u5982\u4e0b\uff1a<\/p>\n<p>(0; 2 ^ -19], (2 ^ -19; 2 ^ -18], (2 ^ -18; 2 ^ -17], &#8230;, (2 ^ -1; 2 ^1], (2 ^ 1; 2 ^ 2]&#8230;(2 ^<\/p>\n<p>\u7b2c\u4e00\u4e2a\u533a\u95f4\u603b\u662f\u6700\u63a5\u8fd10.000001\u533a\u95f4\u5f00\u59cb(2^19\u6b21\u65b9\u6700\u63a5\u8fd1)\uff1b\u6700\u540e\u4e00\u4e2a\u533a\u95f4\u662f\u5230\u6700\u63a5\u8fd1\u4e14\u5c0f\u4e8e10000000\u5904\u7ed3\u675f\u3002<\/p>\n<p>\u5177\u4f53\u7684\uff1a<\/p>\n<div class=\"mycode\">set global query_response_time_range_base=2;<br \/>\nflush QUERY_RESPONSE_TIME;<br \/>\nSHOW QUERY_RESPONSE_TIME;<br \/>\n|       0.000001 |  711 |       0.000000 |<br \/>\n|       0.000003 | 1456 |       0.001456 |<br \/>\n&#8230;&#8230;<br \/>\n|       0.125000 |    0 |       0.000000 |<br \/>\n|       0.250000 |    0 |       0.000000 |<br \/>\n|       0.500000 |    0 |       0.000000 |<br \/>\n|       1.000000 |    0 |       0.000000 |<br \/>\n|       2.000000 |    0 |       0.000000 |<br \/>\n|       4.000000 |    0 |       0.000000 |<br \/>\n|       8.000000 |    0 |       0.000000 |<br \/>\n|      16.000000 |    0 |       0.000000 |<br \/>\n&#8230;&#8230;<br \/>\n|  8388608.00000 |    0 |       0.000000 |<br \/>\n| TOO LONG       |    0 | TOO LONG       |\n<\/div>\n<div class=\"myt1\">\u4e00\u4e9b\u6ce8\u610f\u4e8b\u9879<\/div>\n<p>\u5728\u5907\u5e93\u4e0a\uff0c\u53ea\u6709\u6253\u5f00\u4e86\u53c2\u6570<a href=\"log_slow_slave_statements\">log_slow_slave_statements<\/a>(\u8fd9\u4e2a\u53c2\u6570\u4e5f\u662fPercona Server\u4e0a\u7279\u6709\u7684)\u65f6\uff0cslave sql\u7ebf\u7a0b\u7684SQL\u624d\u4f1a\u88ab\u7edf\u8ba1\u3002<\/p>\n<p>\u542f\u52a8\u53d8\u91cfhave_response_time_distribution\uff0c\u4e0d\u80fd\u914d\u7f6e\uff0c\u8be5\u53c2\u6570\u53ea\u8868\u793a\u8be5Server\u662f\u5426\u5177\u6709\u4e0a\u8ff0\u7684RT\u7edf\u8ba1\u529f\u80fd\u3002<\/p>\n<p>\u8bbe\u7f6e\u4e86query_response_time_range_base\uff0c\u5fc5\u987bflush QUERY_RESPONSE_TIME\u540e\u624d\u751f\u6548\u3002\u8fd9\u91ccflush\u505a\u4e24\u4ef6\u4e8b\u60c5\uff0c\u4f7f\u5f97QUERY_RESPONSE_TIME\u751f\u6548\uff0c\u53e6\u5916\u6e05\u7a7a\u4e4b\u524d\u7684\u7edf\u8ba1\u7ed3\u679c\u3002<\/p>\n<p>\u7d2f\u4e86\uff0c\u542c\u9996\u6b4c\uff0c\u626d\u626d\u8116\u5b50\u5427:)<\/p>\n<p><script type=\"text\/javascript\" src=\"http:\/\/www.xiami.com\/widget\/player-single?uid=0&#038;sid=3599116&#038;mode=js\"><\/script><\/p>\n<p>\u5e7f\u544a\uff1a<a href=\"http:\/\/www.orczhou.com\/index.php\/projects\/we-are-hunting-mysql-hacker\/\">\u6211\u4eec\u5bfb\u627e\u9760\u8c31\u7684\u4eba<\/a> | <a href=\"http:\/\/www.orczhou.com\/index.php\/wish-list\/\">\u611f\u8c22\u4f5c\u8005<\/a><\/p>\n<p>\u53c2\u8003\u6587\u732e\uff1a<\/p>\n<p>1. <a href=\"http:\/\/www.mysqlperformanceblog.com\/2010\/08\/31\/introducing-tcprstat-a-tcp-response-time-tool\/\">Introducing tcprstat, a TCP response time tool<\/a><\/p>\n<p>2. <a href=\"http:\/\/www.percona.com\/docs\/wiki\/tcprstat:start\">The tcprstat User&#8217;s Manual<\/a><\/p>\n<p>3. <a href=\"http:\/\/www.megalinux.net\/using-tcpdump-for-mysql-query-logging\/\">Using Tcpdump for MySQL query logging<\/a><\/p>\n<p>4. <a href=\"http:\/\/www.mysqlperformanceblog.com\/2011\/04\/18\/how-to-use-tcpdump-on-very-busy-hosts\/\">How to use tcpdump on very busy hosts<\/a><\/p>\n<p>5. <a href=\"http:\/\/ronaldbradford.com\/blog\/take-a-look-at-mk-query-digest-2009-10-08\/\">Take a look at mk-query-digest<\/a><\/p>\n<p>6. <a href=\"http:\/\/www.percona.com\/docs\/wiki\/percona-server:features:implementation_details:details_response_time_distribution\">Implementation Details: Response Time Distribution<\/a><\/p>\n<p>7. <a href=\"http:\/\/www.percona.com\/docs\/wiki\/percona-server:features:response_time_distribution\">Percona Software Documentation :Response Time Distribution<\/a><\/p>\n<p>\u3010\u9644\u5f55\u3011<\/p>\n<p>\u65e2\u7136\u4e0a\u9762\u8bfb\u53d6INFORMATION_SCHEMA\u7684SQL\u5df2\u7ecf\u6709\u4e9b\u590d\u6742\u4e86\uff0c\u90a3\u5c31\u4e0d\u6015\u518d\u590d\u6742\u4e00\u4e9b\uff1a<\/p>\n<pre><div class=\"mycode\">SELECT \t\r\n    case TRIM(time) \r\n    \twhen '0.000001' then '<  1us'\r\n    \twhen '0.000010' then '< 10us'\r\n    \twhen '0.000100' then '<100us'\r\n    \twhen '0.001000' then '<  1ms'\r\n    \twhen '0.010000' then '< 10ms'\r\n    \twhen '0.100000' then '<100ms'\r\n    \twhen '1.000000' then '<   1s'\r\n    \twhen '10.000000' then '<  10s'\r\n    \twhen '100.000000' then '<100s'\r\n    \telse '>100s'\r\n    END as `RT area`,\r\n    CONCAT(ROUND(100*count\/query_count,2),\"%\") as percent,\r\n    count\r\nFROM(\r\n    SELECT\r\n    \tc.count,\r\n    \tc.time,\r\n    \t(\r\n    \t    SELECT SUM(a.count)\r\n    \t    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a\r\n    \t    WHERE a.count != 0\r\n    \t) as query_count\r\n    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c\r\n    WHERE c.count > 0\r\n) d;\r\n+---------+---------+--------+\r\n| RT area | percent | count  |\r\n+---------+---------+--------+\r\n| < 10us  | 41.92%  | 404409 |\r\n| <100us  | 21.34%  | 205881 |\r\n| <  1ms  | 35.20%  | 339574 |\r\n| < 10ms  | 1.30%   |  12586 |\r\n| <100ms  | 0.18%   |   1736 |\r\n| <   1s  | 0.02%   |    160 |\r\n| <  10s  | 0.04%   |    340 |\r\n+---------+---------+--------+\r\n<\/div><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5728Percona\u76845.1.53\u548c5.5.8\u7248\u672c\uff0c\u5f00\u59cb\u5c06RT\u7684\u7edf\u8ba1\u5185\u7f6e\u5230MySQL Server\u7aef\u3002Thanks, Percona. Percona\u5728\u63d0\u4f9b\u4e86tcprstat\u5de5\u5177\u7edf\u8ba1RT\u65f6\u95f4\u4e4b\u540e\uff0c\u5f88\u5feb\u5c31\u5728Percona Server\u4e2d\u96c6\u6210\u4e86\u54cd\u5e94\u65f6\u95f4\u7edf\u8ba1\u7684\u529f\u80fd\u3002\u8fd9\u91cc\u4ecb\u7ecd\u4e00\u4e0b\u8be5\u529f\u80fd\uff0c\u5404\u4f4d\u770b\u5b98\u5982\u679c\u5728\u72b9\u8c6b\u9009\u62e9Percona Server\u8fd8\u662fMySQL Community Server\uff0c\u8fd9\u91cc\u7ed9Percona Server\u52a0\u4e00\u4e2a\u7b79\u7801\u3002 &#8220;\u54cd\u5e94\u65f6\u95f4&#8221;\uff08Response time\uff0c\u540e\u9762\u7b80\u79f0RT\uff09\u5728\u6570\u636e\u5e93\u5e94\u7528\u4e2d\uff0c\u7279\u522b\u662fOLTP\u7684\u573a\u666f\uff0c\u53ef\u4ee5\u8bf4\u975e\u5e38\u91cd\u8981\uff0c\u4e0d\u8fc7\uff0cMySQL\u5b98\u65b9\u7248\u672c\u4e2d\u4e00\u76f4\u6ca1\u6709\u52a0\u4e0a\u8fd9\u4e2a\u7edf\u8ba1\u529f\u80fd\u3002\u6700\u65e9\uff0c\u793e\u533a\u5f00\u59cb\u5c1d\u8bd5tcmdump+perl\u811a\u672c\u7684\u65b9\u5f0f\u67e5\u770bRT\uff0c\u540e\u6765\u544a\u8b66\u4e00\u70b9\u7528tcpdump+mk-query-digest\uff0c\u518d\u540e\u6765Ignacio Nin\u548cBaron Schwartz\u5b8c\u6210\u4e86tcprstat\u3002 \u5728Percona\u76845.1.53\u548c5.5.8\u7248\u672c\uff0c\u5f00\u59cb\u5c06RT\u7684\u7edf\u8ba1\u5185\u7f6e\u5230MySQL Server\u7aef\u3002 1. \u914d\u7f6e\u8be5\u529f\u80fd \u8fd9\u4e2a\u662f\u529f\u80fd\u9ed8\u8ba4\u662f\u5173\u95ed\u7684\uff0c\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u53c2\u6570query_response_time_stats=1\u6253\u5f00\u8fd9\u4e2a\u529f\u80fd\uff0c\u8fd9\u662f\u4e00\u4e2a\u52a8\u6001\u53c2\u6570\uff0c\u6240\u4ee5\u5728\u670d\u52a1\u5668\u4e0a\u53ef\u4ee5\u5f88\u65b9\u4fbf\u7684\u6253\u5f00\u6216\u8005\u5173\u95ed\u8fd9\u4e2a\u529f\u80fd\u3002\u53ef\u4ee5\u901a\u8fc7\u547d\u4ee4SHOW QUERY_RESPONSE_TIME\u67e5\u770b\u54cd\u5e94\u65f6\u95f4\u7edf\u8ba1\u3002<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[6],"tags":[118,74],"class_list":["post-3068","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql","tag-percona-mysql"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=3068"}],"version-history":[{"count":35,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3068\/revisions"}],"predecessor-version":[{"id":3099,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/3068\/revisions\/3099"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=3068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=3068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=3068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}