首先,感谢腾讯面试官提出的非常好的问题,mysql主从复制肯定会出现延迟问题,导致读写分离后,从库读取数据很慢。
下来仔细学习后,发现这是很重要的知识点。
问题出现的原因
1.大事务的执行,如果主库的一个事务执行了10分钟,而binlog的写入必须要等待事务完成之后,才会传到备库,那么此时在开始执行relaylog从放就开始延迟了很久。
2.主从复制io线程读取binlog是顺序读,速度非常快,但是到了sql线程回放relaylog的操作时变成了随机读取,如过还是单线程就会变得很慢。
3.当主库的tps并发非常高的时候,产生的DDL超过了一个线程能承受的范围,也会带来延迟
4.像网络不好,cpu占用率过高等也会出现
如何解决
若遵循基本的口诀,大事务拆小事务,则MySQL从服务器的I/O线程不会有特别大的延迟。然,SQL线程的延迟依然无法解决。
MySQL 5.7推出了多线程从机复制机制(下简称MTS,Multi-Thread Slave,又称并行复制 parallel replication)。
目前MTS机制基于组提交实现,简单来说在主上是怎样并行执行的,从服务器上就怎么回放。这里存在一个可能,即若主服务器的并行度不够,则从机的并行机制效果就会大打折扣。
MySQL 8.0最新的基于writeset的MTS才是最终的解决之道。即两个事务,只要更新的记录没有重叠(overlap),则在从机上就可并行执行,无需在一个组,即使主服务器单线程执行,从服务器依然可以并行回放。相信这是最完美的解决之道,MTS的最终形态
- MySQL必须遵循大事务拆小事务的基本原则;
- 目前最优的DDL操作方式是通过从服务器roll upgrade方式;
- MySQL 5.7必须打开MTS功能,并升级到至少5.7.19版本;
- MySQL 8.0的writeset MTS是解决主从延迟的最终形态;
- 可期待MySQL 8.0的快速加列功能;
实验
目前MTS机制基于组提交实现,简单来说在主上是怎样并行执行的,从服务器上就怎么回放。这里存在一个可能,即若主服务器的并行度不够,则从机的并行机制效果就会大打折扣。
简单的一种方法,可以使用performance_schema库来观察,比如下面这条SQL可以统计每个Worker Thread执行的事务数量,在此基础上再做一个聚合分析就可得出每个MTS的并行度:
SELECT thread_id,count_start
FROM performance_schema.events_transactions_summary_by_thread_by_event_name
WHERE thread_id IN (
SELECT thread_id FROM performance_schema.replication_applier_status_by_worker
)
通过上述语句,观察到我们的监控系统MTS的并行度非常一般:
超过58%的事务是单线程执行,反应在系统上的表现就是主从服务器的延迟超过7.5+小时。
这种场景下,可以通过调整主服务器上的参数binlog_group_commit_sync_delay、binlog_group_commit_sync_no_delay_count。
前者表示延迟多少时间提交事务,后者表示组提交事务凑齐多少个事务再一起提交。总体来说,都是为了增加主服务器组提交的事务比例,从而增大从机MTS的并行度。