Khi database bỗng dưng quá tải: Bắt đầu từ đâu?
Website đang chạy êm ru thì CPU server bất ngờ vọt lên 99%. API phản hồi từ 50ms tăng lên 8s, kết nối database chạm trần max_connections, và người dùng liên tục gặp lỗi 504 Gateway Timeout. Vấn đề thường không nằm ở toàn bộ hệ thống. Đa phần chỉ do một vài câu query viết ẩu đang âm thầm quét hàng triệu bản ghi và ngốn sạch tài nguyên.
Mở error.log ra? Bạn sẽ chẳng thấy gì bất thường. Query vẫn chạy đúng cú pháp, không quăng lỗi, chỉ là nó chạy quá lâu. Giữa hàng triệu request mỗi ngày, bạn không thể ngồi đoán mò xem query nào đang kéo sập server.
Cặp công cụ cốt lõi: Slow Query Log và pt-query-digest
Để xử lý tận gốc vấn đề dựa trên số liệu thực tế, bạn cần hai công cụ: Slow Query Log và pt-query-digest.
Slow Query Log là gì?
MySQL tích hợp sẵn tính năng Slow Query Log. Bạn chỉ cần đặt một ngưỡng thời gian qua biến long_query_time, ví dụ 0.5 giây. Bất kỳ câu lệnh nào chạy lâu hơn ngưỡng này, hoặc quét bảng mà không dùng index, đều được ghi lại đầy đủ vào một file log riêng.
Tại sao log thô thôi là chưa đủ?
Sau 24 giờ hoạt động của một website thương mại điện tử tầm trung, file log có thể phình lên 2GB với hơn 300.000 dòng. Mở file bằng less hay grep là bất khả thi.
Đây là lúc pt-query-digest trong bộ Percona Toolkit vào cuộc. Công cụ này phân tích toàn bộ file log, gom nhóm các query có cùng cấu trúc (thay tham số cụ thể bằng dấu hỏi ?), tính toán tỉ lệ thời gian chiếm dụng, và xếp hạng những query gây hại nhiều nhất cho hệ thống.
Các bước cấu hình và phân tích thực tế
Bước 1: Bật Slow Query Log không cần restart MySQL
Restart MySQL giữa giờ cao điểm là điều tối kỵ trên môi trường production. Bạn hoàn toàn có thể cấu hình log trực tiếp trong phiên làm việc của MySQL:
mysql -u root -pSau đó, chạy các câu lệnh cấu hình runtime:
-- Kích hoạt Slow Query Log
SET GLOBAL slow_query_log = 'ON';
-- Đường dẫn lưu log
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- Ghi nhận các query chạy trên 0.5 giây (hỗ trợ microsecond từ MySQL 5.1+)
SET GLOBAL long_query_time = 0.5;
-- Ghi nhận cả query không dùng index
SET GLOBAL log_queries_not_using_indexes = 'ON';Lưu ý: long_query_time hỗ trợ cả số thập phân như 0.2 hay 0.5. Ngưỡng 1.0 giây mặc định thường là quá trễ cho các hệ thống web tải cao.
Để giữ nguyên cấu hình này sau khi khởi động lại server, bạn mở file cấu hình MySQL (thường ở /etc/mysql/mysql.conf.d/mysqld.cnf hoặc /etc/my.cnf) và thêm vào block [mysqld]:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1Kiểm tra quyền ghi file cho user mysql để tránh lỗi ghi log âm thầm:
sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo chmod 640 /var/log/mysql/mysql-slow.logBước 2: Cài đặt Percona Toolkit
Cài đặt trên Ubuntu/Debian thông qua apt:
sudo apt update
sudo apt install -y percona-toolkitVới CentOS, RHEL hoặc AlmaLinux:
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install -y percona-toolkitBước 3: Phân tích file log bằng pt-query-digest
Hãy để hệ thống chạy qua ít nhất một khung giờ cao điểm (từ 2 đến 4 tiếng) để thu thập mẫu dữ liệu đủ tin cậy. Sau đó, xuất báo cáo ra file text:
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_report.txt
less /tmp/slow_report.txtBáo cáo gồm 3 phần chính:
- Overall Metrics: Tổng thời gian thực thi, số lượng query duy nhất, tải trung bình của hệ thống.
- Profile: Bảng xếp hạng các nhóm query theo phần trăm thời gian chiếm dụng (Response Time %).
- Query Details: Thống kê chi tiết từng query: số lần gọi, thời gian chạy min/max/95%, tỉ lệ dòng duyệt so với dòng trả về.
Bảng xếp hạng sẽ có dạng tương tự như sau:
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# === ================== ============== ====== ======== ===== ==============
# 1 0x4F18B9A6D2C8E7B1 1420.50s 65.2% 2300 0.6176 0.02 SELECT orders
# 2 0x9A3B5C7D1E8F2A40 420.10s 19.3% 120 3.5008 0.15 SELECT usersĐừng để bị lừa bởi query Rank 2 (chạy mất 3.5s). Query Rank 1 (SELECT orders) mỗi lần chạy chỉ tốn 0.61s, nhưng bị gọi tới 2.300 lần và chiếm 65.2% tổng thời gian phục vụ của database. Tối ưu query này sẽ giải phóng gần hai phần ba tải cho server.
Bước 4: Đọc chỉ số Rows examine vs Rows sent để tối ưu
Kéo xuống phần chi tiết của query Rank 1, bạn kiểm tra các thông số sau:
# Attribute pct total min max avg 95% stddev median
# Exec time 65 1420s 100ms 2s 617ms 1s 200ms 500ms
# Rows examine 88 1200M 50k 600k 521k 580k 50k 500k
# Rows sent 1 2.3k 1 5 1 2 1 1Dữ liệu cho thấy:
Rows examine: trung bình phải quét 521.000 dòng.Rows sent: chỉ trả về đúng 1 dòng kết quả.
Tỷ lệ 521.000 : 1 là dấu hiệu rõ ràng của Full Table Scan. MySQL phải lùng sục hơn nửa triệu bản ghi chỉ để lấy một dòng dữ liệu duy nhất.
Chạy thử câu lệnh với EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND customer_id = 9821;Nếu cột type hiển thị ALL, hãy bổ sung ngay composite index:
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);Sau khi đánh index, số dòng duyệt giảm từ 521.000 xuống còn 1 dòng. Thời gian phản hồi hạ từ hơn 600ms xuống dưới 2ms.
Lưu ý trên production: Với bảng có hàng chục triệu bản ghi, lệnh ALTER TABLE thông thường có thể gây khóa bảng hoặc nghẽn replication. Hãy cân nhắc dùng công cụ tạo index online như pt-online-schema-change hoặc gh-ost để tránh downtime.
Tổng kết
Tối ưu database không dựa vào cảm tính. Bật Slow Query Log giúp bạn ghi lại chính xác hiện trường. Dùng pt-query-digest giúp bạn định lượng thiệt hại của từng câu lệnh. Kết hợp hai công cụ này, bạn sẽ biết rõ từng mili-giây CPU đang bị lãng phí ở đâu và xử lý chính xác điểm nghẽn có tác động lớn nhất.
