Tối ưu Undo Tablespace MySQL 8: Kiểm soát History List Length và thu hồi dung lượng đĩa - ITFROMZERO - Share tobe shared! Hướng dẫn thực tế xử lý Undo Tablespace MySQL 8 phình to do long-running transactions — từ cách phát hiện History List Length cao, cấu hình purge threads, đến truncate undo tablespace và thu hồi dung lượng đĩa trong môi trường production.

Table of Contents

Vấn đề thực tế: Undo Tablespace ăn hết dung lượng ổ đĩa

Database production của mình chạy MySQL 8.0 với khoảng 50GB data, và một buổi sáng thức dậy thấy cảnh báo: ổ đĩa còn 2GB trong khi hôm qua vẫn còn hơn 20GB. File undo_001 và undo_002 trong thư mục /var/lib/mysql đã phình lên hơn 18GB mỗi file.

Nếu bạn đã từng gặp cảnh tương tự, hoặc bạn thấy query ngày càng chậm dù đã có index đầy đủ, rất có thể thủ phạm là History List Length (HLL) tăng cao do undo log tích tụ.

Vấn đề này không hiếm trong môi trường production — đặc biệt khi có batch job chạy transaction dài, hoặc một developer quên commit/rollback khi debug trực tiếp trên DB production.

Nguyên nhân: Tại sao HLL tăng và undo không được thu hồi?

MySQL InnoDB dùng MVCC (Multi-Version Concurrency Control) để cho phép nhiều transaction đọc dữ liệu đồng thời mà không cần lock. Mỗi khi bạn UPDATE hay DELETE một row, InnoDB không xóa dữ liệu cũ ngay — nó ghi phiên bản cũ vào undo log để các transaction đang chạy vẫn thấy được snapshot nhất quán của mình.

Vấn đề xảy ra khi:

Một transaction mở ra nhưng không commit/rollback trong thời gian dài
Batch job chạy UPDATE hàng triệu row trong một transaction duy nhất
Replication slave lag khiến slave vẫn cần đọc undo log cũ từ master

MySQL có purge thread chịu trách nhiệm dọn undo log cũ, nhưng nó chỉ có thể dọn những phần không còn transaction nào cần đọc nữa. Nếu có một transaction cũ vẫn đang mở, purge thread bị chặn hoàn toàn — HLL tăng vô hạn, undo file phình to.

Kiểm tra HLL ngay bây giờ:

-- Xem History List Length hiện tại
SHOW ENGINE INNODB STATUS\G
-- Tìm dòng: History list length XXXX

-- Hoặc query trực tiếp từ performance_schema
SELECT
  NAME,
  SUBSYSTEM,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';

HLL dưới 1000 là bình thường. Từ 10.000 trở lên bắt đầu ảnh hưởng hiệu năng. Trên 100.000 thì query sẽ chậm rõ rệt vì mỗi lần đọc dữ liệu, InnoDB phải duyệt qua hàng loạt version cũ trong undo log để tìm đúng snapshot cho transaction hiện tại.

Các cách giải quyết

1. Phát hiện transaction đang chặn purge

Trước tiên, tìm ngay transaction nào đang giữ undo log lâu nhất:

-- Tìm long-running transactions (đang chạy > 60 giây)
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_query,
  trx_tables_in_use,
  trx_tables_locked
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started ASC;

-- Tìm thread ID để kill nếu cần
SELECT
  p.id AS process_id,
  p.user,
  p.host,
  p.db,
  p.command,
  p.time,
  p.info
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 60;

Nếu thấy transaction đã chạy vài tiếng mà không có lý do gì hợp lý, kill nó:

KILL 12345; -- thay 12345 bằng process_id tìm được ở trên

2. Tăng số lượng purge threads

Mặc định MySQL chỉ dùng 4 purge threads. Với workload write nặng, tăng lên giúp dọn undo log nhanh hơn:

-- Xem cấu hình hiện tại
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- Tăng lên 8 (không cần restart, có hiệu lực ngay)
SET GLOBAL innodb_purge_threads = 8;

Thêm vào /etc/mysql/mysql.conf.d/mysqld.cnf để persistent:

[mysqld]
innodb_purge_threads = 8
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 300000

innodb_max_purge_lag_delay giới hạn thời gian delay tối đa (microseconds) mà InnoDB áp vào DML operations khi HLL quá cao. Đặt 300000 (0.3 giây) giúp tránh system bị treo hoàn toàn trong trường hợp purge lag lớn.

3. Truncate Undo Tablespace để thu hồi dung lượng đĩa

Đây là tính năng mới của MySQL 8 — rất hữu ích sau khi HLL đã được xử lý nhưng file undo vẫn còn lớn trên đĩa.

Trước tiên, xem có bao nhiêu undo tablespace:

-- Xem trạng thái các undo tablespace
SELECT
  TABLESPACE_NAME,
  FILE_NAME,
  AUTOEXTEND_SIZE,
  TOTAL_EXTENTS,
  EXTENT_SIZE,
  (TOTAL_EXTENTS * EXTENT_SIZE) / 1024 / 1024 AS size_mb
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

MySQL 8 hỗ trợ truncate tự động qua biến innodb_undo_log_truncate:

-- Bật tự động truncate (mặc định đã ON trong MySQL 8)
SET GLOBAL innodb_undo_log_truncate = ON;

-- Đặt ngưỡng truncate: khi undo file > 128MB thì truncate
SET GLOBAL innodb_max_undo_log_size = 134217728; -- 128MB

-- Xem trạng thái truncate
SELECT
  NAME,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%undo%truncate%';

Thêm vào config file để persistent:

[mysqld]
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728
innodb_purge_rseg_truncate_frequency = 128

innodb_purge_rseg_truncate_frequency quyết định tần suất purge thread kiểm tra xem có cần truncate không. Giá trị càng nhỏ, truncate xảy ra càng thường xuyên (mặc định 128).

4. Giám sát HLL liên tục

Viết script monitoring đơn giản để alert khi HLL vượt ngưỡng:

#!/bin/bash
# /usr/local/bin/check_mysql_hll.sh

MYSQL_CMD="mysql -u monitor_user -pYOUR_PASSWORD -N -e"
THRESHOLD=10000

HLL=$($MYSQL_CMD "SELECT COUNT FROM performance_schema.innodb_metrics WHERE NAME = 'trx_rseg_history_len'" 2>/dev/null)

if [ -z "$HLL" ]; then
  echo "ERROR: Cannot connect to MySQL"
  exit 2
fi

if [ "$HLL" -gt "$THRESHOLD" ]; then
  echo "WARNING: MySQL HLL = $HLL (threshold: $THRESHOLD)"
  # Gửi alert qua Telegram, Slack, v.v.
  exit 1
fi

echo "OK: MySQL HLL = $HLL"
exit 0

Thêm vào crontab để chạy mỗi 5 phút:

*/5 * * * * /usr/local/bin/check_mysql_hll.sh >> /var/log/mysql_hll_check.log 2>&1

Cách tốt nhất: Kết hợp phòng ngừa + tự động thu hồi

Sau khi trải qua sự cố undo tablespace phình to, mình đúc kết ra quy trình sau là hiệu quả nhất:

Bước 1 — Cấu hình MySQL ngay từ đầu (thêm vào mysqld.cnf):

[mysqld]
# Undo tablespace tự động truncate
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728

# Tăng purge threads cho write-heavy workload
innodb_purge_threads = 8
innodb_purge_rseg_truncate_frequency = 64

# Giới hạn delay khi HLL cao
innodb_max_purge_lag_delay = 300000

# Tự động kill transaction chạy quá lâu
# (MySQL 8.0.26+)
innodb_lock_wait_timeout = 50
wait_timeout = 28800
interactive_timeout = 28800

Bước 2 — Thêm constraint ở tầng application: Đừng để một transaction ôm quá nhiều row. Nếu cần UPDATE 1 triệu row, chia thành batch nhỏ 10.000 row mỗi lần:

-- Thay vì:
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';

-- Dùng batch loop:
SET @batch_size = 10000;
repeat_loop: LOOP
  UPDATE orders
  SET status = 'archived'
  WHERE created_at < '2023-01-01'
    AND status != 'archived'
  LIMIT @batch_size;
  
  IF ROW_COUNT() = 0 THEN
    LEAVE repeat_loop;
  END IF;
  
  -- Thêm sleep nhỏ để purge thread có thời gian dọn
  DO SLEEP(0.1);
END LOOP;

Bước 3 — Nếu undo file đã phình to và cần thu hồi ngay: MySQL 8 không thể shrink undo file tự động ngay lập tức nếu vẫn còn active transactions. Quy trình xử lý khẩn cấp:

-- 1. Kill tất cả long-running transactions
SELECT CONCAT('KILL ', p.id, ';')
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300;
-- Copy kết quả và chạy từng lệnh KILL

-- 2. Tạo undo tablespace mới (MySQL 8.0.14+)
CREATE UNDO TABLESPACE undo_003
  ADD DATAFILE 'undo_003.ibu';

-- 3. Đánh dấu undo tablespace cũ là INACTIVE để truncate
ALTER UNDO TABLESPACE undo_001 SET INACTIVE;
ALTER UNDO TABLESPACE undo_002 SET INACTIVE;

-- 4. Chờ MySQL tự truncate (theo dõi trạng thái)
SELECT TABLESPACE_NAME, STATE
FROM information_schema.INNODB_TABLESPACES
WHERE TABLESPACE_TYPE = 'UNDO';
-- Chờ cho đến khi STATE = 'empty'

-- 5. Sau khi empty, kích hoạt lại
ALTER UNDO TABLESPACE undo_001 SET ACTIVE;
ALTER UNDO TABLESPACE undo_002 SET ACTIVE;

-- 6. Drop tablespace tạm nếu không cần
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
-- Chờ empty rồi DROP
DROP UNDO TABLESPACE undo_003;

Optimization này đã giúp database production của mình ổn định hơn nhiều — query time giảm từ trung bình 200ms xuống còn 40-60ms sau khi HLL được kiểm soát dưới 1000, và ổ đĩa không còn bị ăn mòn bởi undo log không được dọn dẹp nữa.

Điều quan trọng nhất: đừng chờ đến khi sự cố xảy ra mới xử lý. Thêm monitoring HLL vào hệ thống alerting từ sớm, và review lại code batch job để đảm bảo không có transaction nào giữ lock quá 5 phút.