Posted inMySQL

Theo dõi hiệu suất MySQL với Performance Schema và sys Schema: Tìm bottleneck nhanh chóng

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Khi nào bạn cần đến Performance Schema?

Hồi table users trong dự án của mình mới có vài trăm nghìn row, mọi thứ chạy ổn. Nhưng khi dữ liệu vượt 10 triệu row, slow query bắt đầu xuất hiện — response time tăng đột ngột, CPU MySQL leo lên 80–90%. Lúc đó mình mở Slow Query Log (đã có bài riêng về cái này), thấy danh sách câu SQL chậm, nhưng vẫn chưa rõ tại sao chậm: do thiếu index? Do lock? Do I/O? Hay do quá nhiều connection cùng lúc?

Đó là lúc Performance Schema vào cuộc. Công cụ chẩn đoán nội tại này của MySQL cho bạn thấy chi tiết từng câu query đang chạy bao lâu, thread nào đang đợi lock, disk bị đọc mấy lần — những thứ mà Slow Query Log không với tới được.

sys Schema là lớp bọc bên ngoài Performance Schema. Thay vì phải tự tay JOIN 4–5 bảng, sys Schema cung cấp sẵn các view dễ dùng như sys.statement_analysis hay sys.innodb_lock_waits — truy vấn thẳng, đọc kết quả ngay.

Khái niệm cốt lõi cần nắm trước

Performance Schema là gì?

Đây là một database đặc biệt trong MySQL (tên là performance_schema), bật mặc định từ MySQL 5.6+. MySQL âm thầm ghi lại mọi hoạt động vào đó: query nào chạy bao lâu, thread nào đang đợi gì, bảng nào được đọc nhiều nhất.

Dữ liệu lưu hoàn toàn trong RAM — truy vấn cực nhanh, nhưng sẽ mất sạch sau khi restart MySQL.

sys Schema là gì?

sys Schema (database tên sys) có sẵn từ MySQL 5.7.7+. Bản thân nó không thu thập dữ liệu mới — chỉ đọc từ performance_schema và information_schema rồi trình bày lại dưới dạng view dễ đọc hơn. Ví dụ: thay vì JOIN 4–5 bảng trong performance_schema, bạn chỉ cần SELECT * FROM sys.statements_with_runtimes_in_95th_percentile.

Kiểm tra Performance Schema đã bật chưa

-- Kiểm tra trạng thái
SHOW VARIABLES LIKE 'performance_schema';

-- Kết quả mong đợi:
-- +--------------------+-------+
-- | Variable_name      | Value |
-- +--------------------+-------+
-- | performance_schema | ON    |
-- +--------------------+-------+

Nếu kết quả là OFF, thêm dòng sau vào /etc/mysql/my.cnf rồi restart MySQL:

[mysqld]
performance_schema = ON

Thực hành: Tìm bottleneck từng bước

Bước 1 — Xác định query nào ngốn tài nguyên nhiều nhất

Câu lệnh mình chạy đầu tiên mỗi khi debug:

-- Top 10 câu query chậm nhất (dùng sys Schema)
SELECT 
    query,
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

Chú ý cột rows_examined_avg: nếu con số này lớn hơn nhiều so với rows_sent_avg, MySQL đang scan thừa rất nhiều row — thường do thiếu index hoặc index chưa phù hợp.

Ví dụ thực tế: mình từng thấy một query có rows_examined_avg = 9,800,000 nhưng rows_sent_avg = 12. Full table scan trên bảng 10 triệu row chỉ để lấy 12 bản ghi. Thêm composite index là xong.

Bước 2 — Phát hiện lock wait (query bị block)

Lock wait là thủ phạm thầm lặng — query bản thân chạy nhanh, nhưng phải xếp hàng đợi lock từ transaction khác. Slow Query Log không bắt được cái này.

-- Xem các thread đang bị block hiện tại
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Hoặc dùng sys Schema gọn hơn:

-- Tóm tắt lock wait theo table
SELECT * FROM sys.innodb_lock_waits\G

Nếu thấy một thread đang block nhiều thread khác, kiểm tra xem đó có phải transaction chưa COMMIT không. Nguyên nhân hay gặp nhất: ứng dụng mở transaction, gọi API bên ngoài mất 3–5 giây, rồi mới COMMIT — trong suốt thời gian đó lock vẫn giữ nguyên.

Bước 3 — Phân tích I/O: Table nào bị đọc/ghi nhiều nhất?

-- Top table theo số lần I/O
SELECT 
    table_name,
    total_read_latency,
    total_write_latency,
    io_read_requests,
    io_write_requests
FROM sys.schema_table_statistics
ORDER BY total_read_latency DESC
LIMIT 10;

Table nào có total_read_latency cao bất thường so với phần còn lại — đó là ứng cử viên cần xem lại index và caching strategy.

Bước 4 — Kiểm tra index không được dùng

View mình hay chạy sau mỗi đợt tối ưu — săn index thừa, ngốn RAM mà không ai dùng:

-- Index chưa từng được dùng từ lần restart MySQL gần nhất
SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema', 'sys', 'information_schema', 'mysql');

Không nên vội xóa ngay. Chờ ít nhất vài ngày để chắc không có query theo chu kỳ nào dùng đến (ví dụ báo cáo cuối tháng chạy mỗi 30 ngày).

Bước 5 — Xem user/host nào đang tạo tải nhiều nhất

-- Thống kê theo user
SELECT 
    user,
    total_latency,
    statements,
    table_scans,
    rows_sent
FROM sys.user_summary
ORDER BY total_latency DESC;

Hữu ích khi nhiều service cùng kết nối vào một MySQL server — pinpoint được đúng service nào đang gây tải bất thường thay vì mò kim đáy bể.

Bước 6 — Reset thống kê để đo từ đầu

Sau khi tối ưu xong, reset dữ liệu Performance Schema để đo lại từ trạng thái sạch:

-- Reset toàn bộ thống kê (không cần restart MySQL)
CALL sys.ps_truncate_all_tables(FALSE);

-- Hoặc reset từng loại:
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

Quy trình chẩn đoán thực tế

Khi nhận báo cáo “MySQL chậm”, mình thường đi theo thứ tự sau:

  1. Chạy sys.statement_analysis — tìm top query theo total_latency và rows_examined_avg
  2. Chạy sys.innodb_lock_waits — kiểm tra có lock contention không
  3. Chạy sys.schema_table_statistics — xem table nào bị I/O nhiều
  4. EXPLAIN câu query nghi ngờ — xác nhận có dùng index chưa
  5. Fix (thêm index / tối ưu query / thu hẹp transaction scope)
  6. Reset thống kê → theo dõi lại 24–48 giờ

Bước 4 không thể bỏ: Performance Schema cho bạn biết cái gì đang chậm, còn EXPLAIN mới cho biết tại sao chậm ở mức execution plan.

Kết luận

Performance Schema và sys Schema không thay thế Slow Query Log — chúng bổ sung cho nhau. Slow Query Log nhanh để bật và xem ngay. Performance Schema cho chiều sâu hơn: lock wait, I/O breakdown, index usage, tải theo từng user.

Performance Schema nên để luôn bật trong production. Overhead thực tế dưới 5% theo benchmark của Oracle — không đáng lo. Quan trọng hơn: khi sự cố xảy ra, bạn đã có dữ liệu lịch sử thay vì phải tái hiện lại vấn đề từ đầu.

Bắt đầu đơn giản thôi: mở sys.statement_analysis, nhìn vào cột rows_examined_avg. Con số đó phản ánh ngay sức khỏe của MySQL — query nào đang scan thừa, bảng nào đang bị lạm dụng.

Share:
Tags: