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