RDS for MySQL 大表操作

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: RDS for MySQL 大表操作

RDS for MySQL 大表操作

1. 名词解释

2. 实现原理

3. 推荐方法



RDS for MySQL 的大表操作(比如 空间回收、添加字段、调整索引)一直是比较困扰 RDS for MySQL 用户的问题。

近期相关咨询和 反馈的问题(比如 主实例上执行 optimize table 导致只读实例高延迟)比较多,下面汇总说明下操作的几种方法及其各自的特点。

1. 名词解释

 

2. 实现原理

2.1 Online DDL

Online DDL 主要思路是将 DDL 执行过程中的 DML 增量数据保存在独立的日志文件中,在完成全量数据后进行数据回放 来支持并发 DML (仍旧存在某些操作不支持并发 DML)。



由此带来了几个问题:

  • 大表 DDL 操作执行时间长,如果 DML 增量数据过多,会导致存放增量数据的日志空间不够,引发 DDL 操作失败。

  • DML 增量数据存储在独立日志中,如果表上有唯一键约束,DDL 执行过程中会 忽略 唯一性约束,会有一定概率在重放增量数据时由于 duplicated key 错误而导致 DDL 操作失败。

  • 不是全部操作都支持并发 DML。

  • DDL 一旦开始执行无法暂停,并且无法控制完成时间点。
  • 大表 DDL 操作执行时间长,完成后复制到只读实例上执行,会导致只读实例复制延迟。
  • 在 DDL 开始和结束部分都需要获取表 Metadata Lock 的 排他锁,容易引发表 MDL 锁等待进而导致表不可访问。

 

2.2 Percona Toolkit - Online Schema Change

PT-OSC 的主要思路是 创建目标结构的新表,将原表中数据拷贝到新表中,同时通过原表上的 Insert、Update 和 Delete 触发器(Trigger)同步拷贝过程中的增量数据;数据同步后 rename 新表为原表。



由于是基于 Trigger 的实现,Trigger 绑定在并发 DML 操作的事务中执行,由此带来一些问题。

  • 速度慢,Trigger 是基于 (per-row)变化来解释执行(每行变化都要解释执行一次,无法预编译,无法批量执行)。

  • 支持原表上存在 Trigger

  • 原表必须定义主键 或 唯一键。

  • Trigger 对被绑定的事务引入 新表 上的锁竞争。
  • Rename 表 和 删除表上创建的 Trigger 都需要获取表 Metadata Lock 的 排他锁,容易引发表 MDL 锁等待进而导致表不可访问。

2.3 GH - Online Schema Transfer 

GH-OST 结合 RDS for MySQL 使用的主要思路是创建目标结构的新表,然后通过将自身模拟为一个 Slave 实例,获取表上的增量数据并将之再应用到新表上;规避了使用 Trigger 带来的问题。

Step 1、RDS for MySQL 主实例上持续存在 对 原表 old_table 的 DML 操作。

Step2、GH-OST 客户端连接 RDS for MySQL 主实例。

Step 3、根据 DDL 创建影子表 ghost_table。

Step 4、GH-OST 客户端将自身注册为一个 Slave 实例,获取 RDS for MySQL 主实例的增量 binlog 信息(要求登录账户有 replication slave,replication client 权限)。

Step 5、并发拷贝 表 old_table 中的全量 和 增量数据(来自 binlog)到影子表 ghost_table。

Step 6、当用户触发切换,阻止原表 old_table 上的 DML 操作。

Step 7、等待影子表 ghost_table 和 原表 old_table 数据一致。

Step 8、切换(rename)影子表 ghost_table 为 原表 old_table。

 

2.4 方法对比 

下面小节下各个工具的优缺点:

 

RDS for MySQL Online DDL 使用
RDS for MySQL 表上 Metadata Lock 的产生和处理
MySQL 5.7 Online DDL Operations
MySQL 5.6 Online DDL Operations
MySQL 5.7 Online DDL Limitations
MySQL 5.6 Online DDL Limitations

RDS for MySQL 如何使用 Percona Toolkit
Percona Toolkit Documentation

gh-ost: GitHub's online schema migration tool for MySQL
GH-OST Requirements and limitations

MySQL · 社区动态 · Online DDL 工具 gh-ost 支持阿里云 RDS

3. 推荐方法

对于不会导致锁表的操作,并且能够支持只读实例延迟或不使用只读实例一定时间的业务,可以考虑直接使用 Online DDL。

对于要批量执行的低风险操作,可以考虑直接使用 Online DDL。

对于有只读实例并且对延迟敏感的一个或几个大表操作,建议考虑 GH-OST。

3.1 Online DDL


需要注意的地方:

  • 调整 Innodb_online_alter_log_max_size 到最大值避免增量日志空间问题。

  • alter 语句带 algorithm=inplace, lock=none 选项执行,避免自动降级锁表。

  • 正式执行前创建带部分数据的同结构测试表,估算空闲空间使用情况和是否能支持并发 DML (DDL 结束后返回 N rows affected;如果 N = 0 则证明采用 inplace 方式执行)。

 

3.2 GH-OST


样例一  - 直接连接主实例收缩表:

gh-ost \
--aliyun-rds="true" \
--critical-load=Threads_running=100 \
--max-load=Threads_running=70 \
--chunk-size=1000 \
--assume-master-host="jacky01.mysql.rds.aliyuncs.com:3306" \
--assume-rbr \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--host="jacky01.mysql.rds.aliyuncs.com" \
--port=3306 \
--user="jacky" \
--password="xxxx" \
--database="jacky" \
--table="ghost" \
--verbose \
--alter="engine=innodb" \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute

# --chunk-size 指定每批次操作的表行数,默认 1000 行,取值范围: 100-100000
# --initially-drop-old-table 如果存在已经存在的 OLD table(也许来自上次直接退出的 GH-OST 执行),则删除该表;不带该参数运行如果发现存在该表,则直接退出。
# --initially-drop-ghost-table 如果存在已经存在的 ghost table(也许来自上次直接退出的 GH-OST 执行),则删除该表;不带该参数运行如果发现存在该表,则直接退出。
# --initially-drop-socket-file 如果存在已经存在的 socket file(也许来自上次直接退出的 GH-OST 执行),则删除该 socket 文件;不带该参数运行如果发现存在该文件,则直接退出。
# --host 指定要操作的 RDS for MySQL 实例
# --port 指定该实例端口
# --user 指定操作使用的账户,该账户必须有 replication slave、replication client 权限
# --password 指定账户密码
# --database 指定操作的 库 名
# --table 指定要操作的表,该参数不能为空
# --alter 指定 DDL 操作
# --panic-flag-file 当这个文件被创建后,GH-OST 直接终止退出,不做任何清理操作(比如删除 ghost table)。

样例二 - 同时连接只读实例监控只读实例复制延迟

gh-ost \
--aliyun-rds="true" \
--critical-load=Threads_running=64 \
--max_load=Threads_running=32 \
--chunk-size=1000 \
--throttle-control-replicas="rr-2ze559088x3836.mysql.rds.aliyuncs.com:3306" \
--max-lag-millis=1500 \
--assume-master-host="jacky01.mysql.rds.aliyuncs.com:3306" \
--assume-rbr \
--initially-drop-old-table \
--initially-drop-ghost-table \
--initially-drop-socket-file \
--ok-to-drop-table \
--host="jacky01.mysql.rds.aliyuncs.com" \
--port=3306 \
--user="jacky" \
--password="xxxx" \
--database="jacky" \
--table="ghost" \
--verbose \
--alter="engine=innodb" \
--allow-on-master \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--execute

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
SQL 人工智能 分布式计算
基于阿里云PAI平台搭建知识库检索增强的大模型对话系统
基于原始的阿里云计算平台产技文档,搭建一套基于大模型检索增强答疑机器人。本方案已在阿里云线上多个场景落地,将覆盖阿里云官方答疑群聊、研发答疑机器人、钉钉技术服务助手等。线上工单拦截率提升10+%,答疑采纳率70+%,显著提升答疑效率。
|
3月前
|
机器学习/深度学习 负载均衡 数据可视化
性能比肩最强开源,QwQ-32B一键部署,百万Token免费送!
本文介绍如何通过百炼平台调用QwQ-32B开源模型。百炼平台提供的标准化 API 接口,免去了自行构建模型服务基础设施的麻烦,并支持负载均衡及自动扩缩容,确保了 API 调用的高稳定性。此外,结合使用 Chatbox 可视化界面客户端,用户无需进行命令行操作,即可通过直观的图形界面轻松完成 QwQ 模型的配置与使用。
|
缓存 Linux 开发工具
CentOS 7- 配置阿里镜像源
阿里镜像官方地址http://mirrors.aliyun.com/ 1、点击官方提供的相应系统的帮助 :2、查看不同版本的系统操作: 下载源1、安装wget yum install -y wget2、下载CentOS 7的repo文件wget -O /etc/yum.
240226 0
|
存储 弹性计算 固态存储
三分钟磁盘存储性能IOPS、I/O及吞吐量指标详解
什么是磁盘I/O?存储IOPS是什么?磁盘性能指标吞吐量是指什么?
16772 39
|
应用服务中间件 nginx
nginx反向代理POST请求
nginx反向代理POST请求
816 1
|
前端开发 安全 JavaScript
HTTP的系统登录页面,如何避免明文传输用户密码?
该文讨论了登录页面中密码安全传输的问题。当使用HTTP时,密码以明文形式传输,存在风险。在示例中,前端使用JavaScript的CryptoJS库和当前时间戳作为动态加密key对密码进行DES加密。后端接收到密文后,利用相同的时间戳解密。为了增强安全性,文章还建议使用RSA等非对称加密算法。
1649 7
|
存储 SpringCloudAlibaba 数据可视化
SpringCloudAliBaba之Nacos配置中心:轻松管理配置文件
SpringCloudAliBaba之Nacos配置中心:轻松管理配置文件
2123 0
|
数据可视化 IDE Java
PlanUML和Mermaid哪个好?
PlanUML和Mermaid哪个好?
1812 0
|
JSON 负载均衡 Java
SpringCloud Feign 远程调用(史上最详细讲解)
SpringCloud Feign 远程调用(史上最详细讲解)
11856 0
SpringCloud Feign 远程调用(史上最详细讲解)
|
存储 SQL 关系型数据库
mysql的varchar字段最大长度真的是65535吗?
mysql的varchar字段最大长度真的是65535吗?
605 0