Query chạy 30 giây, server đứng hình — chuyện không hiếm gặp
Lần đầu mình gặp vấn đề này là khi một report query trên hệ thống e-commerce bắt đầu mất 30–40 giây để chạy. Server MySQL bình thường chạy ổn, nhưng đúng lúc cần xuất báo cáo cuối tháng là CPU tăng vọt, disk I/O đỏ rực trên Grafana. Khách hàng phàn nàn web chậm, thậm chí timeout.
Chạy SHOW PROCESSLIST thì thấy hàng chục query đang ở trạng thái Creating sort index hoặc Copying to tmp table on disk. Đó là lúc mình hiểu ra vấn đề: MySQL đang tạo temporary tables (bảng tạm) và ghi chúng xuống disk thay vì giữ trong RAM.
Mình từng gặp sự cố database corruption lúc 3 giờ sáng và phải restore từ backup — từ đó mình luôn kiểm tra backup hàng ngày. Nhưng vấn đề performance với temporary tables thì khác: nó không crash server ngay, nó chỉ làm server chậm dần cho đến khi người dùng không chịu được nữa.
Tại sao MySQL dùng bảng tạm và khi nào chúng bị ghi ra disk?
MySQL tự động tạo temporary tables nội bộ cho nhiều loại query: GROUP BY, ORDER BY trên cột không có index, DISTINCT, subquery trong mệnh đề FROM, UNION, và một số trường hợp khác.
Ban đầu, MySQL cố giữ bảng tạm trong RAM bằng engine MEMORY. Nhưng khi kích thước vượt quá giới hạn cấu hình, MySQL sẽ convert sang MyISAM (hoặc InnoDB từ MySQL 8.0) và ghi xuống disk. Đây chính là lúc hiệu năng tụt dốc vì disk I/O chậm hơn RAM hàng trăm lần.
Hai biến quyết định giới hạn này:
tmp_table_size— kích thước tối đa của một internal temporary table trong RAMmax_heap_table_size— giới hạn tối đa của bảng MEMORY engine
MySQL lấy giá trị nhỏ hơn trong hai biến trên làm giới hạn thực tế. Mặc định cả hai thường là 16MB — quá thấp cho các query phức tạp với dataset lớn. Kiểm tra ngay:
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';Chẩn đoán xem temporary tables có đang gây ra vấn đề không
Trước khi thay đổi bất cứ thứ gì, cần xác nhận đây đúng là nguyên nhân. Dùng SHOW STATUS để xem thống kê toàn server:
SHOW GLOBAL STATUS LIKE 'Created_tmp%';Output trả về dạng:
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1847 |
| Created_tmp_files | 23 |
| Created_tmp_tables | 9341 |
+-------------------------+-------+Tính tỷ lệ: Created_tmp_disk_tables / Created_tmp_tables. Nếu tỷ lệ này trên 10–15%, đang có vấn đề. Ví dụ trên là 1847/9341 ≈ 19.7% — khá cao. Reset counter để đo theo khoảng thời gian cụ thể:
FLUSH STATUS;
-- Chờ vài giờ rồi đo lại
SHOW GLOBAL STATUS LIKE 'Created_tmp%';Với từng query cụ thể, dùng EXPLAIN để xem có tạo bảng tạm không:
EXPLAIN SELECT category, COUNT(*), AVG(price)
FROM orders
GROUP BY category
ORDER BY AVG(price) DESC;Nếu cột Extra trong kết quả có Using temporary; Using filesort, query đó đang tạo bảng tạm và sắp xếp dữ liệu trên disk.
Các cách giải quyết — từ đơn giản đến sâu hơn
1. Tăng giới hạn bảng tạm trong RAM
Cách nhanh nhất. Sửa trong /etc/mysql/my.cnf:
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256MHoặc set ngay mà không cần restart (chỉ có hiệu lực cho session mới):
SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;Lưu ý quan trọng: con số này nhân với số connection đồng thời. Nếu có 100 connection và mỗi cái dùng 256MB temp table thì RAM tiêu thụ lý thuyết lên đến 25GB. Mình thường đặt 128–256MB cho server 16GB RAM với khoảng 50–100 connection đồng thời. Công thức tham khảo:
tmp_table_size ≤ (RAM × 0.25) ÷ max_connections2. Viết lại query để tránh sinh bảng tạm
Đây là giải pháp bền vững hơn. Nhiều trường hợp bảng tạm xuất hiện vì query chưa tối ưu.
Tránh SELECT * khi GROUP BY:
-- Tệ: kéo toàn bộ cột vào temp table rồi mới group
SELECT * FROM orders GROUP BY category;
-- Tốt: chỉ lấy đúng cột cần thiết
SELECT category, COUNT(*), SUM(amount)
FROM orders
GROUP BY category;Thay subquery trong FROM bằng CTE (MySQL 8.0+):
-- Cách cũ: derived table = temporary table ẩn
SELECT u.name, t.total
FROM users u
JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
) t ON u.id = t.user_id;
-- Tốt hơn với CTE — optimizer MySQL 8.0 xử lý hiệu quả hơn
WITH order_totals AS (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
)
SELECT u.name, ot.total
FROM users u
JOIN order_totals ot ON u.id = ot.user_id;3. Thêm index đúng chỗ để MySQL bỏ qua bảng tạm
MySQL cần tạo temporary table phần lớn khi không có index phù hợp cho ORDER BY hoặc GROUP BY. Thêm composite index thường giải quyết được:
-- Query hay tạo temp table
SELECT category, status, COUNT(*)
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY category, status;
-- Thêm composite index bao gồm cả cột WHERE lẫn GROUP BY
ALTER TABLE orders
ADD INDEX idx_date_cat_status (created_at, category, status);Sau khi thêm index, chạy lại EXPLAIN và kiểm tra xem Using temporary còn xuất hiện trong cột Extra không.
4. Dùng TempTable engine (MySQL 8.0+)
MySQL 8.0 giới thiệu TempTable engine thay thế MEMORY engine cũ. TempTable hiệu quả hơn với kiểu VARCHAR, TEXT, BLOB — những kiểu mà MEMORY engine không hỗ trợ tốt và hay bị forced xuống disk sớm hơn.
-- Kiểm tra engine đang dùng
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';
-- Đảm bảo dùng TempTable (mặc định MySQL 8.0)
SET GLOBAL internal_tmp_mem_storage_engine = 'TempTable';
-- Giới hạn RAM pool cho TempTable (mặc định 1GB)
SET GLOBAL temptable_max_ram = 1073741824;Cách tốt nhất: kết hợp đo — tối ưu — đo lại
Thực tế không có một giải pháp duy nhất giải quyết mọi trường hợp. Quy trình mình thường làm:
- Đo baseline: Chạy
FLUSH STATUS, để server chạy 24 giờ, rồi đo lạiCreated_tmp_disk_tables / Created_tmp_tables - Xác định query phạm nhân: Bật slow query log với
log_queries_not_using_indexes = ONtrongmy.cnfđể tìm query nào thường xuyên tạo temp table nhất - Tối ưu query trước: Xem có thể thêm index hoặc viết lại query không — ROI thường cao nhất ở bước này
- Điều chỉnh config: Sau khi đã tối ưu query, mới nâng
tmp_table_sizeđể xử lý những trường hợp không tránh được - Theo dõi lại: Đo lại sau vài ngày, so sánh tỷ lệ disk temp tables trước và sau
Sau lần tối ưu đó, tỷ lệ Created_tmp_disk_tables / Created_tmp_tables của hệ thống mình từ gần 20% xuống còn dưới 3%. Query báo cáo cuối tháng từ 30 giây xuống còn 2–3 giây. Disk I/O trên Grafana trở lại bình thường ngay trong ngày hôm đó.
Một điểm cần nhớ: temporary tables ra disk không phải lúc nào cũng tệ. Với dataset thực sự lớn, đôi khi không thể tránh hoàn toàn. Mục tiêu là giảm tần suất và kích thước của chúng xuống mức chấp nhận được, không phải loại bỏ tuyệt đối.
