数据库 undo log 文件无限膨胀，性能下降问题解决方案
1. 问题描述
在 Mysql 数据目录中发现有个 undo 文件非常大，并且持续增长
- 并且 History list length 非常大
------------
TRANSACTIONS
------------
Trx id counter 3569860310
Purge done for trx's n:o < 3185146100 undo n:o < 0 state: running but idle
History list length 194564756
LIST OF TRANSACTIONS FOR EACH SESSION:
- 并且可能会发现同时如下问题：
- Mysql 的性能在下降，TPS 并不高的情况下 IO wait 非常高，服务器负载也上升。
- 如果是从库，那么主从复制延迟也会增加。
2. 问题原因
- 复制延迟会导致从库的 History list length 增加，因为未处理的事务会保留在undo日志中，此时 undo log 就会膨胀。
- 长查询和事务锁会导致 History list length 增加，因为相关的undo日志无法被及时清理，此时 undo log 就会膨胀。
- Mysql 写入负载太重
- 存在失败的垃圾事务，并且无法清理掉。
3. 问题排查过程
-
使用sys.session或sys.processlist视图来查找活动事务。
SELECT * FROM sys.session WHERE trx_state IS NOT NULL;
-
使用 SHOW ENGINE INNODB STATUS; 命令查看InnoDB的状态信息，特别关注 TRANSACTIONS 部分，重点关注History list length ， 以及是否存在死锁 。
-
SELECT * FROM information_schema.INNODB_TRX; 查看当前正在运行的事务
- 在 show processlist; 中也会发现这个语句！
4. 解决方案
4.1. 清理长时间没有执行完成的事物
kill 247570;
4.2. 检查 innodb_undo_log_truncate 功能是否开启
-
在MySQL 5.7及更高版本中，可以使用独立的undo tablespace，并通过相关参数来控制undo log的生成和回收。
-
设置 innodb_max_undo_log_size 可以限制单个undo tablespace文件的大小，当文件大小超过这个阈值时，MySQL会自动触发truncate操作来回收空间。
-
设置 innodb_purge_rseg_truncate_frequency 参数可以控制回收undo log的频率。
mysql> show variables like '%undo%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | innodb_max_undo_log_size | 1073741824 | | innodb_undo_directory | ./ | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | innodb_undo_tablespaces | 2 | +--------------------------+------------+ 5 rows in set (0.10 sec) ``` -
所以为了尽快收缩 undo 文件，我们可以将 innodb_purge_rseg_truncate_frequency 值调小，提高 purge 线程释放回滚段的频率
- 例如： SET GLOBAL innodb_purge_rseg_truncate_frequency=32;
-
-
MySQL8.0 新增支持使用 SQL 语句来管理 undo 表空间
Oracle/MySQL undo表空间设置自动扩展，如果业务上有跑批量或者大表的DML操作时，引起大事物或针对多张大表关联更新时间较长，可能短时间内会将undo"撑大"，Oracle可通过创建一个新的undo，通过在线替换的方式，将膨胀的undo使用drop删除以释放空间。
MySQL 8.0同样可以使用这种方式来处理，因大事物或长事物引起的undo过大占用空间较多的情况。-
示例： /mnt/mysqldata/undo_002 文件膨胀严重，需要清理
```sh mysql> 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.05 sec) # 创建一个新的 create undo tablespace undo_003 add datafile '/mnt/mysqldata/undo_003.ibu'; # 查看三个 undo 文件状态 cd /mnt/mysqldata ll -h -rw-r----- 1 mysql mysql 1.1G 2024-03-17 14:58:55 undo_001 -rw-r----- 1 mysql mysql 110G 2024-11-20 18:34:15 undo_002 -rw-r----- 1 mysql mysql 10M 2024-11-20 18:34:15 undo_003.ibu # 手动禁用 innodb_undo_002 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE; # 手动设置 innodb_undo_002 inactive 后，undo 表空间被标记为截断，purge 线程会增加返回频率，快速清空并最终截断 undo 表空间，状态变为 empty。（需要等待一段时间） mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%'; +-----------------+--------+ | NAME | STATE | +-----------------+--------+ | innodb_undo_001 | active | | innodb_undo_002 | empty | | undo_003 | active | +-----------------+--------+ # 最后： empty 状态的 undo 表空间可以重新激活使用 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 | | undo_003 | active | +-----------------+--------+ 3 rows in set (0.01 sec) ```- 附录： MySQL8.0 支持删除 undo 表空间（DROP UNDO TABLESPACE xxx;），但前提是该表空间为 empty 状态
# 先禁用 mysql> ALTER UNDO TABLESPACE undo_003 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 | active | | undo_003 | empty | +-----------------+--------+ 3 rows in set (0.01 sec) # 后删除表空间 mysql> DROP UNDO TABLESPACE undo_003; Query OK, 0 rows affected (0.02 sec) mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG'; +-----------------+------------+ | TABLESPACE_NAME | FILE_NAME | +-----------------+------------+ | innodb_undo_001 | ./undo_001 | | innodb_undo_002 | ./undo_002 | +-----------------+------------+ 2 rows in set (0.01 sec)- Undo tablespace是存储undo log的物理空间。
在MySQL中，可以配置多个undo tablespace，以便更好地管理undo log的存储和回收。
- Undo tablespace是存储undo log的物理空间。
- 附录： MySQL8.0 支持删除 undo 表空间（DROP UNDO TABLESPACE xxx;），但前提是该表空间为 empty 状态
-
4.3. 终极方案： 若发现按上面的方案执行后，如果 undo log 文件依然不会自动减少，并且 History list length 持续增加。
-
重启数据库： sudo systemctl restart mysqld
-
修改隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;- 注意，这个设置是全局的，并且会影响从库上的所有新会话。现有的会话可能会继续使用它们自己的隔离级别设置，直到它们被关闭并重新打开。
-
检查效果：
#查看 History list length 是否逐渐减少 SHOW ENGINE INNODB STATUS;