【Mysql 数据库 History list length 超长，且undo log 文件无限膨胀，性能下降等问题解决方案】

最新推荐文章于 2024-12-03 14:26:11 发布

原创最新推荐文章于 2024-12-03 14:26:11 发布 · 1k 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

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是指在回滚空间中的未清除事务数。随着事务的提交，它的值会增加；随着清除线程的运行，它的值会减小。

实例: 发现主库的 History list length 值正常，从库的 History list length 非常大

此时可能有如下问题表现：
1. 主从复制延迟增大
2. 数据库数据读写慢，性能降低， iowait 较高。
3. 从库的 undolog 无限膨胀, 或者 innodb_undo_log_truncate 机制未生效，无法收缩 undolog 文件的大小。
4. 由于较长的 InnoDB 历史记录列表会减慢数据库关闭速度，因此在启动涉及数据库关闭的操作之前，请减小列表大小

查看发现未提交的事物，可见这个事物长达几个月没有结束。

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 421966903910096
                 trx_state: RUNNING
               trx_started: 2024-06-13 19:11:42
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 247570
                 trx_query: select count(*) from t_xxx_user
       trx_operation_state: counting records
         trx_tables_in_use: 1
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)

原因可能：
- 复制延迟会导致从库的 History list length 增加，因为未处理的事务会保留在undo日志中。
- 长查询和事务锁会导致 History list length 增加，因为相关的undo日志无法被及时清理。
- Mysql 写入负载太重
- 失败的事务
  mysql中使用的隔离级别是默认的：REPEATABLE-READ。在这个级别下，保证了多次读的结果一样（跟MVCC有关）。而这个业务对于失败的事务不做任何操作，导致可能存在这个事务一直没commit，为了保持隔离级别REPEATABLE-READ 的特性，在undo中一直保留了这个事务以后的版本号，也就导致未清理的事务数越来越大，History list length 值也就越大。
  InnoDB 实现了多版本并发控制（MVCC），这意味着不同的用户将看到他们正在交互的数据的不同版本（有时称为快照，这是一个有点误导性的术语）。这样做是为了允许用户看到系统的一致视图，而不会出现成本高昂且影响性能的锁定，这会限制并发性。（这就是术语的“并发控制”部分的来源;一种替代方法是锁定用户可能需要的所有内容。撤消日志记录和 InnoDB 的“历史”系统是其实现 MVCC 的基础机制
  - InnoDB 中的挂起事务将导致 InnoDB 历史长度不受控制地增长，这将直接对 MySQL 中的 SELECT 查询性能产生负面影响。我们已经通过将 TRANSACTION ISOLATION LEVEL 更改为 READ-COMMITTED 为多个客户解决了这个问题。从技术上讲， InnoDB History Length 是撤消日志，可用于为 MVCC 目的重新创建历史记录，也可以回滚。

解释 HLL 值

 解释 HLL 值时，请考虑下表中列出的准则：
 值  备注
 小于 ~10,000  正常值，表明垃圾回收未滞后。
 介于 ~10,000 和 ~1,000,000 之间 这些值表明垃圾回收有轻微的滞后。 如果此类值保持稳定且不会增加，则这些值可能是可接受的。
 大于 ~1,000,000  应调查这些值，可能需要纠正措施

3. 解决方案

解决方案：
- 识别并结束长时间运行的事务
  SELECT * FROM information_schema.INNODB_TRX\G
  - 注意： kill掉失败的事物所处的线程，若 kill后依然出现在 processList 中，那么只能重启数据库试试。
- 使用READ-COMMITTED 而不是默认级别 REPEATABLE-READ
  - 如果是读写分离的主从架构，建议主库使用 REPEATABLE-READ 而从库使用 READ-COMMITTED
  - 修改方案
    - 如果 undolog 非常大，建议先检查 innodb_undo_log_truncate 是否开启。
```
show variables like '%undo%';
```
    - 然后检查隔离级别
```
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
```
    - 临时修改隔离级别
```
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
      - 注意，这个设置是全局的，并且会影响从库上的所有新会话。现有的会话可能会继续使用它们自己的隔离级别设置，直到它们被关闭并重新打开。
    - 永久修改 : 修改 my.cnf 文件，然后重启 Mysql
```
[mysqld]
transaction_isolation=READ-COMMITTED
```
      - 重启：
```
# 从库建议先主动停止复制，然后重启
stop slave;
sudo systemctl restart mysqld
```
    - 检查效果：
```
# 查看 History list length 是否逐渐减少
SHOW ENGINE INNODB STATUS;
```
如何避免？
- 避免长事物！如果无法避免，建议针对长事物单独使用 READ-COMMITTED
  - 以 SpringBoot 为例： @Transactional(isolation = Isolation.READ_COMMITTED)
- 使用READ-COMMITTED 而不是默认级别 REPEATABLE-READ
- 如果是读写分离的主从架构，建议主库使用 REPEATABLE-READ 而从库使用 READ-COMMITTED
  - 主库用 REPEATABLE-READ（可重复读）
    - 优势：
      - 确保同一事务的多次读取操作会看到相同的数据。
      - 解决了脏读（读取未提交数据）和不可重复读（同一事务中多次读取结果不一致）的问题。
      - 但可能存在幻读（一个事务读取某个范围的数据行时，另一个事务在该范围内插入了新行，导致前一个事务再次读取时出现“幻影”行）的问题。不过，MySQL 的 InnoDB 存储引擎通过多版本并发控制（MVCC）机制缓解了幻读问题。
    - 使用场景：
      - 确保数据一致性的写操作频繁的场景。
      - 主库上，由于涉及到数据的更新和删除，使用 REPEATABLE-READ 可以确保事务的原子性和一致性。
  - READ-COMMITTED（读已提交）
    - 特点：
      - 一个事务只能看到已经提交事务所做的更改。
      - 解决了脏读问题，但可能出现不可重复读和幻读。
      - 提高了并发性能，因为读取操作不需要等待其他事务的提交。
    - 适用场景：
      - 读操作频繁，且对一致性要求不高的场景。
      - 从库上，由于主要用于读取数据，使用 READ-COMMITTED 可以提高并发性能，减少锁争用。
其他参考： (https://minervadb.xyz/troubleshooting-innodb-history-length-with-hung-mysql-transaction/)
Mysql官网建议：（https://dev.mysql.com/blog-archive/performance-impact-of-innodb-transaction-isolation-modes-in-mysql-5-7/）
- 更多参考：
  - https://docs.aws.amazon.com/en_us/AmazonRDS/latest/AuroraUserGuide/proactive-insights.history-list.html
  - https://minervadb.xyz/troubleshooting-innodb-history-length-with-hung-mysql-transaction/
  - https://blog.jcole.us/2014/04/16/the-basics-of-the-innodb-undo-logging-and-history-system/
  - https://dev.mysql.com/blog-archive/performance-impact-of-innodb-transaction-isolation-modes-in-mysql-5-7/