高可用数据库主从复制延时的解决

从现象上，如果从库执行SHOW SLAVE STATUS的输出中，检查Exec_Master_Log_Pos一直未动，在排除主库执行大事务的情况下，那么就有可能是在执行大表的 DDL。这一点结合分析主库binlog，看主库当前执行的事务就可以进行确认。

DDL语句的执行情况，可以进一步细分现象来更好地判断：

1.DDL未开始，被阻塞，这时SHOW SLAVE STATUS的结果能检查到Slave_SQL_Running_State为waiting for table metadata lock，且Exec_Master_Log_Pos不变;

2.DDL正在执行，SQL Thread单线程应用导致延时增加。这种情况下观察SHOW SLAVE STATU的结果能发现Slave_SQL_Running_State为altering table，而Exec_Master_Log_Pos不变。

如果有上述的现象，那么很有可能主库对大表执行DDL语句，同步到从库并在从库回放时，就产生了主从复制延时。

原因分析

DDL导致的主从复制延时的原因和大事务类似，也是因为从库执行DDL的binlog较慢而产生了主从复制延时。

解决思路

遇到这种情况，我们主要通过SHOW PROCESSLIST或对information_schema.innodb_trx做查询，来找到阻塞DDL语句，并KILL掉相关查询，让DDL正常在从库执行。

DDL本身造成的延时难以避免，建议考虑：

• 避免业务高峰，尽量安排在业务低峰期执行 ;
• set sql_log_bin=0后，分别在主从库上手动执行DDL(此操作对于某些DDL操作会造成数据不一致，请务必严格测试)，这一条如果用户使用云数据库UDB，可以联系UCloud UDB运维团队进行协助操作。

◆ 案例四：主库与从库配置不一致

如果主库和从库使用了不同的计算资源和存储资源，或者使用了不同的内核调教参数，可能会造成主从不一致。

现象描述

我们会详细比对主库和从库的性能监控数据，如果发现监控数据差异巨大，结合查看主从的各个配置情况，即可作出明确判断。

原因分析

各种硬件或者资源的配置差异都有可能导致主从的性能差异，从而导致主从复制延时发生：

• 硬件上：比如，主库实例服务器使用SSD磁盘，而从库实例服务器使用普通SAS盘，那么主库产生的写入操作在从库上不能马上消化掉，就产生了主从复制延时;
• 配置上：比如，RAID卡写策略不一致、OS内核参数设置不一致、MySQL落盘策略不一致等，都是可能的原因。

解决思路

考虑尽量统一DB机器的配置(包括硬件及选项参数)。甚至对于某些OLAP业务，从库实例硬件配置需要略高于主库。

◆ 案例五：表缺乏主键或合适索引

如果数据库的表缺少主键或者合适索引，在主从复制的binlog_format设置为'row'的情况下，可能会产生主从复制延时。

现象描述

我们进行数据库检查时，会发现：

• 观察SHOW SLAVE STATUS的输出，发现Slave_SQL_Running_State为Reading event from the relay log;
• SHOW OPEN TABLES WHERE in_use=1的表一直存在;
• 观察SHOW SLAVE STATUS的Exec_Master_Log_Pos字段不变;
• mysqld进程的CPU接近100%(无读业务时)，IO压力不大。

这些现象出现的情况下，可以认为很可能有表缺乏主键或唯一索引。

原因分析

在主从复制的binlog_format设置为'row'的情况下，比如有这样的一个场景，主库更新一张500万表中的20万行数据。binlog在row格式下，记录到binlog的为20万次update操作，也就是每次操作更新1条记录。如果这条语句恰好有不好的执行计划，如发生全表扫描，那么每一条update语句需要全表扫描。此时SQL Thread重放将特别慢，造成严重的主从复制延时。

解决思路

这种情况下，我们会去检查表结构，保证每个表都有显式自增主键，并协助用户建立合适索引。

◆ 案例六：从库自身压力过大

有时候，从库性能压力很大的情况下，跟不上主库的更新速度，就产生了主从复制延时。

现象描述

观察数据库实例时，会发现CPU负载过高，IO利用率过高等现象，这些导致SQL Thread应用过慢。这样就可以判断是因为从库自身压力过大引起主从复制延时。

原因分析

部分UCloud用户对于数据库的主从会使用读写分离模式，读请求大部分在从库上执行。在业务有大量读请求的场景下，从库会产生比主库大得多的性能压力。有的用户甚至会在从库运行十分耗费计算资源的OLAP业务，这也对从库造成了更高的性能挑战，这些都会造成主从复制的延时。

解决思路

这种情况下，我们会建议用户建立更多从库，打散读请求，降低现有从库实例的压力。对于OLAP业务来说，可以专门建立一个从库来做OLAP业务，并对这个从库，允许适当的主从复制延时。

总结在使用MySQL的主从复制模式进行数据复制时，主从复制延时是一个需要考量的关键因素。它会影响数据的一致性，进而影响数据库高可用的容灾切换。

（编辑：西安站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!