MySQL 8 撤消日誌在過度增長後不會被截斷
顯然我在
SELECT20 天前開始了一個從未完成的查詢。即使客戶端已斷開連接，它也會繼續執行，並且執行時間很長，以至於其中一個撤消日誌的大小增加到 230 GB。（有問題的數據庫是 320 GB 的數據）。我已經強制終止查詢，但撤消日誌不會縮小到正常大小，導致磁碟已滿。作為記錄，這是磁碟上的大小：
root@the-db:/var/lib/mysql# du -h undo_00* erik_* 11G undo_001 244G undo_002 1.5G erik_temporary_undo_004.ibu 22G erik_undo_003.ibu一件有趣的事情是所有撤消日誌都非常龐大？
我試圖理解文件，但無法讓它工作：https ://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
執行版本：
root@the-db:/var/lib/mysql# mysql --version mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)自動截斷
如果我理解正確，要讓清除執行緒自動截斷撤消日誌，需要滿足三個條件：
Step 1. undo log需要大於
@@innodb_max_undo_log_size，即：mysql> SELECT @@innodb_max_undo_log_size; +----------------------------+ | @@innodb_max_undo_log_size | +----------------------------+ | 1073741824 | +----------------------------+步驟 2.
@@innodb_undo_log_truncate需要ONmysql> select @@innodb_undo_log_truncate; +----------------------------+ | @@innodb_undo_log_truncate | +----------------------------+ | 1 | +----------------------------+第 3 步。 總共需要有 2 個活動的撤消日誌
Automated truncation of undo tablespaces requires a minimum of two active undo tablespaces（不確定這些是否意味著 2 個活動*，然後再將*一個從輪換中取出以進行截斷）。mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME; +-------------------------+------------+----------+ | NAME | SPACE_TYPE | STATE | +-------------------------+------------+----------+ | erik_temporary_undo_003 | Undo | active | | erik_temporary_undo_004 | Undo | active | | innodb_undo_001 | Undo | active | | innodb_undo_002 | Undo | inactive | +-------------------------+------------+----------+根據上面，我們有 3 個活動日誌和 4 個總撤消日誌。
然而，似乎什麼都沒有發生。
手動截斷
還有一個用於截斷日誌的手動過程，該過程具有另一組先決條件。
步驟 1 和 2 與自動截斷相同。
步驟 3 的不同之處在於
Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces（仍然不確定這是否意味著 3 在一個被取消旋轉之前處於活動狀態）。為了解決我的不確定性，我創建了 2 個額外的撤消表空間。CREATE UNDO TABLESPACE erik_temporary_undo_003 ADD DATAFILE 'erik_undo_003.ibu'; CREATE UNDO TABLESPACE erik_temporary_undo_004 ADD DATAFILE 'erik_temporary_undo_004.ibu';第 4 步是停用需要截斷的表空間：
ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;如果我理解正確，這將觸發截斷，完成
STATUS後將是empty. 同樣，這是目前狀態：mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME; +-------------------------+------------+----------+ | NAME | SPACE_TYPE | STATE | +-------------------------+------------+----------+ | erik_temporary_undo_003 | Undo | active | | erik_temporary_undo_004 | Undo | active | | innodb_undo_001 | Undo | active | | innodb_undo_002 | Undo | inactive | +-------------------------+------------+----------+大約 3 天前它被設置為非活動狀態，此後一直沒有改變。
一件奇怪的事情是，根據以下查詢，只有 2 個活動撤消日誌，而上一個查詢顯示 3。我錯過了什麼嗎？
mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_undo_tablespaces_total | 4 | | Innodb_undo_tablespaces_implicit | 2 | | Innodb_undo_tablespaces_explicit | 2 | | Innodb_undo_tablespaces_active | 2 | +----------------------------------+-------+
Innodb_undo_tablespaces_active至少不應該是3嗎？頻率
文件提到
innodb_purge_rseg_truncate_frequency了一種讓清除執行緒更頻繁地執行的方法。預設值為128，範例顯示將其設置為32. 就時間而言，這實際上意味著什麼還不清楚。它只是提到“每 32 次執行”。To increase that frequency, decrease the innodb_purge_rseg_truncate_frequency setting. For example, to have the purge thread look for undo tabespaces once every 32 timees[sic] that purge is invoked, set innodb_purge_rseg_truncate_frequency to 32.為了更好地衡量，我將其設置為 1。
mysql> show variables like "%truncate%"; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_purge_rseg_truncate_frequency | 1 | | innodb_undo_log_truncate | ON | +--------------------------------------+-------+指標
我最近才了解如何獲取 InnoDB 指標：
mysql> set global innodb_monitor_enable = all;並且會在指標可用時更新。
相關配置
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 | +--------------------------+------------+ mysql> show variables like "%truncate%"; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | innodb_purge_rseg_truncate_frequency | 1 | | innodb_undo_log_truncate | ON | +--------------------------------------+-------+為了讓 MySQL 截斷撤消日誌大小，我缺少什麼？
我只是假設截斷意味著磁碟上的實際文件會縮小。或許還有別的意思？
在與不斷增長的撤消表空間鬥爭了幾天之後，我們終於弄明白了，所以我要分享結果：
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;我們有 2/2 活動（預設）撤消表空間。1 個大約 1GB，另一個 90GB 並且還在增長。
每個文件：
駐留在選定撤消表空間中的回滾段變為非活動狀態，因此它們不會分配給新事務。目前正在使用回滾段的現有事務被允許完成。
那部分是關鍵。據我了解，所有交易都需要在清理之前完成。
我們查找所有正在執行的事務：
SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 SECOND AND ps.user != 'system_user';在交易列表中，有一個是 2 天前的。
如果您啟用了性能架構，您可以獲得保存它的程序和查詢：
SELECT * FROM performance_schema.threads WHERE processlist_id = thread_id;我們終止了該程序，一切都在 20 分鐘內恢復。所有儲存都被回收。