转载

【案例】复制静止问题一则

一背景
早上7点多接到一个数据库服务器空间报警，磁盘空间不足。登陆数据库查看，MySQL slave 大量延迟，有68G 的relay log。查看slave status 发现Relay_Log_Pos ,Exec_Master_Log_Pos 位点始终不变，当时的状态展示如下:
二分析
根据slave 复制的原理可知 relay_log_pos 是指sql_thread 进程读取relay log文件的位点，exec_master_log_pos是sql_thread 执行relay log中相对于主库master binlog file的位点。那为什么sql_thread 一直显示静止状态呢？
我们如下几个方面思考
1 主机的IO层面，是否是服务器的io能力不足导致的呢？

从结果上看IO利用率极低，不可能导致sql_thread执行缓慢的或者静止的。

2 sql_thread 等待MySQL的MDL锁，或者备份的flus table with read lock 等常见的所等待
登陆数据库查看，备库无任何业务连接，也没有备份在进行。因此可以排除MySQL 锁层面的问题。
【案例】复制静止问题一则

使用 pt-pmp工具查看数据库进程此时的状态，能否获得一些线索
【案例】复制静止问题一则

从pt-pmp执行结果上来看,注意 execute_command, reader_loop 函数，说明数据库一直在执行sql语句，只是执行的比较缓慢，循环读取relay log中的event 。我们进一步将问题范围缩小。

3 分析有没有大事务，全表delete 等，sql_thread将主库的log event重新再备库上执行一遍，遇到全表delete /update且表结构设计不合理导致sql_thread 执行极其缓慢
使用工具分析当前relaylog 具体执行什么事务？
mysqlbinlog ../mysql/relay-bin.000421 --start-position=233450854 > relay.log
【案例】复制静止问题一则

果然是有大事务--大批量的delete操作，查询数据库该表有1200w左右的记录，无主键(在这里给无专职DBA的公司开发提个醒，所有的表务必要创建主键)且没有任何索引。至此问题的原因找到了，那么如何解决呢？
可以参考的解决方法有

1 提高数据库的写速度。
set global sync_binlog=0
set global innodb_log_flush_at_trx_commit=0;
2 在从库给表添加索引
set session sql_log_bin=0;
alter table xxx add key idx_curday(current_day)

实际过程中该问题于晚上22点左右将延迟的relaylog应用完毕，该服务器的磁盘为sas 机械盘，io能力较差。如果更换为SSD 存储介质应该会更快一些。各位读者朋友如果还有其他想法，可以留言相互交流提供更好的解决方法。
三小结
主从复制结构是大家常用的MySQL 高可用的方案，但是往往由于各种因素，表结构设计不合理，主库大量写，从库备份等原因导致slave延迟，影响整体的可用性。MySQL DBA 一定要仔细review 数据库表结构设计，杜绝这类延迟隐患发生。关于slave 延迟问题，推荐之前写的拙作《常见slave 延迟原因以及解决方法》。

正文到此结束