MySQL 一个让你怀疑人生的hang死现象
1、故障现象
- 某客户反馈，在利用binlog日志的解析内容做基于时间点的恢复时，出现hang死（超过12小时那种），恢复过程无法继续，而且100%重现
- 基于时间点恢复的实现方式为：使用mysqlbinlog命令指定一个结束时间点，解析binlog并使用管道直接导入数据库中执行（例如：mysqlbinlog binlog_file | mysql -uroot -pxx）
- 对此，我们收集了最基本的一些环境信息（注：这是客户环境），如下
- 数据库版本：MySQL 5.7.26
- 操作系统：CentOS 7.2 x64
- 服务器配置：
* CPU：8 vcpus
* 磁盘：磁盘阵列
* 内存：16G - 数据库关键参数：buffer pool为物理内存的一半，row格式复制
- 数据库复制拓扑：一主一 从，恢复数据的过程是使用从库的数据全量快照 + 从库的binlog实现，恢复目标服务器是另外一台单独的数据库服务器，该服务器只用于临时恢复数据，不存在复制拓扑，未配置监控或心跳检测
- 通过简单的询问，确认系统负载不高，数据库也几乎无负载。综合这些信息，在未看到现场之前，我们进行了简单的推理，认为该hang死现象很可能是由于大事务导致的，所以，我们一上去现场就直接围绕数据库进行排查，几经周折，终于发现了问题的原因，而且也找到了解决问题的方法。但，导致hang死的原因远没有想象的那么简单，而且具有一定的迷惑性，我们对此进行一个简单的总结并分享给大家
- PS1：下文中的数据为复现数据，复现的服务器配置如下（数据库使用沃趣最佳实践配置模板）
- CPU：32 vcpus
- 内存：256G
- 磁盘：1.6T LSI FLASH卡
- 网络：万兆网络
- PS2：留意下文代码段中的中文注释
2、分析排查
- 首先，查看mysqlbinlog进程，可以看到它正在运行着
|
1 2 3 |
|
-
然后，查看了当前正在运行的线程状态信息，发现一个sleep长达157269S的线程，这是什么鬼。。大事务未提交吗？窃喜！
|
1 2 3 4 5 6 7 8 |
|
-
接着，我们查看了事务和锁信息
|
1 2 3 4 5 6 |
|
- 至此，排查视乎陷入了僵局。既然不存在大事务，也没有锁信息，用于恢复数据的客户端连接也处于Sleep状态，那就表示此时数据库未做任何事情（该数据库是一个用于数据恢复的临时库，并没有任何其他访问业务，也并没有监控、心跳等，只有2个连接，一个是我们登录上去排查问题的连接，另外一个就是使用mysqlbinlog命令恢复数据的客户端连接），那么，为什么会hang？百思不得其解！
-
再接着，我们查看了一下系统负载，内存、网络、磁盘都几乎无负载，CPU也没啥负载，但却有一个奇怪的现象，有一个CPU核心的利用率为100%，如下
|
1 2 3 4 5 6 7 8 9 10 11 12 |
|
-
最后，在恢复库中，我们查看了一下GTID信息，发现GTID号不连续，少了一个事务的GTID（3759）
|
1 2 3 4 5 6 7 |
|
-
通过查看从库中的GTID（也就是恢复库的数据源对应的库），并没有少GTID号为3759的事务，为什么莫名在恢复库中会少，而不是连续的？带着好奇，我们使用该GTID号解析了从库中的所有被指定用于恢复的binlog file列表（这里指的就是mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019），从这些binlog中提取了GTID号为3759的事务的binlog日志内容
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
|
- 分析到这里，总算是发现了导致MySQL 客户端hang死的直接原因（注意，是客户端hang死，不是服务端），即，MySQL客户端在应用一个400W行大事务的binlog时，导致MySQL客户端出现hang死。
- 但是，问题又来了，一个400W行的大事务，为什么会导致客户端hang死长达12个小时？而且在此期间，数据库和操作系统的负载并不高，数据库的各项参数设定也并没有达到上限，而且，查看了数据库的错误日志和操作系统日志(/var/log/messages)，也并没有发现任何有用信息。
- so..，接下来我们就需要进一步分析为何一个大事务会导致客户端hang死
3、找到罪魁祸首
- 上文中我们提到过，该故障现象可以100%重现，这为我们进一步分析该问题提供了便利。在开始进一步分析之前，我们先列出在不同磁盘设备的服务器环境中插入400W行大事务的时间参考数据（经验值，不精确）
- 假定内存和CPU不成瓶颈的情况下
* 使用15000转/分的SAS盘时，insert into x select * from y;语句插入400W行数据大约15分钟内完成
* 使用普通SSD时，insert into x select * from y;语句插入400W行数据大约5分钟内完成
* 使用FLASH卡时，insert into x select * from y;语句插入400W行数据大约3分钟内完成
- 假定内存和CPU不成瓶颈的情况下
- 虽然上述提供的参考时间数据不精确，处理一个大事务的BINLOG编码也可能有一些额外的开销，但导入一个400W行大事务的binlog无论如何都不可能超过12个小时
- so..，接下来，我们将使用strace、pstack等工具，再结合翻看源码的方式来排查该问题
3.1. strace 工具排查
-
使用strace工具查看登录MySQL并加载binlog解析文本过程中的一些输出信息（这里直接使用上文中提到的b.sql）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
-
现在，另起一个终端会话，查看strace.txt文件中的内容
|
1 2 3 4 5 6 7 8 9 10 |
|
- 很无奈，我们使用strace工具在这里并没有发现实质性有用信息，需要使用其他的工具进行排查，现在，你可以关闭用于导数测试的客户端连接了（注意，使用了strace命令之后，客户端连接可以立即终止，如果没有使用strace命令，数据库的终端会话hang死了，无法终止数据库的导数操作，别说我没有等它回滚，我等了好几个小时...）
-
注意：如果你在跟随我们的复现过程玩耍，建议你查看一下show processlist的信息，你可能会发现之前导入b.sql文件的客户端连接在数据库中并没有被断开，如果发现此情况，则需要在数据库中手工执行kill操作
|
1 2 3 4 5 6 7 8 9 10 |
|
3.2. pstack 工具排查
-
登录到MySQL并执行导入b.sql文件操作（不要用strace）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
|
-
使用pstack工具查看MySQL客户端连接的进程堆栈信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
- 开心，使用pstack工具发现了一个较为有用的信息，从这里我们知道了MySQL客户端在导入b.sql时，卡在了使用__memmove_ssse3_back ()函数做内存拷贝的阶段了
- 那么问题又来了，为什么会卡在这里？结合上述获取到的信息，我们知道，MySQL客户端只是与服务端建立了一个连接，后续服务端没有收到任何信息（也就是整个客户端hang死期间，服务端没有做任何事情），so..，接下来我们必须要去翻阅源码来寻找答案了：为什么MySQL客户端会卡在 __memmove_ssse3_back ()函数内存拷贝阶段？
3.3. 翻阅客户端命令mysql的源码排查
-
由于是在MySQL 5.7.26版本中发生的故障，所以我们首先查看了MySQL 5.7.26版本的客户端源码，MySQL客户端在读取一个文件时（或者说判断什么时候可以将一个操作发送给服务端时），碰到';'分号会直接将前面读取到的内容作为一整个数据包丢给服务端，对上文中提到的b.sql文件中的BINLOG编码，整个BINLOG编码只有一个分号，所以会等到整个BINLOG编码读取完成之后才会发送给服务端，在读取这个超大BINLOG编码的过程中，MySQL客户端会一行一行的读，并记录到global_buffer中。当global_buffer申请的空间不足时，就需要扩展内存空间，，扩展内存空间的代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
|
- 通过翻阅源码中的相关代码，再结合b.sql中400W行大事务产生的BINLOG编码，我们可以得出如下结论：
- MySQL客户端读取b.sql文件中的BINLOG编码时，会把整个BINLOG编码当做一整个数据包对待，也就是说客户端先要将其完整读取并存放在内存中，然后再将其作为一整个数据包发送给服务端（包的大小由客户端和服务端的max_allowed_packet参数共同控制，以最小的为准），客户端在读取BINLOG编码的过程中，每读一行都会先判断原先分配的内存是否足够，如果内存不足就需要扩展一下，申请新的一段内存，并且把原来的内存中的数据拷贝过去（新申请的内存），自然就非常慢，看上去就好像客户端hang死一样，而客户端读取BINLOG编码的整个过程中，服务端都没有收到任何的操作，所以，从服务端的show processlist看到线程状态一直是Sleep状态，也没有事务，也没有锁信息。
- 也就是说，这里可以排除掉mysqlbinlog命令解析binlog的嫌疑
4、干掉罪魁祸首
- 虽然，我们发现了问题的根本原因，是因为MySQL客户端读取大事务的BINLOG编码时分配内存的效率太低导致的，但是，怎么解决它呢？改代码？怎么改？改了出其他问题咋办？
- IO_SIZE变量是一个比较底层的变量，不同用途的buffer分配都会使用该变量作为一个基准参考，所以不能直接修改此变量的大小。因此，我们并没有急于去修改客户端代码，而是先尝试使用MySQL 8.0.16、MariaDB 10.1.40/10.2.25/10.3.16/10.4.6、Percona Server 5.7.26进行测试，发现如下现象
- MySQL 5.7.x和Percona Server 5.7.x：表现一致，客户端hang死
- MySQL 8.0.x：在两三分钟之后，客户端连接被服务端断开
- MariaDB：表现与MySQL 8.0.x一致
- 那么，问题又双叒叕来了
- 1）、为什么MariaDB和MySQL 8.0.x会将客户端连接断开，而不像MySQL 5.7.x和Percona Server 5.7.x那样导致MySQL客户端hang死
- 2）、MySQL 8.0.x在MySQL 5.7.x的基础上做了什么改动使得他们在导入相同数据时的表现不同呢？
- 我们先解决第一个问题（我想大多数人也是这么想的，对吧），过程很辛酸，最后发现一个很low的问题，就是。。服务端的max_allowed_packet系统变量值设置太小了（64M），将其改为1G之后
- 在MariaDB和MySQL 8.0.x中，这个400W行的大事务产生的binlog可以在3分钟以内应用完成（注意，该参数最大值为1G，如果你的事务产生的BINLOG编码长度大于该参数，则仍然会被服务端断开连接）
- 在MySQL 5.7.x和Percona Server 5.7.x版本中修改该参数为1G之后，也可以应用完成，只是要差不多2天左右的时间，这跟hang死没什么两样，所以问题并没有解决（因为客户使用的是MySQL 5.7.26版本嘛...）
-
接下来，我们解决第二个问题，比对MySQL 5.7.26和MySQL 8.0.16版本中，关于客户端缓存读取文件数据这块的内存分配代码有什么不同？
|
1 2 3 4 5 6 |
|
- so...看起来，需要怎么修改MySQL的客户端代码已经很清晰了，只需要新定义一个batch_io_size变量，然后将'buffer.mem_realloc(buffer.length()+length+IO_SIZE)' 修改为'buffer.mem_realloc(buffer.length() + length + batch_io_size)'即可
- 说干就干，我们按照此结论对MySQL的客户端mysql的源码进行了修改，重新编译，然后，将服务端和客户端的max_allowed_packet参数都设置为1G，重新导入400W行的大事务产生的row格式binlog（即，使用insert into sbtest1 select * from sbtest.sbtest1 limit 4000000 语句产生的row格式binlog解析文本进行测试），3分钟内完成了导入，至此，问题解决！
- 最后，多说一句，MariaDB虽然也同样解决了这个问题，但是解决方法完全不同（粗略看了一下代码，没细看），通过比对解析的BINLOG编码，发现MariaDB的BINLOG编码是存在一个个的空缺，所以猜测可能是采用了"mysqlbinlog的解析格式变更"配合"mysql客户端的解析逻辑变更"来解决该问题的（MariaDB在执行导入binlog解析内容时，show processlist查看线程状态也能发现导入数据的线程一直在干活，并不是一直处于Sleep状态），类似如下
-
MariaDB
* BINLOG编码格式
* show processlist状态 -
MySQL
* BINLOG编码格式
* show processlist状态
-