Database chậm mà không biết bắt đầu từ đâu
Có một tình huống mà bất kỳ ai làm backend lâu ngày cũng gặp: ứng dụng bỗng dưng chậm, người dùng than phiền, nhưng khi bạn nhìn vào server thì CPU chỉ 30%, RAM còn thoải mái. Vậy vấn đề ở đâu?
Câu trả lời thường nằm ở database — một vài câu query đang ngốn thời gian không tương xứng. Mình đã làm việc với cả MySQL, PostgreSQL lẫn MongoDB trong các dự án khác nhau. Mỗi cái có điểm mạnh riêng, nhưng khi nói đến chuyện debug performance, MySQL có một vũ khí mà nhiều người hay bỏ qua: pt-query-digest trong bộ Percona Toolkit.
Bài này đi thẳng vào vấn đề: bật Slow Query Log, thu thập dữ liệu, rồi dùng pt-query-digest để tìm đúng query cần tối ưu — không đoán mò nữa.
Hiểu rõ hai thứ trước khi bắt tay làm
Slow Query Log là gì?
MySQL có tính năng ghi lại tất cả các câu query chạy lâu hơn một ngưỡng nhất định vào một file log. File này gọi là Slow Query Log. Mặc định MySQL không bật tính năng này vì nó tốn thêm I/O, nhưng khi cần debug performance thì đây là nguồn thông tin quan trọng nhất bạn có.
Rắc rối là file log này có thể rất dài và rất lộn xộn. Đọc tay thì gần như không thể rút ra được gì. Một hệ thống production chạy vài giờ đồng hồ là đã sinh ra hàng chục nghìn dòng.
pt-query-digest làm gì?
pt-query-digest là một tool trong bộ Percona Toolkit — tập hợp các tiện ích command-line do Percona phát triển để quản lý và tối ưu MySQL/MariaDB. Tool này đọc Slow Query Log, gom nhóm các query tương tự nhau (dù giá trị tham số khác nhau), rồi thống kê: query nào chạy nhiều lần nhất, query nào ngốn tổng thời gian nhiều nhất, query nào có thời gian trung bình cao nhất.
Kết quả: 50.000 dòng log rút gọn thành báo cáo 2 trang với ranking ưu tiên cụ thể.
Bật Slow Query Log trong MySQL
Không cần restart MySQL để bắt đầu — bật trực tiếp trong session đang chạy:
-- Kết nối vào MySQL với quyền root
mysql -u root -p
-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';
-- Ngưỡng thời gian (giây) — query chạy lâu hơn sẽ được ghi log
SET GLOBAL long_query_time = 1;
-- Ghi cả những query không dùng index
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- Kiểm tra file log đang ở đâu
SHOW VARIABLES LIKE 'slow_query_log_file';Nếu muốn bật vĩnh viễn (tồn tại qua restart), thêm vào file /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) hoặc /etc/my.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1Sau đó restart: sudo systemctl restart mysql
Để có dữ liệu test ngay, chạy thử một số query chậm:
-- Query không dùng index — sẽ bị ghi vào log
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Hoặc tạo tải thật với SLEEP
SELECT SLEEP(2);Cài đặt Percona Toolkit
Trên Ubuntu/Debian:
# Thêm Percona repository
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
# Cài Percona Toolkit
sudo apt-get install percona-toolkit
# Kiểm tra
pt-query-digest --versionTrên CentOS/RHEL:
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install percona-toolkitPhân tích log với pt-query-digest
Sau khi để hệ thống chạy một thời gian (tối thiểu vài giờ trong production để có đủ dữ liệu), chạy lệnh phân tích:
# Phân tích cơ bản
pt-query-digest /var/log/mysql/mysql-slow.log
# Chỉ xem top 5 query tệ nhất
pt-query-digest --limit 5 /var/log/mysql/mysql-slow.log
# Chỉ phân tích log trong 1 giờ gần nhất
pt-query-digest --since '1h' /var/log/mysql/mysql-slow.log
# Lưu báo cáo ra file để đọc sau
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow-query-report.txtĐọc kết quả báo cáo
Output của pt-query-digest có hai phần chính. Phần đầu là tổng quan toàn bộ log:
# 120 queries in 0.5k lines, 0.00 QPS, 0x concurrency ______________
# Time range: 2024-01-15 09:00:00 to 2024-01-15 11:00:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 245s 100ms 8s 2s 5s 1s 2s
# Lock time 120ms 0us 30ms 1ms 5ms 3ms 500us
# Rows sent 1.50k 1 500 13 100 50 5
# Rows examined 45.00k 1 5000 375 2000 800 100Phần thứ hai liệt kê từng nhóm query, xếp theo tổng thời gian thực thi (Response time) — cái này mới quan trọng, đọc từ trên xuống:
# Query 1: 0.50 QPS, 4.00x concurrency, ID 0xABC123 at byte 1234
# Scores: V/M = 0.10
# Time range: all events
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 5 60
# Exec time 40% 98s 1s 8s 1s 5s 1s 1s
# Rows examined 60% 27000 50 5000 450 2000 800 100
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM orders
WHERE YEAR(created_at) = 2024
AND status = 'pending'\GBa con số cần chú ý nhất:
- Count: Query này chạy bao nhiêu lần trong khoảng thời gian phân tích
- Exec time (total): Tổng thời gian — 40% của toàn bộ thời gian chậm nằm ở query này
- Rows examined: MySQL phải quét bao nhiêu row để trả về kết quả — số này cao là dấu hiệu thiếu index
Từ báo cáo đến hành động tối ưu
Sau khi xác định được query tệ nhất, bước tiếp theo là chạy EXPLAIN để xem MySQL đang thực thi như thế nào:
EXPLAIN SELECT * FROM orders
WHERE YEAR(created_at) = 2024
AND status = 'pending';Cột type hiện ALL là báo động đỏ: MySQL đang scan toàn bộ bảng mà không dùng index. Đây chính là nguyên nhân khiến exec time lên đến 8 giây trong báo cáo.
Vấn đề với query trên là hàm YEAR() bao bọc column khiến MySQL không dùng được index. Sửa lại:
-- Thay vì dùng hàm (không dùng được index)
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Viết lại để MySQL dùng index trên created_at
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01'
AND status = 'pending';
-- Tạo composite index cho cả hai column
CREATE INDEX idx_orders_created_status
ON orders (created_at, status);Sau khi tạo index, chạy EXPLAIN lại — cột type phải chuyển từ ALL sang range hoặc ref, và rows phải giảm đáng kể.
Mình hay làm thêm một bước nữa sau khi deploy fix: để log chạy thêm vài giờ rồi chạy lại pt-query-digest. So sánh báo cáo trước và sau — query đó còn xuất hiện trong top không, thời gian thực thi có giảm không. Đây là cách duy nhất để biết chắc tối ưu có tác dụng.
Tổng kết
pt-query-digest không phức tạp, nhưng hiệu quả đúng chỗ. Từ hàng chục nghìn dòng log lộn xộn, bạn nhận được ranking với con số cụ thể. Trong thực tế, thường chỉ cần fix top 2-3 query là đủ để thấy rõ sự khác biệt — đôi khi response time giảm từ vài giây xuống dưới 100ms chỉ nhờ thêm một cái index đúng chỗ.
Quy trình: bật Slow Query Log → để chạy vài giờ → pt-query-digest → EXPLAIN query đầu bảng → thêm index hoặc viết lại → so sánh báo cáo trước/sau. Lặp lại cho đến khi không còn query nào đáng lo trong top.
