Câu chuyện thực tế: Khi server ‘đứng hình’ dù CPU vẫn rảnh
Mình từng quản lý hệ thống tracking cho một sàn thương mại điện tử. Mọi thứ êm đẹp cho đến khi lượng user tăng vọt. Mỗi hành động click, xem hàng hay thêm giỏ đều phải log lại để phân tích. Ban đầu, 300-500 record/phút chẳng là gì. Nhưng khi con số chạm mốc 5.000 record/giây, I/O Wait (thời gian chờ đĩa) bắt đầu nhảy vọt lên 40-50%, báo động đỏ liên tục.
Lúc đó, các câu lệnh INSERT mất tới 2-3 giây để hoàn thành. Cả hệ thống treo cứng dù RAM và CPU vẫn còn dư dả. Mình nhận ra cấu hình mặc định của MySQL rất an toàn, nhưng chính sự an toàn đó lại là ‘nút thắt cổ chai’ cho các hệ thống ghi dữ liệu liên tục (Write-Heavy).
Giải mã: Tại sao MySQL lại ghi chậm?
Để bảo vệ dữ liệu, InnoDB (storage engine của MySQL) phải làm việc rất kỹ. Khi bạn INSERT một dòng, nó không ghi thẳng vào file dữ liệu ngay. Thay vào đó, MySQL thực hiện một chuỗi thao tác phức tạp để đảm bảo tính ACID:
- Redo Log: Ghi thay đổi vào sổ nháp trước khi cập nhật chính thức.
- Doublewrite Buffer: Ghi dữ liệu hai lần để tránh hỏng trang (torn pages) nếu mất điện đột ngột.
- Flush đĩa: Mỗi lần commit, MySQL ép đĩa cứng thực hiện thao tác ghi vật lý (fsync).
Việc ép đầu đọc đĩa di chuyển liên tục để xử lý các transaction nhỏ lẻ chính là thủ phạm gây nghẽn I/O.
4 bước ‘mở khóa’ hiệu năng ghi cho MySQL
1. Nới rộng ‘sổ nháp’ Redo Log
Redo Log (innodb_log_file_size) giống như một cuốn sổ ghi chép tạm. Nếu sổ quá bé, MySQL phải dừng việc ghi mới để dọn dẹp dữ liệu cũ (checkpoint). Quá trình này khiến hệ thống bị khựng (lag) định kỳ.
Khi bảng log của mình vượt 50 triệu dòng, file log mặc định 128MB là quá nhỏ. Mình đã tăng nó lên 1GB. Kết quả là tần suất checkpoint giảm hẳn, giúp hệ thống chạy mượt hơn trong giờ cao điểm.
# Cấu hình trong file my.cnf
[mysqld]
innodb_log_file_size = 1G
innodb_log_files_in_group = 2Lưu ý: Bạn cần restart MySQL để thay đổi này có hiệu lực.
2. Tắt Doublewrite Buffer (Có điều kiện)
Doublewrite Buffer giúp chống hỏng dữ liệu nhưng lại ngốn gấp đôi băng thông I/O. Nếu bạn dùng SSD chuyên dụng có tụ điện bảo vệ hoặc hệ thống file hiện đại như ZFS, hãy mạnh dạn tắt nó. Thao tác này có thể giúp tốc độ ghi tăng thêm khoảng 30% ngay lập tức.
[mysqld]
innodb_doublewrite = 03. Tuyệt chiêu Batch Insert
Đừng bao giờ chạy 1.000 câu lệnh INSERT riêng biệt. Mỗi lệnh SQL đơn lẻ đều tốn chi phí mở transaction và flush đĩa. Hãy gộp chúng lại thành một lô (batch).
Cách làm chậm: Insert từng dòng trong vòng lặp. Rất tốn tài nguyên.
Cách làm nhanh: Gộp 500 – 1.000 bản ghi vào một câu lệnh duy nhất. Đây là ‘điểm ngọt’ (sweet spot) giúp tăng tốc độ ghi lên gấp hàng chục lần mà không làm quá tải bộ nhớ đệm.
# Dùng executemany trong Python để gộp 1000 records
cursor.executemany("INSERT INTO logs (msg) VALUES (%s)", list_of_1000_items)
connection.commit()4. Nới lỏng tính an toàn với innodb_flush_log_at_trx_commit
Đây là thông số ‘quyền lực’ nhất. Mặc định là 1 (an toàn nhất nhưng chậm nhất). Nếu bạn set về 2, MySQL sẽ ghi log vào OS cache sau mỗi commit nhưng chỉ flush xuống đĩa mỗi giây một lần.
Với hệ thống tracking, mất 1 giây dữ liệu khi server sập nguồn là rủi ro chấp nhận được để đổi lấy tốc độ ghi cực nhanh.
[mysqld]
innodb_flush_log_at_trx_commit = 2Kết quả sau khi tối ưu
Sau khi áp dụng combo: Tăng Redo Log lên 2GB, set flush_log về 2 và dùng Batch Insert, chỉ số I/O Wait trên server mình giảm từ 40% xuống còn dưới 5%. Hệ thống chịu tải gấp 10 lần mà CPU vẫn ‘thong dong’. Tối ưu database không chỉ là viết code xịn, mà là hiểu cách nó tương tác với phần cứng bên dưới.
