意想不到的 MySQL 复制延迟原因

标签: dev | 发表时间:2017-06-01 08:00 | 作者:
出处:http://itindex.net/admin/pagedetail

导读

线上有个MySQL实例,存在严重的复制延迟问题,原因出乎意料。

线上有个MySQL 5.7版本的实例,从服务器延迟了3万多秒,而且延迟看起来好像还在加剧。

MySQL版本

   Server version:     5.7.18-log MySQL Community Server (GPL)

看下延迟状况

   yejr@imysql.com:mysql3306.sock : (none) > show slave status\G
              Master_Log_File: mysql-bin.013225
          Read_Master_Log_Pos: 1059111551
        Relay_Master_Log_File: mysql-bin.013161
          Exec_Master_Log_Pos: 773131396
                  Master_UUID: e7c35a95-ffb1-11e6-9620-90e2babb5b90

我们看到, binlog文件落后了64个,相当的夸张。

MySQL 5.7不是已经实现并行复制了吗,怎么还会延迟这么厉害?

先检查系统负载。

看到 mysqld进程其实负载还好,不算太高,也不存在严重的SWAP等问题

再看I/O子系统负载,没看到这方面存在瓶颈( await\svctm\%util都不高)。


再看mysqld进程的CPU消耗。

虽然mysqld进程的CPU消耗总是超过100%,不过也不算太高。

再检查MySQL复制现场,确认了 几个频繁更新的表都有主键,以及必要的索引。相应的DML操作也几乎都是基于主键或唯一索引条件执行的, 排除无主键、无合理索引方面的因素

最后只能祭出 perf top神器了。

   perf top -p `pidof mysqld`

看到perf top最后的报告是这样的

   Samples: 107K of event 'cycles', Event count (approx.): 29813195000                                                                                                                              
Overhead  Shared Object        Symbol                                                                                                                                                            
  56.19%  mysqld               [.]    bitmap_get_next_set                                                                                                                                          
  16.18%  mysqld               [.]    build_template_field                                                                                                                                         
   4.61%  mysqld               [.] ha_innopart::try_semi_consistent_read                                                                                                                         
   4.44%  mysqld               [.] dict_index_copy_types                                                                                                                                         
   4.16%  libc-2.12.so         [.] __memset_sse2                                                                                                                                                 
   2.92%  mysqld               [.] ha_innobase::build_template

我们看到, bitmap_get_next_set这个函数调用占到了 56.19%,非常高,其次是 build_template_field函数,占了 16.18%。

经过检查MySQL源码并请教MySQL内核开发专家,最后确认这两个函数跟启用表分区有关系。

查询下当前实例有多少个表分区:

   yejr@imysql.com:mysql3306.sock : (none) > select count(*) from partitions where partition_name is not null;
+----------+
| count(*) |
+----------+
|    32128 |
+----------+
1 row in set (11.92 sec)

额滴神啊,竟然有3万多个表分区,难怪上面那两个函数调用那么高。

这个业务数据库几个大表采用每天一个分区方案,而且把直到当年年底所有分区也都给提前创建好了,所以才会有这么多。

不过,虽然有这么多表分区,在master服务器上却不存在这个瓶颈,看起来是在主从复制以及大量表分区的综合因素下才有这个瓶颈,最终导致主从复制延迟越来越严重。

知道问题所在,解决起来就简单了。 把到下个月底前用不到的表分区全部删除,之后约只剩下1.6万个分区。重启slave线程,问题解决,主从复制延迟很快就消失了


延伸阅读:

[MySQL FAQ]系列 — MySQL复制中slave延迟监控

[MySQL优化案例]系列 — slave延迟很大优化方法

FAQ系列 | SLAVE为什么停滞一直不动了

FAQ系列 | 复制线程长时间Opening tables

[MySQL FAQ]系列 — 5.6版本GTID复制异常处理一例

FAQ系列 | 列类型被自动修改导致复制失败

FAQ系列 | table id问题导致主从复制失败

浅析 MySQL Replication


不再加原创

喜欢就转发

打赏可勾搭


靠谱好茶&在线培训,都在〖老叶茶馆〗http://yejinrong.com


相关 [mysql 复制 延迟] 推荐:

[MySQL FAQ]系列 — MySQL复制中slave延迟监控

- - MySQL中文网
在MySQL复制环境中,我们通常只根据 Seconds_Behind_Master 的值来判断SLAVE的延迟. 这么做大部分情况下尚可接受,但并不够准确,而应该考虑更多因素. 首先,我们先看下SLAVE的状态:. 可以看到 Seconds_Behind_Master 的值是 3296,也就是SLAVE至少延迟了 3296 秒.

MySQL主从复制延迟的监测及缓解

- - 数据库 - ITeye博客
MySQL的主从复制有多种原因可以导致延迟,这个是公认的了,下面我们谈谈怎样监测复制的延迟,以及怎样尽量的解决延迟的问题. 在SLAVE上执行SHOW SLAVE STATUS,监控Seconds_behind_master列值,备库Seconds_Behind_Master值是通过将服务器当前的时间戳(这里其实有个主从服务器时间差的问题,但是实际上主从在连接上后会做一次主从时间差的对比并记录该偏移量)与二进制日志中的事件时间戳相对比得到的,如果在I/O线程没有延时的情况下,这个还是准的.

意想不到的 MySQL 复制延迟原因

- - IT瘾-dev
线上有个MySQL实例,存在严重的复制延迟问题,原因出乎意料. 线上有个MySQL 5.7版本的实例,从服务器延迟了3万多秒,而且延迟看起来好像还在加剧. 我们看到, binlog文件落后了64个,相当的夸张. MySQL 5.7不是已经实现并行复制了吗,怎么还会延迟这么厉害. 看到 mysqld进程其实负载还好,不算太高,也不存在严重的SWAP等问题.

记一次 MySQL 主从复制延迟的踩坑

- - 文章 – 伯乐在线
最近开发中遇到的一个 MySQL 主从延迟的坑,记录并总结,避免再次犯同样的错误. 一个活动信息需要审批,审批之后才能生效. 因为之后活动要编辑,编辑后也可能触发审批,审批中展示的是编辑前的活动内容,考虑到字段比较多,也要保存审批活动的内容,因此设计采用了一张临时表,审批中的活动写进审批表(activity_tmp),审批通过之后才把真正的活动内容写进活动表(activity).

mysql主从复制

- - SQL - 编程语言 - ITeye博客
从库的配置,mysql5.5不支持配置文件的配置了,问了数据库的人,用命令行指定. 修改从库的配置 #default-storage-engine = InnoDB #修改 default-storage-engine = blackhole server-id = 11215004 #新增 replicate-do-db = test log-bin = mysql-bin #新增 binlog_format = row.

relay fetch 解决mysql replication 主从延迟

- - CSDN博客推荐文章
      mysql replication 中主从延迟是一个比较常见的问题,请看前期一篇博文: 怎样解决MySQL数据库主从复制延迟的问题. 根据目前有些公司使用的方案,最近测试了两个,其中之一是阿里的relay fetch ,业绩说法数据预热,当然也有其他开源类似开源工具,目前诸如 mk-slave-prefetch及 replication-prefetch等,感兴趣可以去看看.

MySQL主从复制配置

- - 天空极速
在实际企业应用环境当中,单台MySQL数据库是不足以满足日后业务需求的. 譬如服务器发生故障,没有备份服务器来提供服务的话,业务就得停止. 使用MySQL主从复制的好处有:. 1、采用主从服务器这种架构,稳定性得以提升. 如果主服务器发生故障,我们可以使用从服务器来提供服务;. 2、在主从服务器上分开处理用户的请求,可以提升数据处理效率;.

MySQL 主从延迟监控脚本(pt-heartbeat)

- - CSDN博客数据库推荐文章
    对于MySQL数据库主从复制延迟的监控,我们可以借助percona的有力武器pt-heartbeat来实现. pt-heartbeat通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟. 本文主要是通过脚本来定期检查从库与主库复制的延迟度并发送邮件,供大家参考.

[MySQL优化案例]系列 — slave延迟很大优化方法

- - MySQL中文网
备注:插图来自网络搜索,如果觉得不当还请及时告知 :). 一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发. 简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master.