Setup MySQL và bạn bỏ quên những thứ này thì thật dở

Thời đại AI và bạn dễ dàng deploy MySQL lên, cách đơn giản nhất là:
"Hãy viết cho tôi docker compose để deploy mysql"

AI trong vòng 3 giây sẽ trả ngay cho bạn response sau:

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql_server
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: my_secure_root_password
      MYSQL_DATABASE: my_database
      MYSQL_USER: my_user
      MYSQL_PASSWORD: my_password
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql

volumes:
  mysql_data:

Tuyệt quá, chạy thử xem nào: 

docker compose up -d

Và MySQL chạy đúng như bạn mong đợi, cắm backend vào thử, ngon quá, tốt quá rồi!

Mình đã trở thành DevOps Engineer

Hoặc chi ít là vậy, nếu sự cố vài ngày sau đó không xảy ra.

Ram tổng server có 16GB RAM

Container MySQL chỉ đang dùng có 1GB RAM

Nhưng mỗi query, cho select theo id, dù là đơn giản nhất tốn tới gần 1 giây.

Tại sao?

Bạn lại đi hỏi con AI dấu yêu của bạn

AI đáp

"Bạn hãy thử kiểm tra xem có query nặng nào đang giữ thread không"

"Bạn hãy thử kiểm tra xem có bảng đó có đánh index chưa"

Thử hết tất cả, và tất nhiên là vẫn không có tác dụng. Đó là lý do vì sao chúng ta có bài viết này.

Series này sẽ tổng hợp cho bạn tất tần tật những thứ quan trọng mà bạn không để ý khi setup MySQL, xoáy sâu vào file docker-compose ban đầu mà AI kia generate, mình sẽ cùng bạn soi lõi sai của file này.

Lỗi đầu tiên, và cũng là bài đầu tiên trong series:

1. Thiếu cấu hình innodb_buffer_pool_size

Chính xác đây là nguyên nhân quan trọng nhất gây nên tình trạng mà mình đã đề cập ở đầu bài viết. 

Nếu bạn nóng vội thì để sửa lỗi trên, chỉ cần set thêm:
--innodb_buffer_pool_size=

Vậy là xong. Lỗi được fix, giá trị điền vào innodb_buffer_pool_size là bao nhiêu tùy thuộc mức bạn ước lượng cho traffic hệ thống của bạn.

Nhưng bạn chỉ biết tới nhiêu đó, thì bạn dở.

Bạn nên hiểu sâu hơn chút xíu

Đầu tiên cần biết:

2. innodb_buffer_pool_size là gì?

Để hiểu tường tận và trực quan nhất về innodb_buffer_pool_size, bạn hãy tưởng tượng:

 Bạn có chơi Minecraft không? Ổ cứng giống như cả cái kho đồ. RAM giống như cái bàn chế tạo - crafting table của bạn vậy 

Mỗi khi bạn cần tìm dữ liệu (đọc một trang sách) hoặc sửa dữ liệu (viết vào trang sách):

• Nếu trang sách đó đang nằm sẵn trên mặt bàn, bạn chỉ mất một cái liếc mắt (vài nano giây).
• Nếu trang sách đó đang nằm sâu trong kho, bạn phải đi vào kho lấy ra rồi mới đọc được (mất vài mili giây – chậm hơn từ hàng chục nghìn đến cả triệu lần).

innodb_buffer_pool_size chính là diện tích mặt bàn làm việc mà bạn cấp cho MySQL.

Trong storage engine InnoDB, dữ liệu trên đĩa không được lưu rải rác từng dòng nhỏ lẻ, mà chia thành từng khối gọi là Page (mặc định mỗi page nặng 16 KB).

Buffer Pool là một vùng RAM chuyên dụng để chứa các Page này. Nó đảm nhận hai nhiệm vụ sống còn:

1. Data & Index Caching (Tăng tốc đọc)

Khi bạn chạy SELECT * FROM users WHERE id = 100:

1. MySQL tìm xem Page chứa id = 100 (và cả cây Index B-Tree tương ứng) đã có trong Buffer Pool chưa.
2. Nếu có rồi (Cache Hit): Trả dữ liệu ngay lập tức, đĩa không tốn 1 vòng quay/đọc nào.
3. Nếu chưa có (Cache Miss): MySQL buộc phải đọc Page 16KB đó từ ổ cứng, nạp nó vào Buffer Pool rồi mới xử lý tiếp.

2. Write Buffering

Khi bạn chạy UPDATE hoặc INSERT:

• MySQL không chạy ra ổ cứng ghi ngay lập tức vì làm thế ổ cứng sẽ nghẽn IOPS liên tục.
• Thay vào đó, MySQL sửa trực tiếp trên Page đang nằm trong Buffer Pool. Lúc này, Page đó được gọi là Dirty Page (trang dữ liệu bị bẩn/đã bị sửa đổi nhưng chưa đồng bộ xuống đĩa).
• Song song đó, thao tác này chỉ được ghi vắn tắt vào một file tuần tự gọi là Redo Log (cái này super fast).
• Định kỳ, tiến trình ngầm sẽ gom nhiều Dirty Page lại từng cục từng cục và flush xuống đĩa theo lô.

Hay quá, vậy hỏi tiếp, bộ nhớ RAM có giới hạn, trong khi dữ liệu database ngày một lớn. Khi Buffer Pool bị đầy, làm sao MySQL biết phải vứt trang nào ra khỏi RAM để đón trang mới?

Key ở đây là InnoDB dùng một biến thể nâng cao của thuật toán LRU (Least Recently Used):

• Buffer Pool được chia làm 2 phần: New (chiếm khoảng 62.5%) và Old (chiếm khoảng 37.5%).
• Khi một trang mới được nạp từ đĩa, nó không được nhảy ngay lên đầu bảng New, mà rơi vào đầu danh sách Old.
• Tại sao lại chia như vậy? Để tránh hiện tượng "ô nhiễm cache" (Scan Resistance). Ví dụ bạn chạy một câu lệnh backup hoặc SELECT * FROM big_tablequét qua 10GB dữ liệu:
  • Nếu dùng LRU thông thường, toàn bộ dữ liệu quan trọng đang có trên RAM sẽ bị đẩy bay màu chỉ vì một truy vấn quét rác một lần.
  • Với cơ chế của InnoDB, dữ liệu quét đó chỉ đi qua vùng Old và bị đẩy ra ngoài ngay nếu không được truy cập lại sau một khoảng thời gian (innodb_old_blocks_time).

Quay trở lại, ván cờ, vậy thì câu hỏi đặt ra là:

3. Nếu đặt SAI kích thước thì điều gì xảy ra?

Kịch bản 1: Đặt quá NHỎ (mặc định 128MB)

• Hiện tượng: Bộ nhớ đệm luôn chật chội. Tỷ lệ Cache Miss cao chót vót.
• Hậu quả: Ổ cứng phải đọc/ghi liên tục (Disk I/O 100%, IOPS cạn kiệt, iowait trên server tăng vọt). Các câu truy vấn đơn giản bắt đầu phản hồi chậm chạp, ứng dụng rơi vào tình trạng giật lag dù CPU và RAM của server vẫn đang trống rất nhiều.

Kịch bản 2: Đặt quá BỰ (ví dụ máy 16GB RAM mà set 15.5GB)

• Nguyên nhân: Buffer Pool chỉ là bộ nhớ dùng chung (Global). MySQL còn cần RAM cho các buffer trên từng kết nối (Per-thread) như sort_buffer_size, join_buffer_size, bộ nhớ chạy OS, hệ điều hành cache file system...
• Hậu quả: Khi lượng traffic tăng, nhiều connection mở ra, tổng RAM tiêu thụ vượt quá RAM vật lý. Hệ điều hành Linux sẽ kích hoạt OOM Killer (Out Of Memory) và tiến trình bị khai tử đầu tiên chính là mysqld. Database sập đột ngột không báo trước.

Đừng lo, mình có CÔNG THỨC cho bạn

4. Áp công thức là ra

• - Nếu Buffer Pool đặt từ 8GB trở lên, hãy chia nó thành nhiều instance nhỏ (thường là 8 instance) qua tham số: innodb_buffer_pool_instances = 8
• - Lý do là vì: Mỗi khi đọc ghi vào Buffer Pool, MySQL cần khóa danh sách LRU. Chia nhỏ pool thành nhiều block giúp các thread truy cập song song mà không tranh chấp lẫn nhau.

Biết cách trị bệnh rồi, vậy BẮT BỆNH thế nào?

5. Làm sao biết Buffer Pool của bạn có đang hoạt động tốt?

Chạy câu lệnh sau trong MySQL:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Hãy chú ý 2 thông số:

• Innodb_buffer_pool_read_requests: Tổng số lần MySQL cần đọc Page.
• Innodb_buffer_pool_reads: Số lần MySQL phải mò xuống tận ổ cứng vì trong Buffer Pool không có.

Tỷ lệ Hit Rate tính bằng:

• Hit Rate > 99%: Tốt quá rồi, Buffer Pool đang làm việc tuyệt vời, gần như toàn bộ dữ liệu nóng đều nằm gọn trên RAM.
• Hit Rate < 95%: Dở vậy, Buffer Pool của bạn đang quá chật so với tập dữ liệu hoạt động thường xuyên, đã đến lúc tăng RAM cho máy chủ hoặc tối ưu lại truy vấn/index.

Sẵn nói về Buffer Pool, có nhiều thứ hay ho về cái này mà mình muốn chia sẻ lắm, ví dụ:

• Nếu trong Buffer có data rồi, vậy thì nó lấy luôn data này trong cache ra phải không?
• Nếu vậy cây B+Tree có được lưu trên đây luôn không?
  ....

Nhưng mình sẽ để trong bài viết khác, để còn content mà viết nữa.

Mình kết thúc ở đây nhé. Cảm ơn các bạn đã đọc.