Orczhou

MySQL/InnoDB和Group Commit(2)

orczhou — Tue, 20 Dec 2011 23:51:20 +0800

今天发现Percona Release的Percona-Server-5-5-18-23-0已经完成了Group Commit工作，而且是用最优雅的方式（移植了MariaDB的实现，而不是workaround），心里难掩激动。

这篇文章接前篇继续介绍一下问题的背景：什么是Group Commit，现在的官方版本Group Commit做到了什么程度？

1. 什么是Group Commit

MySQL/InnoDB在做事务的时候使用的日志先行（Write-ahead logging）的方式保证事务的快速和持久，所以每次事务完成都要求日志必须持久化到磁盘，在Linux上对应的操作就是“write and fsync”，write速度是很快的，一般对应的写Page Cache，而fsync则要求文件系统把对应文件的哦Page Cache必须持久化到磁盘上，而对于传统磁盘一次写操作大约需要1~3ms（不说BBU），这意味着对于传统磁盘1秒钟最多最多做333～1000个事务，这是不理想的，对硬件的利用率（吞吐量）也是非常低。

所以，这里就有了Group操作的概念，即当好几个线程都要fsync同一个日志文件时，我们将这些fsync合并到一次来做。简单举例：我们让系统每2ms做一次fsync，这样如果这2ms内有100个线程都需要做fsync，那就赚到了，原本100个fsync都独立来做那耗时会非常多的。

你肯定会说，难道这不是很简单的想法吗？是的，这就是原本是很简单、也很自然的想法。

但对MySQL来说却变成了一种奢求（看看这个Bug讨论）。

2. 为啥MySQL一直没有实现？

MySQL是不是太弱了，这么简单的事情都搞不定？不是的。

MySQL从开源到现在，成功的一个非常重要的原因，就是MySQL的插件式架构。如果MySQL只是MyISAM估计不会有现在的流行程度，插件式的架构让诸如Heikki Tuuri有了发挥空间，在InnoDB和MySQL一起时，MySQL才能算是一个真正的DBMS。再到后来，有Infobright、 FEDERATED、PBXT等等。

插件式的架构给MySQL带来了活力，做出牺牲便是在上层(MySQL)和下层(存储引擎)交互时带来的额外消耗，有时甚至上层和下层需要做一些重复工作。无法做Group Commit就是这其中的牺牲之一。

3. 现在的官方版本Group Commit做到了什么程度

InnoDB在一次事务中，有两次fsync，分别是prepare阶段和commit阶段，对于这两次操作InnoDB都是实现了Group操作的。

如果你设置了binlog_sync=1，则又多了一次fsync操作（参考MYSQL_BIN_LOG::flush_and_sync），这次操作在innobase_xa_prepare和innodb_commit之间：

binlog_prepare (do nothing)
innodb_prepare
...
binlog_commit
innobase_commit

这次fsync，在MySQL中一直都无法做Group。所以，一直以来当innodb_flush_log_at_trx_commit和binlog_sync都等于1的时候，MySQL的性能就非常、非常糟糕。原因是为了保证InnoDB内部Commit的顺序和MySQL日志的顺序一致（参考）。

4. 开源社区的努力

难道没人去解决这个问题吗？不是的。有很多人已经做出了努力，直到Kristian Nielsen@MariaDB提出了理论和实际上的最优解决方案。

Mark Callaghan@Facebook关于Group Commit做的工作：FB的基本实现 | FB的问题和改进 | 性能 | 对比MariaDB的实现(对比的结果是MariaDB完胜，无论是方案架构还是性能)

Facebooke可以说是通过一个Trick快速实现了Group Commit。而Kristian Nielsen@MariaDB这是从架构上根本的解决了这个问题。

MariaDB关于Group Commit架构和实现： WL116 | WL132 | WL164

5. 未完待续

后面还有很多值得写的，我希望自己能够一直写完这个系列。

使用tbdba-restore-mysqldump.pl切割mysqldump文件

orczhou — Wed, 14 Dec 2011 22:40:42 +0800

这里介绍一个最近用得很多的一个小工具：tbdba-restore-mysqldump.pl。

主要有两个功能：

(1) 尽可能快的从一个非常大的mysqldump文件的分离出某个单表的备份文件

(2) 可以帮你把一个大的mysqldump文件，切割成非常小的单表备份文件（可继续做并行恢复）

1. 什么时候需要这么做

(1) 如果把MySQL中某一个表数据弄丢了，需要从很大的mysqldump备份文件中恢复这个表

(2) 如果你想并行恢复整个mysqldump备份文件时，这个脚本可以帮你把大文件切割成多个小的单表备份文件，然后就可以方便并行恢复多个文件了

2. 如何使用这个脚本

这里以实例的方式介绍如何使用该脚本：

(1) 从backup.sql文件中获取表process的备份：

tbdba-restore-mysqldump.pl -t process -f backup.sql

(2) 从backup.sql文件中获取数据库monitor中的表process的备份：

tbdba-restore-mysqldump.pl -t process -s monitor -f backup.sql

(3) 从backup.sql文件中获取多个表的备份文件（例如表process、users）：

tbdba-restore-mysqldump.pl -t process,user -s monitor -f backup.sql

(4) 直接接收来自管道的输出（如果你的mysqldump备份是压缩后，则可以使用）：

gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -t process,user -s monitor

(5) 从backup.sql文件中获取数据库monitor下所有表的备份文件：

gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -s monitor

(6) 从backup.sql文件中获取所有数据库下所有表的备份文件：

gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl --all-tables

(7) 使用-d参数，则可以看到切割的过程中的更多信息：

date && gunzip -c /backdir/backup.sql.gz|tbdba-restore-mysqldump.pl -d -a && date

3. tbdba-restore-mysqldump.pl有什么优势

(1) 如果指定了-s（获取某个数据库中的备份）参数，则脚本在成功截取需要恢复的表后就会立刻退出，所以如果你要恢复的表恰好在备份文件的比较靠前的位置时，该脚本的速度会非常快。

一个实际工作例子：

$ls -lh backup.sql.gz
-rw-r--r-- 1 mysql dba 14G Nov 21 04:49 backup.sql.gz
$date && gunzip -c backup.sql.gz|./tbdba-restore-mysqldump.pl -s monitor_general -t monitor_host_info && date
Fri Nov 25 14:35:06 CST 2011
Fri Nov 25 14:46:49 CST 2011
(the unzip of backup.sql.gz is 88G)

如果要全量恢复的话，根据经验值：88GB的sql文件完全恢复约需要400分钟（经验值）。

(2) 为了让每个独立的单表备份文件能够准确恢复，脚本做了两个额外的处理工作：在每个单表备份前加上'use db'，让该表能够恢复到正确的数据库；为了让单表恢复时字符集不出错误，脚本在某个单表备份前加上了对应的SET NAMES utf8、SET TIME_ZONE等命令。

4. 其他人都怎么做

Extract a Single Table from a mysqldump File：这篇文章提到了三个办法，分别是：perl脚本（我这里的做法基本“雷同”），awk解析后切割，先恢复到临时库(对大文件这个不现实...)。对比了我们的Perl脚本，这里做了几个改进：可以同时解析出多个表；完成目标表的切割后，则立刻退出，不再扫描剩余部分；会把mysqldump头部输出放到每一个切割文件中，方便各种字符集的恢复；

Restore a Single Table From mysqldump：这篇文章介绍如何用Sed来完成这个工作。

MySQL - Restoring a single table from nightly backup ：这位朋友则，想出一个“更损”的招：只给恢复用户赋予需要恢复的表的权限，然后用--force参数恢复整个mysqldump文件。

On restoring a single table from mysqldump：这篇文章则对比了使用grep sed 和“权限控制”三种方法的速度。

最后，如果不喜欢mysqldump这种一股脑的备份方式，可以考虑试用mydumper。

OK，That's all.

广告：我们寻找靠谱的人 | 感谢作者

使用mod_proxy_html完善Apache反向代理

admin — Fri, 09 Dec 2011 22:37:02 +0800

在使用和配置过程中，还是遇到了一些波折，这里分享、记录一下。

1. 模块mod_proxy_html可以做些什么

使用Apache内置的mod_proxy的Proxypass可以帮助我们实现基本的反向代理功能。但是，代理返回的结果页面包含的链接并不会被重写，所以如果被代理的Server返回的是绝对地址，用户则无法正确访问这些链接。例如：

我们有反向代理服务器http://myproxy.taobao.org/，而内网有需要代理的服务器http://internalserver.taobao.org/，通过基本的反向代理指令Proxypass：

ProxyPass ^/proxy_internal/(.*)$ http://internalserver.taobao.org/$1

可以完成基本的反向代理任务，但是如果internalserver.taobao.org返回的HTML代码中有如下链接，则客户端则无法正常链接到目标页面了：

<a href="/test/index.html">Sample Link</a>

因为，客户端点击该链接时会定向到http://myproxy.taobao.org/test/index.html，而资源实际是在http://internalserver.taobao.org/test/index.html上。

Apache自带的mod_proxy无法解决这个问题，开源的第三方模块mod_proxy_html为此而生。经过代理服务器的HTML可以通过该模块做一次替换，从而变成可以正常访问的URL（上例中会把/oninternal/index.html替换成/proxy_internal/test/index.html）。

2. 编译安装mod_proxy_html模块

我是在RHEL5.4上安装的，并不容易。理想的安装步骤（为了支持多字符集需要同时安装mod_xml2enc）：

1. 检查是否安装了libxml2。

2. 下载mod_proxy_html源码：地址；下载mod_xml2enc的源码：地址mod_xml2enc

3. 编译mod_proxy_html和mod_xml2enc：

sudo /usr/sbin/apxs -c -I/usr/include/libxml2 -I. -I /usr/lib -i mod_proxy_html.c
sudo /usr/sbin/apxs -c -I/usr/include/libxml2 -I. -i mod_xml2enc.c
chmod 755 /usr/lib64/httpd/modules/mod_proxy_html.so

如果你这么顺利就编译好了模块，那你很幸运。

编译中的问题

$ /usr/sbin/apxs -c -I/usr/include/libxml2 -I. -I /usr/lib -i mod_proxy_html.c
"my" variable $libdir masks earlier declaration in same scope at /usr/sbin/apxs line 36.
/apr-1/build/libtool --silent --mode=compile gcc -prefer-pic -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fno-strict-aliasing -DLINUX=2 -D_REENTRANT -D_GNU_SOURCE -pthread -I/usr/include/httpd -I/usr/include/apr-1 -I/usr/include/apr-1 -I/usr/include/libxml2 -I. -I/usr/lib -c -o mod_proxy_html.lo mod_proxy_html.c && touch mod_proxy_html.slo
sh: /apr-1/build/libtool: No such file or directory
apxs:Error: Command failed with rc=8323072

仔细看是：/apr-1/build/libtool: No such file or directory。对libtool和apr-1不很熟悉，看出是因为没有正确找到libtool的路径导致的，所以修改了一下脚本apxs，强制指定libtool的位置，虽然这不是“优雅的”解决办法：

$locate libtool #找到你的libtool
......
$vi /usr/sbin/apxs
...
my $libtool = `$apr_config --apr-libtool`;
chomp($libtool);
$libtool = "/usr/lib64/apr-1/build/libtool";
...

It works! 如果有个有更好的办法，或者知道根本原因的，请告诉我，谢过先。

3. 配置该模块

强烈建议参考mod_proxy_html源码中的proxy_html.conf，这是一个示例配置文件，非常有指导意义。我这里的配置如下：

1. 加载必要的模块：

LoadFile /usr/lib64/libxml2.so
LoadModule xml2enc_module modules/mod_xml2enc.so
LoadModule proxy_html_module modules/mod_proxy_html.so

2. 指定需要修改的链接标签：

SetOutputFilter proxy-html
ProxyHTMLExtended On
ProxyHTMLLinks a href
ProxyHTMLLinks area href
ProxyHTMLLinks link href
ProxyHTMLLinks img src longdesc usemap
ProxyHTMLLinks object classid codebase data usemap
ProxyHTMLLinks q cite
ProxyHTMLLinks blockquote cite
ProxyHTMLLinks ins cite
ProxyHTMLLinks del cite
ProxyHTMLLinks form action
ProxyHTMLLinks input src usemap
ProxyHTMLLinks head profile
ProxyHTMLLinks base href
ProxyHTMLLinks script src for
ProxyHTMLEvents onclick ondblclick onmousedown onmouseup \
onmouseover onmousemove onmouseout onkeypress \
onkeydown onkeyup onfocus onblur onload \
onunload onsubmit onreset onselect onchange

3. 指定的URL替换规则：


ProxyPassMatch ^/proxy_dir/(.*)$ http://yourdomain/$1
<LocationMatch "/proxy_dir/">
        ProxyPassReverse /
        ProxyHTMLURLMap  / /proxy_dir/
        ProxyHTMLURLMap  http://some_(reg_exp)_url_(is_supported)you_wanna_replace/  /proxy_dir/$1/$2/ R
        RequestHeader    unset  Accept-Encoding
</LocationMatch>

OK，That's all.

广告：我们寻找靠谱的人 | 感谢作者

参考：

1. mod_proxy_html主页

2. Running a Reverse Proxy in Apache

MySQL如何传输二进制日志

admin — Sun, 20 Nov 2011 23:53:29 +0800

MySQL Replication可以很方便的用来做应用的读扩展，也可以帮MySQL实现一定程度的HA方案。MySQL通过向备库传送二进制日志来实现Replication，本文将通过二进制日志相关源代码的主要接口来解释：“MySQL如何传输二进制日志，是主库推，还是备库拉？MySQL日志传输的实时性如何？”。

在MySQL Replication结构中，备库端初次通过CHANGE MASTER TO完成Replication配置，再使用start slave命令开始复制。更细致的，备库通过IO Thread向主库发起读取binlog的请求（COM_BINLOG_DUMP命令），主库收到COM_BINLOG_DUMP请求后，使用单独线程（dump thread）不断向备库IO Thread发送Binlog。示意图(大图)：

在主库端一旦有新的日志产生后，立刻会发送一次广播，dump线程在收到广播后，则会读取二进制日志并通过网络向备库传输日志，所以这是一个主库向备库不断推送的过程；

新日志在产生后，只需一次广播和网络就会立刻（<1ms）向发送到备库，如果主备之间网络较好的话（例如RTT<1ms），备库端的日志也就小于2ms了。所以，一般的（依赖于RTT），备库的实时性都非常好。

参考：

1. MySQL Replication Manual

2. 图解"How MySQL Replication Works"

Have fun!

广告：我们寻找靠谱的人 | 感谢作者

TCP/IP重传超时--RTO

orczhou — Thu, 27 Oct 2011 20:19:27 +0800

概述：本文讨论主机在发送一个TCP数据包后，如果迟迟没有收到ACK，主机多久后会重传这个数据包。主机从发出数据包到第一次TCP重传开始，RFC中这段时间间隔称为retransmission timeout，缩写做RTO。本文会先看看RFC中如何定义RTO，然后看看Linux中如何实现。本文旨在分享：当遇到了TCP层问题改如何去查找、阅读文档，该如何去在Linux源码中寻求答案。

1. 起源

在分析MySQL Semi-sync故障时，我们用Tcpdump+Wireshark（感谢淘宝雕梁）抓住当时的网络包传送细节，观察到了一次TCP重传最终导致了Semi-sync超时：

第一次传输
13:55:11.893291 master => slave	Binlog pos:319890197
重传：
13:55:12.094596	master => slave	Binlog pos:319890197

看到两次传送间隔约201毫秒，即第一次传输201毫秒后，还没有收到ACK响应，TCP认为传输超时，开始重传。

疑问：host和host之间的RTT大约是0.5毫秒，为什么第一次重传需要等200毫秒？（我希望是<20ms）socket程序可以配置吗RTO吗？TCP有参数可配置RTO吗？

2. Google/书籍/RFC

翻开TCP/IP详解找到关于TCP Retransmission章节，较详细的介绍TCP的超时机制，书中是个概述，于是又找到RFC1122。

RFC1122的4.2.2.15和4.2.3.1都介绍了Retransmission Timeout的处理（说来惭愧，这是第一次阅读TCP相关RFC）。

在RFC中搜索Retransmission发现RFC 793 1122 2988 6298都有对重传算法、和初次重传超时的描述。于是开始阅读这个四个RFC，耗时约2小时，了解了大致的重传超时算法。

3. RFC中如何计算RTO(Retransmission Timeout)

3.1 RFC-793如何计算RTO

概述：先根据该socket的RTT计算出SRTT（Smoothed Round Trip Time），然后根据一个最大、最小超时时间确定当前RTO。说明：srtt可以理解为“平滑化”的RTT，即在保持计算简单的情况尽量考虑历史RTT。

详细计算：SRTT = ( ALPHA * SRTT ) + ((1-ALPHA) * RTT)

基于SRTT，我们再来计算RTO：RTO = min[UBOUND,max[LBOUND,(BETA*SRTT)]]

UBOUND是RTO上线，ALPHA是平滑因子（smoothing factor， e.g., .8 to .9），BETA是一个延迟方差因子（BETA is a delay variance factor (e.g., 1.3 to 2.0)）。

仔细看这两个公式大概就能理解了RTO的计算了。

这里对上面两个公式做一个简单的注释：公式1中计算SRTT，ALPHA越接近于0，则表示SRTT越相信这一次的RTT；越接近于1，则表示SRTT越相信上次统计的RTT。公式二给RTO分别设置了一个上限和下限。

3.2 RTO重传间隔是指数增加的

上面我们介绍的是初次重传时的RTO，如果重传后还没收到另一端的响应，下一次重传RTO则会指数增加，例如第一次重传RTO是1，之后分别2，4，8，16...。

3.3 RFC-2988和RFC-6298中的RTO计算

在RFC-2988和RFC-6298中又重新改进了RTO的计算方法，Linux中的实现即使参考RFC-2988。算法核心公式：

初始：
SRTT <- R
RTTVAR <- R/2
RTO <- SRTT + max (G, K*RTTVAR)
where K = 4.

根据RTT计算SRTT：
RTTVAR <- (1 - beta) * RTTVAR + beta * |SRTT - R'|
SRTT <- (1 - alpha) * SRTT + alpha * R'

最后RTO：
RTO <- SRTT + max (G, K*RTTVAR)

4. Linux中的RTO(Retransmission Timeout)

这里说的是RHEL5.4的2.6.18内核，RFC-2988实现参考net/ipv4/tcp_input.c中的tcp_rtt_estimator和tcp_set_rto。可以看到，在Linux中alpha=1/8，RTO最小为TCP_RTO_MIN。因为我们的系统中RTT总是很小，所以RTO取值总是能够取到TCP_RTO_MIN。

在看看TCP_RTO_MIN在Linux中的定义：

123#define TCP_RTO_MAX     ((unsigned)(120*HZ))
124#define TCP_RTO_MIN     ((unsigned)(HZ/5))

(这里简单的介绍介绍一下HZ，HZ可以理解为1s，所以120*HZ就是120秒，HZ/5就是200ms。详细的：HZ表示CPU一秒种发出多少次时间中断--IRQ-0，Linux中通常用HZ来做时间片的计算，参考)

5. 其他：Linux中可配置重传参数

/proc/sys/net/ipv4/tcp_retries1 (integer; default: 3)

TCP尝试了3次（tcp_retries1默认3）重传后，还没有收到ACK的话，则后续每次重传都需要network layer先更新路由。

/proc/sys/net/ipv4/tcp_retries2 (integer; default: 15)

TCP默认最多做15次重传。根据RTO(retransmission timeout)不同，最后一次重传间隔大概是13到30分钟左右。如果15次重传都做完了，TCP/IP就会告诉应用层说：“搞不定了，包怎么都传不过去！”

6. 最后

回答前面的问题：即使RTT很小（0.8ms），但是因为RTO有下限，最小必须是200ms，所以这是RTT再小也白搭；RTO最小值是内核编译是决定的，socket程序中无法修改，Linux TCP也没有任何参数可以改变这个值。

好了，不容易。

广告：我们寻找靠谱的人 | 感谢作者

参考文献

1. RFC 1122 ... (在哪儿查找RFC) TCP协议相关的RFC：
RFC 675 - Specification of Internet Transmission Control Program, December 1974 Version
RFC 793 - TCP v4
RFC 1122 - includes some error corrections for TCP
RFC 1323 - TCP-Extensions
RFC 1379 - Extending TCP for Transactions—Concepts
RFC 1948 - Defending Against Sequence Number Attacks
RFC 2018 - TCP Selective Acknowledgment Options
RFC 2988 - Computing TCP's Retransmission Timer
RFC 4614 - A Roadmap for TCP Specification Documents
RFC 5681 - TCP Congestion Control

MySQL5.5 Semi-sync备库响应协议分析

orczhou — Tue, 18 Oct 2011 22:53:58 +0800

这又是一篇介绍Semi-sync的文章。

Semi-sync主库在一定时间内（可配置的超时时间），如果没有收到备库的响应，则会超时从而降级为普通的replication复制。如果超时发生，有时需要查清什么原因导致备库没有及时响应，一方面可以从备库的日志着手，另一方面，如果需要更细致的信息则需要从备库端的网络包查找原因。这里介绍如何分析一个Semi-sync备库响应主库的数据包。

概述：先使用tcpdump抓取正确（主要是src和dst都正确）的数据包；然后借助wireshark玻璃TCP/IP等层的头信息，仅保留发送的MySQL数据包；再分析MySQL Semi-sync Slave响应的协议。

1. tcpdump原始数据包

通过如下tcpdump抓取主机的网络包：

nohup tcpdump -n -nn -tttt -i bond0 -s 65535 'port 3306 and ((dst host master.host and src host slave.host and len &lt 100) or (dst host slave.host and src host master.host))' -w tcpdump.ret -C 50 &

参数简单说明：

-n  表示ip不要转换为主机名
-nn表示端口号，不要转为为服务名（例如3306会被转换为mysql）
-tttt 打印出完成的格式化的时间戳
-C 50 表示抓取的结果放到文件中，文件大小不超过50MB

2. 使用wireshark找到对应的包

Semi-sync备库响应主库包，一般大小整个包大小约95字节。找到该网络包如下：

完整网络包内容：

0000  00 24 e8 57 76 5e 00 1d  70 82 ca c0 08 00 45 00   .$.Wv^.. p.....E.
0010  00 51 a6 e2 40 00 3e 06  89 34 ac 13 46 44 ac 17   .Q..@.>. .4..FD..
0020  6e 21 b3 35 0c ea b2 2d  26 2a da 46 3c df 80 18   n!.5...- &*.F<...
0030  04 82 35 ef 00 00 01 01  08 0a 5a a2 7f cd 50 43   ..5..... ..Z...PC
0040  c1 5b 19 00 00 00 ef 38  40 01 00 00 00 00 00 6d   .[.....8 @......m
0050  79 73 71 6c 2d 62 69 6e  2e 30 30 31 35 31 36      ysql-bin .001516

3. MySQL数据包分析

剥去TCP/IP等层（使用wireshark就可以了），剩下MySQL 包如下：

0040        19 00 00 00 ef 38  40 01 00 00 00 00 00 6d   .[.....8 @......m
0050  79 73 71 6c 2d 62 69 6e  2e 30 30 31 35 31 36      ysql-bin .001516

更具Semi-sync备库响应源码分析如下：

+============================+
| packetlength      0 : 4    | 对应的：19 00 00 00 表示包数据长度为0x19=25字节
+----------------------------+
| magic_num         4 : 1    | 对应的：ef 这是一个kPacketMagicNum，表示这是一个Semi-sync slave响应
+----------------------------+
| binlog_pos        5 : 4    | 对应的：38  40 01 00 Binlog Pos:0x14038 = 81976
+----------------------------+
| event_length      9 : 20   | 对应的：00 00 00 00 6d 79 73 71 6c .... 31 36 （mysql-bin.001516）
+----------------------------+

可以看出，改网络包，响应主库端的mysql-bin.001516中的偏移为81976的事务。

4. 最后【更新2011-10-19】

通过tcpdump的网络包，我们可以看到毫秒（甚至微秒）级别数据包的传送情况。依据此，通常可以精确分析出Semi-sync在何时、何处出现超时。然后，可以继续分析为何出现超时。

That's all... 这篇枯燥吧，哈哈

广告：我们寻找靠谱的人 | 感谢作者

orczhou@Google Reader [2011-09-20]

Tue, 20 Sep 2011 00:00:00 +0800

Oracle adding close source extensions to MySQL
Oracle has now officially announced that MySQL is not a full free software project anymore and that they will go for an Open Core model.This is inconsistent to the original MySQL business model. The main strength of MySQL has always been that all M..

Percona-Server/MySQL响应时间统计

orczhou — Mon, 19 Sep 2011 21:02:57 +0800

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

Percona在提供了tcprstat工具统计RT时间之后，很快就在Percona Server中集成了响应时间统计的功能。这里介绍一下该功能，各位看官如果在犹豫选择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 |

一些注意事项

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

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

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

累了，听首歌，扭扭脖子吧:)

广告：我们寻找靠谱的人 | 感谢作者

参考文献：

1. Introducing tcprstat, a TCP response time tool

2. The tcprstat User's Manual

3. Using Tcpdump for MySQL query logging

4. How to use tcpdump on very busy hosts

5. Take a look at mk-query-digest

6. Implementation Details: Response Time Distribution

7. Percona Software Documentation :Response Time Distribution

【附录】

既然上面读取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 |
+---------+---------+--------+

orczhou@Google Reader [2011-08-16]

Tue, 16 Aug 2011 00:00:00 +0800

What is happening with the MySQL conference?
Shared by orczhou Good news for community, bad news for Oracle. What is happening with the MySQL conference? I'm at the moment perplexed, confused and disappointed in what's going on around the traditional MySQL user conference. Percona yesterd..

orczhou@Google Reader [2011-07-30]

Sat, 30 Jul 2011 00:00:00 +0800

Intel 320 SSD read performance
(this is cross-post from http://www.ssdperformanceblog.com/) While PCI-e Flash cards show great performance, I am often asked about alternatives, as price for PCI-e cards is still significant and not acceptable for small companies and startups. Int..