关于mysql:万答18MySQL80-如何快速回收膨胀的UNDO表空间

欢送来到 GreatSQL 社区分享的 MySQL 技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

在我的项目选型中，在 KVM(16c 16G ssd160G) 的 Linux7.6 零碎上部署了 MYSQL MGR 集群 (GreatSQL 8.0.25)。

应用 sysbench 创立了 100 仓数据，且针对表创立为 partition 表，进行间断 12 小时的稳固下压测，来评估对应的架构的可能撑持的业务并发数，以及最高的 TPS/QPS 是多少。

在应用 256 并发，间断压测进行了 12 个小时之后，发现节点的 SSD 磁盘空间使用率达到 95% 以上，过后第一工夫去查看 log 目录，log 目录曾经达到 100G+，认为是 binlog 设置的工夫太长导致的 binlog 没有及时清理造成的，去清理 binlogbinlog 过期工夫设置的 1800s，理论 binlog 和 MGR 的 relay-group 空间占用在 11G 左右而 du -sh * 查看到的日志文件大小时，发现其中 undo 大小 1 个是 71G 另一个 4.1G，且 MGR 的 3 个节点的 undo 均是这个状况，急需开释空间。

然而 MySQL8.0 是否反对类型 oracle 的 undo 在线的替换来进行膨胀呢，答案是必定的，而且有些相似。

oracle/mysql undo 表空间设置主动扩大，如果业务上有跑批量或者大表的 DML 操作时，引起大事物，或针对多张大表关联更新工夫较长，可能短时间内会将 undo” 撑大 ”，oracle 咱们能够通过创立一个新的 undo，通过在线的替换的形式，将收缩的 undo 应用 drop 删除以开释空间。

mysql 8.0 同样能够应用这种形式来解决，因大事物或长事物引起的 undo 过大占用空间较多的状况。

1、增加新的 undo 文件 undo003。mysql8.0 中默认 innodb_undo_tablespace 为 2 个，有余 2 个时，不容许设置为 inactive，且默认创立的 undo 受爱护，不容许删除。
2、将收缩的 undo 长期设置为 inactive，以及 innodb_undo_log_truncate=on，主动 truncate 开释收缩的 undo 空间。
3、从新将开释空间之后的 undo 设置为 active，可从新上线应用。

[greatdb@mysql ~]$ mysql -ugreatsql -pgreatsql -h172.16.130.15 -P3307    
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 8.0.25-15 GreatSQL, Release 15, Revision c7feae175e0

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql[(none)]> show variables like '%undo%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| innodb_max_undo_log_size | 4294967296                  |
| innodb_undo_directory    | /app/dbdata/sqlnode3306/log |
| innodb_undo_log_encrypt  | OFF                         |
| innodb_undo_log_truncate | ON                          |
| innodb_undo_tablespaces  | 2                           |
+--------------------------+-----------------------------+
5 rows in set (0.01 sec)

1、查看 undo 大小

mysql[(none)]> system du -sh  /app/dbdata/datanode3307/log/undo*
4.1G /app/dbdata/datanode3307/log/undo_001
71G /app/dbdata/datanode3307/log/undo_002       -----12 小时间断稳定性压测，导致节点 undo 过大，达到 71G

2、增加新的 undo 表空间 undo003。零碎默认是 2 个 undo，大小设置 4G

mysql[(none)]> 
mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo003.ibu';
Query OK, 0 rows affected (0.21 sec)
留神：创立增加新的 undo 必须以.ibu 结尾，否则触发如下谬误提醒
mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo_003.' ;
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.

3、查看零碎中的 undo 表空间信息，如下：

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where  name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME            | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE   | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  4311744512 |     4311764992 |               0 | 8.0.25         |             1 | N          | active |
| 4294967278 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 | 76067897344 |    76068229120 |               0 | 8.0.25         |             1 | N          | active |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |    16777216 |       16777216 |               0 | 8.0.25         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.03 sec)

4、查看到上述视图中 innodb_undo_002 大小达到 76067897344（约 71G）其状态 state 为 active。手动将其设置为 inactive，使其主动触发 innodb_undo_log_truncate 回收。

mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;
Query OK, 0 rows affected (0.00 sec)

5、查看对应视图如下

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where  name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME            | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 |    0 | Undo       |     16384 |             0 | Undo       |          4096 | 4311744512 |     4311764992 |               0 | 8.0.25         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |   16777216 |        2179072 |               0 | 8.0.25         |             1 | N          | empty  |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |   16777216 |       16777216 |               0 | 8.0.25         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)

此时能够查看对应操作系统目录中的 undo_002 大小，innodb_undo_002 FILE_SIZE 16777216 默认大小 2179072，STATE 为 empty

mysql[(none)]> system du -sh  /app/dbdata/datanode3307/log/undo*
4.1G /app/dbdata/datanode3307/log/undo_001
2.1M /app/dbdata/datanode3307/log/undo_002
16M /app/dbdata/datanode3307/log/undo003.ibu

6、从新将其设置为 active 状态

mysql[(none)]> alter undo tablespace innodb_undo_002 set active;
Query OK, 0 rows affected (0.01 sec)

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where  name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME            | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 |    0 | Undo       |     16384 |             0 | Undo       |          4096 | 4311744512 |     4311764992 |               0 | 8.0.25         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |   16777216 |        2195456 |               0 | 8.0.25         |             1 | N          | active |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |   16777216 |       16777216 |               0 | 8.0.25         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)

7、有人说，为什么间接不能间接针对收缩的 undo 设置为 inactive，零碎默认创立的 undo 表空间默认 2 个，处于 active 小于 2 个时，会有如下提醒：

mysql[(none)]> mysql[(none)]> show variables like 'innodb_undo_tablespaces';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| innodb_undo_tablespaces  | 2                           |
+--------------------------+-----------------------------+
5 rows in set (0.01 sec)
mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;
ERROR 3655 (HY000): Cannot set innodb_undo_002 inactive since there would be less than 2 undo tablespaces left active.
mysql[(none)]>

8、新创建增加的能够失常设置为 inactive 之后，应用 drop 形式删除，如下：

mysql[(none)]> alter undo tablespace undo003 set inactive;
Query OK, 0 rows affected (0.00 sec)

mysql[(none)]> drop undo tablespace undo003;
Query OK, 0 rows affected (0.01 sec)

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where  name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE      | NAME            | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE  | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 |    0 | Undo       |     16384 |             0 | Undo       |          4096 | 4311744512 |     4311764992 |               0 | 8.0.25         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |   16777216 |        2244608 |               0 | 8.0.25         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
2 rows in set (0.01 sec)

通过以上操作咱们就能够针对 unod 因遇到大事务，undo 持续增长的状况下，通过新增长期 undo，手动开释零碎默认的 2 个 undo 表空间大小。

当然截断 UNDO 表空间文件对数据库性能是有肯定的影响的，尽量在绝对闲暇工夫进行。

当 UNDO 表空间被截断时，UNDO 表空间中的回滚段将被停用。其余 UNDO 表空间中的流动回滚段负责整个零碎负载，这可能会导致性能略有降落。性能受影响的水平取决于许多因素:

1、UNDO 表空间的数量
2、UNDO 记录日志的数据量
3、UNDO 表空间大小
4、磁盘 I / O 零碎的速度
5、现有长期运行的事务

那么防止潜在性能影响的最简略的办法:

1、就是通过 create undo tablespace undo_XXX add datafile ‘/path/undo_xxx.ibu’; 多增加几个 UNDO 表空间。
2、磁盘上如果条件容许采纳高性能的 SSD 来存储数据，存储 REDO,UNDO 等。
引起 UNDO 适度收缩的起因大多数是因为根底数据量大，业务并发高，表关联操作较频繁，呈现大且长的事物操作，导致 UNDO 始终处于 active 状态，不能及时开释回滚段等起因，大事物引起的问题由来已久，即便咱们能躲避 99% 的大事物，但理论业务遇到那 1% 的大事物刚性需要发过来时，这还要咱们的 MySQL 各种场景，各种架构和业务层好好磨合磨合。

Enjoy GreatSQL :)

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12，MGR 整个集群挂掉后，如何能力主动选主，不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』：《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15，都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili：
https://space.bilibili.com/13…

微信 &QQ 群：
可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

关于mysql:万答18MySQL80-如何快速回收膨胀的UNDO表空间

背景介绍

办法如下

具体操作如下

总结

文章举荐：

对于 GreatSQL