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

数据库 MySQL
虽然mysqld进程的CPU消耗总是超过100%,不过也不算太高。再检查MySQL复制现场,确认了几个频繁更新的表都有主键,以及必要的索引。相应的DML操作也几乎都是基于主键或唯一索引条件执行的,排除无主键、无合理索引方面的因素。

导读

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

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

MySQL版本

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

看下延迟状况

  1. yejr@imysql.com:mysql3306.sock : (none) > show slave status\G 
  2.  
  3. Master_Log_File: mysql-bin.013225 
  4.  
  5. Read_Master_Log_Pos: 1059111551 
  6.  
  7. Relay_Master_Log_File: mysql-bin.013161 
  8.  
  9. Exec_Master_Log_Pos: 773131396 
  10.  
  11. 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***的报告是这样的

  1. Samples: 107K of event 'cycles', Event count (approx.): 29813195000 
  2.  
  3. Overhead Shared Object Symbol 
  4.  
  5. 56.19% mysqld [.] bitmap_get_next_set 
  6.  
  7. 16.18% mysqld [.] build_template_field 
  8.  
  9. 4.61% mysqld [.] ha_innopart::try_semi_consistent_read 
  10.  
  11. 4.44% mysqld [.] dict_index_copy_types 
  12.  
  13. 4.16% libc-2.12.so [.] __memset_sse2 
  14.  
  15. 2.92% mysqld [.] ha_innobase::build_template  

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

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

 

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

  1. yejr@imysql.com:mysql3306.sock : (none) > select count(*) from partitions where partition_name is not null
  2.  
  3. +----------+ 
  4.  
  5. count(*) | 
  6.  
  7. +----------+ 
  8.  
  9. | 32128 | 
  10.  
  11. +----------+ 
  12.  
  13. 1 row in set (11.92 sec)  

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

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

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

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

责任编辑:庞桂玉 来源: 老叶茶馆
相关推荐

2015-08-05 17:16:03

OpenStackUnitedstack

2022-08-02 15:04:36

JavaScript

2012-05-31 10:00:00

2022-10-11 14:39:18

泄露数据数据安全

2018-01-30 10:47:50

数据分析医疗保险数据科学

2012-04-26 14:34:22

HTML5

2020-08-25 13:22:07

数据可视化

2015-10-20 17:55:58

2017-01-20 13:37:40

大数据人工智能技术

2016-04-06 11:29:10

京东云基础云数据云

2014-08-07 10:19:43

Android系统应用领域

2017-05-19 10:55:19

DRaaS提供商灾难恢复

2018-02-25 12:23:36

AI技术视频网站

2018-10-12 13:53:22

2011-04-12 09:12:06

程序员

2016-09-25 15:00:48

2010-04-09 15:12:49

中文SSID无线网络设

2011-08-02 09:31:52

SQL语句字符串

2018-01-30 15:30:05

电商网购消费者

2020-12-15 15:35:43

安全管理应用
点赞
收藏

51CTO技术栈公众号