RDS for MySQL 大表操作

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
SQL 关系型数据库 数据库
MySQL · 社区动态 · Online DDL 工具 gh-ost 支持阿里云 RDS
背景 Online DDL 一直都是 DBA 运维时比较头疼的事,一般都会选择在业务低峰期谨慎的操作,比较常用的几个工具比如 percona pt-online-schema-change , Facebook OSC, 本质上它们都是基于触发器的,简单来讲就是通过数据库的触发器把作用在源表的操作在一个事务内同步到修改后的表中,这在业务高峰期时会极大的加重主库的负载。
5088 0
|
SQL 关系型数据库 MySQL
pt-tools系列:pt-online-schema-change 最佳实践
pt的详细步骤 Step 1: Create the new table. Step 2: Alter the new, empty table. This should be very quick, or die if the user specified a bad alter statement.
5530 0
|
Python Windows
一键生成朋友圈公众号转发截图,自定义朋友圈点赞评论
Python实现朋友圈转发截图制作,生成自定义点赞评论
|
SQL 算法 关系型数据库
MySQL8.0大表秒加字段,是真的吗?
很早就听说 MySQL8.0 支持快速加列,可以实现大表秒级加字段。笔者自己本地也有8.0环境,但一直未进行测试。本篇文章我们就一起来看下 MySQL8.0 快速加列到底要如何操作。
1328 0
|
缓存 Linux 开发工具
CentOS 7- 配置阿里镜像源
阿里镜像官方地址http://mirrors.aliyun.com/ 1、点击官方提供的相应系统的帮助 :2、查看不同版本的系统操作: 下载源1、安装wget yum install -y wget2、下载CentOS 7的repo文件wget -O /etc/yum.
256252 0
|
机器学习/深度学习 负载均衡 数据可视化
性能比肩最强开源,QwQ-32B一键部署,百万Token免费送!
本文介绍如何通过百炼平台调用QwQ-32B开源模型。百炼平台提供的标准化 API 接口,免去了自行构建模型服务基础设施的麻烦,并支持负载均衡及自动扩缩容,确保了 API 调用的高稳定性。此外,结合使用 Chatbox 可视化界面客户端,用户无需进行命令行操作,即可通过直观的图形界面轻松完成 QwQ 模型的配置与使用。
|
存储 关系型数据库 MySQL
MySQL——如何快速删除大表
MySQL——如何快速删除大表
239 0
|
存储 数据库
Union All:数据库查询的得力助手
Union All:数据库查询的得力助手
|
11月前
|
机器学习/深度学习 人工智能 弹性计算
阿里云AI服务器价格表_GPU服务器租赁费用_AI人工智能高性能计算推理
阿里云AI服务器提供多种配置选项,包括CPU+GPU、CPU+FPGA等组合,支持高性能计算需求。本文汇总了阿里云GPU服务器的价格信息,涵盖NVIDIA A10、V100、T4、P4、P100等多款GPU卡,适用于人工智能、机器学习和深度学习等场景。详细价格表和实例规格见文内图表。
1958 0