前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >pt-osc工具引发的主从延迟

pt-osc工具引发的主从延迟

作者头像
AsiaYe
发布2020-04-26 16:17:25
1.5K0
发布2020-04-26 16:17:25
举报
文章被收录于专栏:DBA随笔

//

pt-osc工具引发的主从延迟

//

今天早上上班来,接了一个需求,需要对线上一个大表做个归档的操作,其实就是rename一下,将表table从A库转移到B库里面,然后在A库中创建一个同名的表table,同时保持自增id连续。

这个需求的处理过程比较简单,通过rename的方法处理完成之后,业务方临时考虑给B库中的table表添加一个二级索引,添加索引的时候引发了主从复制延迟的问题。关于pt-osc工具,可以看我之前写过的文章,里面讲的也比较清楚了。

《MySQL大表删除工具pt-osc

要添加索引,首先查看这个表的数据量:

select count(*) from table;

+-----------+

| count(*) |

+-----------+

| 384434314 |

+-----------+

1 row in set (3 min 55.49 sec)

发现数据量大概3.8亿左右

这是个非常大的数据量了。这张表本身里面存储的是日志数据,3.8亿的量是几乎一年的数据,从设计上来讲,这是不合理的,存储这类数据量比较大的日志,可能ES或者大数据更适合,如果非要用MySQL存,也不是不可以,尽量在设计的时候,将表设计成周期表的形式,例如日表、月表、周表、季度表等等。

实际执行添加索引的时候,我使用了pt工具,当然,这里考虑到MySQL原生支持Online DDL操作,也可以使用原生Online DDL操作(这个具体的延迟没测试,4亿数据的表,Online DDL添加索引具体多长时间,不知道,有知道的朋友可以告知下),使用pt工具操作是为了保证一定对线上的表没有任何影响。(事实证明还是百密一疏~)

pt工具的执行如下:

代码语言:javascript
复制
[root@ ]$pt-online-schema-change --user=dba_admin --password=xxxx -hrm-xxxxxxx.mysql.rds.aliyuncs.com -P3306 --alter " ADD index idx_uid_defid(uid,defid) " D=db_name,t=table  --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute
No slaves found.  See --recursion-method if host g23h01247.cloud.nu17 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `db_name`.`table`; ignoring --alter-foreign-keys-method.
Altering `db_name`.`table`...
Creating new table...
CREATE TABLE `db_name`.`_table` (
  `uid` bigint(20) DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  xxxxxx
  `server` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=384440258 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
Created new table `db_name`.`_table_new` OK.
Altering new table...
xxx
INSERT LOW_PRIORITY IGNORE INTO `db_name`.`_table_new` (`uid`, `id`, `itemid`, `defid`, `amount`, `process`, `jsondata`, `server`, `time`) SELECT `uid`, `id`, `itemid`, `defid`, `amount`, `process`, `jsondata`, `server`, `time` FROM `db_name`.`table` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 15169 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db_name`.`table` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Copying `db_name`.`table`:   0% 01:43:57 remain
Copying `db_name`.`table`:   0% 01:44:29 remain
Copying `db_name`.`table`:   1% 01:46:58 remain
Copying `db_name`.`table`:   1% 01:46:46 remain
Copying `db_name`.`table`:   2% 01:46:34 remain
Copying `db_name`.`table`:   2% 01:46:23 remain
Copying `db_name`.`table`:   3% 01:46:01 remain
Copying `db_name`.`table`:   3% 01:45:49 remain
Copying `db_name`.`table`:   4% 01:45:28 remain
Copying `db_name`.`table`:   4% 01:45:17 remain
Copying `db_name`.`table`:   4% 01:45:08 remain
Copying `db_name`.`table`:   5% 01:44:52 remain
Copying `db_name`.`table`:   5% 01:44:47 remain
Copying `db_name`.`table`:   6% 01:44:42 remain
Copying `db_name`.`table`:   6% 01:44:38 remain

可以看到,执行的预估时间是1小时45分钟左右,实际的情况下,执行到40%的时候,就发现主从复制延迟了,而且SBM的值一直在增长。(其实本质是pt-osc工具的执行过程中会使用insert的方法拷贝表里面的数据,产生的大量binlog在从节点上无法被立即重放,重放速度太慢导致的主从延迟)

当时发现这个情况后,做了几件事情:

1、将pt-osc工具执行的中间表给drop掉,这样pt-osc工具的执行过程就停止了。

2、调整了从库上的innodb_flush_log_at_trx_commit值为0,sync_binlog值设置为0,buffer_pool值增加了一倍

在这种情况下,主从复制始终没有得到缓解,SBM的值也一直在增加。最后是等到这个pt-osc工具的insert操作做完之后,SBM值才变成0,主从数据才实现同步。

在主从复制的场景中,复制延迟是一个令人头疼的问题,一般来讲,复制延迟没有特别好的解决办法,《MySQL运维内参》一书中有一部分对主从复制延迟情况下的参数调优解决方案的描述,大概在MySQL5.7并行复制章节,改天找个机会补充下,大家可以参考下。

今天这篇文章告诉大家的一个知识点就是:

在使用pt-osc进行主从复制时候,可能会产生比较大的复制延迟,如果你的应用能够接受,那ok,如果不能接受,你可能就需要考虑其他的方法,比如调节--chunk-size参数(优化空间有限),有或者Online DDL等(我没测试,有兴趣可以测试下)...

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-04-23,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档