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

今天药忘吃喽~ 2023-06-04 08:54 8阅读 0赞

*  或许某些人会说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表）

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 </td> 
 <td> <code>mysql&nbsp;&gt;&nbsp;call&nbsp;sys.ps_setup_enable_instrument(</code><code>'wait'</code><code>);</code> <code>+</code><code>-------------------------+</code> <code>|&nbsp;summary&nbsp;|</code> <code>+</code><code>-------------------------+</code> <code>|&nbsp;Enabled&nbsp;310&nbsp;instruments&nbsp;|</code> <code>+</code><code>-------------------------+</code> <code>1&nbsp;row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.01&nbsp;sec)</code> <code>Query&nbsp;OK,&nbsp;0&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(0.01&nbsp;sec)</code> <code>mysql&nbsp;&gt;&nbsp;call&nbsp;sys.ps_setup_enable_consumer(</code><code>'wait'</code><code>);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> <code>+</code><code>---------------------+</code> <code>|&nbsp;summary&nbsp;|</code> <code>+</code><code>---------------------+</code> <code>|&nbsp;Enabled&nbsp;3&nbsp;consumers&nbsp;|</code> <code>+</code><code>---------------------+</code> <code>1&nbsp;row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

*  主库造数2张1000W的表

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

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

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

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

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>[root@node2&nbsp;~]#&nbsp;sh&nbsp;check_slave_delay.sh&nbsp;</code> <code>#&nbsp;脚本运行过程中，会按照指定的时间间隔打印一些执行日志</code> <code>当前备库复制延迟(intervel=1,2019-09-11_13:41:31):&nbsp;0</code> <code>当前备库复制延迟(intervel=1,2019-09-11_13:41:32):&nbsp;0</code> <code>当前备库复制延迟(intervel=1,2019-09-11_13:41:33):&nbsp;0</code> <code>......</code> </td> 
 </tr> 
 </tbody> 
</table>

*  主从库各自先查看一下慢查询日志、解析binlog，并分析慢查询日志（这里的步骤是为了与后续执行完成大事务后的分析结果做对比，没有对比就没有伤害嘛）

*  pt-query\_digest 解析结果截图
    
     *  图1（主库）
    
    ![format_png][]
    
     *  图2（从库）
    
    ![format_png 1][]
 *  关于主从库binlog解析中，exec\_time大于0秒的事务比对信息如下，通过这些信息可以看到，在从库回放binlog时，事务的执行时间被拉长了（exec\_time这个属性的统计时间，是从每个事务修改的第一行记录开始计时，直到事务提交完成为止，期间，如果事务存在锁等待，则锁等待的时间也会被一并计算在内。但是要注意，在一个事务包含多个SQL语句时，或者中间夹杂着一些操作间隔时间，例如sleep函数等，那么这个值就不准确，但在我们的测试案例中，一个事务只修改一行记录，所以这个时间在这里是准确的，因此在这里可以认为该值为这个事务真正执行的总时间）

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 </td> 
 <td> <code>#&nbsp;主库（从这里可以看到，exec_time&gt;0的记录为1行，所以主库的binlog分析结果中，第一个语句也是唯一的一个语句的</code><code>Exec</code>&nbsp;<code>time</code><code>行的Total列是1S）</code> <code>[root@node1&nbsp;binlog]#&nbsp;cat&nbsp;a.sql&nbsp;|grep&nbsp;exec_time&nbsp;|grep&nbsp;-v&nbsp;</code><code>'exec_time=0'</code> <code>#190911&nbsp;13:52:26&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;1010517&nbsp;CRC32&nbsp;0x9481512e&nbsp;Query&nbsp;thread_id=96524&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#&nbsp;从库（从这里可以看到，exec_time&gt;0的有10行记录，每行都是1，所以从库的binlog分析结果中，第一个语句也是唯一的一个语句的</code><code>Exec</code>&nbsp;<code>time</code><code>行的Total列是10S）</code> <code>[root@node2&nbsp;binlog]#&nbsp;cat&nbsp;b.sql&nbsp;|grep&nbsp;exec_time&nbsp;|grep&nbsp;-v&nbsp;</code><code>'exec_time=0'</code>&nbsp; <code>#190911&nbsp;13:42:44&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;181363&nbsp;CRC32&nbsp;0x0be0bb2f&nbsp;Query&nbsp;thread_id=88584&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:42:45&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;182787&nbsp;CRC32&nbsp;0x86386f64&nbsp;Query&nbsp;thread_id=88587&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:44:26&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;323911&nbsp;CRC32&nbsp;0x258368bc&nbsp;Query&nbsp;thread_id=89320&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:46:16&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;477703&nbsp;CRC32&nbsp;0xb91bf3e6&nbsp;Query&nbsp;thread_id=90964&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:47:04&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;545343&nbsp;CRC32&nbsp;0x8dae4390&nbsp;Query&nbsp;thread_id=91691&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:48:53&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;697711&nbsp;CRC32&nbsp;0xcbe1b667&nbsp;Query&nbsp;thread_id=93332&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:48:54&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;698423&nbsp;CRC32&nbsp;0x44114126&nbsp;Query&nbsp;thread_id=93338&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:50:41&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;847943&nbsp;CRC32&nbsp;0x27c5a588&nbsp;Query&nbsp;thread_id=94939&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:52:26&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;995327&nbsp;CRC32&nbsp;0x3307103c&nbsp;Query&nbsp;thread_id=96524&nbsp;exec_time=1&nbsp;error_code=0</code> <code>#190911&nbsp;13:53:19&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;1069375&nbsp;CRC32&nbsp;0x0540667c&nbsp;Query&nbsp;thread_id=97310&nbsp;exec_time=1&nbsp;error_code=0</code> </td> 
 </tr> 
 </tbody> 
</table>

*  现在，准备工作已经就绪，在主从库中执行切换binlog，准备入场了

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>mysql&nbsp;&gt;&nbsp;flush&nbsp;</code><code>binary</code>&nbsp;<code>logs;</code> </td> 
 </tr> 
 </tbody> 
</table>

### 3、执行大事务 ###

*  首先，在主从库中，各自执行一个按照指定的时间间隔循环打印数据库redo log、binlog、ibdata、undo、表数据文件写盘的吞吐统计脚本（脚本代码详见文末的下载链接）

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>#&nbsp;该脚本的作用是，按照指定间隔时间，从sys.x$io_global_by_file_by_bytes视图中抓取文件IO吞吐数据，该视图中的数据基本可以认为是实时数据（详情可查看微信公众号</code><code>"沃趣技术"</code><code>中的sys&nbsp;</code><code>schema</code><code>系列文章）。注意，针对主库运行的脚本中的mysql_ip参数修改为主库IP，针对从库运行的脚本中mysql_ip参数修改为从库IP</code> <code>##&nbsp;主库（执行打印结果详见下图3）</code> <code>[root@node1&nbsp;~]#&nbsp;sh&nbsp;count_mysql_disk_file_io.sh</code> <code>##&nbsp;从库（执行打印结果详见下图4）</code> <code>[root@node2&nbsp;~]#&nbsp;sh&nbsp;count_mysql_disk_file_io.sh</code> </td> 
 </tr> 
 </tbody> 
</table>

*  count\_mysql\_disk\_file\_io.sh脚本打印截图如下
    
     *  图3（主库）
    
    ![format_png 2][]
    
     *  图4（从库）
    
    ![format_png 3][]
 *  从图3和图4的比对结果来看，主从库中都频繁抓取到redo log和binlog有0~30KB范围内的吞吐，说明此时，对于主库中的UPDATE事务产生的数据文件的吞吐量，在主从库中是一致的
 *  现在，登录到主从数据库中，各自使用sys.io\_global\_by\_file\_by\_bytes视图查询当前主从库中相关的数据文件总吞吐统计值，以便后续大事务执行完成之后，再次抓取该统计值进行对比

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>#&nbsp;主库（执行结果详见下图5）</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>sys.io_global_by_file_by_bytes&nbsp;</code><code>where</code>&nbsp;<code>count_read!=0&nbsp;</code><code>order</code>&nbsp;<code>by</code>&nbsp;<code>count_read&nbsp;</code><code>desc</code><code>;</code> <code>......</code> <code>#&nbsp;从库（执行结果详见下图6）</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>sys.io_global_by_file_by_bytes&nbsp;</code><code>where</code>&nbsp;<code>count_read!=0&nbsp;</code><code>order</code>&nbsp;<code>by</code>&nbsp;<code>count_read&nbsp;</code><code>desc</code><code>;</code> <code>......</code> </td> 
 </tr> 
 </tbody> 
</table>

*  sys.io\_global\_by\_file\_by\_bytes视图的执行结果截图如下
    
     *  图5（主库）
    
    ![format_png 4][]
    
     *  图6（从库）
    
    ![format_png 5][]
 *  从图5和图6的对比数据可以看到，主库的写流量主要在binlog上，而且读流量在binlog上也有几兆；从库的写流量主要在relay log上，读流量也主要在relay log；另外，proc.MYD文件的读流量主要是因为相关的辅助脚本持续调用相关的查询视图产生的。当然，这里的数据可能不准确（例如：从库中relay log在重放完成之后，如果启用了purge\_relay\_log参数，会被自动清理，在这个视图中也就看不到被清理的relay log文件相关的统计信息了），但不要紧，这里仅仅只是让我们知晓一个流量的量级，等到后续大事务执行完成之后，再次查询该视图中的数据，你将会发现大事务执行前后相关的数据文件在读写流量上存在量级上的差异
 *  现在，登录到主库中，执行修改数据量级超过400W行的UPDATE语句（给定id值范围不要与exec\_parallel\_sql\_master.sh脚本中给定的id值发生锁冲突），这里我们以操作sbtest1表为例执行大事务

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>mysql&nbsp;&gt;&nbsp;use&nbsp;sbtest</code> <code>Database</code>&nbsp;<code>changed</code> <code>#&nbsp;注意，这里不要加limit，会干扰exec_parallel_sql_master.sh和exec_parallel_sql_slave.sh脚本中的判断逻辑（通过上述环境信息可知，我们造数为1000W，且自增不长设置为2，所以，id&gt;10000000大概是500W行的数据量级别）</code> <code>mysql&nbsp;&gt;&nbsp;explain&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;10000000;</code> <code>+</code><code>----+-------------+---------+------------+-------+---------------+---------+---------+-------+---------+----------+-------------+</code> <code>|&nbsp;id&nbsp;|&nbsp;select_type&nbsp;|&nbsp;</code><code>table</code>&nbsp;<code>|&nbsp;partitions&nbsp;|&nbsp;type&nbsp;|&nbsp;possible_keys&nbsp;|&nbsp;</code><code>key</code>&nbsp;<code>|&nbsp;key_len&nbsp;|&nbsp;ref&nbsp;|&nbsp;</code><code>rows</code>&nbsp;<code>|&nbsp;filtered&nbsp;|&nbsp;Extra&nbsp;|</code> <code>+</code><code>----+-------------+---------+------------+-------+---------------+---------+---------+-------+---------+----------+-------------+</code> <code>|&nbsp;1&nbsp;|&nbsp;</code><code>UPDATE</code>&nbsp;<code>|&nbsp;sbtest1&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;range&nbsp;|&nbsp;</code><code>PRIMARY</code>&nbsp;<code>|&nbsp;</code><code>PRIMARY</code>&nbsp;<code>|&nbsp;4&nbsp;|&nbsp;const&nbsp;|&nbsp;4938195&nbsp;|&nbsp;100.00&nbsp;|&nbsp;Using&nbsp;</code><code>where</code>&nbsp;<code>|</code> <code>+</code><code>----+-------------+---------+------------+-------+---------------+---------+---------+-------+---------+----------+-------------+</code> <code>1&nbsp;row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> <code>#&nbsp;使用显式的事务执行该语句</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>begin</code><code>;&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;10000000;</code><code>commit</code><code>;</code> <code>......</code> </td> 
 </tr> 
 </tbody> 
</table>

*  在主库大事务未执行完成之前，可在主库反复执行show processlist查看线程状态信息（该步骤不是必须的）

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>mysql&nbsp;&gt;&nbsp;show&nbsp;processlist;</code> <code>+</code><code>--------+------+--------------------+--------+------------------+-------+---------------+--------------------------------------------+</code> <code>|&nbsp;Id&nbsp;|&nbsp;</code><code>User</code>&nbsp;<code>|&nbsp;Host&nbsp;|&nbsp;db&nbsp;|&nbsp;Command&nbsp;|&nbsp;</code><code>Time</code>&nbsp;<code>|&nbsp;State&nbsp;|&nbsp;Info&nbsp;|</code> <code>+</code><code>--------+------+--------------------+--------+------------------+-------+----------------+--------------------------------------------+</code> <code>|&nbsp;4&nbsp;|&nbsp;repl&nbsp;|&nbsp;10.10.30.163:33375&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;Binlog&nbsp;Dump&nbsp;GTID&nbsp;|&nbsp;80999&nbsp;|&nbsp;Master&nbsp;has&nbsp;sent&nbsp;</code><code>all</code>&nbsp;<code>binlog&nbsp;</code><code>to</code>&nbsp;<code>slave;&nbsp;waiting&nbsp;</code><code>for</code>&nbsp;<code>more&nbsp;updates&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;5&nbsp;|&nbsp;repl&nbsp;|&nbsp;10.10.30.164:49847&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;Binlog&nbsp;Dump&nbsp;GTID&nbsp;|&nbsp;80998&nbsp;|&nbsp;Master&nbsp;has&nbsp;sent&nbsp;</code><code>all</code>&nbsp;<code>binlog&nbsp;</code><code>to</code>&nbsp;<code>slave;&nbsp;waiting&nbsp;</code><code>for</code>&nbsp;<code>more&nbsp;updates&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;88201&nbsp;|&nbsp;root&nbsp;|&nbsp;localhost&nbsp;|&nbsp;sbtest&nbsp;|&nbsp;Query&nbsp;|&nbsp;19&nbsp;|&nbsp;updating&nbsp;|&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;10000000&nbsp;|</code> <code>|&nbsp;107409&nbsp;|&nbsp;root&nbsp;|&nbsp;localhost&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;Query&nbsp;|&nbsp;0&nbsp;|&nbsp;starting&nbsp;|&nbsp;show&nbsp;processlist&nbsp;|</code> <code>+</code><code>--------+------+--------------------+--------+------------------+-------+-----------------+--------------------------------------------+</code> <code>4&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

*  查看主库中数据文件的流量吞吐情况（该信息为count\_mysql\_disk\_file\_io.sh脚本的打印日志），从下图中可以发现，事务未执行完成之前，除了binlog之外，表空间文件、共享表空间、undo、redo的磁盘流量都有不同程度的大幅增加，说明事务未提交时，这几种文件都会写盘且，binlog不写盘（以往没少听说过“事务提交之后，脏页在后台刷新”这种话吧？嘿，这话听起来挺有迷惑性的。事务提交之后，后台线程的确会做刷脏的事情，不过，这句话可没说事务未提交之前后台线程不刷脏。这样设计显然是合理的，否则大事务产生的脏页总量超过内存时，事务就无法执行了）

![format_png 6][]

*  持续观察主库中的语句执行进度，直到执行完成（这一步可千万不要中断了，否则上述一系列步骤就要重新来过了）

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>mysql&nbsp;&gt;&nbsp;</code><code>begin</code><code>;&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;10000000;</code><code>commit</code><code>;</code> <code>Query&nbsp;OK,&nbsp;0&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(0.00&nbsp;sec)&nbsp;&nbsp;#&nbsp;这里是</code><code>begin</code><code>语句执行的时间，为0.00&nbsp;sec</code> <code>Query&nbsp;OK,&nbsp;5000000&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(2&nbsp;</code><code>min</code>&nbsp;<code>58.55&nbsp;sec)&nbsp;&nbsp;#&nbsp;这里可以看到</code><code>UPDATE</code><code>语句本身执行了2&nbsp;</code><code>min</code>&nbsp;<code>58.55&nbsp;sec</code> <code>Rows</code>&nbsp;<code>matched:&nbsp;5000000&nbsp;Changed:&nbsp;5000000&nbsp;Warnings:&nbsp;0</code> <code>Query&nbsp;OK,&nbsp;0&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(5.01&nbsp;sec)&nbsp;#&nbsp;从这里可以看到，这里是</code><code>UPDATE</code><code>语句后边的</code><code>commit</code><code>语句，执行了5.01&nbsp;sec</code> </td> 
 </tr> 
 </tbody> 
</table>

*  待到主库大事务执行完成之后，迅速观察从库的复制延迟情况，并查看下show processlist;

<table> 
 <tbody> 
 <tr> 
 <td> 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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 </td> 
 <td> <code>#&nbsp;查看从库复制延迟</code> <code>mysql&nbsp;&gt;&nbsp;show&nbsp;slave&nbsp;status\G</code> <code>***************************&nbsp;1.&nbsp;row&nbsp;***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Slave_IO_State:&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>master&nbsp;</code><code>to</code>&nbsp;<code>send&nbsp;event</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_Host:&nbsp;10.10.30.162</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_User:&nbsp;repl</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_Port:&nbsp;3306</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Connect_Retry:&nbsp;10</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_Log_File:&nbsp;mysql-bin.000025</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Read_Master_Log_Pos:&nbsp;139733</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Log_File:&nbsp;mysql-relay-bin.000064</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Log_Pos:&nbsp;642431</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Master_Log_File:&nbsp;mysql-bin.000024</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Slave_IO_Running:&nbsp;Yes</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Slave_SQL_Running:&nbsp;Yes</code> <code>......</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Exec_Master_Log_Pos:&nbsp;642218</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Log_Space:&nbsp;1909355863</code> <code>......</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Seconds_Behind_Master:&nbsp;101&nbsp;#&nbsp;从这里可以看到，这个延迟数值已经有126秒了（虽然不准确，但是这个量级的延迟数字已经足够说明复制存在延迟了）</code> <code>......</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Retrieved_Gtid_Set:&nbsp;6f43ada4-d39d-11e9-9c97-5254002a54f2:1-27419</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Executed_Gtid_Set:&nbsp;6a3227ed-d39d-11e9-a7b0-525400c33752:1-2,</code> <code>6f43ada4-d39d-11e9-9c97-5254002a54f2:1-27224</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Auto_Position:&nbsp;1</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Replicate_Rewrite_DB:</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Channel_Name:</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_TLS_Version:</code> <code>1&nbsp;row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> <code>#&nbsp;查看从库线程状态</code> <code>mysql&nbsp;&gt;&nbsp;show&nbsp;processlist;</code> <code>+</code><code>--------+-------------+-----------+------+---------+-------+----------------+--------------------------------------------+</code> <code>|&nbsp;Id&nbsp;|&nbsp;</code><code>User</code>&nbsp;<code>|&nbsp;Host&nbsp;|&nbsp;db&nbsp;|&nbsp;Command&nbsp;|&nbsp;</code><code>Time</code>&nbsp;<code>|&nbsp;State&nbsp;|&nbsp;Info&nbsp;|</code> <code>+</code><code>--------+-------------+-----------+------+---------+-------+---------------+--------------------------------------------+</code> <code>|&nbsp;5&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>master&nbsp;</code><code>to</code>&nbsp;<code>send&nbsp;event&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;6&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;0&nbsp;|&nbsp;Reading&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>the&nbsp;relay&nbsp;log&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>#&nbsp;大事务的SQL语句在这里（注意，row格式下数据回放的是数据的逐行变更日志，并不是SQL语句文本，这里看到的SQL语句文本来自binlog_rows_query_log_events=</code><code>ON</code><code>参数记录的注释性的原始SQL文本，在从库回放主库binlog时会当做注释用途展示在这里）</code> <code>|&nbsp;7&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;316&nbsp;|&nbsp;Executing&nbsp;event&nbsp;|&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;10000000&nbsp;|</code> <code>|&nbsp;8&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;10270&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;9&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;10&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;11&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;12&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;13&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;14&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;15&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;16&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;17&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;18&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;19&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;20&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;21&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;22&nbsp;|&nbsp;system&nbsp;</code><code>user</code>&nbsp;<code>|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;</code><code>Connect</code>&nbsp;<code>|&nbsp;81296&nbsp;|&nbsp;Waiting&nbsp;</code><code>for</code>&nbsp;<code>an&nbsp;event&nbsp;</code><code>from</code>&nbsp;<code>Coordinator&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;107297&nbsp;|&nbsp;root&nbsp;|&nbsp;localhost&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;Sleep&nbsp;|&nbsp;596&nbsp;|&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|</code> <code>|&nbsp;127830&nbsp;|&nbsp;root&nbsp;|&nbsp;localhost&nbsp;|&nbsp;</code><code>NULL</code>&nbsp;<code>|&nbsp;Query&nbsp;|&nbsp;0&nbsp;|&nbsp;starting&nbsp;|&nbsp;show&nbsp;processlist&nbsp;|</code> <code>+</code><code>--------+-------------+-----------+------+---------+-------+-----------------+--------------------------------------------+</code> <code>20&nbsp;</code><code>rows</code>&nbsp;<code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

*  顺便看一眼从库的数据文件流量探测脚本的情况（这些信息来自脚本count\_mysql\_disk\_file\_io.sh的打印日志），从下图中标记的地方，对比下主库的数据文件流量打印日志，可以发现它们在量级上是差不多的

![format_png 7][]

*  关注check\_slave\_delay.sh脚本的打印情况，当发现复制延迟降为0时，表示大事务已经在从库回放完成了，通过check\_slave\_delay.sh脚本打印的信息，我们可以非常直观地看到，从07分26秒开始出现5秒延迟，10分54秒延迟降为0，毛估估的算一下，这个大事务在从库中的执行时间为3分28秒（该事务在主库中的执行时长为3分4秒），如下
    
     *  探测到开始大幅延迟的时间点
    
    ![format_png 8][]
    
     *  探测到延迟结束的时间点
    
    ![format_png 9][]
 *  此时，大事务在主从库都已经执行完成，可以将所有辅助脚本停止运行了
 *  上述对于主从库数据文件的流量探测截图中（这里指的是count\_mysql\_disk\_file\_io.sh脚本打印日志截图）都没有截取到binlog磁盘流量大幅增加的信息，难道这个大事务没写binlog？非也，因为binlog不是持续写的，在事务持久化的两阶段提交中，事务持久化完成之后，写binlog就结束了，因为人为操作的截图，错过打印的时间点没有截取到，我们打开脚本日志找找看

*  上述代码段可能不便阅读，这里将上述内容截图列出
    
     *  图7（主库）
    
    ![format_png 10][]
    
     *  图8（从库）
    
    ![format_png 11][]

### 4、分析大事务的影响 ###

*  首先在主从库分别查看慢查询日志文件

*  顺便用pt-query-digest工具解析下主库的慢查询日志，截个图列出
    
     *  图9（主库）
    
    ![format_png 12][]
    
     *  图10（从库）
    
    ![format_png 13][]
 *  现在，在主从库分别解析binlog日志查看，并使用pt-query-digest工具分析binlog

*  pt-query\_digest 解析结果截图
    
     *  图11（主库）
    
    ![format_png 14][]
    
     *  图12（从库）
    
    ![format_png 15][]
 *  从上面两个图的对比结果来看
    
     *  大事务在主库binlog中逃脱了，因为row格式中有效的binlog记录的是数据的逐行变更数据，而非SQL语句文本，同理，在从库中也应该不会记录该大事务的语句
     *  从库的binlog分析结果中，语句的总执行时间大幅增加。在上文中更早之前我们通过分析binlog已经知道，从库回放会拉长事务的执行时间，但是图12中从库binlog解析记录的SQL总执行时间为43853s，换算成小时，约12个小时，可我们在这里，整个操作过程不到一个小时，很显然，pt-query-digest工具的分析结果中，是将所有BEGIN语句的时间都加在一起了，又因为在测试环境中的从库启用了多线程复制，每个worker线程独立运行，也就是说，假设有N个worker线程同时在回放事务，而每个worker线程同时将执行时间拉长1S，那么，pt-query-digest工具的统计结果中的总延迟时间就变成了N\*1S=NS，以此推理可得，大事务在执行过程中，小事务分配binlog event变慢了，又或者被阻塞了，so...
     *  下面，我同样也将exec\_time属性不为0的记录过滤出来瞧瞧

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>#&nbsp;主库（发现主库一共有2行exec_time属性不为0的记录）</code> <code>[root@node1&nbsp;binlog]#&nbsp;cat&nbsp;b.txt&nbsp;|grep&nbsp;exec_time&nbsp;|grep&nbsp;-v&nbsp;</code><code>'exec_time=0'</code> <code>#190911&nbsp;14:07:21&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;338&nbsp;CRC32&nbsp;0xdd75591e&nbsp;Query&nbsp;thread_id=109957&nbsp;exec_time=4&nbsp;error_code=0</code> <code>#190911&nbsp;14:07:22&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;1061&nbsp;CRC32&nbsp;0x9482c46d&nbsp;Query&nbsp;thread_id=109974&nbsp;exec_time=3&nbsp;error_code=0</code> <code>#&nbsp;从库（发现从库一共有423行exec_time属性不为0的记录）</code> <code>[root@node2&nbsp;binlog]#&nbsp;cat&nbsp;c.txt&nbsp;|grep&nbsp;exec_time&nbsp;|grep&nbsp;-v&nbsp;</code><code>'exec_time=0'</code> <code>......</code> <code>#190911&nbsp;14:07:21&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;367&nbsp;CRC32&nbsp;0xe616b934&nbsp;Query&nbsp;thread_id=109957&nbsp;exec_time=212&nbsp;error_code=0</code> <code>#190911&nbsp;14:07:22&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;1079&nbsp;CRC32&nbsp;0x03ca1c89&nbsp;Query&nbsp;thread_id=109974&nbsp;exec_time=211&nbsp;error_code=0</code> <code>#190911&nbsp;14:07:25&nbsp;server&nbsp;id&nbsp;33061&nbsp;end_log_pos&nbsp;1791&nbsp;CRC32&nbsp;0xc4e998ea&nbsp;Query&nbsp;thread_id=110012&nbsp;exec_time=208&nbsp;error_code=0</code> <code>......</code> <code>[root@node2&nbsp;binlog]#&nbsp;cat&nbsp;c.txt&nbsp;|grep&nbsp;exec_time&nbsp;|grep&nbsp;-v&nbsp;</code><code>'exec_time=0'</code>&nbsp;<code>|wc&nbsp;-l</code> <code>423</code> </td> 
 </tr> 
 </tbody> 
</table>

*  现在，主从库分别查看脚本日志（主库中查看exec\_parallel\_sql\_master.sh脚本的执行日志，从库中查看exec\_parallel\_sql\_slave.sh脚本的执行日志），看看主库是否有发生较长时间的写阻塞，从库是否有发生较长时间的读阻塞（注：以下代码段中为部分日志，完整日志请从文末的下载链接中获取）

<table> 
 <tbody> 
 <tr> 
 <td> 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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 </td> 
 <td> <code>#&nbsp;主库（发现了SQL被阻塞的记录，从下面show&nbsp;processlist输出结果中可以看到，两个</code><code>update</code><code>语句只修改一行记录，原本是不会被阻塞的，但是，现在，因为大事务，发生了阻塞，从后续的sys.session视图这里可以大致判断，其他小事务由于大事务在提交binlog阶段发生了阻塞，甚至导致出现了2个一模一样的</code><code>UPDATE</code>&nbsp;<code>SQL，因为前一个SQL被阻塞未执行完，后一个完全一样的SQL进入之后甚至导致出现了锁等待）</code> <code>[root@node1&nbsp;binlog]#&nbsp;vim&nbsp;/tmp/exec_parallel_sql_master.sh.log</code> <code>......</code> <code>==============================================================================================</code> <code>警告！SQL发生阻塞！！！！！！</code><code>-----------------------------------------</code> <code>==============================================================================================</code> <code>show&nbsp;processlist信息如下：</code><code>---------------------------------</code> <code>Id&nbsp;</code><code>User</code>&nbsp;<code>Host&nbsp;db&nbsp;Command&nbsp;</code><code>Time</code>&nbsp;<code>State&nbsp;Info</code> <code>4&nbsp;repl&nbsp;10.10.30.163:33375&nbsp;</code><code>NULL</code>&nbsp;<code>Binlog&nbsp;Dump&nbsp;GTID&nbsp;81163&nbsp;Master&nbsp;has&nbsp;sent&nbsp;</code><code>all</code>&nbsp;<code>binlog&nbsp;</code><code>to</code>&nbsp;<code>slave;&nbsp;waiting&nbsp;</code><code>for</code>&nbsp;<code>more&nbsp;updates&nbsp;</code><code>NULL</code> <code>5&nbsp;repl&nbsp;10.10.30.164:49847&nbsp;</code><code>NULL</code>&nbsp;<code>Binlog&nbsp;Dump&nbsp;GTID&nbsp;81162&nbsp;Master&nbsp;has&nbsp;sent&nbsp;</code><code>all</code>&nbsp;<code>binlog&nbsp;</code><code>to</code>&nbsp;<code>slave;&nbsp;waiting&nbsp;</code><code>for</code>&nbsp;<code>more&nbsp;updates&nbsp;</code><code>NULL</code> <code>88201&nbsp;root&nbsp;localhost&nbsp;sbtest&nbsp;Query&nbsp;4&nbsp;starting&nbsp;</code><code>commit</code> <code>107409&nbsp;root&nbsp;localhost&nbsp;</code><code>NULL</code>&nbsp;<code>Sleep&nbsp;164&nbsp;</code><code>NULL</code> <code>109940&nbsp;admin&nbsp;10.10.30.16:62563&nbsp;sbtest&nbsp;Query&nbsp;4&nbsp;starting&nbsp;</code><code>commit</code> <code>109957&nbsp;admin&nbsp;10.10.30.16:62597&nbsp;sbtest&nbsp;Query&nbsp;3&nbsp;updating&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;1000000&nbsp;limit&nbsp;1</code> <code>109974&nbsp;admin&nbsp;10.10.30.16:62631&nbsp;sbtest&nbsp;Query&nbsp;2&nbsp;updating&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;1000000&nbsp;limit&nbsp;1</code> <code>109991&nbsp;admin&nbsp;10.10.30.16:62665&nbsp;</code><code>NULL</code>&nbsp;<code>Query&nbsp;0&nbsp;starting&nbsp;show&nbsp;processlist</code> <code>sys.session视图信息如下：</code><code>---------------------------------</code> <code>***************************&nbsp;1.&nbsp;row&nbsp;***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>thd_id:&nbsp;109982</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>conn_id:&nbsp;109940</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>user</code><code>:&nbsp;admin@10.10.30.16</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>db:&nbsp;sbtest</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>command:&nbsp;Query</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>state:&nbsp;starting</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>time</code><code>:&nbsp;4</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>current_statement:&nbsp;</code><code>commit</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>statement_latency:&nbsp;3.09&nbsp;s</code> <code>......</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>current_memory:&nbsp;0&nbsp;bytes</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>last_wait:&nbsp;wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>last_wait_latency:&nbsp;Still&nbsp;Waiting</code> <code>......</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>pid:&nbsp;24345</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>program_name:&nbsp;mysql</code> <code>***************************&nbsp;2.&nbsp;row&nbsp;***************************</code> <code>#&nbsp;锁等待信息</code> <code>***************************&nbsp;1.&nbsp;row&nbsp;***************************</code> <code>***************************&nbsp;2.&nbsp;row&nbsp;***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>wait_started:&nbsp;2019-09-11&nbsp;14:07:22</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>wait_age:&nbsp;00:00:02</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>wait_age_secs:&nbsp;2</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>locked_table:&nbsp;`sbtest`.`sbtest1`</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>locked_index:&nbsp;</code><code>PRIMARY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>locked_type:&nbsp;RECORD</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_trx_id:&nbsp;49083</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_trx_started:&nbsp;2019-09-11&nbsp;14:07:22</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_trx_age:&nbsp;00:00:02</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_trx_rows_locked:&nbsp;2</code> <code>&nbsp;&nbsp;&nbsp;</code><code>waiting_trx_rows_modified:&nbsp;0</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_pid:&nbsp;109974</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_query:&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;1000000&nbsp;limit&nbsp;1</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_lock_id:&nbsp;49083:41:6881:62</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>waiting_lock_mode:&nbsp;X</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_trx_id:&nbsp;49082</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_pid:&nbsp;109957</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_query:&nbsp;</code><code>update</code>&nbsp;<code>sbtest1&nbsp;</code><code>set</code>&nbsp;<code>k=k+1&nbsp;</code><code>where</code>&nbsp;<code>id&gt;1000000&nbsp;limit&nbsp;1</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_lock_id:&nbsp;49082:41:6881:62</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_lock_mode:&nbsp;X</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_trx_started:&nbsp;2019-09-11&nbsp;14:07:21</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_trx_age:&nbsp;00:00:03</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>blocking_trx_rows_locked:&nbsp;2</code> <code>&nbsp;&nbsp;</code><code>blocking_trx_rows_modified:&nbsp;0</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>sql_kill_blocking_query:&nbsp;KILL&nbsp;QUERY&nbsp;109957</code> <code>sql_kill_blocking_connection:&nbsp;KILL&nbsp;109957</code> <code>***************************&nbsp;3.&nbsp;row&nbsp;***************************</code> <code>......</code> <code>#&nbsp;从库（在从库中并未发现SQL阻塞和锁等待记录）</code> <code>[root@node2&nbsp;~]#&nbsp;cat&nbsp;/tmp/exec_parallel_sql_slave.sh.log&nbsp;|grep&nbsp;-E&nbsp;</code><code>'sys.session|wait_started'</code> <code>......</code> </td> 
 </tr> 
 </tbody> 
</table>

*  现在，登录到主从数据库中，各自使用sys.io\_global\_by\_file\_by\_bytes视图查询相关数据文件的总吞吐统计数据

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>#&nbsp;主库（见图13）</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>sys.io_global_by_file_by_bytes&nbsp;</code><code>where</code>&nbsp;<code>count_read!=0&nbsp;</code><code>order</code>&nbsp;<code>by</code>&nbsp;<code>count_read&nbsp;</code><code>desc</code><code>;</code> <code>......</code> <code>#&nbsp;从库（见图14）</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>sys.io_global_by_file_by_bytes&nbsp;</code><code>where</code>&nbsp;<code>count_read!=0&nbsp;</code><code>order</code>&nbsp;<code>by</code>&nbsp;<code>count_read&nbsp;</code><code>desc</code><code>;</code> <code>......</code> </td> 
 </tr> 
 </tbody> 
</table>

*  sys.io\_global\_by\_file\_by\_bytes视图的执行结果截图
    
     *  图13（主库）
    
    ![format_png 16][]
    
     *  图14（从库）
    
    ![format_png 17][]
 *  从上述图13和图14中可以看到，吞吐统计数据都毫无悬念地大幅增加了，不过。。主库中出现了一个临时文件/data/mysqldata1/tmpdir/MLrxkcac，读取和写入的总流量都为1.78G，从库中也出现了一个临时文件/data/mysqldata1/tmpdir/MLirV7PX，读取和写入总流量也都为1.78G，额，还真是巧了，这个临时文件是做啥的呢？再仔细一看图13中主库的/data/mysqldata1/binlog/mysql-bin.000024文件的写入总流量也为1.78G，主从库中出现的临时文件会不会跟写binlog有啥关系呢？咋们来推导一下，大事务未提交时，binlog是不能落盘的，那整个事务的binlog日志量往哪里放呢？内存？那万一内存不够用怎么办？那写磁盘吧？往哪儿写呢？临时文件？会不会这里看到的这个临时文件就是用来暂存未提交大事务的binlog的呢？
 *  查看一下磁盘上的binlog文件看看，从侧面证实下推导的结论

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 </td> 
 <td> <code>#&nbsp;查看binlog目录下的mysql-bin.000024文件</code> <code>[root@node1&nbsp;binlog]#&nbsp;ls&nbsp;-lh</code> <code>total&nbsp;22G</code> <code>......</code> <code>-rw-r</code><code>-----&nbsp;1&nbsp;mysql&nbsp;mysql&nbsp;1.8G&nbsp;Sep&nbsp;11&nbsp;14:07&nbsp;mysql-bin.000024&nbsp;#&nbsp;咦，不对啊，磁盘上这个文件是1.8G，对不上？别慌，我们换个思路，继续往下看</code> <code>-rw-r</code><code>-----&nbsp;1&nbsp;mysql&nbsp;mysql&nbsp;449K&nbsp;Sep&nbsp;11&nbsp;14:12&nbsp;mysql-bin.000025</code> <code>#&nbsp;会不会是单位算法不一样呢？sys.io_global_by_file_by_bytes视图中的字节流单位是使用sys.format_bytes()函数进行转换的，那我们改用ls&nbsp;-l查看，去掉-h选项查看磁盘文件，把字节数据拿出来，使用数据库中的sys.format_bytes()函数算算看</code> <code>[root@node1&nbsp;binlog]#&nbsp;ls&nbsp;-l</code> <code>total&nbsp;22186044</code> <code>......</code> <code>-rw-r</code><code>-----&nbsp;1&nbsp;mysql&nbsp;mysql&nbsp;1909214744&nbsp;Sep&nbsp;11&nbsp;14:07&nbsp;mysql-bin.000024&nbsp;&nbsp;#&nbsp;从这里可以看到，字节数据为1909214744</code> <code>-rw-r</code><code>-----&nbsp;1&nbsp;mysql&nbsp;mysql&nbsp;459299&nbsp;Sep&nbsp;11&nbsp;14:12&nbsp;mysql-bin.000025</code> <code>-rw-r</code><code>-----&nbsp;1&nbsp;mysql&nbsp;mysql&nbsp;1300&nbsp;Sep&nbsp;11&nbsp;14:07&nbsp;mysql-bin.index</code> <code>#&nbsp;登录数据库算算</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>select</code>&nbsp;<code>sys.format_bytes(1909214744);</code> <code>+</code><code>------------------------------+</code> <code>|&nbsp;sys.format_bytes(1909214744)&nbsp;|</code> <code>+</code><code>------------------------------+</code> <code>|&nbsp;1.78&nbsp;GiB&nbsp;|&nbsp;&nbsp;#&nbsp;从这里可以看到，正好1.78G</code> <code>+</code><code>------------------------------+</code> <code>1&nbsp;row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00&nbsp;sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

*  PS：对于ML开头的临时文件，事务未执行提交之前，在ps中无法被捕获到，但可以在操作系统层面使用lsof命令查看，如下（看到某个文件的字节数在不断增加的时候，就表示mysqld进程正在不断地使用临时文件存放一些临时数据，但是，当事务提交时，lsof命令捕获到的临时文件条目的字节数会被清零）：

![format_png 18][]

### 5、优化大事务的影响 ###

*  主库参数优化，添加如下两个优化参数

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td> <code>#&nbsp;将优化参数持久化</code> <code>[root@node1&nbsp;binlog]#&nbsp;cat&nbsp;/etc/my.cnf</code> <code>......</code> <code>[mysqld]</code> <code>binlog_transaction_dependency_tracking=WRITESET</code> <code>transaction_write_set_extraction=XXHASH64</code> <code>......</code> <code>#&nbsp;也可以动态修改，但是顺序不能搞错，需要先修改transaction_write_set_extraction参数为XXHASH64，否则会报错</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>set</code>&nbsp;<code>global</code>&nbsp;<code>transaction_write_set_extraction=XXHASH64;</code> <code>Query&nbsp;OK,&nbsp;0&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(0.00&nbsp;sec)</code> <code>mysql&nbsp;&gt;&nbsp;</code><code>set</code>&nbsp;<code>global</code>&nbsp;<code>binlog_transaction_dependency_tracking=WRITESET;</code> <code>Query&nbsp;OK,&nbsp;0&nbsp;</code><code>rows</code>&nbsp;<code>affected&nbsp;(0.00&nbsp;sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

*  现在，咋们将上述所有步骤重新跑一下，看看大事务是否会阻塞其他的小事务（过程略）
 *  从测试结果来看，很遗憾，没有太大的优化效果
    
     *  对于主库来说，小事务仍然会在大事务提交binlog阶段发生短暂阻塞（如果磁盘吞吐不太好，那这个问题可大可小了）
     *  对于从库来说，复制延迟仍然存在，但通过pt-query-digest工具解析binlog结果来看，总执行时间减小了一些，但仍然与未优化前处于同一个量级
 *  那是不是说这两个优化参数没啥用呢？不是的，测试结果只能证明，这两个参数的优化效果对大事务场景没太大用处，但对于其他一些特定场景就可能有用。那在什么场景下有用呢？这个话题，咱们日后再聊
 *  现在回过头来继续聊大事务究竟怎么搞呢？当你没有办法去优化某件某个大事务的，最好的办法就是...消灭它，如何消灭大事务呢？回到文章开头那句话：“在开发规范里明令禁止大事务（单个事务操作的数据量不建议超过10000行），尽可能将大事务拆分为小事务，即便特殊情况不得不跑大事务，至少也要在会话级别将binlog格式改成statement。
 *  最后
    
     *  特别强调一下，对于MySQL InnoDB存储引擎来说，建议设置主键或唯一索引，再不济也要有二级索引，否则，那感觉一定很酸爽，你可以准备两张没有任何索引的表，用上述过程操作一遍试试看！
     *  另外，喜欢用级联复制的同学们注意了，除非必须，否则不建议使用级联复制，以现在的服务器硬件配置来说，使用级联复制带来的好处还不如带来的问题多

### 6、附录 ###

*  上文中使用到的相关脚本和相关日志文件下载链接如下
    
     *  脚本： [https://pan.baidu.com/s/11t-WbJns42jO8\_s7YW5pDQ][https_pan.baidu.com_s_11t-WbJns42jO8_s7YW5pDQ]
     *  日志： [https://pan.baidu.com/s/1SvVhvt59peB1gHuhHqAA0g][https_pan.baidu.com_s_1SvVhvt59peB1gHuhHqAA0g]
 *  PS：
    
     *  关于exec\_time属性的详细解释，详情可参考 高鹏（重庆八怪）的《深入理解MySQL主从原理》专栏中的第八讲
     *  关于大事务binlog未落盘之前，是否会写临时文件的详细解释，详情可参考 高鹏（重庆八怪）的《深入理解MySQL主从原理》专栏中的第十三、第十四讲。最后，附一张高清图（该图为第十四讲文末的高清图），留意左侧绿色方框内的内容
    
    ![format_png 19][]

**| 作者简介**

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

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

[format_png]: /images/20230601/88cd57279fd447ac869916055bc3a10a.png
[format_png 1]: /images/20230601/c70505e898744ec897dc0b8ab4ee6f3e.png
[format_png 2]: /images/20230601/94023dee43be4fabb321d93a642624d2.png
[format_png 3]: /images/20230601/f1f0969bd40246be8712fe75c32d9a58.png
[format_png 4]: /images/20230601/4a260b8f5fef4ad580f04ba99fb3c749.png
[format_png 5]: /images/20230601/549e6cf5a9754893a179b1aaf7ef79cf.png
[format_png 6]: /images/20230601/d548a2bc73394478a0828ca43f561ebc.png
[format_png 7]: /images/20230601/7cc72c6746c4488fbdc2bbdfbea1e653.png
[format_png 8]: /images/20230601/ef3cc75f3dd74b72b0e3b645eb2cabae.png
[format_png 9]: /images/20230601/46c981da37164c8297299a041bdfbacb.png
[format_png 10]: /images/20230601/3a6d66e4bbab4b5e97d266f2569dad6c.png
[format_png 11]: /images/20230601/692e0c34660542cfafeda6b3c770b467.png
[format_png 12]: /images/20230601/0a1adff918b54499a6d2fb115d09a434.png
[format_png 13]: /images/20230601/16cfc67111154f5b987d0a27bc318332.png
[format_png 14]: /images/20230601/2a52ae19269d4cf98233a02c13030063.png
[format_png 15]: /images/20230601/b01ea8f360d54924b55d749819d5a8c9.png
[format_png 16]: /images/20230601/57b82d4f2be94c5b828eb4f16a6814e5.png
[format_png 17]: /images/20230601/ae4d494cf49143a0bef2ecb2cdeba6f6.png
[format_png 18]: /images/20230601/5e82d47c447d454490aadeeb2b46660d.png
[https_pan.baidu.com_s_11t-WbJns42jO8_s7YW5pDQ]: https://pan.baidu.com/s/11t-WbJns42jO8_s7YW5pDQ
[https_pan.baidu.com_s_1SvVhvt59peB1gHuhHqAA0g]: https://pan.baidu.com/s/1SvVhvt59peB1gHuhHqAA0g
[format_png 19]: /images/20230601/f927540b81b549228ad8e7c35cc6e976.png