本文是翻譯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/