处理一个关于binlog增量恢复很慢的问题

来源：互联网发布：国内域名国外空间编辑：程序博客网时间：2024/05/01 00:53

今天帮网友处理了一个关于binlog增量恢复很慢的问题。
问题描述：
网友是先通过xtrabackup将mysql全量恢复到测试环境中，然后再增量恢复binlog，具体操作为：用mysqlbinlog -v -v --base64-output=DECODE-ROWS解析binlog到一个sql文件中，然后再将sql文件应用到mysql中。在binlog增量恢复时非常慢，基于2分钟才生成1M的binlog。

问题分析：
1.我先要网友检查并提供服务器硬件资源使用情况，特别是IO，网友反应硬件资源很闲，没有任何负载，IO情况如下：
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await svctm %util
vda               0.00     0.00    1.00    0.00     0.01     0.00    16.00     0.01    8.00   8.00   0.80
vdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await svctm %util
vda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
vdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

上面说明IO很闲。
2.请网友用show processlist提供mysql进程情况
网码提供如下：
(root@localhost) [(none)]>show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | test | Sleep   | 3348 |       | NULL             |
| 2 | root | localhost | NULL | Query   |    0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
上面也没有发现明显异常.
3.请网友查看解析出来的binlog中是些什么操作
网友提供如下：
BINLOG '
q4ZxVxMBAAAAigAAABFSFQAAACiYBQAAAAEACndqZGJjZW50ZXIAE3dqX3RibF9tZW1iZXJfY2Fj
aGUAHggPAwMDDw8PCg8PDxIPDw/29vb29gMSEgESEvYBEiZIADwAlgDAADwAPADAAACWADwAPAAU
AhQCFAIUAhQCAAAAABQCAGARwDgoHXRQ
q4ZxVx8BAAAA6gEAAPtTFQAAACiYBQAAAAEAAgAe//////////8AAADAFzA0AAAAAAAA1gAAANcP
AAAFAAAABDgxMDgIMTAwMDAyMDIG6Z2e5rSyCosPA+eUtwnouqvku73or4ESNDUwMjIxMTk4OTA4
MTAyOTMymZP9XGggZDQxZDhjZDk4ZjAwYjIwNGU5ODAwOTk4ZWNmODQyN2UG5ZCv55SoCzEzNzY4
NTc1NzY2gAAAAAAAAAAAgAAAAAAAAAAAgAAAAAAAAAAAgAAAAAAAAAAAgAAAAAAABrAADgAAAJmU
oh4ImbR8AAAAmZjUpAeZmbhA7oAAAAAAAAAAAACZmbhA7gAAAMAXMDQAAAAAAADWAAAA1w8AAAUA
AAAEODEwOAgxMDAwMDIwMgbpnZ7mtLIKiw8D55S3Cei6q+S7veivgRI0NTAyMjExOTg5MDgxMDI5
MzKZk/1caCBkNDFkOGNkOThmMDBiMjA0ZTk4MDA5OThlY2Y4NDI3ZQblkK/nlKgLMTM3Njg1NzU3
NjaAAAAAAAAAAACAAAAAAAAAAACAAAAAAAAAAACAAAAAAAAAAACAAAAAAAAGsAAOAAAAmZSiHgiZ
tHwAAACZmNSkB5mZuED4gAAAAAAAAAAAAJmZuED4yYjykg==
5 QUERY COMMIT

上面应该没有使用--base64-output=DECODE-ROWS转换显示内容，表面上看是没有任何有用的信息，但从5 QUERY COMMIT中的commit字眼提醒了我。

4.请网友提供查看一下sync_binlog和innodb_flush_log_at_trx_commit两个参数的设置是否都为1。
网友很快回复参数有设置为1，同时有跟他确认了机器为虚拟机，现在问题的原因应该就是这两个参数的设置，因为我曾经同样在测试环境的虚拟机上测试过这两个参数在不同值下的性能比较。详见blog:http://blog.csdn.net/zengxuewen2045/article/details/51476186。
期间我同时也怀疑恢复操作可能有大量的insert，而且是每插入一行，就commit一次，也跟网友说过：用show global status like 'Handler_commit';查看一下commit次数，间隔一段时间查看一次，确认值是否在不断增加，不过这些数据没有提供，不影响这个问题的处理。

问题解决：
当发现上面参数设置为1后，将参数全都改为默认值(都改为0性能更好，反正测试嘛，安全不是重要的),就重新来一次全量恢复，再进行一次binlog增量恢复，这次速度很快。

下面是网友在QQ上的回复：
午后单车(175686647) 16:52:06
果然快了非常多
午后单车(175686647) 17:00:03
@代言人多谢哈，看来还是缺乏经验
要多测试
这个坑困扰我2天了
代言人(171057655) 17:02:48
不会吧
呵呵

0 0