TL;DR Sửa Nhanh
MySQL báo lỗi ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size vì sort buffer cấp phát cho mỗi query quá nhỏ. Chạy lệnh này trong session MySQL của bạn — không cần khởi động lại:
SET SESSION sort_buffer_size = 8 * 1024 * 1024; -- 8 MB
Vậy là xong cho bản sửa nhanh. Để giữ cài đặt sau khi khởi động lại, thêm vào /etc/mysql/mysql.conf.d/mysqld.cnf (hoặc /etc/my.cnf tùy distro) rồi khởi động lại MySQL.
Nguyên Nhân Gây Ra Lỗi Này
Mỗi query dùng ORDER BY trong MySQL cần bộ nhớ tạm để sắp xếp các hàng. Bộ nhớ đó lấy từ sort_buffer_size, mặc định chỉ 256 KB — một giá trị được đặt từ giữa thập niên 2000 khi tập kết quả còn nhỏ và chẳng ai để ý đến nó.
Ba nguyên nhân phổ biến:
- Sắp xếp hàng trăm nghìn hàng mà không có covering index
- Nhiều kết nối đồng thời cùng chạy các query nặng về sắp xếp — mỗi kết nối có sort buffer riêng, nên mức dùng bộ nhớ nhân lên rất nhanh
- Dùng
GROUP BY,DISTINCT, hoặcUNION— tất cả đều âm thầm sử dụng sort buffer bên trong
Kiểm tra cài đặt hiện tại:
SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
262144 bytes = 256 KB. Giá trị mặc định này không còn phù hợp với workload hiện đại nữa.
Cách 1: Tăng sort_buffer_size Cho Session Hiện Tại
Cần sửa nhanh mà không muốn động vào config server? Đặt ở cấp độ session:
-- Chỉ áp dụng cho kết nối này
SET SESSION sort_buffer_size = 8 * 1024 * 1024;
-- Bây giờ chạy query gốc của bạn
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000;
Cách này rất hữu ích trong các migration script hoặc tác vụ chạy một lần khi không thể khởi động lại MySQL.
Cách 2: Cố Định Trong my.cnf
Tìm file config MySQL. Đường dẫn khác nhau tùy hệ điều hành:
- Ubuntu/Debian:
/etc/mysql/mysql.conf.d/mysqld.cnf - CentOS/RHEL:
/etc/my.cnfhoặc/etc/mysql/my.cnf - Windows:
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
Thêm hoặc cập nhật giá trị dưới [mysqld]:
[mysqld]
sort_buffer_size = 4M
Sau đó khởi động lại:
# Ubuntu/Debian
sudo systemctl restart mysql
# CentOS/RHEL
sudo systemctl restart mysqld
Nên đặt bao nhiêu?
Tùy thuộc vào workload, nhưng đây là điểm khởi đầu hợp lý:
- 4 MB: Mặc định an toàn cho hầu hết ứng dụng
- 8–16 MB: Database nặng về báo cáo với các thao tác sắp xếp lớn
- Giới hạn tối đa 64 MB ở cấp global — cài đặt này tính theo từng kết nối, không dùng chung. 100 kết nối × 64 MB = 6,4 GB RAM chỉ riêng cho sort buffer. Cộng dồn rất nhanh.
Với những query khổng lồ thỉnh thoảng xuất hiện, hãy tăng theo session thay vì global:
SET SESSION sort_buffer_size = 32 * 1024 * 1024; -- tăng lên cho query này
SELECT ... ORDER BY ... ;
SET SESSION sort_buffer_size = 4 * 1024 * 1024; -- đặt lại sau đó
Cách 3: Thêm Index (Giải Pháp Đúng Đắn Lâu Dài)
Tăng sort buffer chỉ là giải pháp vá víu. Cách khắc phục thực sự — khi có thể — là đánh index cho cột đang sắp xếp. Với index phù hợp, MySQL đọc các hàng theo thứ tự đã sắp xếp mà không cần động đến sort buffer.
Giả sử query chậm của bạn trông như thế này:
SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 50;
Thêm index:
ALTER TABLE users ADD INDEX idx_created_at (created_at);
Sau đó xác nhận MySQL thực sự dùng nó:
EXPLAIN SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 50;
Kiểm tra cột Extra. Lý tưởng là để trống hoặc hiển thị Using index. Nếu vẫn thấy Using filesort, MySQL vẫn đang sắp xếp trong bộ nhớ — thường do query lấy quá nhiều cột hoặc có điều kiện phức tạp mà index không thể bao phủ.
Cách 4: Sắp Xếp Ít Hàng Hơn
Đôi khi bản thân câu query mới là thủ phạm. Sắp xếp full table scan 2 triệu hàng sẽ làm cạn sort buffer dù bạn có tăng lên bao nhiêu.
Thay vì:
SELECT * FROM events ORDER BY event_time DESC;
Lọc trước, rồi mới sắp xếp:
SELECT * FROM events
WHERE event_time >= NOW() - INTERVAL 7 DAY
ORDER BY event_time DESC
LIMIT 100;
Kết hợp với index trên event_time và vấn đề sort buffer thường biến mất hoàn toàn.
Xác Nhận Đã Sửa Được
Kiểm tra sort_buffer_size đã được cập nhật:
SHOW VARIABLES LIKE 'sort_buffer_size';
Chạy lại query gốc — ERROR 1038 sẽ không còn nữa. Để theo dõi hoạt động sắp xếp trên toàn server:
SHOW STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 5 |
| Sort_rows | 12430 |
| Sort_scan | 3 |
+-------------------+-------+
Chú ý Sort_merge_passes. Bằng 0 là lý tưởng. Nếu con số đó cứ tăng dần, MySQL đang liên tục đẩy dữ liệu sắp xếp ra đĩa — buffer vẫn còn quá nhỏ, hoặc query cần được tối ưu lại.
Tóm Tắt
- Sửa nhanh cho session:
SET SESSION sort_buffer_size = 8 * 1024 * 1024; - Sửa vĩnh viễn: đặt
sort_buffer_size = 4Mtrongmy.cnfdưới[mysqld] - Đừng đặt giá trị global quá cao — tính theo từng kết nối, không phải bộ nhớ dùng chung
- Đánh index cho cột trong
ORDER BYđể tránh hoàn toàn việc dùng sort buffer - Theo dõi
Sort_merge_passes— nếu khác 0 và tiếp tục tăng, buffer vẫn còn quá nhỏ