【Mysql 数据库 undo log 文件无限膨胀，性能下降问题解决方案】

数据库 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的存储和回收。

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;
  

• 更多参考： Mysql 数据库 History list length 超长