MySQL History List Length
2024-09-29 08:17 潇湘隐者 阅读(174) 评论(0) 编辑 收藏 举报本文是翻译A graph a day, keeps the doctor away ! – MySQL History List Length[1]这篇文章，翻译如有不当的地方，敬请谅解，请尊重原创和翻译劳动成果，转载的时候请注明出处。谢谢！
这是MySQL趋势系列文章的第二篇。
正如我之前所写，了解您MySQL数据库的负载并观察其随时间的演变可以帮助您预测问题并在发生故障之前找到解决方案。
本文介绍MySQL历史列表长度（也称为 HLL 英文全称为History List Length）。
MySQL历史列表与InnoDB Undo日志相关。InnoDB是一种多版本存储引擎 (MVCC)。它保留有关已更改行的旧版本的信息，以支持并发和回滚等事务功能。此信息存储在Undo表空间中称之为回滚段的数据结构中。
这意味着你可以开始一个事务，即使数据被其他事务更改，你仍然可以继续看到一致性的快照。此行为与隔离级别有关。在MySQL 中，默认情况下，事务隔离级别是REPEATABLE-READ：
SQL> show global variables like '%isola%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
为了提供这种事务隔离级别，InnoDB需要保留修改后的行的旧版本，如何有相关事务没有关闭的话，就必须一直保留。
所有这些变更/更改都保存在一个链接的列表中，指向同一行的前一个版本，而该版本本身又指向同一行的前一个版本，等等……这意味着每次在新的事务中更新一行时，旧版本都会被复制到指向它的相应回滚段中。
然后，每行都有一个 7 字节的DB_ROLL_PTR字段，称为回滚指针。回滚指针指向写入回滚段的回滚段日志记录。如果行已更新，则Undo日志记录包含重建行更新前内容所需的信息。
事务99已启动START TRANSACTION;但尚未提交或回滚。
在上图中，第二个事务(tx 100) 插入了一条记录。默隔离情况下 ( REPEATABLE_READ)，第二行在事务trx 99 中不可见（该行的 TRX_ID 大于 99）。
现在，当数据更新时，数据更改也会保留在回滚段中。
在回滚段被清除之前，这个数字(链表的长度)会一直增加。
这是有关InnoDB中如何工作的高水平说明。
历史列表长度量化了更改/变更的数量（大量的记录包含以前的数据变更）。
如果一条记录包含大量版本，那么在最早的事务中检索值可能需要更长时间。
在MySQL手册中，我们可以看到：回滚段中的undo日志被分为插入回滚日志和更新回滚日志。插入回滚日志仅在事务回滚时才需要，并且可以在事务提交后立即丢弃。更新回滚日志也用于一致性读取，但只有在没有事务需要InnoDB分配的快照时才能丢弃它们，InnoDB一致性读取可能需要更新回滚日志中的信息来构建数据库行的早期版本。
阅读这些信息后，我们可以理解，如果我们有一个长事务（有可能事务状态为inactive），并且事务访问了一些未被任何其他事务使用的行，那么这不会影响历史列表......事实并非如此！
当启用后，这些指标可在INFORMATION_SCHEMA.INNODB_METRICS表中找到或在SHOW ENGINE INNODB STATUS\G的输出中获取：
MySQL> select * from INFORMATION_SCHEMA.INNODB_METRICS
where name='trx_rseg_history_len'\G
*************************** 1. row ***************************
NAME: trx_rseg_history_len
SUBSYSTEM: transaction
COUNT: 8319
MAX_COUNT: 92153
MIN_COUNT: 7
AVG_COUNT: NULL
COUNT_RESET: 8319
MAX_COUNT_RESET: 92153
MIN_COUNT_RESET: 7
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-05-25 10:23:17
TIME_DISABLED: NULL
TIME_ELAPSED: 135495
TIME_RESET: NULL
STATUS: enabled
TYPE: value
COMMENT: Length of the TRX_RSEG_HISTORY list
MySQL> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-27 00:01:46 139760858244672 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4146 srv_active, 0 srv_shutdown, 76427 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5954
OS WAIT ARRAY INFO: signal count 60629
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 903438
Purge done for trx's n:o < 883049 undo n:o < 0 state: running but idle
History list length 9746
趋势图
让我们看看下面这个图
我们可以看到历史列表长度(History List Lengt，也就是字段trx_rseg_history_len)的值是线性增加，但是数据库的负载不是这样的。如下所示：
当HLL在一段时间内增长非常明显时，它意味着InnoDB因为一个或多个长事务尚未提交或者被放弃，从而也没有回滚。它导致InnoDB保留了大量的行版本记录而没有清除它们。
在MySQL中启动一个事务然后执行一个简单的SELECT就会启动这个 MVCC 机制。
Daniel Nichter 在他的《Efficient MySQL Performance》一书中解释说，正常的innodb.trx_rseg_history_len的值应该小于1,000。如果超过 100,000，就会出现问题，应该发出警报。
我建议你阅读Daniel 的书的第 276 页“MVCC和回滚日志(MVCC and the Undo Logs)”一章。
HLL大小很重要！
需要了解的重点是，MySQL 中未显示HLL的更改量，因此HLL表示大量变更，而不是变更的大小。因此，如果这些变更中包含了巨大的 blob数据，那么即使少于1,000 的变更也可能有问题。
让我们再次看一下接下来 10 分钟的历史列表长度：
我们可以看到，一旦我们停止保持打开状态（休眠状态）的事务，所有问题几乎都会立即得到解决！
我们使用sysbennch OLTP insert构造工作负载（不是使用employees数据库），我们使用employees数据库创建了一个长事务。这个长事务语句是
译者注：这里的翻译让我矛盾，尽量理解原文
The workload is sysbench OLTP insert (not using the employees database) and we created a long transaction using the employees database. This long transaction statement was:
MySQL> start transaction;
MySQL> select * from employees.titles limit 10;
+--------+-----------------+------------+------------+
| emp_no | title | from_date | to_date |
+--------+-----------------+------------+------------+
| 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
| 10002 | Staff | 1996-08-03 | 9999-01-01 |
| 10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
| 10004 | Engineer | 1986-12-01 | 1995-12-01 |
| 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
| 10005 | Senior Staff | 1996-09-12 | 9999-01-01 |
| 10005 | Staff | 1989-09-12 | 1996-09-12 |
| 10006 | Senior Engineer | 1990-08-05 | 9999-01-01 |
| 10007 | Senior Staff | 1996-02-11 | 9999-01-01 |
| 10007 | Staff | 1989-02-10 | 1996-02-11 |
+--------+-----------------+------------+------------+
10 rows in set (0.0002 sec)
MySQL> We did nothing for 10 minutes
MySQL> rollback;
下面的图表表示在 sysbench OLT 读/写 10 分钟内中间同一事务空闲了4分钟：
较大的 HLL 究竟意味着什么？
History List Length 增加的原因是InnoDB Purge 活动滞后！
清除线程负责清空和截断Undo表空间（参考手册）。
造成清除过程如此滞后的原因是什么？
写入活动过高，清除线程无法快速处理 长时间运行的事务阻止了清除，并且清除操作直到事务完成才会进行
我们稍后会看到如何处理这个问题，但首先让我们看一下性能。
性能
即使 HLL 不会直接影响性能，当需要遍历大量版本的行时，它可能会出现问题。
让我们用上面的例子来看一下这种行为。如果我们在启动事务时执行SELECT语句，打开（放弃）的长事务，请注意 HLL 的大小和执行时间：
MySQL> SELECT id, k, (
SELECT count FROM information_schema.innodb_metrics
WHERE name='trx_rseg_history_len') HLL
FROM sbtest.sbtest1 WHERE c LIKE '36%' LIMIT 10;
+-----+-------+-----+
| id | k | HLL |
+-----+-------+-----+
| 10 | 34610 | 98 |
| 288 | 561 | 98 |
| 333 | 54800 | 98 |
| 357 | 96142 | 98 |
| 396 | 82983 | 98 |
| 496 | 65614 | 98 |
| 653 | 38837 | 98 |
| 684 | 61922 | 98 |
| 759 | 8758 | 98 |
| 869 | 50641 | 98 |
+-----+-------+-----+
10 rows in set (0.0006 sec)
如果我们稍后在同一事务中再试一次（我们没有回滚或提交它），同样的查询我们会注意到一些不同的东西：
MySQL> SELECT id, k, (
SELECT count FROM information_schema.innodb_metrics
WHERE name='trx_rseg_history_len') HLL
FROM sbtest.sbtest1 WHERE c LIKE '36%' LIMIT 10;
+-----+-------+--------+
| id | k | HLL |
+-----+-------+--------+
| 10 | 34610 | 391836 |
| 288 | 561 | 391836 |
| 333 | 54800 | 391836 |
| 357 | 96142 | 391836 |
| 396 | 82983 | 391836 |
| 496 | 65614 | 391836 |
| 653 | 38837 | 391836 |
| 684 | 61922 | 391836 |
| 759 | 8758 | 391836 |
| 869 | 50641 | 391836 |
+-----+-------+--------+
10 rows in set (1.9848 sec)
当历史列表长度很大时，查询速度会变得更慢。
正如Jeremy Cole 的这篇精彩文章[2]所解释的那样，在写入密集型数据库中，如果历史列表长度过大，可能需要将大量行的版本恢复为非常旧的版本。这会减慢事务本身的速度，在最坏的情况下，可能意味着写入密集型数据库中运行时间非常长的查询实际上永远无法完成；它们运行的时间越长，读取的成本就越高。
拥有较大的 HLL 意味着Undo日志也会增加。使用 MySQL 8.0，您可以更好地控制Undo日志表空间（请参阅手册），但您仍然需要监视磁盘空间！
解决方案
如果 HLL 正在增长，第一步是确定系统正在经历上述两个原因中的哪一个。
清除线程无法跟得上大量写入
如果清除线程无法跟上写入的工作负载，则需要限制写入活动。
在 MySQL 8.0 中，可以为 InnoDB 配置最大清除滞后变量：innodb_max_purge_lag。
当清除滞后/延迟超过innodb_max_purge_lag的阈值时，将对INSERT、UPDATE和DELETE操作施加延迟，以便清除处理有时间赶上。
在某些极其罕见的情况下，延迟会变得太高，这就是为什么您也可以使用来限制它innodb_max_purge_lag_delay。
与 InnoDB 的 Purge 相关的另一个可调设置是innodb_purge_threads， 它表示专用于 Purge 操作的后台线程数。
没有推荐的理想数字，像之前一样，这取决于实际情况
手册对这一点解释得很好：
如果innodb_max_purge_lag超出设置，则清除工作将自动在可用的清除线程之间重新分配。在这种情况下，过多的活动清除线程可能会导致与用户线程争用，因此请合理的管理设置innodb_purge_threads。
如果 DML 操作集中在少数几个表上，请将innodb_purge_threads设置保持在较低水平，以便线程不会相互争用对繁忙表的访问权。如果 DML 操作分布在许多表上，请考虑更高的innodb_purge_threads设置。清除线程的最大值为 32。
该innodb_purge_threads设置是允许的最大清除线程数。清除系统会自动调整使用的清除线程数。
长事务
如前所述，长事务（即使是休眠/停滞的事务）都会阻止清除，并且无论写入工作负载如何，即使工作负载非常低，HLL 也会在该事务的整个生命周期内持续增长。
解决此问题的唯一方法是停止那些长事务（提交、回滚、终止）。
为了找到此类长时间运行的事务，可以使用以下SQL查询Performance_Schema：
MySQL> SELECT ROUND(trx.timer_wait/1000000000000,3) AS trx_runtime_sec,
format_pico_time(trx.timer_wait) AS trx_runtime,
processlist_id, trx.thread_id AS thread_id,
trx.event_id AS trx_event_id, trx.isolation_level,
trx.autocommit, stm.current_schema AS db,
stm.sql_text AS query,
stm.rows_examined AS rows_examined,
stm.rows_affected AS rows_affected,
stm.rows_sent AS rows_sent,
IF(stm.end_event_id IS NULL, 'running', 'done') AS exec_state,
ROUND(stm.timer_wait/1000000000000,3) AS exec_time
FROM performance_schema.events_transactions_current trx
JOIN performance_schema.events_statements_current stm USING (thread_id)
JOIN threads USING (thread_id)
WHERE trx.state = 'ACTIVE' AND trx.timer_wait > 1000000000000 * 1\G
*************************** 1. row ***************************
trx_runtime_sec: 1040.443
trx_runtime: 17.34 min
processlist_id: 107
thread_id: 147
trx_event_id: 73
isolation_level: REPEATABLE READ
autocommit: NO
db: sbtest
query: select * from employees.titles limit 10
rows_examined: 10
rows_affected: 0
rows_sent: 10
exec_state: done
exec_time: 0.000
1 row in set (0.0004 sec)
如果状态和查询在多次运行之间没有变化，则可以认为该查询已停滞或被放弃。DBA 应该采取行动并终止该查询。
隔离级别也会影响这一点，我建议使用READ-COMMITTED隔离级别而不是默认值REPEATABLE-READ隔离级别，因为它有助于减少 HLL。
实际上，使用READ-COMMITTED事务隔离级别，会为每个 SQL 语句生成一个新的读取视图，并且仅在其持续时间内保持活动状态，而不是REPEATABLE-READ隔离级别的读取视图的生命周期与整个事务绑定在一起。这意味着，REPEATABLE-READ如前面的示例所示，如果您启动事务并执行一个 SELECT 并去喝咖啡，您仍然会阻止撤消日志清除，但使用READ-COMMITTED只要查询完成，Undo日志清除就不再受阻。
READ-COMMITTED隔离级别一直更好吗？
DimitriK 指出，正如他在这篇文章中所解释的那样，使用隔离级别READ-COMMITTED还有一些注意事项。这是您需要探索的东西，也许只为那些长事务的会话更改隔离级别，如果您能承受脏读，最好使用隔离级别READ-UNCOMMITTED。
回收Undo日志的磁盘空间
在 MySQL 8.0 中，我们有两种方法来截断Undo表空间以回收磁盘空间，可以单独使用也可以组合使用来管理Undo表空间大小。
第一种方法是通过启用innodb_undo_log_truncate来自动实现的，现在默认情况下已启用。
第二种是手动的，DBA 可以使用 SQL 语句将Undo日志表空间标记为非活动状态。允许完成使用该特定表空间中的回滚段的所有事务。事务完成后，清除系统将释放撤消表空间中的回滚段，然后将其截断为其初始大小，撤消表空间状态从inactive变为empty。
始终需要两个Undo日志，因此，当您将Undo日志表空间设置为非活动状态时，必须至少有 3 个活动的Undo日志（包括设置为非活动的Undo日志）。
手动SQL语句为：
MySQL> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
可以通过运行以下查询来列出Undo日志表空间及其状态：
MySQL> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
还有一些与Undo表空间相关的状态变量：
MySQL> SELECT * FROM global_status WHERE variable_name
LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+----------------+
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
+----------------------------------+----------------+
以上输出是 MySQL 8.0 的默认设置。如果我们想设置 inactive innodb_undo_001，我们会得到以下错误：
ERROR 3655 (HY000): Cannot set innodb_undo_001 inactive since there
would be less than 2 undo tablespaces left active
因此我们需要首先使用以下语句创建另一个Undo表空间：
MySQL> CREATE UNDO TABLESPACE my_undo_003 ADD DATAFILE 'my_undo_003.ibu';
Query OK, 0 rows affected (0.47 sec)
在文件系统上，我们可以看到新添加的表空间：
[root@imac ~]# ls /var/lib/mysql/*undo* -lh
-rw-r----- 1 mysql mysql 16M May 31 20:13 /var/lib/mysql/my_undo_003.ibu
-rw-r----- 1 mysql mysql 32M May 31 20:12 /var/lib/mysql/undo_001
-rw-r----- 1 mysql mysql 32M May 31 20:13 /var/lib/mysql/undo_002
现在我们可以将其设置为非活动状态：
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 row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | empty |
| innodb_undo_002 | active |
| my_undo_003 | active |
+-----------------+--------+
3 rows in set (0.00 sec)
当它为空时，我们可以将其设置回活动状态，并且如果我们愿意，我们也可以像下面这样删除多余的Undo表空间：
MySQL> ALTER UNDO TABLESPACE my_undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
MySQL> DROP UNDO TABLESPACE my_undo_003;
Query OK, 0 rows affected (0.01 sec)
MySQL> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
2 rows in set (0.00 sec)
结论
现在您明白了为什么监控 InnoDB 历史列表长度很重要，并且如果它增加太多，请确定清除线程是否无法满足写入工作负载的要求，或者某些长事务是否完全阻塞了 InnoDB 清除工作。
另一种方法是手动的，使用 SQL 语句执行。
1: https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-history-list-length/
[2]1: https://blog.jcole.us/2014/04/16/the-basics-of-the-innodb-undo-logging-and-history-system/
