​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进程,可以看到它正在运行着
[root@localhost ~]# ps aux |grep mysqlbinlog
root 27822 1.3 0.0 24568 3204 pts/2 R+ 15:11 0:04 mysqlbinlog --stop-datetime='2019-07-14 16:30:00' mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019
......
  • 然后,查看了当前正在运行的线程状态信息,发现一个sleep长达157269S的线程,这是什么鬼。。大事务未提交吗?窃喜!
admin@localhost : test 02:18:27> show processlist;
+----+-------+-----------+------+---------+--------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+------+---------+--------+----------+------------------+
| 14 | admin | localhost | test | Query | 0 | starting | show processlist |
| 15 | admin | localhost | test | Sleep | 157269 | | NULL |
+----+-------+-----------+------+---------+--------+----------+------------------+
2 rows in set (0.00 sec)
  • 接着,我们查看了事务和锁信息
# 先查看事务信息
admin@localhost : test 03:02:36> select * from information_schema.innodb_trx\G
Empty set (0.00 sec) # 纳尼!!,居然不存在事务正在运行
# 然后查看锁信息
admin@localhost : test 03:30:25> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec) # WTF!!居然也没有锁信息
  • 至此,排查视乎陷入了僵局。既然不存在大事务,也没有锁信息,用于恢复数据的客户端连接也处于Sleep状态,那就表示此时数据库未做任何事情(该数据库是一个用于数据恢复的临时库,并没有任何其他访问业务,也并没有监控、心跳等,只有2个连接,一个是我们登录上去排查问题的连接,另外一个就是使用mysqlbinlog命令恢复数据的客户端连接),那么,为什么会hang?百思不得其解!
  • 再接着,我们查看了一下系统负载,内存、网络、磁盘都几乎无负载,CPU也没啥负载,但却有一个奇怪的现象,有一个CPU核心的利用率为100%,如下
top - 15:40:50 up 117 days, 8:09, 5 users, load average: 1.97, 1.36, 1.15
Tasks: 496 total, 2 running, 494 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
# 利用率为100%的CPU核心在这里
%Cpu2 : 34.4 us, 65.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 0.3 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu4 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu5 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu6 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu7 : 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
......
  • 最后,在恢复库中,我们查看了一下GTID信息,发现GTID号不连续,少了一个事务的GTID(3759)
admin@localhost : (none) 03:52:08> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000013 | 500 | | | 5de97c46-a496-11e9-824b-0025905b06da:1-3758,3760-3767 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
  • 通过查看从库中的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日志内容
[root@localhost binlog]# mysqlbinlog mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 --include-gtids='5de97c46-a496-11e9-824b-0025905b06da:3759' > b.sql
[root@localhost binlog]# ls -lh b.sql
-rw-r--r-- 1 root root 996M Jul 14 15:59 b.sql # My God,GTID号为3759的一个事务binlog日志量快接近一个G了
# 使用mysqlbinlog命令加-vv选项重新解析(使用-vv选项解析,会导致解析结果的大小翻3倍左右),查看事务的原始语句是怎样的(binlog_rows_query_log_events=ON才会记录事务的原始语句文本)
[root@localhost binlog]# mysqlbinlog -vv mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 --include-gtids='5de97c46-a496-11e9-824b-0025905b06da:3759' > c.sql
# vim打开c.sql文件查看
[root@localhost binlog]# ls -lh
total 12G
-rw-r--r-- 1 root root 994M Jul 14 16:38 b.sql
-rw-r--r-- 1 root root 2.7G Jul 14 16:40 c.sql
......
[root@localhost binlog]# vim c.sql
......
# at 336
#190714 16:29:10 server id 3306102 end_log_pos 422 CRC32 0x3b2c9b07 Rows_query
# 发现一个400W行的大事务(经查看sbtest.sbtest1表中的数据量,确认该语句会往test.sbtest1表中插入400W行)
# insert into sbtest1 select * from sbtest.sbtest1 limit 4000000
......
BINLOG '
1ucqXRN2cjIAOQAAAN8BAAAAAHUAAAAAAAEABHRlc3QAB3NidGVzdDEABAMD/v4E7mj+tABkzbY1
1ucqXR52cjIADSAAAOwhAAAAAHUAAAAAAAAAAgAE//ABAAAAlyomAHcAMzMzNjgzMDQzMTQtMjYx
MDIwNDA4ODktMTU4NzIxODA4NzYtMTU0NDIxOTgxNjQtNDYzOTM1NDIxMzEtMTQwODg3NzUzNTQt
NzY4MDU0ODgyMTEtNzg0ODM1NTU5NjEtNjMyMDM5NjA0ODEtNDcxNjQ5MDg4MjY7NTA3NzUyOTM0
MzctMjE4MzMzNTAzNzYtODc2MTE2NjU0NTYtNjI4NTU3NjAyOTItNDQ3Mjc0MzA4MTjwAwAAAA0z
HQB3ADM4NjUyNzU5NDQwLTg4NjY4MjU5MDE3LTk0MDk4ODI4Nzc1LTYxMzMzNjEwMjg0LTYyODc3
NDgxMTY3LTY1NzM3Mjg3NTExLTA4MDYwNzA5NTU1LTIzMTUyNjI5NTcxLTE2MDMzMDM2NDE5LTYy
MjA0MDgxMzc0OzczOTE3OTQ0NjMzLTc0ODMwMjgwMjE3LTAxMTYzODkwMzkzLTU3NTEzNDA4MDY1
LTMwNjgzOTA1MTQ08AUAAAAlNCYAdwAwMjU2MTIyODQ3MC05OTAwMzk1ODMyOC0zMDQ1OTgyMzQw
NC0zMTY1MTgyNzE4OC02MDMxODU1MDA5OS03Njk5Njk5MTY3Mi02MTI1Nzg5NTU5MC03NDA3OTQz
MDg4MC01NzMyMDA4MzY4NC0zNjAzMDY2NDE4NjszOTA4Nzk4NjM5NC02MjA0NjQ4MDk0Ny01NjQ0
NTE4NzA3My0yODQxNDg5MzQyNC03OTYxOTMzMTg1N/AHAAAAn1MmAHcAODgxMzg4MjkxMjEtMDkx
NTk1NDI1OTctNzc4ODUwODczMzMtMjA1MzE3NDM2MjktODE3NTQ0NDc2MjgtMjczNDMyMzQ2ODEt
...... # 这就是这个大事务对应的BINLOG编码,整个b.sql文件的994MB的内容主要就是这些BINLOG编码
  • 分析到这里,总算是发现了导致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分钟内完成
  • 虽然上述提供的参考时间数据不精确,处理一个大事务的BINLOG编码也可能有一些额外的开销,但导入一个400W行大事务的binlog无论如何都不可能超过12个小时
  • so..,接下来,我们将使用strace、pstack等工具,再结合翻看源码的方式来排查该问题

3.1. strace 工具排查

  • 使用strace工具查看登录MySQL并加载binlog解析文本过程中的一些输出信息(这里直接使用上文中提到的b.sql)
[root@localhost ~]# strace mysql 2> strace.txt
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
admin@localhost : (none) 10:49:36> use test
Database changed
admin@localhost : test 10:49:38> source /data/mysqldata1/binlog/b.sql;
......
Charset changed
......
Warning (Code 1681): Updating 'collation_database' is deprecated. It will be made read-only in a future release.
Query OK, 0 rows affected (0.00 sec)
# 卡在这里了,此时你可以先去干点别的,20分钟之后再回来看看导入成功了没有,不过,不管你等不等,这个客户端要留着后续排查用,不能断开了
  • 现在,另起一个终端会话,查看strace.txt文件中的内容
[root@localhost ~]# tailf strace.txt
......
munmap(0x7f8d6607d000, 58281984) = 0
read(4, "wMjE4ODQzLTE5MTYzMTE4NDk4LTQwNTA"..., 4096) = 4096
mmap(NULL, 58290176, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8d5f150000
...... # 发现不断地打印类似下面这一段信息,貌似卡在内存分配这里了
munmap(0x7f8d628e1000, 58298368) = 0
read(4, "UxNzUtODUyMjQ1MTkxMDEtMTM4MTk3OD"..., 4096) = 4096
mmap(NULL, 58306560, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f8d66077000
......
  • 很无奈,我们使用strace工具在这里并没有发现实质性有用信息,需要使用其他的工具进行排查,现在,你可以关闭用于导数测试的客户端连接了(注意,使用了strace命令之后,客户端连接可以立即终止,如果没有使用strace命令,数据库的终端会话hang死了,无法终止数据库的导数操作,别说我没有等它回滚,我等了好几个小时…)
  • 注意:如果你在跟随我们的复现过程玩耍,建议你查看一下show processlist的信息,你可能会发现之前导入b.sql文件的客户端连接在数据库中并没有被断开,如果发现此情况,则需要在数据库中手工执行kill操作
admin@localhost : (none) 11:14:54> show processlist;
+----+-------+-----------+------+---------+-------+----------------------------------+----------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+------+---------+-------+----------------------------------+----------------------------------------------------------------------+
| 22 | admin | localhost | test | Sleep | 67545 | | NULL |
......
| 32 | admin | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------+-----------+------+---------+-------+----------------------------------+----------------------------------------------------------------------+
admin@localhost : (none) 11:14:55> kill 22;
Query OK, 0 rows affected (0.00 sec)

3.2. pstack 工具排查

  • 登录到MySQL并执行导入b.sql文件操作(不要用strace)
[root@localhost ~]# mysql -uadmin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
admin@localhost : (none) 11:08:58> use test
Database changed
admin@localhost : test 11:09:00> source /data/mysqldata1/binlog/b.sql;
......
  • 使用pstack工具查看MySQL客户端连接的进程堆栈信息
# 查看MySQL客户端连接进程号
[root@localhost ~]# ps aux |grep 'mysql -uadmin' |grep -v grep
root 4614 81.2 0.0 175080 41284 pts/21 R+ 11:18 0:20 mysql -uadmin -px xxxx
# 使用pstack工具查看
[root@localhost ~]# pstack 4614 |tee -a pstack.txt
# 发现它卡在__memmove_ssse3_back ()内存拷贝的阶段了。此时你可以看到系统的某一个CPU的idle是0%(与上文中发现的CPU负载诡异现象吻合),这个CPU被耗尽的CPU做的事情就是,持续申请内存、拷贝数据、释放内存,自然把一个CPU给用完了
#0 0x00007f1009f5c315 in __memmove_ssse3_back () from /lib64/libc.so.6
#1 0x0000000000422adf in my_realloc (key=<optimized out>, ptr=0x7f0ffde45030, size=30197464, flags=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/mysys/my_malloc.c:100
#2 0x000000000041a2ba in String::mem_realloc (this=0xa45460 <glob_buffer>, alloc_length=30197460, force_on_heap=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/sql-common/sql_string.cc:121
#3 0x0000000000417922 in add_line (truncated=false, ml_comment=<optimized out>, in_string=<optimized out>, line_length=76, line=0x16b4309 "NDQ4NDA0LTE5MjQ2NjgzMDgxLTY2MTA0Mjk0ODQ2LTYzNzk3MjcwMjU0LTQ3NjA2Nzk0MTY0LTEx\nODQwNjExOTY5OzY5NTQ1Mjc5MDA2LTM5NTgwMjUzMDEzLTgzMjQxNjU0MzQ1LTA4MDkxMDEzODk1\nLTk5NzMxMDYyMzU58B2TAwABKiYAdwAwNTEzMjEzNDUzNC"..., buffer=...) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:2795
#4 read_and_execute (interactive=false) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:2364
#5 0x00000000004181ef in com_source (buffer=<optimized out>, line=<optimized out>) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:4709
#6 0x0000000000417099 in add_line (truncated=false, ml_comment=<optimized out>, in_string=<optimized out>, line_length=37, line=0x16b2a10 "source /data/mysqldata1/binlog/b.sql;", buffer=...) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:2664
#7 read_and_execute (interactive=true) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:2364
#8 0x0000000000418c98 in main (argc=15, argv=0x160c348) at /export/home/pb2/build/sb_0-33648028-1555164244.06/mysql-5.7.26/client/mysql.cc:1447
  • 开心,使用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申请的空间不足时,就需要扩展内存空间,,扩展内存空间的代码如下:
// client/mysql.cc 文件
    if (buffer.length() + length >= buffer.alloced_length())
      buffer.mem_realloc(buffer.length()+length+IO_SIZE); # 每次除了扩展不够的内存以外(原有的内存长度+新读取的内容长度),额外只多扩展了4k的空间(也就是IO_SIZE,IO_SIZE变量的定义大小详见代码段后续的"include/my_global.h 文件")
// mysys/my_malloc.c 文件
  new_ptr= my_malloc(key, size, flags); # 扩展内存时先申请一段新的更大空间的内存(新申请的一段内存的大小为client/mysql.cc 文件中描述的扩展之后的内存大小)
  if (likely(new_ptr != NULL))
  {
#ifndef DBUG_OFF
    my_memory_header *new_mh= USER_TO_HEADER(new_ptr);
#endif
    DBUG_ASSERT((new_mh->m_key == key) || (new_mh->m_key == PSI_NOT_INSTRUMENTED));
    DBUG_ASSERT(new_mh->m_magic == MAGIC);
    DBUG_ASSERT(new_mh->m_size == size);
    min_size= (old_size < size) ? old_size : size;
    memcpy(new_ptr, ptr, min_size); # 然后把数据拷贝过去
    my_free(ptr);
    return new_ptr;
  }
// include/my_global.h 文件
#define OS_FILE_LIMIT UINT_MAX
/*
  Io buffer size; Must be a power of 2 and a multiple of 512. May be
  smaller what the disk page size. This influences the speed of the
  isam btree library. eg to big to slow.
*/
#define IO_SIZE 4096 # IO_SIZE 变量的大小定义为4K
  • 通过翻阅源码中的相关代码,再结合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版本中,关于客户端缓存读取文件数据这块的内存分配代码有什么不同?
# 以下只列出MySQL 8.0.16版本中的代码
// client/mysql.cc 文件
    if (buffer.length() + length >= buffer.alloced_length())
      buffer.mem_realloc(buffer.length() + length + batch_io_size); ## 8.0中每次扩内存时,额外分配的内存大小变更为了batch_io_size,在5.7中,batch_io_size位置是IO_SIZE
// client/my_readline.h 文件
static const unsigned long int batch_io_size = 16 * 1024 * 1024; ## 这里定义batch_io_size为16M,从4K变为16M,这就使得客户端在缓存读取的数据时,发现内存不够之后的内存分配效率提高几个数量级
  • 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状态

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年,主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写,熟悉MySQL体系结构,Innodb存储引擎,喜好专研开源技术,多次在公开场合做过线下线上数据库专题分享,发表过多篇数据库相关的研究文章。

Image placeholder
xyp070809
未设置
  72人点赞

没有讨论,发表一下自己的看法吧

推荐文章
毕业即失业,找工作找到怀疑人生 | 十年系列

01.写在前面十年前,我还是象牙塔中数学系的一名普通的大三学生。九年前,我是富士康流水线工厂的一名工人。六年前,我包里揣着3000元RMB来北漂。三年前,我在一家互联网金融公司做到了技术负责人。两年前

【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

Go编程语言教程_1.3. Golang的Hello World

你好,世界!是任何编程语言中的第一个基本程序。让我们使用以下步骤用Go语言编写第一个程序: 首先打开Go编译器。在Go语言中,该程序以.go扩展名保存,它是UTF-8文本文件。 现在,首先在程序中添加

PingCAP马晓宇:TiDB的HTAP之路

HTAP是目前数据库领域比较热门的一个概念,它既能支持OLTP(在线事务处理),又能支持OLAP(在线分析处理),可以涵盖大部分企业级应用的需求,一站式解决他们的问题。本次,小编有幸采访到PingCA

从艺术到数学再到编程,这个蚂蚁金服技术人的奇异人生

2020年已经到来,第一批90后互联网大军也步入了而立之年,回首他们的来时路,那些熬夜奋战的日夜,失败后的坚守,不断的创新与突破都成为他们送给自己最值得回味的礼物。第三次AI浪潮下,AI技术大军应用而

欧洲最大MySQL用户之一,Booking.com数据库构架探秘!

吴鑫Booking.com数据库工程师TeamLead2015年加入总部位于阿姆斯特丹的Booking.com数据团队,现任数据库工程师团队负责人,主要是负责Booking.com里MySQL相关的运

回馈开源,我如何排查一个MySQL Bug

X-Engine是阿里巴巴自研的高性能低成本存储引擎,经过多年的努力,我们在集团内部以AliSQL(X-Engine)的形式(AliSQL是阿里的MySQL分支)支持了许多业务,为用户带来了显著的成本

百度智能监控场景下的HBase实践

作者简介   张洋洋  百度高级研发工程师负责百度智能运维产品(Noah)的分布式时序数据库和通用配额管理平台的设计研发工作,在分布式存储和配额管理方向有广泛的实践经验。干货概览通过百度大规模时序数据

HPC CHINA 2019:面向E级计算时代的HPC/AI协同设计

蓝天飘白云,牧场奶茶香。蒙古大营雄风在,琴声传远方。骑马又滑沙,豪饮河套王。敖包相会歌一曲,笑发少年狂。中国一年一度高性能计算领域的盛会——全国高性能计算学术年会(HPCCHINA2019)在内蒙古呼

innerHTML与jquery里的html()区别?

innerHTML与jquery里的html()区别?●html()可以设置tbody、tr这些只读标签,而innerHTML在低版本IE下不行;jQuery的html()做了些容错处理,原生的Dom

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS

MySQL 数据库操作:删除数据库

使用MySQL的DROPDATABASE命令可以很容易的删除一个数据库。数据库删除的同时,所属的数据表将一起被删除。如果删除的数据库不存在,则会引发错误。为了避免错误的发生,可以在DROPDATABA

MySQL 定时备份

1设置好crontab定时任务备份 #每天备份三次数据库 05,15,22***sh/data/script/alldatabase_back.sh&>/dev/null #每天2点备份 02***s

Laravel-Binlog 扩展(用于实时监听 MySQL 数据变更、数据同步等场景)

Laravel-Binlogv0.2.1 (该扩展当前用于我司测试环境实时同步Mysql数据变更到ElasticSearch,稳定性待测试!!哈哈哈)我司正式环境走的阿里云DTS数据订阅 基于Sw

MySQL 读后总结

一条SQL查询语句是如何执行的? Mysql基本架构示意图Mysql分为Server层和存储引擎层两部分 Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化