MySQL Undo 工作机制历史演变-低调大师

MySQL Undo 工作机制历史演变

2024-08-30 223

前几天遇到一个关于 Undo 变大的 CASE，为了方便后续排查问题，于是系统的梳理 Undo 表空间的相关知识，希望对读者朋友有所帮助。

>作者：杨奇龙，网名“北在南方”，资深 DBA，主要负责数据库架构设计和运维平台开发工作，擅长数据库性能调优、故障诊断。 > >爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 > >本文约 1700 字，预计阅读需要 6 分钟。

前言

前几天遇到一个关于 Undo 变大的 CASE（大致的场景参见文章末尾），为了方便后续排查问题，于是系统的梳理 Undo 表空间的相关知识，希望对读者朋友有所帮助。

Undo 工作机制

保障事务原子性,提供数据回滚

当数据库崩溃时或者事务回滚时，InnoDB 事务系统可以利用 Undo Log 来进行数据回滚。

多版本并发控制（MVCC）- 隔离性

InnoDB 存储引擎中 MVCC 的实现是通过 Undo Log 来实现的。

当事务 A 读取某一行记录时，若该记录已经被其他事务 B 占用，当前事务 A 可以通过 Undo Log 读取之前的行版本信息，以此实现非锁定读取（如果有长时间的查询，会导致历史的 Undo 不能及时清理，进而导致 Undo Log 膨胀）。

UNDO 表空间管理发展史

3.1 MySQL 5.6 版本之前

Undo Tablespace 是在 ibdata 中与系统表空间一起。比较常见的问题是由于大事务不提交导致 ibdata 膨胀，而且事务提交之后不能回收空间，进而浪费大量的空间甚至把磁盘打爆，同时也增加了数据库物理备份的时间。

此时，重建数据库是唯一的解决方法。

3.2 MySQL 5.6 版本

InnoDB 支持设置独立的 Undo Tablespace，也即 Undo Log 可以存储于 ibdata 文件之外。但是该特性存在一定的限制：

使用者必须在初始化实例的时候，通过设置 innodb_undo_tablespaces 的值来实现 Undo Tablespace 独立，而且在初始化完成后不可更改。默认值为 0，表示不独立设置 Undo 的 Tablespace，默认记录到 ibdata 中。
修改 innodb_undo_tablespaces 的值会导致数据库无法启动。
Undo Tablepsace 的 Space ID 必须从 1 开始，无法增加或者删除 Undo Tablespace。

3.3 MySQL 5.7 版本

引入一个让 DBA 开心的功能 -- 在线 truncate undo tablespace。该功能通过 innodb_undo_log_truncate 参数来控制。

3.4 MySQL 8.0 版本

MySQL 对 Undo Tablespace 进一步优化。

在 8.0 版本中，独立 Undo Tablespace 特性默认打开。从 8.0.3 版本开始，默认 Undo Tablespace 的个数从 0 调整为 2。
支持动态在线增加/删除 Undo Tablespace 。
Undo Tablespace 的命名从 undoNNN 修改为 undo_NNN 。
在 8.0 之前只能创建 128个回滚段，而在 8.0 版本开始，每个 Undo Tablespace 可以创建 128 个回滚段。共有 innodb_rollback_segments * innodb_undo_tablespaces 个回滚段。在高并发下可以显著的减少因为分配到同一个回滚段内的事务间产生的锁冲突，从而提高系统并行性能。
Innodb_undo_truncate 参数默认打开， Undo Tablespace 大小超过 innodb_max_undo_log_size 来控制时，就会触发 Online Truncate。
支持 Undo Tablespace 加密。

Undo 表空间维护

我们通过模拟 Undo 文件增大、手动添加 Undo 表空间和文件的过程，来学习 Undo 表空间的管理操作。基本思路是：

一般默认 MySQL 实例有 2 个 Undo Tablespace。
添加新的 Undo 表空间 A。
设置老的 Undo 表空间 B 为 inactive，系统基于 innodb_undo_log_truncate =ON 自动回收 Undo 文件空间。
设置老的 Undo 表空间 B 位 active。
次数可以保留新的 Undo 表空间 A 或者设置 Undo 表空间 A 为 inactive ，然后删除。

接下来我们根据以上的思路进行测试。

1. 查看当前 Undo 表空间和状态

mysql &gt; SELECT NAME, STATE 
        &gt; FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
        &gt; WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
2 rows in set (0.02 sec)

2. 添加新的 Undo 表空间

系统默认会分配 2 个 innodb 开头的表空间。如果手工新增 Undo 表空间，创建 innodb 开头的表空间名称会报错，提示以 innodb 开头的表空间名称被系统占用。

mysql &gt; create undo tablespace innodb_undo_003 add datafile 'undo_003.ibu';
ERROR 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.

文件必须是 .ibu 结尾，否则也是会报错。

3. 创建 Undo Tablespace

创建 Undo Tablespace 文件 undo_003。

mysql &gt; create undo tablespace undo_003 add datafile 'undo_003.ibu';
Query OK, 0 rows affected (0.14 sec)
mysql &gt; SELECT NAME, STATE 
        &gt; FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
        &gt; WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.00 sec)

4. 自动回收 Undo 文件空间

设置 innodb_undo_002 为 inactive ，让系统自动收缩 Undo 文件。

mysql &gt; ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
mysql &gt; SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | empty  | ## 状态为 empty 时即可触发系统 undo表空间回收
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.00 sec)

我们不能删除系统默认创建 innodb 开头的 Undo 表空间，系统会提示 该空间位系统保留空间。

mysql &gt; DROP UNDO TABLESPACE  innodb_undo_002;
ERROR 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.
mysql &gt;ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

5. 删除新的 Undo 表空间

mysql &gt; ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
mysql &gt; SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | empty  |
+-----------------+--------+
3 rows in set (0.00 sec)
mysql &gt;DROP UNDO TABLESPACE undo_003;
Query OK, 0 rows affected (0.01 sec)
mysql &gt; SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
2 rows in set (0.00 sec)

UNDO 相关参数

innodb_undo_directory：存储 Undo 文件的目录。
innodb_undo_log_truncate：用于打开/关闭 Truncate Undo 特性，可动态调整。MySQL 8.0 默认开启。
innodb_undo_tablespaces：默认为 2，用于初始化实例时设置 Undo Tablespace 的个数，该参数可以动态调整。要实现在线 Truncate Undo，该参数需要大于等于 2，因为在 Truncate 一个 Undo Log 文件时，需要保证另外一个是可用的。手动维护的时候可以设置为 3 或者更大。
innodb_purge_rseg_truncate_frequency：默认最大值 128，用于控制 purge 回滚段的频率。也就是 128 次后才会触发一次 Undo 的 Truncate，而每次清理的 Undo Page 由 innodb_purge_batch_size 参数决定。innodb_purge_batch_size 默认为 300，也即 300×128 个 Undo 批次清理后才会触发 Undo 表空间的收缩操作。该参数越小，Undo 表空间被尝试 Truncate 的频率越高。
innodb_max_undo_log_size：控制 Undo 表空间文件的大小，超过这个阈值时才会去尝试 Truncate。Truncate 后的大小默认为 10M。

学以致用

纸上来得终觉浅，绝知此事要躬行。

前面介绍了 Undo 的理论知识，现在 DBA 小明遇到一个问题：一套 MySQL 生产实例（8.0 版本），其架构为一主多从，其中一个备库承担只读服务。Undo 文件在一天内暴涨到 270G 左右。大家有什么思路帮助小明来解决 Undo 表空间回收的问题？

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/15573542

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Rust 开发的高成长性的数据服务平台 —— GrowthStore 发布

GrowthStore 是一个基于 Rust 体系的成长型的企业级后台服务的开发框架。它提供了 “约定大于配置，配置即服务” 的开发理念，采用统一的约定、灵活的配置来实现绝大多数的业务，通过灵活高效的扩展体系，来满足各种特殊化的业务需求，快速应对企业的业务成长需要。GrowthStore 的出现，为企业级后端开发注入了新鲜的血液。 GrowthStore 有一套统一的服务语言，即它的 InvokeURI 统一调用服务语言，使得它的各种功能可以使用一套统一化的 URI 来进行表示，而前端只需要根据这些 URI 的规范即可知道 GrowthStore 的具体提供服务。因此，GrowthStore 可以为前端开发人员提供更好的开发范式，让前端开发人员与后台服务开发更容易达成一致。 GrowthStore 提供了支持多种关系型数据库的映射能力，如 MySQL、PostgreSQL、MSSQL Server 等，GrowthStore 提供了可配置性的数据库表及查询的映射方式，可以支持类似于 1..1, 1..N， N..N 之类的表与表之间的关系型映射，并提供基于 CRUD 的 11 种操作...

2024-09-04

243

对于新兴技术而言，社区管理并不难。经过 29 年的发展，MySQL 已成为全球数百万用户最常使用和最信赖的开源数据库之一。管理如此规模的社区可能非常复杂。我们努力在稳定性和创新性之间找到平衡，为客户提供稳定且可预测的平台，并为技术用户创建新功能。Oracle 通过投资于工程、社区和市场对该技术的采用，持续提供对 MySQL 社区的管理和领导。作者：Frederic Descamps，EMEA 和亚太地区的 MySQL 社区经理。于 2016 年 5 月加入 MySQL 社区团队。担任开源和 MySQL 顾问已超过 15 年。最喜欢的主题是高可用和高性能。本文和封面来源：https://blogs.oracle.com/，爱可生开源社区翻译。本文约 1800 字，预计阅读需要 6 分钟。引言对于新兴技术而言，社区管理并不难。经过 29 年的发展，MySQL 已成为全球数百万用户最常使用和最信赖的开源数据库之一。管理如此规模的社区可能非常复杂。我们努力在稳定性和创新性之间找到平衡，为客户提供稳定且可预测的平台，并为技术用户创建新功能。Oracle 通过投资于工程、社区和市场对该技...

2024-09-02

206

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。