43.主从复制延时的原因

1.主库DML请求频繁(TPS较大)：主库写请求较多，有大量的insert、delete、update并发操作，短时间产生了大量的binlog日志

　　原因：主库并发写入数据，而从库的SQL Thread为单线程应用日志，很容易造成relay log 堆积，产生延迟。

　　解决：1.做sharding,通过scale out 打散写请求，或考虑升级5.7+，开启基于逻辑时钟的并且复制(通常所说的多线程复制)　　　

#slave_parallel_type='logical_clock';
#slave_parallel_workers=4

2.主库执行大事务

　　比如大量导入数据，比如update/delete了全表等，此时 Exec_Master_Log_Pos一直未变，Slave_SQL_Running为Reading event from the relay log或者show processlist中有Waiting for dependent transaction to commit

　　原因：假如主库花费200s更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的events无法更新

　　解决：拆分大事务，及时提交。

3.主库对大表执行DDL语句

　　现象和主库执行大事务相近，检查Exec_Master_Log_Pos一直未动，也有可能是在执行DDL，分析主库binlog，看主库当前执行的事务也可知晓

　　原因：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不变

　　解决：通过processlist或information_schema.innodb_trx来找到阻塞DDL语句的查询，干掉该查询，让DDL正常在从库执行。

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

　　　　　① 业务低峰期执行
　　　　　② set sql_log_bin=0后，分别在主从库上手动执行DDL（此操作对于某些DDL操作会造成数据不一致，请务必严格测试）　　

4、主库与从库配置不一致：

　　　原因：硬件上：主库用SSD，从库上使用普通的SAS、CPU主频不一致等

　　　　　　配置上：如RAID卡写策略不一致，OS内核参数设置不一致，Mysql落盘策略不一致等。

　　　解决：

　　　　　　尽量统一DB机器的配置，甚至对于某些OLAP业务，从库实例的硬件配置高于主库等

5.表缺乏主见或唯一索引

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

　　原因分析：做个极端情况下的假设，主库更新一张500w表中的20w行数据，该update语句需要全表扫描，而row格式下，记录到binlog的为20w次update操作，此时SQL Thread重放将特别慢，每一次update可能需要进行一次全表扫描。

　　解决思路：检查表结构，保证每个表都有显式自增主键，并建立合适索引调整参数为 slave_rows_search_algorithms=INDEX_SCAN,HASH_SCAN。

6.从库自身压力过大

　　原因分析：从库执行大量select请求，或业务大部分select请求被路由到从库实例上，甚至大量OLAP业务，或者从库正在备份等，此时可能造成cpu负载过高，io利用率过高等，导致SQL Thread应用过慢。

　　解决思路：建立更多从库，打散读请求，降低现有从库实例的压力。

7、MyISAM存储引擎：

　　此时从库Slave_SQL_Running_State为Waiting for table level lock

　　原因：MyISAM只支持表级锁，并且读写不可并发操作。主库在设置@@concurrent_insert对应值的情况下，能并发在select时执行insert，但从库SQL Thread重放时并不可并发，有兴趣可以再去看看myisam这块的实现。

　　解决思路：建议改成Innodb存储引擎

总结：通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况。（顺便也可排除在从库备份时这种原因）

　　若Exec_Master_Log_Pos不变，考虑大事务、DDL、无主键，检查主库对应的binlog及position即可。

　　若Exec_Master_Log_Pos变化，延迟逐步增加，考虑从库机器负载，如io、cpu等，并考虑主库写操作与从库自身压力是否过大

第二种概括性总结：

1.io_thread无法赶上主库binlog
问题分析:1 主从网络通信受限,比如丢包率严重
2 主库短时间内产生的binlog太多
分析解决:
1 保障网络通信
2 优化主库binlog产生数量和大小
2 备库IO硬件条件较主库差
问题分析:1 由于从库硬件条件不如主库,所以产生延时
分析解决:1 提升硬件水平
3 主库执行出现大事务，导致出现延时的突刺
问题分析:1 由于出现大事务,导致从库迟迟无法提交 Waiting for dependent transaction to commit 从库出现
分析解决: 1 分析binlog 找到大事务让研发进行优化
4 备机当前会话存在元数据锁等待
问题分析:1 通常是由于在从库提供查询功能导致的锁等待
分析解决:1 kill慢查询即可
5 无主键表操作
问题分析:1 表上没有主键或者唯一键状态为:system lock 或者 reading event from the relay log
分析解决:1 表加主键

2 调整参数为 slave_rows_search_algorithms=INDEX_SCAN,HASH_SCAN

6.分析大事务脚本

参考：https://www.cnblogs.com/danhuangpai/p/16159599.html

延迟优化建议：

（1）建议主库和从库的硬件配置要和主库一样，强烈建议SSD硬盘，并且修改配置参数innodb_flush_method为O_DIRECT，提升写入性能。

（2）适当增大innodb从库参数innodb_buffer_pool_size的值，减少I/O压力。

（3）对于主库来说数据安全性较高，比如sync_binlog=1、innodb_flush_log_at_trx_commit=1之类的设置，但是slave库不需要这么高的数据安全，完全可以将sysnc_binlog设置为0或者500，且innodb_flushlog_at_trx_commit也可以设置为2来提高sql的执行效率，以减少磁盘i/O压力。

（4）所有的表都要有主键

（5）拆分大事务

（6）修改master_info_repository、relay_log_info_repository为table，减少直接i/0

（7）升级到5.7版本，并行复制的特性大大降低mysql主从延迟。