MySQL Chậm Trên VPS: Cách Tìm Slow Query Và Tối Ưu Đúng Chỗ

MySQL chậm không đồng nghĩa VPS thiếu RAM. Một truy vấn thiếu index, query trả quá nhiều dòng, lock kéo dài, storage chậm hoặc cấu hình buffer không phù hợp đều có thể làm website phản hồi chậm.

Sai lầm phổ biến là tìm một file my.cnf “tối ưu sẵn”, copy nguyên các giá trị buffer rồi restart MySQL. Mỗi workload có kích thước dữ liệu, tỷ lệ đọc/ghi và concurrency khác nhau, vì vậy cách an toàn hơn là đo → tìm query cụ thể → tối ưu schema/query → kiểm tra tài nguyên → mới tinh chỉnh server variable khi có lý do.

Nếu bạn chưa cài MySQL, có thể xem trước bài Cài MySQL trên VPS Ubuntu. Bài này giả định MySQL đã chạy và tập trung vào hiệu năng.

1. Xác định MySQL có thực sự là nút thắt không

Trước khi chỉnh database, xem tải hệ thống:

uptime
free -h
df -h
iostat -xz 1 5

Nếu chưa có iostat:

sudo apt update
sudo apt install -y sysstat

Các tín hiệu đáng chú ý:

• RAM liên tục cạn và swap hoạt động mạnh;
• disk latency cao;
• CPU 100% kéo dài;
• filesystem gần đầy;
• load average cao hơn bình thường;
• MySQL process chiếm phần lớn CPU hoặc I/O.

Nếu ổ đĩa gần 100%, hãy xử lý storage trước theo bài VPS Ubuntu bị đầy ổ đĩa. Database rất dễ lỗi khi không còn chỗ ghi.

2. Xem query đang chạy

Đăng nhập MySQL:

mysql -u root -p

Kiểm tra process list:

SHOW FULL PROCESSLIST;

Hoặc:

SELECT * FROM information_schema.PROCESSLIST
ORDER BY TIME DESC;

Hãy chú ý các query có TIME lớn, trạng thái chờ lock hoặc nhiều connection cùng chạy một mẫu query.

Một snapshot duy nhất có thể bỏ lỡ lỗi, nên đừng kết luận chỉ từ một lần SHOW PROCESSLIST.

3. Bật slow query log

MySQL có slow query log để ghi lại các statement vượt ngưỡng long_query_time.

Kiểm tra trạng thái:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

Bật tạm thời ở runtime:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Ngưỡng 1 giây chỉ là ví dụ. Với ứng dụng yêu cầu latency thấp, 1 giây đã rất chậm; với batch workload, ngưỡng có thể khác.

MySQL 8.4 mặc định long_query_time là 10 giây nếu chưa thay đổi, nên nhiều query gây cảm giác chậm cho web có thể không xuất hiện nếu bạn giữ mặc định.

Để cấu hình tồn tại sau restart, thêm vào file cấu hình phù hợp với distro, ví dụ một file trong /etc/mysql/mysql.conf.d/:

[mysqld]
slow_query_log=ON
long_query_time=1

Sau khi chỉnh, validate cấu hình và restart trong maintenance window nếu cần.

4. Đọc slow query log

Xác định đường dẫn:

SHOW VARIABLES LIKE 'slow_query_log_file';

Sau đó xem phần cuối:

sudo tail -n 200 /var/log/mysql/mysql-slow.log

Đường dẫn thực tế có thể khác.

MySQL có mysqldumpslow để gom các query tương tự:

sudo mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log

Lệnh trên giúp tìm các nhóm query có tổng thời gian cao.

Đừng chỉ tối ưu query chậm nhất một lần. Một query 100 ms chạy hàng triệu lần có thể tốn tài nguyên hơn query 5 giây chỉ chạy mỗi ngày.

5. Dùng EXPLAIN để xem execution plan

Ví dụ query:

SELECT id, email
FROM users
WHERE email = 'user@example.com';

Chạy:

EXPLAIN SELECT id, email
FROM users
WHERE email = 'user@example.com';

MySQL dùng EXPLAIN để mô tả cách thực thi query.

Các điểm cần xem:

• bảng nào được đọc trước;
• index nào có thể dùng;
• index thực tế được chọn;
• số dòng ước tính cần đọc;
• có full table scan không;
• có temporary/filesort hay không.

Trên MySQL hiện đại, EXPLAIN ANALYZE còn thực thi query và trả số liệu runtime thực tế:

EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE email = 'user@example.com';

Cẩn thận: EXPLAIN ANALYZE thực sự chạy statement. Với query nặng hoặc thay đổi dữ liệu, hãy đọc tài liệu phiên bản đang dùng và test trên môi trường an toàn trước.

6. Thiếu index là nguyên nhân phổ biến

Kiểm tra index:

SHOW INDEX FROM users;

Nếu ứng dụng thường xuyên tìm theo email, có thể cần index phù hợp:

CREATE INDEX idx_users_email ON users(email);

Nhưng không nên tạo index cho mọi column.

Mỗi index:

• chiếm dung lượng;
• tăng chi phí INSERT/UPDATE/DELETE;
• cần được MySQL duy trì;
• có thể không được optimizer chọn nếu thiết kế không phù hợp.

Với query nhiều điều kiện, thứ tự column trong composite index rất quan trọng.

Ví dụ:

SELECT id, total
FROM orders
WHERE user_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Một index có thể cân nhắc:

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

Không copy index này cho schema khác. Hãy dựa trên query thật và EXPLAIN.

7. Tránh SELECT * khi không cần

Nếu chỉ cần id, name, price, đừng lấy toàn bộ hàng có nhiều column lớn:

SELECT id, name, price
FROM products
WHERE category_id = 10
LIMIT 50;

Lợi ích:

• giảm dữ liệu đọc từ storage;
• giảm network transfer;
• giảm memory tạm;
• có cơ hội tận dụng covering index trong một số trường hợp.

8. Kiểm tra N+1 query từ ứng dụng

Database có thể không có query đơn lẻ nào cực chậm nhưng một request web lại chạy hàng trăm query nhỏ.

Ví dụ:

1. lấy 100 bài viết;
2. sau đó chạy thêm 100 query để lấy tác giả từng bài.

Đây là mẫu N+1.

Cách khắc phục tùy framework:

• eager loading;
• JOIN phù hợp;
• preload relation;
• cache dữ liệu ít thay đổi.

Đây là tối ưu ở lớp ứng dụng, không thể sửa bằng tăng innodb_buffer_pool_size đơn thuần.

9. Kiểm tra connection

Xem số connection:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

max_connections cao không tự làm MySQL nhanh hơn. Nếu ứng dụng mở quá nhiều connection, mỗi connection đều có overhead và một số buffer có thể cấp phát theo session.

Với ứng dụng web, connection pool nên được cấu hình phù hợp. Đừng đặt pool lớn hơn khả năng database chỉ vì VPS có nhiều RAM.

10. InnoDB buffer pool

Với MySQL/InnoDB, innodb_buffer_pool_size là biến quan trọng vì buffer pool cache data/index pages.

Kiểm tra:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Không có một con số “chuẩn” cho mọi VPS.

Nếu VPS chỉ chạy MySQL, bạn có thể dành tỷ lệ RAM lớn hơn cho buffer pool. Nếu cùng máy còn Nginx, PHP-FPM, Redis, Docker và agent monitoring, cần chừa RAM cho toàn hệ thống.

Trước khi tăng buffer pool, xem:

free -h

và theo dõi swap.

Tăng quá cao có thể khiến kernel phải swap hoặc OOM kill process, làm hệ thống chậm hơn.

11. Đừng chỉnh hàng loạt buffer theo blog cũ

Các biến như:

• sort_buffer_size;
• join_buffer_size;
• read_buffer_size;
• tmp_table_size;
• max_heap_table_size;

có đặc điểm và phạm vi cấp phát khác nhau. Một số buffer có thể cấp phát theo connection hoặc operation, vì vậy tăng mạnh trong môi trường nhiều connection có thể gây áp lực RAM lớn.

Chỉ thay đổi khi bạn đo được lý do.

12. Kiểm tra temporary table

Xem thống kê:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Nếu Created_tmp_disk_tables tăng nhanh, query có thể tạo nhiều temporary table xuống disk.

Đừng lập tức tăng tmp_table_size. Hãy kiểm tra query, data type, GROUP BY, ORDER BY và execution plan trước.

13. Lock và transaction dài

Transaction giữ lock quá lâu có thể khiến query khác xếp hàng.

Xem transaction InnoDB:

SELECT * FROM information_schema.INNODB_TRX\G

Trên hệ thống có nhiều update, hãy chú ý transaction mở lâu bất thường.

Nguyên nhân thường gặp:

• application bắt đầu transaction nhưng không commit nhanh;
• job batch update quá nhiều row một lần;
• query thiếu index khiến update scan quá rộng;
• connection bị treo nhưng transaction vẫn mở.

Không nên kill transaction production khi chưa hiểu rollback có thể mất bao lâu.

14. I/O mới là nút thắt thì sao?

Nếu query đã có index nhưng latency storage cao, database vẫn chậm.

Kiểm tra:

iostat -xz 1 10

Xem thêm:

vmstat 1 10

Nếu %iowait cao và device latency tăng mạnh, cần xem:

• dataset có vượt RAM cache không;
• backup job có chạy cùng thời điểm không;
• log hoặc Docker có tranh I/O không;
• storage VPS có phù hợp workload database không.

Đây là lúc nâng cấp tài nguyên có thể hợp lý hơn tinh chỉnh SQL tiếp.

15. Slow query log cũng cần retention

Slow log có thể tăng dung lượng nhanh nếu long_query_time thấp.

Theo dõi:

sudo du -sh /var/log/mysql

Cấu hình logrotate phù hợp và tránh giữ log vô hạn.

Nếu disk usage đang tăng, xem bài VPS Ubuntu bị đầy ổ đĩa để xử lý tổng thể.

16. Quy trình tối ưu nên thực hiện thế nào?

Một quy trình thực tế:

1. đo latency ứng dụng;
2. kiểm tra CPU, RAM, disk I/O;
3. bật slow query log trong khoảng thời gian đủ đại diện;
4. nhóm query tốn nhiều thời gian;
5. dùng EXPLAIN/EXPLAIN ANALYZE;
6. sửa query hoặc index;
7. đo lại;
8. mới cân nhắc server variable;
9. theo dõi sau thay đổi.

Thay đổi mỗi lần một nhóm nhỏ giúp bạn biết chính xác điều gì tạo ra khác biệt.

Ví dụ: tối ưu query tìm đơn hàng

Query ban đầu:

SELECT *
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;

Nếu bảng có hàng triệu dòng, query có thể đọc nhiều dữ liệu.

Bước 1, kiểm tra plan:

EXPLAIN SELECT *
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;

Bước 2, nếu chưa có index phù hợp, cân nhắc:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

Bước 3, nếu giao diện chỉ hiển thị 20 đơn gần nhất:

SELECT id, status, total, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

Sau đó đo lại thay vì chỉ tin rằng “có index là nhanh”.

FAQ

MySQL chậm có nên tăng RAM VPS ngay không?

Chưa chắc. Hãy kiểm tra slow query, execution plan, buffer pool hit behavior, swap và disk I/O trước. Nếu working set thực sự vượt RAM, tăng RAM mới là giải pháp hợp lý.

Có nên bật slow query log vĩnh viễn không?

Có thể, nếu cấu hình ngưỡng và retention phù hợp. Cần theo dõi dung lượng log và tránh ngưỡng quá thấp làm log tăng mạnh không cần thiết.

EXPLAIN có làm thay đổi dữ liệu không?

EXPLAIN thông thường mô tả plan. EXPLAIN ANALYZE thực thi statement để đo runtime, nên cần thận trọng hơn và kiểm tra đúng phiên bản/câu lệnh.

Tạo nhiều index có làm MySQL nhanh hơn không?

Không phải lúc nào cũng vậy. Index cải thiện một số query đọc nhưng làm tăng chi phí ghi và dung lượng. Chỉ tạo index dựa trên workload thật.

MySQL dùng 80–90% RAM có nguy hiểm không?

Không thể kết luận chỉ từ tỷ lệ RAM. Linux dùng RAM làm cache, còn MySQL có buffer pool. Quan trọng là xem swap, OOM, memory pressure và tổng workload của VPS.

Kết luận

Muốn tối ưu MySQL trên VPS, hãy bắt đầu bằng bằng chứng. Slow query log cho biết query nào đáng điều tra, EXPLAIN cho biết optimizer đang làm gì, còn metric hệ thống cho biết CPU/RAM/storage có phải nút thắt hay không.

Phần lớn hiệu quả bền vững đến từ schema, index và query đúng, không phải một file cấu hình “thần tốc” dùng chung cho mọi VPS.

Nếu sau khi tối ưu query mà workload vẫn thường xuyên chạm giới hạn CPU, RAM hoặc I/O, bạn có thể tham khảo các gói VPS trên HUONG CLOUD và chọn tài nguyên phù hợp với quy mô database thực tế.

Nguồn tham khảo

• MySQL 8.4 Reference Manual — Server System Variables: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html
• MySQL 8.4 Reference Manual — EXPLAIN Statement: https://dev.mysql.com/doc/refman/8.4/en/explain.html
• MySQL 8.0 Reference Manual — The Slow Query Log: https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html