MySQL undo膨胀了怎么办？

    一般来说大事务大查询会导致undo变大。默认配置不会自动收缩。印象中OCP的考试中都没涉及到这样的考点。

     记录了一次在实验环境下模拟收缩的实验。需要在参数文件中设置这几个参数;

innodb_max_undo_log_size = 10M  --回收后的初始状态一般是16M，即使这里设置是10M，最终通过实验测试得出也是16M
innodb_undo_log_truncate = ON   开启自动回收
innodb_undo_tablespaces = 3     要保证至少有3个undo文件。默认是2个。两个不能做回收。

指定purge操作被唤起多少次之后才释放rollback segments。以上设置到参数文件以后，需要重启数据库。

首先需要新增加一个undo文件。因为默认有两个，收缩的时候需要两个在线工作。所以必须增加一个采用轮换制进行处理。增加文件还有一个特殊注意就是一定要有ibu后缀。

mysql> create undo tablespace undo003 add datafile '/var/lib/mysql/undo_003';
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.
 

按照提示处理后，增加了一个undo文件。
mysql> create undo tablespace undo003 add datafile '/var/lib/mysql/undo_003.ibu';
Query OK, 0 rows affected (0.48 sec)

通过命令确认增加的文件有效。
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo003 | active |
+-----------------+--------+
3 rows in set (0.01 sec)

未收缩前的undo大小如下：

-rw-r--r-- 1 mysql mysql 1112 Jan 3 2023 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 3 2023 server-key.pem
drwxr-x--- 2 mysql mysql 28 Jan 3 2023 sys
drwxr-x--- 2 mysql mysql 85 Sep 10 20:24 test_db

-rw-r----- 1 mysql mysql 33554432 Sep 11 16:36 undo_001
-rw-r----- 1 mysql mysql 100663296 Sep 11 16:36 undo_002

 

将undo_002的undo文件设置成为不活动。

mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | empty |
| undo003 | active |
+-----------------+--------+
3 rows in set (0.00 sec)

设置成功。检查undo002的文件已经收缩完成。果然是16M。

-rw-r----- 1 mysql mysql 33554432 Sep 11 16:49 undo_001
-rw-r----- 1 mysql mysql 16777216 Sep 11 16:49 undo_002
-rw-r----- 1 mysql mysql 16777216 Sep 11 16:49 undo_003.ibu
[root@MySQL8M mysql]# ll

那么再将undo002重新激活即可。
mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo003 | active |
+-----------------+--------+
3 rows in set (0.00 sec)

如法炮制，处理undo001文件

mysql> ALTER UNDO TABLESPACE innodb_undo_001 SET InACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | empty |
| innodb_undo_002 | active |
| undo003 | active |
+-----------------+--------+
3 rows in set (0.01 sec)

mysql> ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo003 | active |
+-----------------+--------+
3 rows in set (0.01 sec)