Posted inMySQL

Hướng dẫn dùng Performance Schema và sys Schema xử lý nghẽn I/O trên MySQL

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

1. Cơn ác mộng nghẽn I/O lúc nửa đêm

Đồng hồ điểm 1:30 sáng. Kênh Slack trực ca liên tục réo chuông cảnh báo P1. CPU server nhảy vọt lên 98%, disk I/O chạm trần 100% và disk latency (await) trên ổ NVMe tăng vọt từ 0.5ms lên tận 45ms. API giỏ hàng phản hồi từ 50ms thành timeout 30 giây. Khách hàng bắt đầu khiếu nại dồn dập.

Phản xạ quen thuộc của nhiều bạn là gõ ngay SHOW PROCESSLIST;. Màn hình tuôn ra cả trăm connection. Đa số câu SELECT đều kẹt ở trạng thái Sending data hoặc Creating tmp table. Nhìn vào ma trận đó, bạn không thể biết query nào là mồi lửa đầu tiên. Bảng nào đang khiến ổ cứng quá tải? Thread nào đang vét sạch bộ nhớ? Lệnh SHOW PROCESSLIST; hoàn toàn bất lực.

Mò mẫm giữa hàng trăm dòng trạng thái giống nhau chỉ khiến bạn thêm rối. Bạn cần số liệu đo lường cụ thể thay vì đoán mò trong hoảng loạn.

2. Điểm nghẽn I/O thực sự phát sinh từ đâu?

Ngoại trừ trường hợp phần cứng hỏng, nghẽn I/O trong InnoDB thường xuất phát từ 3 nguồn chính:

  • Quét cạn bảng (Full Table Scan): Query không ăn index buộc InnoDB đọc hàng triệu data page từ đĩa vào Buffer Pool. Khi Buffer Pool đầy, tiến trình flush dirty page chạy điên cuồng để dọn chỗ, bóp nghẹt băng thông đĩa.
  • Bảng tạm tràn xuống ổ cứng (Disk Temp Tables): Các lệnh GROUP BY, DISTINCT hoặc UNION vượt quá ngưỡng temptable_max_ram (trên MySQL 8.0) hay tmp_table_size. Dữ liệu lập tức bị đẩy xuống disk thành bảng tạm InnoDB hoặc MyISAM.
  • Tranh chấp ghi redo log & doublewrite buffer: Transaction với khối lượng insert/update lớn ghi dữ liệu ồ ạt, khiến I/O thread không kịp xả tải.

Các công cụ thông thường chỉ báo ổ đĩa bận. Chúng không chỉ ra thread nào đang bắt ổ đĩa phải chờ.

3. Giới hạn của các công cụ chẩn đoán truyền thống

Cách 1: Lệnh hệ điều hành (top, htop, iostat)

# Theo dõi I/O mở rộng mỗi giây
iostat -xz 1 5

Hạn chế: Lệnh iostat chỉ cho biết tiến trình mysqld ngốn 100% %util và await cao ngất ngưởng. Hệ điều hành không thể phân giải sâu vào bên trong. Bạn không thể biết table nào hay tệp dữ liệu .ibd nào đang phát sinh hàng nghìn IOPS đó.

Cách 2: Đọc Slow Query Log kết hợp EXPLAIN

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC;

Hạn chế: Slow log chỉ ghi lại dữ liệu sau khi query đã chạy xong. Nếu một transaction nặng đang treo 15 phút, slow log lúc đó hoàn toàn trống rỗng. Thêm vào đó, trường hợp ‘cái chết từ ngàn vết cắt’ — 1.000 query nhẹ chạy 20ms đồng loạt trong 1 giây — sẽ lọt qua slow log nếu bạn đặt long_query_time = 1, dù chúng gián tiếp bóp chết I/O.

4. Giải pháp: Tận dụng Performance Schema và sys Schema

Từ MySQL 5.7 và mặc định từ MySQL 8.0, Performance Schema được bật sẵn với overhead rất thấp (chỉ khoảng 1% đến 3% CPU). Đi kèm với nó là sys Schema — hệ thống các View dựng sẵn giúp chuyển hóa số liệu thô phức tạp thành định dạng dễ đọc như ms, sec, MB, GB.

Bước 1: Xác nhận Performance Schema đang hoạt động

Kiểm tra biến hệ thống trên server:

SHOW VARIABLES LIKE 'performance_schema';

Nếu trả về giá trị ON, hệ thống đã sẵn sàng. Nếu hiển thị OFF, bạn thêm thiết lập sau vào tệp /etc/my.cnf rồi khởi động lại MySQL:

[mysqld]
performance_schema=ON

Bước 2: Tìm top 5 câu truy vấn tiêu tốn thời gian nhất

Hãy truy vấn view statement_analysis để tìm câu lệnh chiếm tổng thời gian thực thi (total latency) cao nhất kể từ lúc server khởi động:

SELECT 
    query,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg,
    rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 5;

So sánh hai cột rows_examined_avg và rows_sent_avg. Nếu một query quét trung bình 500.000 dòng chỉ để lấy ra 10 dòng kết quả, bạn đã bắt đúng thủ phạm thiếu index gây đọc đĩa lãng phí.

Bước 3: Định vị bảng bị quét cạn (Full Table Scan) nhiều nhất

View schema_tables_with_full_table_scans giúp lọc ra ngay các bảng đang bị quét cạn nhiều nhất:

SELECT 
    object_schema,
    object_name,
    rows_full_scanned,
    latency
FROM sys.schema_tables_with_full_table_scans
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
ORDER BY latency DESC
LIMIT 5;

Một bảng chứa 10 triệu bản ghi mà có chỉ số rows_full_scanned nhảy liên tục chứng tỏ ứng dụng đang bỏ quên điều kiện lọc trên cột được đánh index.

Bước 4: Kiểm tra file .ibd nào chịu tải I/O nặng nhất

Để biết chính xác file vật lý nào đang làm nghẽn ổ đĩa, truy vấn view io_global_by_file_by_bytes:

SELECT 
    file,
    count_read,
    total_read,
    count_write,
    total_written
FROM sys.io_global_by_file_by_bytes
WHERE file LIKE '%.ibd'
ORDER BY (total_read + total_written) DESC
LIMIT 5;

Kết quả chỉ rõ file tablespace nào gánh dung lượng đọc/ghi lớn nhất. Bạn có thể cân nhắc chuyển bảng đó sang phân vùng ổ cứng riêng, partition lại dữ liệu hoặc lưu cache trên Redis.

Bước 5: Phát hiện query ép dữ liệu tạm tràn xuống ổ cứng

View statements_with_temp_tables giúp bắt trọn những câu lệnh tạo disk temporary table:

SELECT 
    query,
    exec_count,
    memory_tmp_tables,
    disk_tmp_tables,
    avg_latency
FROM sys.statements_with_temp_tables
WHERE disk_tmp_tables > 0
ORDER BY disk_tmp_tables DESC
LIMIT 5;

Nếu disk_tmp_tables lớn, hãy kiểm tra xem câu SQL có chứa SELECT * đi kèm cột TEXT hay BLOB khi GROUP BY không. Đồng thời, kiểm tra thông số temptable_max_ram (trên MySQL 8.0, mặc định 1GB) hoặc tmp_table_size trên MySQL 5.7 để điều chỉnh cho phù hợp.

Bước 6: Reset metric để kiểm chứng kết quả tối ưu

Sau khi tạo index hoặc sửa code ứng dụng, hãy làm sạch bộ đếm thống kê để đo lường phiên chạy mới:

CALL sys.ps_truncate_all_tables(FALSE);

Thao tác này chỉ xóa dữ liệu thống kê trong RAM của Performance Schema. Dữ liệu bảng nghiệp vụ hoàn toàn không bị ảnh hưởng. Giờ bạn có thể đối chiếu chính xác tải I/O trước và sau khi tối ưu.

Share:
Tags: