MySQL主从复制延迟原因分析

数据库

从库复制的status对应参数详解

Master_Log_File：                      SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：        在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File：                        SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos：                        在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File：      由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running：                 I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running：              SQL线程是否被启动
Seconds_Behind_Master：     从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

从库延迟的表现

● show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
● show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
● mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

从库延迟的原因

• Read_Master_Log_Pos能追上主库，但写入Relay_Master_Log_File从库的速度慢：原因可能是 主库上并发TPS高，多个写入线程 ，从库io线程能持续读取，但是sql thread从库上只有一个 ，此时可以开启多线程并发复制
  slave_parallel_workers > 0
• 无主键表引发延迟：
  MySQL 的 binlog 默认使用 row 模式的情况下，binlog 会记录所有的数据变更，这意味着一个 update 或者 delete 语句如果修改了非常多的数据，那么每一行数据的变化都会记录到 binlog 中，最终会产生非常多的 binlog 日志。从库在处理这些日志时，每一行数据的操作都会去尝试定位具体的数据，然后再判断是不是需要执行操作来完成数据变更。如果在某张大表上 update 或者 delete 一些数据，而这张表没有索引，那么定位数据的时候就会变成全表扫描，且 update 或者 delete 的每一行数据都会触发一次全表扫描，从库会产生非常大的延迟。
  refer to : https://cloud.tencent.com/developer/article/1688866
• Myisam 表锁 和 Innodb 行锁：
  当表结构均包含主键和正确索引的情况下，再考虑存储引擎对延迟的影响。myisam表当进行ddl/dml时候都会进行表锁（走索引情况下扫描行数少），无法并发对一张表进行多行记录的变更。而innodb则采用行锁，多线程复制更有意义。在tps很大的情况下使用innodb更为有效。
• innodb表延迟大：修改innodb_flush_commit的值，或者更换更高配置的从库机器。

refer to 'https://www.huaweicloud.com/articles/508e95b7494d24b6f70a1621b63c5567.html'