导致MySQL主从延迟的原因和现象

主从延迟监控

show slave status方式可以看到很多主从相关值

Seconds_Behind_Master值

每个事务的 binlog 里面都有一个时间字段,用于记录主库上写入的时间;备库取出当前正在执行的事务的时间字段的值,计算它与当前系统时间的差值,得到 seconds_behind_master。

主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。
你可能会问,如果主备库机器的系统时间设置不一致,会不会导致主备延迟的值不准?
其实不会的。因为,备库连接到主库的时候,会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致,备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

显示参数Seconds_Behind_Master不为0,可以初步判断主从有延迟。

执行位点比较

Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大,
说明bin-log在从库上没有及时同步,所以近期执行的bin-log和当前IO线程所读的bin-log相差很大。

Master_Log_File 和 Read_Master_Log_Pos,表示的是读到的主库的最新位点;
Relay_Master_Log_File 和 Exec_Master_Log_Pos,表示的是备库执行的最新位点。

Auto_Position=1 ,表示这对主备关系使用了 GTID 协议。那么也可根据Retrieved_Gtid_Set和Executed_Gtid_Set比较。
Retrieved_Gtid_Set,是备库收到的所有日志的 GTID 集合;
Executed_Gtid_Set,是备库所有已经执行完成的 GTID 集合。

relay日志堆积量判断

MySQL的从库数据目录下存在大量mysql-relay-log日志,该日志同步完成之后就会被系统自动删除,存在大量日志,说明主从同步延迟很厉害。

主从延迟的常见情况

1、备库的压力大。
备库上的查询耗费了大量的 CPU 资源,影响了同步速度,造成主备延迟。
2、大事务这种情况很好理解。因为主库上必须等事务执行完成才会写入 binlog,再传给备库。所以,如果一个主库上的语句执行 10 分钟,那这个事务很可能就会导致从库延迟 10 分钟。
3、另一种典型的大事务场景,就是大表 DDL

导致主从延迟常见情况的现象

大事务

比如大量导入数据,INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE等
比如UPDATE、DELETE了全表等
通过show slave statusG 命名可以观察到
Exec_Master_Log_Pos一直未变,Slave_SQL_Running_State为Reading event from the relay log
分析主库binlog,看主库当前执行的事务也可知晓。

大表DDL

1、DDL未开始,被阻塞,SHOW SLAVE STATUS检查到Slave_SQL_Running_State为waiting for table metadata lock,且Exec_Master_Log_Pos不变。
2、DDL正在执行,SQL Thread单线程应用导致延迟增加。
Slave_SQL_Running_State为altering table,
Exec_Master_Log_Pos不变

表缺乏主键或唯一索引

binlog_format=row的情况下,如果表缺乏主键或唯一索引,在UPDATE、DELETE的时候可能会造成从库延迟骤增。
此时Slave_SQL_Running_State为Reading event from the relay log。
并且SHOW OPEN TABLES WHERE in_use=1的表一直存在。
Exec_Master_Log_Pos不变。
mysqld进程的cpu几近100%(无读业务时),io压力不大

从库主机性能差

Exec_Master_Log_Pos变化,延迟逐步增加。

总结

通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况。(顺便也可排除在从库备份时这种原因)
若Exec_Master_Log_Pos不变,考虑大事务、DDL、无主键,检查主库对应的binlog及position即可。
若Exec_Master_Log_Pos变化,延迟逐步增加,考虑从库机器负载,如io、cpu等,并考虑主库写操作与从库自身压力是否过大。

发表评论

相关文章