Bài 4: Tối ưu MySQL: đo truy vấn, bộ nhớ và I/O trước khi sửa
Tối ưu MySQL bằng baseline, query digest, EXPLAIN và số đo bộ nhớ/I/O. Hiểu redo, dedicated server và tác động của từng thay đổi.
Ứng dụng chậm lúc cao điểm nhưng CPU database chưa đầy. Tăng Buffer Pool hoặc I/O thread ngay lúc này có thể không giúp nếu thời gian đang mất ở lock, query plan hoặc hàng đợi kết nối. Việc tối ưu nên bắt đầu bằng một câu hỏi đo được: thao tác nào chậm, vào lúc nào, và chờ ở đâu?
Phạm vi bài là MySQL 8.4/InnoDB. Ví dụ số và SQL là minh họa có điều kiện, chưa chạy benchmark. Không dùng một file my.cnf chung cho mọi loại máy hoặc áp dụng đồng thời nhiều thay đổi.
Ghi baseline trước khi chọn tham số
Thu thập latency p50/p95/p99 ở ứng dụng, throughput, lỗi, concurrency và thời điểm cùng CPU, memory pressure, storage latency. Một lần chạy query nhanh trong CLI không đại diện connection pool và tải thực. Phân biệt cold cache, warm cache và thời gian truyền kết quả cho client.
MySQL 8 không còn Query Cache của server. Redis hoặc cache ứng dụng có thể giảm tải nhưng cần quy tắc invalidation, freshness và quyền truy cập riêng; chúng không phải bản thay thế tự động có cùng semantics. Trước khi thêm cache, xem có đang đọc quá nhiều dòng do thiếu index hay không.
Đọc query và working set trước khi tăng cache
Nhóm query theo digest để thấy loại nào chiếm tổng thời gian, tần suất và rows examined. Một query hiếm chạy 20 giây và một query 10 ms chạy hàng triệu lần tạo hai bài toán khác nhau. Dữ liệu digest phụ thuộc instrumentation và có thể bị reset, nên lưu thời gian cùng phạm vi thu thập.
SELECT digest_text, count_star, sum_timer_wait,
sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
Đọc raw timer theo đơn vị của Performance Schema, không gắn nhầm nhãn millisecond. Tăng pool chỉ sau khi đánh giá working set, đọc vật lý và headroom. Bài Buffer Pool RAM lớn giải thích ngân sách chi tiết; hit ratio cao không loại trừ lock hoặc plan tệ.
Đo cùng cửa sổ và cùng workload. Không tăng nhiều tham số cùng lúc. EXPLAIN ANALYZE thực sự chạy câu lệnh hỗ trợ.
Cuộn ngang hoặc chạm sơ đồ để mở bản đầy đủ. Hình được biên tập, không phải ảnh chụp kiểm thử.
Hiểu cấu hình tự động theo đúng bản
innodb_dedicated_server chỉ phù hợp khi MySQL được dành tài nguyên riêng. MySQL 8.4 có quy tắc tự tính Buffer Pool và redo capacity; không nên mô tả nó bằng bộ biến innodb_log_file_size cũ rồi áp sang mọi nhánh. Giá trị bạn đặt tường minh có thể ghi đè phần tự động tương ứng.
SHOW GLOBAL VARIABLES WHERE Variable_name IN
('innodb_dedicated_server','innodb_buffer_pool_size',
'innodb_redo_log_capacity','innodb_flush_method');
Đọc cấu hình hiệu lực sau khởi động. Trong container hoặc host chia sẻ, kiểm tra memory limit và phần RAM hệ thống phát hiện được. Bật tự động không thay kiểm tra OOM, swap, I/O và các biến do công cụ quản lý cấu hình đang áp dụng.
Redo lớn hơn có trade-off
Redo lưu thay đổi phục vụ crash recovery, không phải cache kết quả SELECT. Dung lượng redo ảnh hưởng áp lực checkpoint và khoảng công việc có thể phải phục hồi; không có giá trị 4 GiB tối ưu cho mọi hệ thống. Quan sát tốc độ phát sinh redo, checkpoint pressure, dung lượng trống và mục tiêu phục hồi.
SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_capacity';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
Biến capacity hỗ trợ thay đổi động ở nhánh phù hợp, nhưng tiến độ điều chỉnh và tác động không nhất thiết tức thì. Trước SET PERSIST, lưu giá trị runtime/persist trước đó và kế hoạch rollback. Đừng giảm durability như innodb_flush_log_at_trx_commit hoặc sync_binlog chỉ để có benchmark đẹp mà chưa được chấp nhận RPO.
Đọc flush và storage latency, giữ cơ chế an toàn
innodb_io_capacity là đầu vào cho hoạt động background, không phải cách đặt trực tiếp giới hạn IOPS toàn bộ MySQL. Giá trị quá thấp hoặc quá cao đều có thể làm phân bố I/O xấu đi. Đánh giá queue depth, latency của storage và tốc độ dirty-page flushing trong tải tương đương.
Doublewrite bảo vệ khỏi một số tình huống ghi trang dở dang. Không tắt chỉ vì ổ là SSD/NVMe hoặc có cache. Muốn thay cơ chế phải có bảo đảm nền tảng và phân tích khả năng phục hồi tương ứng. Nguồn storage thay đổi sau khi migrate VM cũng có thể khiến cấu hình cũ không còn phù hợp.
Các điểm kiểm chứng theo chủ đề; không phải mọi kết quả đều dẫn tới một nguyên nhân duy nhất.
Cuộn ngang hoặc chạm sơ đồ để mở bản đầy đủ. Hình được biên tập, không phải ảnh chụp kiểm thử.
Slow log cần cửa sổ và bảo vệ dữ liệu
Slow log giúp quan sát những câu lệnh thỏa điều kiện ghi log, không phải toàn bộ thời gian phía ứng dụng. Việc bật cần cân nhắc dung lượng, quyền đọc và dữ liệu nhạy cảm trong SQL. Ghi lại giá trị trước, đặt thời gian kết thúc thu thập và có log rotation.
SHOW GLOBAL VARIABLES WHERE Variable_name IN
('slow_query_log','long_query_time','log_output',
'slow_query_log_file','min_examined_row_limit');
Không tự động đặt long_query_time xuống rất thấp trên tải lớn. Với biến có phạm vi session, giá trị toàn cục mới có thể chỉ đi vào kết nối mới; kiểm tra pool ứng dụng thay vì cho rằng mọi phiên đã đổi. Tách mẫu query, bind values phù hợp và timestamp trước khi phân tích.
EXPLAIN ANALYZE thực sự chạy truy vấn
EXPLAIN FORMAT=TREE
SELECT id FROM app_lab.orders WHERE customer_id = 42;
EXPLAIN ANALYZE
SELECT id FROM app_lab.orders WHERE customer_id = 42;
Ví dụ yêu cầu schema/table/index lab đã có. EXPLAIN thông thường cho kế hoạch ước lượng; ANALYZE chạy câu lệnh hỗ trợ và thu thời gian thực tế. Đừng thử tùy tiện query rất nặng trên production. Đọc rows, loops, iterator và khác biệt estimated/actual thay vì chỉ tìm chữ index.
Thử một thay đổi như index phù hợp với filter/join/order, đo lại và kiểm tra chi phí ghi, dung lượng, lock khi DDL. Checklist cuối: so cùng dữ liệu/tải; không đổi nhiều biến; có rollback; đo cả error và latency nghiệp vụ; không nhận ví dụ của bài là kết quả benchmark.
Hit ratio đẹp nhưng một màn hình vẫn chậm
Tình huống giả định để luyện phân tích, không phải kết quả đo.
Giả sử hit ratio ở mức cao nhưng API tìm đơn hàng chậm sau khi bảng tăng dữ liệu. Digest cho thấy rows examined tăng nhiều hơn rows sent, còn storage latency không đổi đáng kể. Đây là lý do mở nhánh query plan và index; chưa có căn cứ tăng pool hoặc đổi flush method.
Lấy query mẫu với parameter đại diện, chạy EXPLAIN trước và ANALYZE trong lab phù hợp. So estimate với actual rows/loops, thứ tự join và sort. Nếu thống kê hoặc độ lệch phân bố làm optimizer chọn khác, cần hiểu dữ liệu thay vì gắn FORCE INDEX như phản xạ. Index còn có chi phí ghi và dung lượng.
Sau một thay đổi, so cùng phân bố parameter và concurrency. Chỉ đo một customer có ít đơn có thể làm kết luận sai với customer lớn. Kiểm tra cả p95/p99, lỗi và throughput thay vì lấy thời gian nhanh nhất trong mười lần chạy.
Nếu plan tốt mà thời gian vẫn tăng, quay lại lock wait, connection pool, transaction dài hoặc phần ứng dụng xử lý kết quả. Database có thể trả một tập dữ liệu lớn khiến thời gian nằm ở serialization và mạng. Phân chia thời gian ở các lớp giúp tránh sửa server variable cho một nút thắt ngoài server.
Nguồn đối chiếu và bài đọc tiếp
Phạm vi tham chiếu là tài liệu theo phiên bản ghi trong bài. Khi áp dụng, đối chiếu model triển khai, bản patch và quyền đang dùng; các ví dụ chưa được kiểm thử trên máy chủ thật.
