MySQL8.0 快速回收膨胀的UNDO表空间

MySQL8.0支持类似oracle的undo在线的替换来进行收缩
oracle/mysql undo 表空间设置自动扩展，如果业务上有跑批量或者大表的DML操作时，引起大事物，或针对多张大表关联更新时间较长，可能短时间内会将undo"撑大"。
oracle 我们可以通过创建一个新的 undo，通过在线的替换的方式，将膨胀的 undo 使用 drop 删除以释放空间。mysql 8.0同样可以使用这种方式来处理，因大事物或长事物引起的undo过大占用空间较多的情况

mysql8.0 innodb_undo_log_truncate参数默认开启的，并且mysql8.0中默认innodb_undo_tablespace为2个。不足2个时，不允许设置为inactive，且默认创建的undo受保护，不允许删除。

演示环境为GA版本 mysql8.0.28
MySQL8.0 快速回收膨胀的UNDO表空间 一共分为3步骤：
1、添加新的undo文件undo003
2、将膨胀的 undo 临时设置为inactive,自动 truncate 释放膨胀的undo空间。
3、重新将释放空间之后的undo设置为active，可重新上线使用。

查看undo相关参数配置：

(Sun Mar 27 22:05:33 2022)[root@MySQL][(none)]>show variables like '%undo%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| innodb_max_undo_log_size | 4294967296            |
| innodb_undo_directory    | /data1/mysql8/undolog |
| innodb_undo_log_encrypt  | OFF                   |
| innodb_undo_log_truncate | ON                    |
| innodb_undo_tablespaces  | 2                     |
+--------------------------+-----------------------+
5 rows in set (0.01 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

1、查看undo大小

(Sun Mar 27 22:06:19 2022)[root@MySQL][(none)]>system du -sh /data1/mysql8/undolog/*
16M	/data1/mysql8/undolog/undo_001
16M	/data1/mysql8/undolog/undo_002


  • 1.
  • 2.
  • 3.
  • 4.

2、添加新的undo表空间undo003。系统默认是2个undo，大小设置4G

(Sun Mar 27 22:07:30 2022)[root@MySQL][(none)]>create undo tablespace undo003 add datafile '/data1/mysql8/undolog/undo003.ibu';
Query OK, 0 rows affected (0.23 sec)

  • 1.
  • 2.

注意：创建添加新的undo必须以.ibu结尾，否则触发错误
3、查看系统中的undo表空间信息，如下：

(Sun Mar 27 22:07:48 2022)[root@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 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967278 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

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


(Sun Mar 27 22:09:49 2022)[root@MySQL][(none)]>alter undo tablespace innodb_undo_002 set inactive;
Query OK, 0 rows affected (0.00 sec)

(Sun Mar 27 22:12:11 2022)[root@MySQL][(none)]>
(Sun Mar 27 22:12:13 2022)[root@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 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | empty  |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.

此时可以查看对应操作系统目录中的 undo_002大小，以及 状态 empty

查看新增的undo003文件大小

(Sun Mar 27 22:12:15 2022)[root@MySQL][(none)]>system du -sh  /data1/mysql8/undolog/*
16M	/data1/mysql8/undolog/undo_001
16M	/data1/mysql8/undolog/undo_002
16M	/data1/mysql8/undolog/undo003.ibu

  • 1.
  • 2.
  • 3.
  • 4.

6、重新将innodb_undo_002设置为active状态

(Sun Mar 27 22:16:58 2022)[root@MySQL][(none)]>alter undo tablespace innodb_undo_002 set active;
Query OK, 0 rows affected (0.01 sec)
(Sun Mar 27 22:17:04 2022)[root@MySQL][(none)]>
(Sun Mar 27 22:17:04 2022)[root@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 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967277 | undo003         |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.00 sec)


  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

7、为什么直接不能直接针对膨胀的undo设置为inactive，系统默认创建的undo表空间默认2个，处于active小于2个时，会有如下提示：

(Sun Mar 27 22:20:12 2022)[root@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.

  • 1.
  • 2.

8、新创建添加的可以正常设置为inactive之后，使用drop方式删除，如下：

(Sun Mar 27 22:18:56 2022)[root@MySQL][(none)]>alter undo tablespace undo003 set inactive;
Query OK, 0 rows affected (0.00 sec)
(Sun Mar 27 22:18:57 2022)[root@MySQL][(none)]>drop undo tablespace undo003;
Query OK, 0 rows affected (0.00 sec)

(Sun Mar 27 22:19:06 2022)[root@MySQL][(none)]>system du -sh  /data1/mysql8/undolog/*
16M	/data1/mysql8/undolog/undo_001
16M	/data1/mysql8/undolog/undo_002
(Sun Mar 27 22:19:40 2022)[root@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 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
| 4294967151 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |          4096 |  16777216 |       16777216 |               0 | 8.0.28         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
2 rows in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

**总结
**通过以上操作我们就可以针对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状态，不能及时释放回滚段等原因
文章内容转载于下面的地址:
 https://mp.weixin.qq.com/s/TGsIoB9jQ4MjlRd6a6R_iw

posted @ 2022-03-27 22:48  勤奋的蓝猫  阅读(12)  评论(0)  编辑  收藏  举报  来源