MySQL复制从库延迟优化思路

老叶茶馆

发布于 2024-05-09 10:59:57

1170

发布于 2024-05-09 10:59:57

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。 1、什么是MySQL复制延迟？本质是MySQL从库的relay log回放跟不上主库生成速度，产生延迟

2、主从延迟常见的原因有哪些？ 1、大事务，从库回放时间较长，导致主从延迟 2、主库写入过于频繁，从库回放跟不上 3、参数配置不合理 4、主从硬件差异 5、网络延迟 6、表没有主键或者索引大量频繁的更新 7、一些读写分离的架构，从库的压力比较大 3、解决主从延迟有哪些方法 1、对于大事务，拆分成小事务 2、开启并行复制 3、升级从库硬件 4、尽量都有主键 4、什么是并行复制，参数有哪些？ 先回顾MySQL并行复制的路程 a. MySQL5.6 是基于数据库级别的并行复制 slave-parallel-type=DATABASE(不同库的事务，没有锁冲突)

b. MySQL5.7 基于group commit的并行复制 slave-parallel-type=LOGICAL_CLOCK : Commit-Parent-Based模式(同一组的事务[last-commit相同]没有锁冲突. 同一组，肯定没有冲突，否则没办法成为同一组) 上面是从库的配置，并行复制依赖于主库的组提交（注意区分组复制） greatsql> show variables like '%group%delay%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | +-----------------------------------------+-------+ 2 rows in set (0.01 sec)

binlog_group_commit_sync_delay：等待多少时间后才进行组提交（单位：微秒）
binlog_group_commit_sync_no_delay_count：等待多少个事务后才进行组提交

以上参数都依赖于主库业务繁忙的情况下，如果业务不频繁，就会很尴尬

binlog_group_commit_sync_no_delay_count：这个参数设置成2个

比如只有一个线程执行一个事务，第二个事务在24h之后执行，那么这个事务需要等待24h才能提交，十分坑

binlog_group_commit_sync_delay

假如设置成200ms，只有一个线程执行一个事务，本来10ms可以提交，还必须等待200ms才可以线上一般是两个都设置，举个例子，就像是小船运人过河假设我们的参数这么设置： binlog_group_commit_sync_delay=200000 binlog_group_commit_sync_no_delay_count=2 要么满足200ms直接走，要么满足2个人直接走，这么人性化了很多，但是在业务不繁忙的情况下依然尴尬

c. MySQL8.0 基于writeset的并行复制 基于主键的冲突检测（binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION, 修改的row的主键或非空唯一键没有冲突，即可并行）。事务检测算法：transaction_write_set_extraction = XXHASH64 MySQL会有一个变量来存储已经提交的事务HASH值，所有已经提交的事务所修改的主键（或唯一键）的值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系这里说的变量，可以通过这个设置大小：binlog_transaction_dependency_history_size 这样的粒度，就到了 row 级别了，就是此时并行的粒度更加精细，并行的速度会更快，某些情况下，说slave的并行度超越master也不为过(master是单线程的写，slave也可以并行回放) 简单来说就是基于行去并行回放，rc级别下不同的行不会有锁冲突组提交的表现：看主库binlog的last_committed值是否一致，一致就可以并行回放，不一致只能串行

5、实战分析 5.1 查看线上主从延迟 Seconds_Behind_Master: 48828 可见延迟很高，接近14个小时，此时主库也在不断的写数据，大概是6分钟一个binlog，一个为500M 5.2 查看当前的复制配置 查看从库配置： greatsql> show variables like '%slave%para%'; +------------------------+---------------+ | Variable_name | Value | +------------------------+---------------+ | slave_parallel_type | LOGICAL_CLOCK | | slave_parallel_workers | 128 | +------------------------+---------------+ 2 rows in set (0.02 sec) 延迟现象：从库一直在追，说明不是大事务，但是Seconds_Behind_Master延迟一直在增长 Retrieved_Gtid_Set: 00000000-0000-0024-0046-41a8003b4b99:242966351-253068975, 00000000-0000-0040-0095-5fff003b4b99:91056629-110569633, 00000000-0000-005c-0ced-7bae003b4b99:150292055-160253193, 31f4399f-ade5-11ed-a544-00163ebdeb51:1-12, Executed_Gtid_Set: 00000000-0000-0024-0046-41a8003b4b99:1-252250235, 00000000-0000-0040-0095-5fff003b4b99:1-109120315, 00000000-0000-005c-0ced-7bae003b4b99:1-159504296, 31f4399f-ade5-11ed-a544-00163ebdeb51:1-12, Auto_Position: 1 此时怀疑并没有并行复制，主库可能没设置组提交（只是一个猜想） 5.3 进一步验证，查看主库的binlog 查看主库的参数配置：还是为组提交的规则 greatsql> show variables like '%binlog_transac%'; +--------------------------------------------+----------+ | Variable_name | Value | +--------------------------------------------+----------+ | binlog_transaction_compression | OFF | | binlog_transaction_compression_level_zstd | 3 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | WRITESET | +--------------------------------------------+----------+ 4 rows in set (0.02 sec) 再看其组提交的配置：表示没有开组提交 greatsql> show variables like '%group%delay%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | +-----------------------------------------+-------+ 2 rows in set (0.01 sec) 进一步验证，看其binlog，发现果然last_committed都不一样，表示不能并行

5.4 主库设置参数，再次解析其binlog 将binlog_transaction_dependency greatsql> show variables +------------------------------- | Variable_name +------------------------------- | binlog_direct_non_transactional_updates | binlog_transaction_compression | binlog_transaction_compression_level_zstd | binlog_transaction_dependency_history_size | binlog_transaction_dependency_tracking | kill_idle_transaction | performance_schema_events_tran | performance_schema_events_tran | replica_transaction_retries | session_track_transaction_info | slave_transaction_retries | transaction_alloc_block_size | transaction_allow_batching | transaction_isolation | transaction_prealloc_size | transaction_read_only | transaction_write_set_extraction +------------------------------- 17 rows in set 再次查看其binlog，看到有很多都可以并行回放 _tracking改为WRITESET模式 like '%transaction%'; ---------------------------+-----------------+ | Value | ---------------------------+-----------------+ | OFF | | OFF | | 3 | | 25000 | | WRITESET | | 300 | sactions_history_long_size | 10000 | sactions_history_size | 10 | | 10 | | OFF | | 10 | | 8192 | | OFF | | REPEATABLE-READ | | 4096 | | OFF | | XXHASH64 | ---------------------------+-----------------+ (0.00 sec)