揭秘MySQL 主从环境中大事务的传奇事迹

• 或许某些人会说MySQL Low，而且乐此不疲，不过，我完全也可以说某些人压根就不会用MySQL，万事万物都具有两面性的，最终结果的好坏还得看是谁在用、怎么用。关于这个问题的争论，相信大家都喜闻乐见了，尤其是数据库界的"郭德纲"的言论尤其精彩，没事听听数据库界的"郭德纲"讲讲故事、讲讲段子，也是一件蛮有意思的事情。
• 说回到大事务，大家或多或少都不太喜欢它，在日常的工作中，或许会在开发规范里明令禁止大事务（操作数据行数过万的事务可以毛估估的算作是大事务），也或许会在开发规范里建议程序员们尽可能将大事务拆分为小事务，即便特殊情况不得不跑大事务，至少也要在会话级别将binlog格式改成statement。大事务对数据库的影响，相信各位都或多或少有些体会。我就不一一列举，在这里，我想说的是，既然大家都觉得跑大事务不好，那大事务在MySQL里跑起来到底长啥样呢？下面我们围绕这个话题，针对"在MySQL主从复制环境中跑一个大事务"的场景，剖析一下大事务在MySQL中的"传奇事迹"

1、环境信息

• 数据库版本：MySQL 5.7.27
• 数据库关键配置参数：
  • 主从库：双一、long_query_time=1、binlog_rows_query_log_events=ON、binlog_format=row、slow_query_log=ON、innodb_buffer_pool_size=10G、max_binlog_size=512M
  • 从库：双TABLE、log_slow_slave_statements=ON、slave_parallel_type=LOGICAL_CLOCK、slave_parallel_workers=16、slave_preserve_commit_order=ON、slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN'、log_slave_updates=ON
• 主从复制拓扑
  • 主库：10.10.30.162
  • 从库：10.10.30.163
• 服务器硬件配置（kvm）
  • CPU：8 vcpus
  • 内存：16G
  • 磁盘：100G（LSI 1.6T FLASH卡）
• 其他工具版本
  • sysbench：sysbench 1.0.9
  • percona-toolkit：percona-toolkit-3.0.13-1.el7.x86_64

2、环境准备

• 主从库都启用所有的等待事件（这里使用了sys schema下的存储过程ps_setup_enable_instrument和ps_setup_enable_consumer进行快捷操作，代替使用UPDATE语句直接修改performance_schema系统库下的setup_instruments和setup_consumers表）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql > call sys.ps_setup_enable_instrument('wait'); +-------------------------+ | summary | +-------------------------+ | Enabled 310 instruments | +-------------------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql > call sys.ps_setup_enable_consumer('wait');           +---------------------+ | summary | +---------------------+ | Enabled 3 consumers | +---------------------+ 1 row in set (0.00 sec)

• 主库造数2张1000W的表

1 2 3 4 5 6 7 8 9 10 11

# 造数命令 [root@physical-machine ~]# sysbench --db-driver=mysql --time=99999 --threads=2 --report-interval=1 --mysql-host=10.10.30.162 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=2 --table-size=10000000 oltp_read_write --db-ps-mode=disable prepare # 表结构 CREATE TABLE `sbtest1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `k` int(11) NOT NULL DEFAULT '0',   `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',   `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin  # 这里AUTO_INCREMENT=20000001是因为自增步长参数设置为了2，实际上数据只插入了1000W行

• 主库造数完成之后，查看从库复制状态，确认从库复制无延迟

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

mysql > show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 10.10.30.162                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 10               Master_Log_File: mysql-bin.000015           Read_Master_Log_Pos: 215657137                Relay_Log_File: mysql-relay-bin.000044                 Relay_Log_Pos: 215657350         Relay_Master_Log_File: mysql-bin.000015              Slave_IO_Running: Yes             Slave_SQL_Running: Yes ......           Exec_Master_Log_Pos: 215657137               Relay_Log_Space: 215657644 ......         Seconds_Behind_Master: 0 ......            Retrieved_Gtid_Set: 6f43ada4-d39d-11e9-9c97-5254002a54f2:1-7485             Executed_Gtid_Set: 6f43ada4-d39d-11e9-9c97-5254002a54f2:1-7485                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name:             Master_TLS_Version:  1 row in set (0.00 sec) # 从上述内容中，我们可以通过两种方式判断是否存在复制延迟（仅从SQL线程追赶IO线程的角度衡量，如果是IO线程读取的binlog日志本身与主库存在延迟，在从库侧无法判断，需要结合主从的实际情况进行判断） ## 方法一：比对二进制日志文件和位置信息，Master_Log_File = Relay_Master_Log_File && Read_Master_Log_Pos = Exec_Master_Log_Pos，如果它们完全相等，则说明从库侧无复制延迟 ## 方法二：比对GTID SET信息，Retrieved_Gtid_Set = Executed_Gtid_Set，如果它们完全相等，则说明从库侧无复制延迟

• 主从库各自清理performance_schema统计数据，并切换binlog，避免对后续的操作过程造成干扰

1 2 3 4 5 6 7 8 9 10 11 12

# 清理ps统计数据（sys.ps_truncate_all_tables()函数的作用：清空performance_schema下的%summary%和%history%表，有一个传参，表示是否在每一张表执行清空前打印表名，如果为FALSE，则不打印， 只在执行操作完成最后打印总操作表数量） mysql > CALL sys.ps_truncate_all_tables(false); +---------------------+ | summary | +---------------------+ | Truncated 44 tables | +---------------------+ 1 row in set (0.09 sec) Query OK, 0 rows affected (0.09 sec) # 切换binlog mysql > flush binary logs; Query OK, 0 rows affected (0.02 sec)

• 主从库分别执行一个加压辅助脚本，主库并行执行2个UPDATE语句（分别对sbtest1和sbtest2表发起UPDATE操作），从库并行执行2个SELECT语句（分别对sbtest1和sbtest2表发起SELECT操作）（脚本代码详见文末的下载链接）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

# 主库（该脚本会按照指定的时间间隔循环检查执行时长超过该指定间隔时间的会话状态，如果存在超时的会话，则尝试使用show processlist语句、sys.session视图打印更加详细的会话信息，并尝试使用sys.innodb_lock_waits视图打印锁等待信息；如果不存在超时会话，则并行使用2个会话对指定的表执行UPDATE语句，注意：脚本中的两个UPDATE语句的id取值为id列的一个较小值，不要与后续需要执行的大事务中的id值范围有锁冲突） [root@physical-machine ~]# sh exec_parallel_sql_master.sh  ## 脚本运行过程中，会按照指定的时间间隔打印一些执行日志，每打印一次就表示UPDATE语句执行成功了一次 now() mark 2019-09-11 13:40:36 ------------------ now() mark 2019-09-11 13:40:37 ------------------ now() mark 2019-09-11 13:40:37 ------------------ ...... # 从库（与主库中的exec_parallel_sql_master.sh脚本作用类似，但在从库中，不存在超时会话时，并行运行的语句从UPDATE改为SELECT） [root@node2 ~]# sh exec_parallel_sql_slave.sh  ## 脚本运行过程中，会按照指定的时间间隔打印一些执行日志，以及SELECT语句的查询结果 2019-09-11 13:40:54 ------------------ id k c pad 1000001 4982373 33215995692-11643009243-86512240766-52474552185-05677806687-01251571470-76461373271-04600478707-30936631606-18731704317 91106890267-89662465049-88180742274-99973317521-51514147703 now() mark 2019-09-11 13:40:55 ------------------ id k c pad 2000001 4979176 84112274222-72580329847-55073805313-86104475687-27118798543-60440415134-55566695297-03778364972-89646151895-46966443039 40891678273-04756075401-41893687834-80508607710-52345565443 now() mark 2019-09-11 13:40:55 ------------------ ......

• 另起一个ssh终端会话，在从库运行一个每秒查询复制延迟的脚本（脚本代码详见文末的下载链接）

1 2 3 4 5 6

[root@node2 ~]# sh check_slave_delay.sh  # 脚本运行过程中，会按照指定的时间间隔打印一些执行日志 当前备库复制延迟(intervel=1,2019-09-11_13:41:31): 0 当前备库复制延迟(intervel=1,2019-09-11_13:41:32): 0 当前备库复制延迟(intervel=1,2019-09-11_13:41:33): 0 ......

• 主从库各自先查看一下慢查询日志、解析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

# 主从库慢查询日志 ## 查看主库慢查询日志文件，可以发现并没有慢查询日志内容 [root@node1 ~]# cat /data//mysqldata1/slowlog/slow-query.log     /usr/local/mysql/bin/mysqld, Version: 5.7.27-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /home/mysql/data/mysqldata1/sock/mysql.sock Time Id Command Argument ...... [root@node1 ~]#  ## 查看从库慢查询日志文件，可以发现仍然没有慢查询日志内容 [root@node2 ~]# cat /data//mysqldata1/slowlog/slow-query.log  /usr/local/mysql/bin/mysqld, Version: 5.7.27-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /hom