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.cnfThê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 = 1Restart MySQL:
sudo systemctl restart mysqlPhâ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.logOutput 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]@tech.nhatkychuyenhoa.link
UPDATE user_stats SET last_login=NOW() WHERE user_id=NỞ đây:
Count: số lần query chạyTime: thời gian trung bình (tổng thời gian)Lock: thời gian chờ lock tableRows: 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.plChạy MySQLTuner
sudo perl mysqltuner.plOutput 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 = 1536MKiể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 = 150query_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 = 2MVớ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 = 32MKiể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 diskrowscao: 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 scanrows = 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 --passwordOutput 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 repairedOptimize 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 --passwordScript 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>&1Tă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
MySQL chạy chậm khi nào?
MySQL chậm khi: Database lớn mà innodb_buffer_pool_size quá nhỏ, thiếu index trên cột WHERE, ORDER BY, JOIN, query quét toàn table thay vì dùng index, disk I/O chậm, quá nhiều connection cùng lúc, table bị phân mảnh sau nhiều lần DELETE. Kiểm tra từng nguyên nhân theo thứ tự để tìm ra thủ phạm.
Tăng innodb_buffer_pool_size có an toàn không?
Có, nhưng phải để lại RAM cho OS và các service khác: VPS 2GB tối đa 1GB cho MySQL, VPS 4GB tối đa 2GB, VPS 8GB tối đa 4-5GB. Nếu set quá cao, OS sẽ swap, làm chậm toàn bộ hệ thống.
MySQLTuner recommend max_connections = 500, có nên áp dụng?
Không nên áp dụng mù quáng. 500 connections tốn ~128MB RAM chỉ riêng thread stack. Website nhỏ thường chỉ cần 50-150 connections. Kiểm tra Max_used_connections thực tế, chỉ tăng khi cần thiết.
Có nên optimize tất cả table hàng ngày không?
Không. OPTIMIZE TABLE lock table trong quá trình chạy, ảnh hưởng website. Chỉ optimize khi: Table có nhiều DELETE/UPDATE, fragmentation trên 10%, disk space không giải phóng sau DELETE. Kiểm tra fragmentation bằng query information_schema.tables.
Phân biệt slow query do thiếu index hay do disk chậm?
Slow query do thiếu index: EXPLAIN shows type=ALL, rows cao, Extra có Using filesort/Using temporary. Slow query do disk chậm: iostat shows %util > 80%, await > 20ms, tất cả queries đều chậm đồng loạt. Fix thiếu index bằng CREATE INDEX, fix disk chậm bằng upgrade storage hoặc tối ưu I/O.
Xem thêm: Disk I/O cao, Tối ưu Nginx PHP-FPM, WordPress VPS chậm
No Comment! Be the first one.