【案例】复制静止问题一则-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: 一背景   早上7点多接到一个数据库服务器空间报警,磁盘空间不足。登陆数据库查看,MySQL slave 大量延迟,有68G 的relay log。查看slave status 发现Relay_Log_Pos ,Exec_Master_Log_Pos  位点始终不变...
一背景
  早上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. 1 提高数据库的写速度。
  2.   set global sync_binlog=0
  3.   set global innodb_log_flush_at_trx_commit=0;

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



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章