Posted inDatabase

MySQL Performance Schema: Chẩn đoán Query Chậm bằng Bảng Thống kê Nội bộ

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Performance Schema là lớp quan sát nội bộ của MySQL — tập hợp hơn 80 bảng ghi lại mọi hoạt động bên trong instance của bạn, từ thời gian thực thi query đến mutex wait. Sau sáu tháng dùng hàng ngày trong môi trường production, tôi gần như không còn phải với tay đến các công cụ profiling ngoài cho hầu hết tác vụ debug.

Đây là những gì thực sự quan trọng.

Khởi động nhanh: Chạy được trong 5 phút

Đầu tiên, kiểm tra xem Performance Schema đã được bật chưa:

SHOW VARIABLES LIKE 'performance_schema';

Nếu kết quả trả về OFF, hãy bật nó trong my.cnf:

[mysqld]
performance_schema = ON

Khởi động lại MySQL, sau đó xác nhận:

USE performance_schema;
SHOW TABLES;

Bạn sẽ thấy hơn 80 bảng. Những bảng bạn dùng đến 90% thời gian là:

  • events_statements_summary_by_digest — thống kê query tổng hợp
  • events_waits_summary_global_by_event_name — MySQL đang chờ đợi gì
  • table_io_waits_summary_by_table — thống kê I/O theo từng bảng
  • file_summary_by_event_name — phân tích I/O theo file

Để có kết quả nhanh, chạy câu này để tìm 5 mẫu query chậm nhất ngay lúc này:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

Chỉ riêng câu query này đã giúp tôi tiết kiệm hàng giờ đào bới log trên nhiều dự án khác nhau.

Tìm hiểu sâu: Ý nghĩa của các con số

Bảng Statement Digest

Bảng events_statements_summary_by_digest chuẩn hóa các query — thay thế giá trị cụ thể bằng ? — nên WHERE id = 1 và WHERE id = 42 trở thành cùng một digest. Điều này rất quan trọng để phát hiện pattern qua hàng nghìn lần thực thi.

Các cột quan trọng cần kéo ra cùng nhau:

SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(MIN_TIMER_WAIT / 1e9, 2) AS min_ms,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT / 1e9, 2) AS max_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_db'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

SUM_NO_INDEX_USED là dấu hiệu rõ ràng nhất — nếu con số này cao với một query chạy hàng nghìn lần mỗi giờ, bạn đã tìm ra index còn thiếu. Tỉ lệ SUM_ROWS_EXAMINED / SUM_ROWS_SENT cho biết hiệu quả của query. Tỉ lệ 1000:1 có nghĩa MySQL phải quét 1000 dòng để trả về 1 — hầu như luôn là vấn đề về index.

Wait Events: Tìm Bottleneck Ẩn

Qua kinh nghiệm làm việc với MySQL, PostgreSQL và MongoDB trên nhiều dự án khác nhau, mỗi hệ thống có mô hình quan sát riêng. Hệ thống wait event của MySQL là thứ tôi ước mình học sớm hơn — nó tiết lộ những bottleneck mà slow query log không bao giờ lộ ra.

SELECT
  EVENT_NAME,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
  AND EVENT_NAME NOT LIKE '%idle%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

Những gì cần chú ý:

  • wait/io/file/innodb/innodb_data_file — disk I/O là bottleneck; cân nhắc tăng RAM hoặc dùng storage nhanh hơn
  • wait/synch/mutex/innodb/buf_pool_mutex — tranh chấp buffer pool; có thể cần tăng innodb_buffer_pool_size
  • wait/lock/table/sql/handler — tranh chấp table lock; kiểm tra transaction còn thiếu hoặc xung đột lock

Phân tích Table I/O

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  ROUND(SUM_TIMER_READ / 1e9, 2) AS read_ms,
  ROUND(SUM_TIMER_WRITE / 1e9, 2) AS write_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'your_db'
ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
LIMIT 10;

Câu này chỉ ra đúng bảng nào đang bị truy cập nhiều nhất. Một lần chạy trên hệ thống của khách hàng, tôi phát hiện một bảng logging tích lũy hàng triệu dòng mà không có chiến lược lưu trữ lâu dài nào — nó đang thêm 400ms vào mỗi request có JOIN đến bảng đó.

Nâng cao: Theo dõi Query Cụ thể

Bật Statement History

Mặc định, MySQL chỉ lưu dữ liệu tổng hợp. Để debug một session cụ thể, bật lịch sử thực thi từng lần:

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

Sau đó chạy query có vấn đề và xem lại lịch sử:

SELECT
  SQL_TEXT,
  TIMER_WAIT / 1e9 AS duration_ms,
  ROWS_EXAMINED,
  ROWS_SENT,
  CREATED_TMP_DISK_TABLES,
  CREATED_TMP_TABLES,
  SELECT_FULL_JOIN,
  NO_INDEX_USED
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 20;

CREATED_TMP_DISK_TABLES khác 0 nghĩa là MySQL đã tràn bảng tạm ra đĩa — hãy điều chỉnh tmp_table_size và max_heap_table_size.

Dùng sys Schema (MySQL 5.7.7+)

Schema sys bọc Performance Schema thành các view dễ đọc hơn:

-- Query tốn nhiều thời gian thực thi nhất
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- Query không dùng index
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- Thống kê bảng tổng quan
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'your_db'
ORDER BY total_latency DESC;

Tôi đã thiết lập một cron job chạy hàng tuần để dump sys.statements_with_runtimes_in_95th_percentile ra file. Phân tích xu hướng theo tuần giúp phát hiện regression mà monitoring theo thời điểm sẽ bỏ sót.

Phân tích theo User

Khi nhiều ứng dụng dùng chung một instance MySQL, câu này cho biết ai đang gây ra tải:

SELECT * FROM sys.user_summary ORDER BY total_latency DESC;

Mẹo Thực tế

Reset thống kê giữa các lần load test. Thống kê tích lũy từ khi server khởi động, điều này làm lệch kết quả benchmark. Trước khi test một thay đổi:

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

Đừng để statement history bật mãi. Bảng events_statements_history_long có giới hạn số dòng cố định (mặc định 10.000) và tốn thêm overhead. Bật khi debug xong thì tắt:

UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME = 'events_statements_history_long';

Kết hợp với EXPLAIN. Performance Schema cho biết cái gì đang chậm, EXPLAIN cho biết tại sao. Lấy một query thực từ slow query log và chạy EXPLAIN ANALYZE sau khi đã xác định nó qua digest.

Xây dựng script capture baseline. Một job Python đơn giản truy vấn Performance Schema vài phút một lần và ghi vào time-series store sẽ cho bạn dữ liệu xu hướng:

import mysql.connector

def capture_top_queries(conn, limit=20):
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            DIGEST,
            DIGEST_TEXT,
            COUNT_STAR,
            ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
            ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms,
            SUM_NO_INDEX_USED AS no_index_count
        FROM performance_schema.events_statements_summary_by_digest
        WHERE SCHEMA_NAME IS NOT NULL
        ORDER BY SUM_TIMER_WAIT DESC
        LIMIT %s
    """, (limit,))
    return cursor.fetchall()

Theo dõi avg_ms thay đổi thế nào sau mỗi lần deploy đã giúp tôi phát hiện regression trước khi người dùng nhận ra không ít hơn một lần.

Chú ý đến sự thay đổi vị trí trong danh sách query hàng đầu. Nếu query ở hạng #1 thay đổi giữa các lần monitoring, có gì đó đã thay đổi — hoặc pattern tải thay đổi, hoặc có query mới được deploy. Hãy coi đó là tín hiệu cần điều tra ngay, đừng đợi đến khi người dùng phàn nàn.

Performance Schema không hào nhoáng, nhưng đây là tín hiệu đáng tin cậy nhất tôi tìm thấy để hiểu MySQL hoạt động bên trong. Dữ liệu đã được thu thập sẵn — hầu hết các team chỉ là chưa biết cách đọc nó.

Share:
Tags: