千家信息网最后更新 2024年11月23日mysql 基于组提交的并发复制小结
一：MySQL 5.7并行复制初理解 我们知道MySQL 5.7并行复制引入了两个值last_committed和sequence_number。last_committed表示事务提交的时候，上次事务提交的编号，在主库上同时提交的事务设置成相同的last_committed。如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。
查看组提交的事务个数,其中9892是last_commited的值，相同的last_commited值，后面的sequnen ce_number是可以并行复制的事务的一个序列号，新的binlog文件是从1开始的，给gtid的xid没有关系！ cat binlog.log | grep GTID | grep last_committed | grep 19408 二：造成延迟的原因： 表没有主键，事务太大，并行力度不够,参数设置不合适，从库硬件差
二：Mysql 5.7并行复制相关的参数 一般主从延迟太大，要不是没有主键，要不是配置的不合理 依次介绍些这些参数的作用，以及设置途径 binlog_group_commit_sync_delay binlog_group_commit_sync_no_delay_count max_allowed_packet slave_max_allowed_packet slave_preserve_commit_order slave_pending_jobs_size_max
一：binlog_group_commit_sync_delay ：| Property | Value |
| Command-Line Format | --binlog-group-commit-sync-delay=# |
| System Variable | binlog_group_commit_sync_delay |
| Scope | Global |
| Dynamic | Yes |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 1000000 |
1）该参数控制mysql 组提交之前等待的微秒数，设置该参数可以让组提交的每个组里面的事务数更多（因为他等待了n微妙），因为每个组里面的事务可以在从库并行的去重放，所以设置该参数大于0，有助于提高从库应用日志的速度，减少从库延迟 设置binlog_group_commit_sync_delay可以增加从库的并行提交事务数，因此可以增加复制从属服务器上的并行执行能力。要受益于此效果，从属服务器必须设置slave_parallel_type=LOGICAL_CLOCK，并且在还设置binlog_transaction_dependency_tracking=COMMIT_ORDER时效果更显著。在调整binlog_group_commit_sync_delay的设置时，必须同时考虑主设备的吞吐量和从设备的吞吐量。 2）但是设置binlog_group_commit_sync_delay会增加主服务器上事务的延迟，这可能会影响客户端应用程序。此外，在
高度并发的工作负载上，可能会增加争用，从而降低吞吐量，导致数据库性能降低，还能在错误日志中看到很多RECORD LOCK信息， 3）当设置sync_binlog=0或sync_binlog=1时，binlog_group_commit_sync_delay指定的延迟将作用于每个事务组提交。当sync_binlog设置为大于1的值n时，该参数的延迟作用于每n个二进制日志提交组提交之间；
建议：该参数设置一定要结合实际情况，评估出mysql的并发量，计算出单位时间内的并发量，然后合理设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count（单位时间每组的事务数） ，一定要限制每个组的事务个数，因为如果不设置的话，如果你的实际并发量挺大，这就会导致你的每个组的事务数可能非常多，然后我们知道组提交的commit阶段是分三个步骤的，每个阶段都是有队列的，如果每个组太多事务，就会导致内存队列不够用，而使用临时文件，这样就降低了commit的性能，造成更多的延迟， 所以要使用 binlog_group_commit_sync_no_delay_count 来限制你的每个组的事务数，这样就能尽可能多的把同一个组的事务数增多，但是也不至于太多，限制在一个合理的范围，这些事务可以在从库并发执行，而如果不设置的话，那么这些事务可能有很多是串行的， 也有可能事务太多导致性能降低而产生更多的延迟！
拓展参数 binlog_transaction_depandency_tracking： 控制检测是不是可以并行复制的方式， 基于主键的冲突检测(binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION, 修改的row的主键或非空唯一键没有冲突，即可并行) 5.7.22 也支持了 write-set 机制 事务依赖关系：
binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION COMMIT_ORDERE: 继续基于组提交方式 WRITESET: 基于写集合决定事务依赖（有问题！） WRITESET_SESSION:
基于写集合，但是同一个session中的事务不会有相同的last_committed 事务检测算法：
transaction_write_set_extraction = OFF| XXHASH64 | MURMUR32 MySQL会有一个变量来存储已经提交的事务HASH值，所有已经提交的事务所修改的主键（或唯一键）的值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系 这里说的变量，可以通过这个设置大小： binlog_transaction_dependency_history_size 这样的粒度，就到了 row级别了，此时并行的粒度更加精细，并行的速度会更快，某些情况下，说slave的并行度超越master也不为过(master是单线程的写，slave也可以并行回放)
二：binlog_group_commit_sync_no_delay_count| Property | Value |
| Command-Line Format | --binlog-group-commit-sync-no-delay-count=# |
| System Variable | binlog_group_commit_sync_no_delay_count |
| Scope | Global |
| Dynamic | Yes |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 1000000 |
该参数必须和binlog_group_commit_sync_delay一起使用才有意义，当已经处于延迟的事务个数达到该参数设置的数的时候，就终止了binlog_group_commit_sync_delay参数设置的微妙延迟（不需要一定要延迟参数binlog_group_commit_sync_delay设置的微妙数），也就是该参数保证同一个组提交的组里面的事务数不能太多！
三：slave_preserve_commit_order| Property | Value |
| Command-Line Format | --slave-preserve-commit-order[={OFF|ON}] |
| System Variable | slave_preserve_commit_order |
| Scope | Global |
| Dynamic | Yes |
| Type | Boolean |
| Default Value | OFF |
1）对于多线程从机，此变量的设置1确保事务在主库提交的顺序与它们在从库中继日志中显示的顺序相同，并防止从库中继日志执行的事务序列中出现间隙。此变量对未启用多线程的从机没有影响。请注意，slave_preserve_commit_order=1不保留非事务性DML更新的顺序，因此这些更新可能在中继日志中它们之前的事务之前提交，这可能会导致间隙； 2）slave_preserve_commit_order=1要求在从机上启用--log bin和
--log-slave-updates ，并且slave_parallel_type设置为
LOGICAL_CLOCK。在更改此变量之前，必须停止所有复制线程（如果使用多个复制通道，则为所有复制通道） 3）在启用slave_preserve_commit_order的情况下，sql线程在提交之前等待所有以前的事务提交， 当从线程等待其他工作线程提交其事务时，它会将其状态报告为:Waiting for preceding transaction to commit,所以如果启用该参数，那么可能会加剧主从延迟！ 4)如果设置slave_preserve_commit_order=0，则slave并行应用的事务可能会无序提交。因此，检查最近执行的事务并不能保证来自主服务器的所有以前的事务都在从服务器上已经执行（也就是说某你在从库看到的不是当前的状态，这不是延迟导致的，而是提交顺序不一样导致的不一致状态）。在从机的中继日志中执行的事务序列中可能存在间隙。这对使用多线程应用的从库的日志记录和恢复有影响。
四：binlog_order_commits| Property | Value |
| Command-Line Format | --binlog-order-commits[={OFF|ON}] |
| System Variable | binlog_order_commits |
| Scope | Global |
| Dynamic | Yes |
| Type | Boolean |
| Default Value | ON |
该参数控制： 当在复制主机上启用此变量（这是默认设置）时，向存储引擎发出的事务提交指令将使用单个线程串行的提交，以便事务始终按写入二进制日志的相同顺序提交。禁用此变量允许使用多个线程发出事务提交指令。
与二进制日志组提交结合使用，可以防止单个事务的提交速率成为吞吐量的瓶颈，因此可能会提高性能（主从都设置） 当所有涉及的存储引擎都已确认事务已准备好提交时，事务将写入二进制日志。然后，二进制日志组提交逻辑在二进制日志写入之后提交一组事务。当binlog_order_commits被禁用时，由于此进程使用多个线程，提交组中的事务可能会以不同于二进制日志中事务顺序的顺序提交。（来自单个客户机的事务总是按时间顺序提交）在许多情况下，这无关紧要，因为既然组提交中的事务可以并行复制，说明这些事务分开执行是会产生一致性的结果的，否则不能并行复制只能单独的事务执行了；
建议：在一些不重要的从库上，可以关闭该参数来提高从库的复制性能！
五：关于packet 数据包的大小： 1.slave_pending_jobs_size_max的用途： 在多线程复制时，在队列中Pending（等待）的事件所占用的最大内存，默认为16M，如果内存富余，或者延迟较大时，可以适当调大; 注意这个值要比主库的max_allowed_packet大，并且这个参数需要重启restart slave才能生效，也就是stop slave,start slave才能生效 查看主库的max_allowed_packet参数！ mysql
> show variables
like 'max_allowed_packet';
-- 134217728 即128M + --------------------+-----------+ | Variable_name
| Value
| + --------------------+-----------+ | max_allowed_packet
| 134217728 | + --------------------+-----------+ 2.max_allowed_packet| Property | Value |
| Command-Line Format | --max-allowed-packet=# |
| System Variable | max_allowed_packet |
| Scope | Global, Session |
| Dynamic | Yes |
| Type | Integer |
| Default Value | 4194304 |
| Minimum Value | 1024 |
| Maximum Value | 1073741824 |
该参数控制mysql限制server接受的数据包大小，需要注意应该设置成正1024的整数倍的k, 因为有些服务器下的mysql由于某些原因可能无法将M转为k。默认是4m;最大是1g, 该参数设置过大，可能会导致由于某个事务过大，导致主从延迟加剧！ set global max_allowed_packet = 2*1024*1024*10 (20m)
3.slave_max_allowed_packet参数 此变量设置slave 的SQL和I/O线程的最大数据包大小，不会因为复制更新超过了允许的最大数据包数，而导致基于行的复制的大型更新失败。设置此变量将立即对所有复制通道生效，包括正在运行的通道。 此全局变量的值始终是1024的正整数倍；如果将其设置为非正整数倍，则该值将向下舍入到1024的下一个最大倍数，以便存储或使用；将slave_max_allowed_packet设置为0将使用1024。（在所有这些情况下都会发出截断警告。）默认值和最大值为1073741824（1 GB）；最小值为1024
| Property | Value |
| Command-Line Format | --slave-max-allowed-packet=# |
| System Variable | slave_max_allowed_packet |
| Scope | Global |
| Dynamic | Yes |
| Type | Integer |
| Default Value | 1073741824 |
| Minimum Value | 1024 |
| Maximum Value | 1073741824 |
