Cơn ác mộng PagerDuty lúc 2 giờ sáng
Lúc đó là 2 giờ sáng, và điện thoại của bạn đang kêu inh ỏi. Dashboard giám sát hiển thị CPU tăng vọt lên 95% trên database chính. Ứng dụng web của bạn đang bị nghẽn với lỗi 504 Gateway Timeout. Bản năng đầu tiên của bạn có thể là nâng cấp phần cứng ngay lập tức — tăng thêm RAM hoặc nâng cấp lên gói RDS mạnh hơn. Nhưng phần cứng hiếm khi là giải pháp thực sự. Thông thường, sự hỗn loạn này do một câu truy vấn SQL “đi lạc” gây ra, nó quyết định quét 10 triệu dòng dữ liệu mà không có index.
MySQL là một “con ngựa thồ” cần mẫn, nhưng nó cũng là một kẻ âm thầm chịu đựng. Nó sẽ trung thành thực thi mọi câu truy vấn tệ hại mà bạn gửi đến, ngay cả khi câu truy vấn đó mất 15 giây và khóa chặt các bảng quan trọng nhất của bạn. Để tóm gọn những kẻ sát nhân hiệu năng này, bạn cần đến Slow Query Log.
Hãy coi bản log này như camera hành trình của database. Nó ghi lại mọi câu truy vấn vượt quá giới hạn thời gian cụ thể hoặc bỏ qua hoàn toàn index. Không có nó, bạn chỉ đang đoán mò xem phần nào trong mã nguồn đang kéo lùi hệ thống.
Kích hoạt: Bật công tắc mà không gây Downtime
Bạn không thể chỉ đơn giản là khởi động lại database production để thay đổi một cài đặt. Việc khởi động lại sẽ xóa sạch bộ nhớ đệm (buffer pools) và ngắt các kết nối đang hoạt động, điều này thường còn tệ hơn cả bản thân các câu truy vấn chậm. May mắn thay, MySQL cho phép bạn bật tắt logging ngay khi đang chạy bằng các biến toàn cục (global variables).
Kiểm tra trạng thái hiện tại
Hãy bắt đầu bằng cách kiểm tra xem tính năng này đã được bật chưa. Truy cập vào MySQL shell và chạy lệnh:
SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';Nếu slow_query_log đang là OFF, database của bạn thực sự đang “bay trong mù quáng”. Để kích hoạt ngay lập tức mà không cần khởi động lại, hãy sử dụng các lệnh sau:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1.0;Tôi thường đặt ngưỡng là 1.0 giây làm mốc cơ bản. Đối với các API lưu lượng cao xử lý hơn 5.000 request mỗi giây, tôi thường siết chặt con số này xuống 0.1 hoặc 0.2 giây. Điều này giúp phát hiện các “điểm nghẽn siêu nhỏ” trước khi chúng tích tụ thành một đợt ngừng hoạt động hoàn toàn.
Lưu cấu hình vĩnh viễn
Các thay đổi khi đang chạy sẽ biến mất nếu server khởi động lại. Để các cài đặt này có hiệu lực vĩnh viễn, hãy cập nhật file cấu hình của bạn (thường là /etc/mysql/my.cnf). Thêm các dòng sau vào dưới khối [mysqld]:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1
min_examined_row_limit = 100Hãy cẩn thận với log_queries_not_using_indexes. Trên một database khổng lồ với mã nguồn cũ, tùy chọn này có thể tạo ra hàng gigabyte log chỉ trong vài phút. min_examined_row_limit = 100 đóng vai trò như một lưới an toàn, đảm bảo chúng ta không ghi log các bảng nhỏ, vô hại vốn dĩ không cần index.
Cấu hình: Tìm tín hiệu trong mớ hỗn tạp
Một bản log chỉ hữu ích khi nó có thể đọc được. Nếu bạn ghi log mọi câu truy vấn, bạn chỉ đang đánh đổi vấn đề CPU lấy vấn đề I/O của ổ đĩa. Tinh chỉnh hiệu năng là việc tìm ra tín hiệu thực sự giữa những tạp âm.
Chiến lược đặt ngưỡng
Đừng bao giờ đặt long_query_time thành 0 trên production trừ khi bạn đang debug một vấn đề rất cụ thể trong vòng năm phút. Trên một server bận rộn, việc này có thể làm treo phân vùng root do đầy dung lượng đĩa. Hãy bắt đầu ở mức 2 giây. Nếu log vẫn trống nhưng ứng dụng có vẻ chậm chạp, hãy giảm xuống 1 giây, rồi 0.5 giây.
Lọc các kết quả sai lệch
Đôi khi một câu truy vấn bị chậm chỉ vì server đang bận làm việc khác, chẳng hạn như backup. Bằng cách sử dụng min_examined_row_limit, bạn sẽ bỏ qua những câu truy vấn “kém may mắn” đó. Bạn chỉ tập trung vào những câu truy vấn thực sự nặng nề vì chúng phải xử lý hàng nghìn dòng dữ liệu.
File và Table
MySQL có thể lưu log vào FILE hoặc TABLE. Mặc dù logging vào TABLE cho phép bạn dùng SQL để phân tích log, nhưng nó gây thêm áp lực đáng kể cho database engine. Trên production, hãy luôn sử dụng FILE. Nó nhanh hơn và các công cụ bên ngoài có thể phân tích nó mà không tiêu tốn tài nguyên database của bạn.
Phân tích: Giải mã dữ liệu
Log thô rất khó đọc. Một mục log trông sẽ như thế này:
# Thời gian: 2023-10-27T14:15:01.123456Z
# Thời_gian_truy_vấn: 8.452100 Thời_gian_khóa: 0.000123 Số_dòng_gửi: 5 Số_dòng_kiểm_tra: 1200000
SELECT * FROM transactions WHERE user_id = 999 ORDER BY created_at DESC;Đây chính là bằng chứng đanh thép. Nó mất 8.4 giây và quét 1.2 triệu dòng chỉ để tìm ra 5 bản ghi. Rõ ràng bạn cần một index cho cột user_id.
Bộ công cụ chuyên nghiệp
Đừng đọc các file này một cách thủ công. Hãy sử dụng mysqldumpslow để tổng hợp dữ liệu. Nó nhóm các câu truy vấn tương tự nhau để bạn có thể thấy câu nào đang gây ra tổng thời gian trễ lớn nhất.
# Sắp xếp theo tổng thời gian thực thi và hiển thị 10 "thủ phạm" hàng đầu
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.logNếu bạn cần thông tin chi tiết hơn, hãy sử dụng pt-query-digest từ bộ công cụ Percona Toolkit. Đây là tiêu chuẩn vàng cho các DBA. Nó cung cấp biểu đồ chi tiết và xác định khung giờ nào trong ngày database của bạn gặp khó khăn nhất.
Bước cuối cùng: EXPLAIN
Khi bạn tìm thấy một câu truy vấn tệ, hãy chạy nó với EXPLAIN. Lệnh này cho bạn thấy chính xác cách MySQL dự định thực thi nó. Nếu bạn thấy type: ALL, đó là quét toàn bộ bảng (full table scan) — database đang đọc từng dòng một trên đĩa. Việc thêm một index đúng mục tiêu thường có thể giảm câu truy vấn 8 giây đó xuống còn 10 mili giây.
Tinh chỉnh database không phải là công việc làm một lần là xong. Hãy duy trì slow query log với một ngưỡng hợp lý. Đó là hệ thống cảnh báo sớm, giúp bạn khắc phục các điểm nghẽn trước khi chúng biến thành những cuộc gọi lúc 2 giờ sáng.
