近期在处理一套mysql主从架构生产系统,是比较老的一套库,在主库进行批处理后发现从库延迟很大。
通过观察slave的状态可以看到,slave io和sql进程都运行正常,seconds bahind master(代表从库和主库之间的延迟差)一直在增加,还有Retrieved gtid set也一直在接受,而executed gtid一直处在某个事物上。
查看processlist进程,8个work线程只有一个thread在处理事务,其余的都处在空闲状态,说明并行已经退化为单线程模式了。
通过binlog日志的pos跟踪,发现这个时间点在执行大事务。具体分析到这个表,表接近100G近4亿条记录,且没有任何索引和主键。导致备库延迟的原有就是该表没有索引和主键,从库在做日志回放的时候产生大量的延迟。
mysql主从基于row行的复制,假设主库执行一个delete操作,mysql会把这个sql按照每条记录,拆分成近4亿条记录在备库上执行,这样做的目的只保持主从间的同步。表没有主键和索引,在从库每一条delete操作都要做全表扫描才能定位到哪一条sql,像这样的大表,延迟可想而知。
这也给我们开发的同学提了个醒:所有的mysql表使用InnoDB引擎、且必须要有主键和索引。