MySQL chậm trên VPS: Cách tìm và tối ưu slow query

MySQL chạy chậm là thủ phạm gây ra 90% tình trạng website tải lâu trên VPS. Khi website load quá 5 giây, người dùng bỏ đi. Google rank xuống. Doanh thu giảm.

Vấn đề thường bắt nguồn từ MySQL database. Không phải do RAM ít hay CPU yếu, mà do cấu hình MySQL chưa phù hợp với workload thực tế.

Lưu ý: Mọi thao tác vào database luôn phải được sao lưu (backup) trước khi làm

Phát hiện MySQL đang chạy chậm

Kiểm tra query đang chạy

Đầu tiên, kết nối vào MySQL và xem các query hiện tại:

SHOW PROCESSLIST;

Output mẫu khi MySQL chậm:

+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 123 | root | localhost | web  | Query   | 45   | Sending data | SELECT * FROM posts WHERE status='published' ORDER BY created_at DESC |
| 124 | app  | 10.0.0.2  | web  | Query   | 23   | Copying to tmp table | SELECT u.name, COUNT(p.id) FROM users u LEFT JOIN posts p ON u.id=p.user_id GROUP BY u.id |
| 125 | app  | 10.0.0.3  | web  | Query   | 67   | Waiting for table level lock | UPDATE user_stats SET views=views+1 WHERE user_id=456 |
+-----+------+-----------+------+---------+------+-------+------------------+

Cột Time cho biết query chạy được bao nhiêu giây. Query trên 10 giây thường là slow query. Query trên 30 giây rất có thể gặp vấn đề.

Đếm số slow query

Kiểm tra tổng số slow query trong database:

SHOW GLOBAL STATUS LIKE 'Slow_queries';

Output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1247  |
+---------------+-------+

⚠️ Nếu số này tăng nhanh (refresh lại sau 1 phút mà tăng thêm 10-20), MySQL đang gặp vấn đề.

Bật slow query log

MySQL mặc định không log slow query. Cần bật để phân tích chi tiết.

Chỉnh file cấu hình MySQL:

sudo nano /etc/mysql/my.cnf

Thêm vào phần [mysqld]:

[mysqld]
# Bật slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# Query nào chạy trên 2 giây sẽ được ghi vào log
long_query_time = 2
# Log cả query không dùng index
log_queries_not_using_indexes = 1

Restart MySQL:

sudo systemctl restart mysql

Phân tích slow query log

Sau 30 phút hoạt động, kiểm tra slow query log:

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

Output mẫu:

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 45  Time=12.34s (555s)  Lock=0.01s (0s)  Rows=1250.0 (56250), app[app]@[10.0.0.2]
  SELECT * FROM posts WHERE status='published' ORDER BY created_at DESC LIMIT N
Count: 23  Time=8.67s (199s)  Lock=0.02s (0s)  Rows=892.5 (20527), app[app]@[10.0.0.3]
  SELECT COUNT(*) FROM comments WHERE post_id IN (N,N,N,N,N)
Count: 12  Time=15.21s (182s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  UPDATE user_stats SET last_login=NOW() WHERE user_id=N

Ở đây:

• Count: số lần query chạy
• Time: thời gian trung bình (tổng thời gian)
• Lock: thời gian chờ lock table
• Rows: số dòng trung bình trả về

Query đầu tiên chạy 45 lần, mỗi lần 12.34 giây. Đây chính là thủ phạm.

Dùng MySQLTuner để phân tích tổng quan

MySQLTuner là script phân tích hiệu năng MySQL tự động.

Cài đặt MySQLTuner

cd /tmp
wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl

Chạy MySQLTuner

sudo perl mysqltuner.pl

Output mẫu:

 >>  MySQLTuner 1.9.8 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file mysql-slow.log doesn't exist
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 2.1G (Tables: 127)
[--] Data in MyISAM tables: 156.2M (Tables: 23)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics -------------------------------------------------------------
[--] innodb_buffer_pool_size / total InnoDB indexes: 128.0M/421.3M
[!!] InnoDB buffer pool <= MySQL's data size: 128.0M <= 2.1G
[!!] InnoDB buffer pool is too small (< 70% of total InnoDB data)
[OK] InnoDB Used buffer: 98.34% (125.9M used / 128.0M cache)
[OK] InnoDB Read efficiency: 99.12% (45234 hits / 45635 total)
[!!] Query cache efficiency: 12.3% (4523 cached / 36789 selects)
[!!] Query cache disabled
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Variables to adjust:
    innodb_buffer_pool_size (>= 1.5G) if dedicated MySQL server, decrease if shared server
    max_connections (> 100)
    query_cache_type = ON if MySQL < 8.0, consider ProxySQL if MySQL >= 8.0

⚠️ Chú ý quan trọng: MySQLTuner chỉ đưa ra gợi ý, không phải mệnh lệnh tuyệt đối. Chỉ thay đổi cấu hình khi hiểu rõ workload thực tế của website.

Tối ưu cấu hình MySQL quan trọng nhất

innodb_buffer_pool_size (quan trọng nhất)

InnoDB buffer pool cache dữ liệu và index trong RAM. Đây là tham số ảnh hưởng lớn nhất đến hiệu năng.

Quy tắc:

• VPS dedicated MySQL: 50-70% RAM
• VPS chạy cả web server: 25-40% RAM
• Shared hosting: 15-25% RAM

Ví dụ VPS 4GB RAM chạy cả Nginx + PHP-FPM:

innodb_buffer_pool_size = 1536M

Kiểm tra hiệu quả:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

Hit ratio = (read_requests – reads) / read_requests * 100%

Hit ratio trên 99% là tốt. Dưới 95% cần tăng buffer pool.

max_connections

Mỗi connection MySQL tốn ~256KB RAM (thread_stack + buffers). Quá nhiều connection làm hết RAM.

Kiểm tra max connection hiện tại:

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

Nếu Max_used_connections gần bằng max_connections, cần tăng. Ngược lại, có thể giảm để tiết kiệm RAM.

Ví dụ:

max_connections = 150

query_cache (MySQL < 8.0)

MySQL 8.0 đã bỏ query cache vì hiệu quả kém. Với MySQL 5.7 trở xuống:

query_cache_type = ON
query_cache_size = 64M
query_cache_limit = 2M

Với MySQL 8.0, dùng ProxySQL hoặc Redis làm query cache.

tmp_table_size và max_heap_table_size

Temporary table quá lớn sẽ ghi xuống disk, chậm hơn RAM nhiều lần.

tmp_table_size = 32M
max_heap_table_size = 32M

Kiểm tra:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Tỷ lệ Created_tmp_disk_tables/Created_tmp_tables dưới 5% là ổn.

Tối ưu query

Phân tích execution plan với EXPLAIN

Với slow query từ log:

EXPLAIN SELECT * FROM posts WHERE status='published' ORDER BY created_at DESC LIMIT 10;

Output:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8542 |    10.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

Dấu hiệu chậm:

• type = ALL: Full table scan (quét toàn bộ table)
• Extra = Using filesort: Sắp xếp trên disk
• rows cao: Query phải đọc nhiều dòng

Thêm index

Query trên cần index cho cột status và created_at:

CREATE INDEX idx_posts_status_created ON posts (status, created_at DESC);

Chạy lại EXPLAIN:

+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | posts | NULL       | range | idx_posts_status_created  | idx_posts_status_created  | 767     | NULL  |  854 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+-------+------+----------+-------+

Hiện tại:

• type = range: Sử dụng index range scan
• rows = 854: Giảm từ 8542 xuống 854
• Không còn Using filesort

Tránh SELECT *

-- Chậm: lấy tất cả cột, nhiều I/O
SELECT * FROM posts WHERE status='published' ORDER BY created_at DESC LIMIT 10;
-- Nhanh: chỉ lấy cột cần thiết
SELECT id, title, created_at FROM posts WHERE status='published' ORDER BY created_at DESC LIMIT 10;

Chuyển từ MyISAM sang InnoDB

MyISAM lock toàn table khi UPDATE/INSERT, gây blocking. InnoDB lock từng dòng.

Kiểm tra engine hiện tại:

SHOW TABLE STATUS WHERE Name = 'posts';

Chuyển sang InnoDB:

ALTER TABLE posts ENGINE=InnoDB;

⚠️ Lưu ý: Backup trước khi chuyển đổi.

Bảo trì định kỳ

Kiểm tra và sửa lỗi database

Chạy hàng tuần:

sudo mysqlcheck --auto-repair --all-databases --user=root --password

Output mẫu:

mysql.columns_priv                         OK
mysql.db                                   OK
mysql.engine_cost                          OK
wordpress.wp_posts                         OK
wordpress.wp_users                         Table is marked as crashed and should be repaired
wordpress.wp_users                         Table repaired

Optimize tables

Sau khi DELETE nhiều dữ liệu, table bị phân mảnh:

OPTIMIZE TABLE posts;
OPTIMIZE TABLE comments;
OPTIMIZE TABLE users;

Hoặc tối ưu tất cả database:

sudo mysqlcheck --optimize --all-databases --user=root --password

Script tự động

Tạo file /home/mysql_maintenance.sh:

#!/bin/bash
# MySQL maintenance script
echo "$(date): Starting MySQL maintenance"
# Repair tables
mysqlcheck --auto-repair --all-databases --user=root --password=$MYSQL_ROOT_PASSWORD
# Optimize tables
mysqlcheck --optimize --all-databases --user=root --password=$MYSQL_ROOT_PASSWORD
echo "$(date): MySQL maintenance completed"

Thêm vào crontab chạy hàng tuần:

0 2 * * 0 /home/mysql_maintenance.sh >> /var/log/mysql_maintenance.log 2>&1

Tăng hiệu năng database với VPS NVMe AZDIGI

Database làm việc với disk liên tục. Disk chậm làm MySQL chậm, dù cấu hình tốt đến đâu.

VPS NVMe AZDIGI sử dụng ổ cứng NVMe tốc độ cao:

• Read: 3.200 MB/s (nhanh gấp 10x so với SSD SATA)
• Write: 2.900 MB/s
• IOPS: 600.000 (cao gấp 20x)

Điều này có nghĩa:

• InnoDB đọc/ghi data pages nhanh hơn
• Temporary tables tạo nhanh hơn
• Slow query log, binary log ghi không bị nghẽn cổ chai I/O

💡 Kết hợp cấu hình MySQL phù hợp + VPS NVMe, website có thể tăng tốc từ 3-5 giây xuống dưới 1 giây load time.

Xem thêm: Tổng hợp lỗi hiệu năng VPS

Xem thêm: Disk I/O cao, Tối ưu Nginx PHP-FPM, WordPress VPS chậm