技術分享 | undo 太大了怎麼辦

愛可生雲資料庫發表於2022-07-08

作者：王雨晨

愛可生資料庫工程師，負責 MySQL 日常維護及 DMP 產品支援。

本文來源：原創投稿

*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

問題背景

有使用者在使用 MySQL5.7 的資料庫時，遇到 undo 暴漲情況，經排查存在一條慢 SQL 執行了上萬秒仍沒有結束，導致後續事務產生的 undo 不能清理，越來越多

線上 truncate undo log 已開啟，將慢 SQL kill 掉之後，undo 大小超過 innodb_max_undo_log_size 設定的大小，但 undo 檔案沒有立即收縮

測試驗證

測試引數如下，開啟 innodb_undo_log_truncate

mysql> show variables like '%undo%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_max_undo_log_size | 104857600 |
| innodb_undo_directory    | ./        |
| innodb_undo_log_truncate | ON        |
| innodb_undo_logs         | 128       |
| innodb_undo_tablespaces  | 3         |
+--------------------------+-----------+
5 rows in set (0.00 sec)

模擬 undo 增長，超過 innodb_max_undo_log_size 設定大小

# du -sh ./undo*
152M    ./undo001
296M    ./undo002
15M     ./undo003

檢視官方文件undo清理策略，簡單概括為以下：

1、啟用 innodb_undo_log_truncate 後，超過 innodb_max_undo_log_size 設定大小的undo表空間被標記為截斷

2、被標記的undo表空間的回滾段被設定為不活躍的，不能分配給新的事務

3、purge執行緒釋放不需要的回滾段

4、釋放回滾段後，undo表空間被截斷為初始大小10M

可以看到在收縮undo大小前，需要purge執行緒先釋放回滾段，這裡涉及另一個引數 innodb_purge_rseg_truncate_frequency，預設值128，表示purge執行緒每呼叫128次，就釋放回滾段一次

此次問題背景中，該引數設定的是預設值

mysql> show variables like 'innodb_purge_rseg_truncate_frequency';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
+--------------------------------------+-------+
1 row in set (0.01 sec)

所以為了儘快收縮 undo 檔案，我們可以將 innodb_purge_rseg_truncate_frequency 值調小，提高 purge 執行緒釋放回滾段的頻率

//調小該值
mysql> show variables like 'innodb_purge_rseg_truncate_frequency';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 16    |
+--------------------------------------+-------+
1 row in set (0.01 sec)

//達到purge執行緒呼叫次數，釋放回滾段，undo表空間被截斷
# du -sh ./undo*
10M     ./undo001
10M     ./undo002
15M     ./undo003

MySQL8.0新增 Manual Truncation

MySQL8.0 新增支援使用 SQL 語句來管理 undo 表空間

1、需要至少三個活躍的 undo 表空間，因為要保證有兩個活躍的 undo 表空間來支援 Automated Truncation

手工建立一個 undo 表空間，必須以 .ibu 結尾

mysql> create undo tablespace undo_003 add datafile '/data/mysql/data/3307/undo_003.ibu';
Query OK, 0 rows affected (0.27 sec)
//三個處於 active 狀態的 undo 表空間
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.00 sec)

2、手工截斷 undo 表空間，需要先將 undo 表空間設定為 inactive

//模擬 undo 增長
# du -sh ./undo*
81M     ./undo_001
157M    ./undo_002
26M     ./undo_003.ibu

mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.01 sec)

3、手工設定 inactive 後，undo 表空間被標記為截斷，purge 執行緒會增加返回頻率，快速清空並最終截斷 undo 表空間，狀態變為 empty

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | empty  |
| undo_003        | active |
+-----------------+--------+
//undo 檔案收縮
# du -sh ./undo*
81M     ./undo_001
2.1M    ./undo_002
26M     ./undo_003.ibu

4、empty 狀態的 undo 表空間可以重新啟用使用

mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.01 sec)

5、MySQL8.0 支援刪除表空間，但前提是該表空間為 empty 狀態

mysql> ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | empty  |
+-----------------+--------+
3 rows in set (0.01 sec)

mysql> DROP UNDO TABLESPACE undo_003;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME  |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.01 sec)

相關文章