RDS for MySQL 大表操作

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: RDS for MySQL 大表操作

RDS for MySQL 大表操作

1. 名词解释

2. 实现原理

3. 推荐方法



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

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

1. 名词解释

 9ca2f46542412a25700bdbde885dec6729fff95c

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 方法对比 

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

7cf2096194cb2909995e4640e49eeb896a90a68e

 

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

e7d50481cfc41ad0f4e046b33d36eee1f64d4f78

需要注意的地方:

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

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

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

 

3.2 GH-OST

4f3e42c07c04d9b3c970ba8570a3165b46424ea7


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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
NoSQL 关系型数据库 MySQL
阿里云RDS关系型数据库大全_MySQL版、PolarDB、PostgreSQL、SQL Server和MariaDB等
阿里云RDS关系型数据库如MySQL版、PolarDB、PostgreSQL、SQL Server和MariaDB等,NoSQL数据库如Redis、Tair、Lindorm和MongoDB
241 0
|
16天前
|
SQL 关系型数据库 MySQL
mysql和polardb
mysql和polardb
15 2
|
5月前
|
SQL 关系型数据库 MySQL
将MySQL 数据迁移到 PostgreSQL
将MySQL 数据迁移到 PostgreSQL 可以采用以下步骤: 安装 PostgreSQL 数据库:首先,需要安装 PostgreSQL 数据库。可以从官方网站(https://www.postgresql.org/)下载最新版本的 PostgreSQL,并根据官方指南进行安装。 创建 PostgreSQL 数据库:在 PostgreSQL 中创建与 MySQL 数据库相对应的数据库。可以使用 pgAdmin 或命令行工具(如 psql)来创建数据库。例如,如果在 MySQL 中有一个名为 "mydb" 的数据库,那么可以在 PostgreSQL 中创建一个具有相同名称的数据库。 导
390 0
|
5月前
|
NoSQL Cloud Native 关系型数据库
阿里云RDS数据库_MySQL_SQL Server_MariaDB_PolarDB_PostgreSQL
阿里云RDS关系型数据库大全:MySQL版、PolarDB、PostgreSQL、SQL Server和MariaDB等
104 0
|
8月前
|
存储 SQL 运维
PolarDB MySQL大表实践-分区表篇
背景:分区表到底是什么?分区作为传统企业级数据库的特性,早已经在很多大数据和数仓场景中得到广泛应用。基于维基百科的解释,分区是将逻辑数据库或其组成元素如表、表空间等划分为不同的独立部分。数据库分区通常是出于可管理性、性能或可用性的原因,或者是为了负载平衡。它在分布式数据库管理系统中很流行,其中每个分区可能分布在多个节点上,节点上的用户在分区上执行本地事务。这提高了具有涉及某些数据视图的常规事务的站
290 0
PolarDB MySQL大表实践-分区表篇
|
存储 SQL JSON
PolarDB MySQL 5.6/MySQL 5.6升级PolarDB MySQL 8.0最佳实践
升级概述为什么选择升级到PolarDB MySQL 8.0?PolarDB MySQL 8.0.1 (基于官方MySQL 8.0.13内核版本)发布于2019-12-03和PolarDB MySQL 8.0.2(基于官方MySQL 8.0.18内核版本)发布于2020-07-22*,增强了诸多卓越的架构增强和内核能力,为业务提供更灵活的技术解决方案和强大收益的性能提升,主要包括:Serverles
442 0
|
存储 SQL JSON
PolarDB MySQL 5.7/RDS 5.7升级到PolarDB MySQL 8.0最佳实践
升级概述PolarDB MySQL 5.7/RDS 5.7 向 8.0 升级过程中,经常遇到的问题主要是性能问题、语法兼容性问题,以及周边组件是否的支持,查询的性能问题一般是由于优化器升级导致执 行计划有变,此类问题需要对性能低下的语句进行针对性的性能优化,但性能问题基本不会引发业务报错以及代码的改写问题,此类问题不在本文讨论范围之内。本文主要讨论真实的兼容性问题,此类问题需要在数据库升级过程中,
842 0
|
存储 JSON 算法
AnalyticDB MySQL-表和索引与MySQL的差异
AnalyticDB MySQL在语法上兼容MySQL,但是它的技术架构不同于MySQL,表和索引也和MySQL差异较大,这篇文章列出了这些差异,在使用AnalyticDB MySQL创建表时可以参考一下。
341 0
|
SQL 关系型数据库 MySQL
【RDS MySQL】为应用选择和创建最佳索引,加速数据读取
在工作中,由于SQL问题导致的数据库故障层出不穷,索引问题是SQL问题中常见的一种,例如:无索引,隐式转换,索引创建不合理。
298 0
【RDS MySQL】为应用选择和创建最佳索引,加速数据读取
|
存储 缓存 运维
RDS for MySQL 表和索引优化实战—田杰
RDS for MySQL 表和索引优化实战 --田杰
522 13
RDS for MySQL 表和索引优化实战—田杰