一般来说大事务大查询会导致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)
