Posted inMySQL

Tối ưu hóa phân mảnh bảng MySQL InnoDB: Cách dùng OPTIMIZE TABLE thu hồi ổ đĩa

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Xóa hàng triệu dòng dữ liệu nhưng ổ đĩa máy chủ không hề giảm dung lượng

Nửa năm trước, phân vùng chứa database bên mình chạm ngưỡng cảnh báo đỏ: 95% disk space. Mình tự tin chạy lệnh DELETE dọn sạch 15 triệu bản ghi log cũ, nhẩm tính sẽ lấy lại ít nhất 40GB dung lượng. Nhưng khi câu lệnh hoàn tất, kiểm tra lại lệnh df -h trên server: ổ cứng không trống thêm dù chỉ 1MB.

Nếu bạn từng gặp cú sốc này, bạn không hề cô độc. Khi chạy lệnh DELETE hoặc cập nhật các cột có kích thước biến động (VARCHAR, TEXT, BLOB), InnoDB không trả lại dung lượng đĩa cho hệ điều hành (OS). Thay vào đó, nó chỉ đánh dấu các block đó là trống để dùng lại sau. Hiện tượng này tạo ra những “lỗ hổng” rỗng bên trong data pages, hay còn gọi là Table Fragmentation (Phân mảnh bảng).

Hậu quả không chỉ dừng lại ở việc tốn tiền mua thêm ổ cứng SSD NVMe. Nguy hiểm hơn, nó làm tụt hiệu năng truy vấn. Thay vì đọc 1.000 records từ 10 data pages, MySQL phải scan tới 50 pages vì một nửa dung lượng bên trong toàn là khoảng trống vô nghĩa, trực tiếp làm tắc nghẽn Buffer Pool.

Cơ chế phân mảnh của MySQL InnoDB hoạt động thế nào?

Để xử lý triệt để mà không làm sập production, trước hết bạn cần hiểu cách InnoDB tổ chức file vật lý.

1. Điều kiện tiên quyết: Cấu hình innodb_file_per_table

InnoDB quản lý dữ liệu thông qua tablespace. Mặc định từ MySQL 5.6 trở đi, thông số innodb_file_per_table luôn được bật (ON). Khi đó, mỗi bảng sẽ là một file .ibd riêng biệt trên ổ đĩa (thường nằm tại /var/lib/mysql/ten_database/).

SHOW VARIABLES LIKE 'innodb_file_per_table';

Trường hợp biến này đang là OFF, mọi dữ liệu bảng và index đều bị nhét chung vào file ibdata1 (system tablespace). Với file ibdata1, một khi đã phình to thì không có bất kỳ lệnh SQL nào co nhỏ lại được. Lựa chọn duy nhất lúc đó là dump toàn bộ database, xóa file, khởi động lại MySQL và import dữ liệu từ đầu.

2. Cơ chế phân mảnh trang (Page Fragmentation)

Dữ liệu trong InnoDB được tổ chức thành các Page cố định 16KB. Khi một bản ghi bị xóa, InnoDB đánh dấu bản ghi đó là deleted (gọi là delete-mark). Không gian này được đưa vào danh sách chờ tái sử dụng cho các lệnh INSERT kế tiếp.

File .ibd vật lý ở tầng OS sẽ giữ nguyên kích thước. Nếu ứng dụng không ghi thêm dữ liệu mới để bù vào các khoảng trống, file .ibd sẽ mãi giữ kích thước khổng lồ so với dung lượng thực tế đang lưu.

Hướng dẫn đo lường và dọn phân mảnh từng bước

Bước 1: Quét tỷ lệ phân mảnh và dung lượng lãng phí

Đừng chạy tối ưu mù quáng trên toàn bộ database. Hãy truy vấn bảng metadata information_schema.TABLES để tìm ra những bảng đang giữ nhiều dung lượng dư thừa (DATA_FREE) nhất.

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Total_Size_MB`,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS `Free_Space_MB`,
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS `Fragment_Percent`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND DATA_FREE > 0
ORDER BY 
    DATA_FREE DESC;

Con số trong cột Free_Space_MB chính là lượng dung lượng bạn có thể giải phóng về cho hệ điều hành sau khi tối ưu.

Bước 2: Thu hồi ổ đĩa bằng lệnh OPTIMIZE TABLE

Khi phát hiện một bảng có Fragment_Percent vượt quá 20% và Free_Space_MB từ vài GB trở lên, bạn có thể thực hiện tối ưu bảng:

OPTIMIZE TABLE ten_database.ten_bang;

Sau khi chạy trên bảng InnoDB, MySQL sẽ trả về kết quả:

+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| ten_database.ten_bang | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ten_database.ten_bang | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+

Thông báo “Table does not support optimize…” trông có vẻ đáng ngờ, nhưng đây là phản hồi hoàn toàn bình thường. Thực chất, InnoDB không hỗ trợ cơ chế optimize tại chỗ của MyISAM cũ. Thay vào đó, nó ngầm chạy lệnh sau:

ALTER TABLE ten_database.ten_bang ENGINE=InnoDB;

Engine sẽ tạo một file .ibd tạm, sao chép toàn bộ dữ liệu thực sang (loại bỏ hoàn toàn các trang trống), sắp xếp lại index rồi đổi tên file mới thay thế file cũ.

Bước 3: Nguyên tắc an toàn khi thao tác trên Production

Tái tạo bảng (table rebuild) tiêu tốn rất nhiều I/O và CPU. Hãy ghi nhớ 4 lưu ý sống còn sau:

  • Dung lượng ổ cứng dự phòng: Server bắt buộc phải còn trống tối thiểu 1.5 đến 2 lần dung lượng bảng hiện tại. Ví dụ bảng nặng 80GB, ổ cứng phải còn trống ít nhất 120GB. Nếu hết đĩa giữa chừng, transaction sẽ rollback và hệ thống có thể bị treo.
  • Kiểm tra biến innodb_online_alter_log_max_size: MySQL hỗ trợ Online DDL (vẫn cho phép INSERT, UPDATE, DELETE trong lúc tạo lại bảng). Tuy nhiên, các thao tác ghi mới sẽ được buffer vào bộ nhớ tạm. Nếu lượng ghi vượt quá giá trị cấu hình (mặc định 128MB), lệnh sẽ văng lỗi ngay lập tức.
  • Giải pháp cho bảng lớn (>50GB): Chạy trực tiếp OPTIMIZE TABLE trên bảng lớn sẽ gây nghẽn I/O và dễ dẫn đến Replication Lag nghiêm trọng trên các Replica. Lúc này, hãy dùng công cụ pt-online-schema-change (trong bộ Percona Toolkit) hoặc gh-ost để copy dữ liệu theo từng chunk nhỏ.
  • Luôn có bản backup trước khi chạy: Dù hiếm gặp, sự cố đứt gãy I/O hay panic giữa quá trình rebuild vẫn có thể làm hỏng tablespace. Luôn tạo snapshot hoặc backup gần nhất trước khi bắt đầu.
# Lệnh tối ưu bảng lớn an toàn bằng pt-online-schema-change
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --execute D=ten_database,t=ten_bang

Tự động hóa theo dõi và dọn dẹp hợp lý

Tuyệt đối không đưa lệnh OPTIMIZE TABLE vào cron job chạy định kỳ cho toàn bộ cơ sở dữ liệu. Việc này vừa lãng phí I/O vừa tiềm ẩn rủi ro khóa tài nguyên.

Cách tiếp cận chuẩn: Viết một script kiểm tra hàng tuần, chỉ lọc ra các bảng có tỷ lệ phân mảnh trên 30% kèm dung lượng trống trên 5GB, sau đó gửi cảnh báo hoặc thực thi tuần tự vào khung giờ thấp điểm (khoảng 2h – 4h sáng). Nắm vững cơ chế tablespace sẽ giúp bạn kiểm soát dung lượng máy chủ một cách chủ động và bền vững.

Share:
Tags: