Phần trước cho thấy bảng InnoDB là một cây B+ trên đĩa. Nhưng gần như không truy vấn nào đọc thẳng từ đĩa: giữa đĩa và câu lệnh có một vùng RAM tên buffer pool, nơi InnoDB giữ các trang 16 KB của clustered index và mọi secondary index. Hiểu buffer pool là hiểu vì sao cùng một truy vấn lúc chậm lúc nhanh, vì sao thêm RAM đôi khi giải quyết được một hệ thống đang ì ạch, và vì sao một báo cáo nặng chạy một lần lại không làm hỏng tốc độ của phần còn lại. Bài này đo thật ba điều đó trong mysql:8, với một con số lặp lại rất sạch: cùng một lần quét bảng, 577 trang chạm đĩa hay 0 trang, chỉ khác nhau ở chỗ bảng có vừa buffer pool hay không.

Đọc logic và đọc vật lý: hai bộ đếm phải phân biệt

Mỗi lần InnoDB cần một trang, nó hỏi buffer pool. Nếu trang đã nằm trong RAM, đó là một đọc logic (Innodb_buffer_pool_read_requests) và gần như tức thì. Nếu chưa, InnoDB phải nạp trang từ file dữ liệu — một đọc vật lý (Innodb_buffer_pool_reads) — rồi mới phục vụ. Tỉ lệ trúng (hit ratio) là phần đọc được phục vụ thẳng từ RAM:

hit ratio = 1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

Trên container đang đo, hai bộ đếm là 9.316 đọc vật lý trên 10.756.254 đọc logic, tức hit ratio ~99,9%. Con số này cao vì phần lớn truy vấn chạm đúng vùng dữ liệu đã nằm sẵn trong pool. Nhưng hit ratio toàn cục che mất câu chuyện thú vị hơn, chỉ lộ ra khi so một bảng vừa pool với một bảng không vừa.

Ảnh chụp sơ đồ nền tối buffer pool của InnoDB là vùng RAM giữ các trang 16 KB, mỗi khi cần một trang InnoDB hỏi buffer pool nếu đã nằm trong RAM là đọc logic read_requests gần như tức thì nếu chưa phải nạp từ file là đọc vật lý reads rồi mới phục vụ, hit ratio bằng 1 trừ reads chia read_requests, buffer pool quản lý bằng LRU hai vùng danh sách young chứa trang nóng hay dùng và danh sách old chứa trang mới nạp hoặc quét một lần, trang từ một lần quét bảng lớn vào thẳng vùng old nên bị tái dụng trong nội bộ vùng old mà không đẩy trang nóng ở vùng young ra đó là vì sao một báo cáo nặng chạy một lần không làm hỏng cache của phần còn lại, cách đo bảng warmtest 46 MB vừa pool 128 MB và bảng seq_pk 131 MB lớn hơn pool, chụp Innodb_buffer_pool_reads trước và sau mỗi lần quét để lấy số trang chạm đĩa đo thời gian thật, rồi nới innodb_buffer_pool_size từ 128 MB lên 256 MB để seq_pk vừa khít và đo lại, lưu ý dữ liệu nằm trên tmpfs nên khoảng cách thời gian hẹp hơn đĩa thật nhưng số trang chạm đĩa là thật

Hình 1: buffer pool giữ trang 16 KB trong RAM, phân biệt đọc logic và đọc vật lý; LRU hai vùng young/old chống việc một lần quét lớn xoá sạch cache.

Bảng vừa pool: quét ấm chạm đĩa 0 trang

Bảng warmtest nặng 46,6 MB, nhỏ hơn buffer pool 128 MB, nên sau lần đầu được nạp, mọi trang của nó nằm gọn trong RAM. Quét toàn bảng một lần nữa rồi đo delta hai bộ đếm: đọc vật lý tăng 0, đọc logic tăng khoảng 4.036. Nghĩa là cả lần quét không chạm đĩa một trang nào — toàn bộ phục vụ từ buffer pool, hit ratio của riêng truy vấn này là 100%. Đây là trạng thái mong muốn: tập dữ liệu làm việc vừa RAM, đĩa gần như không tham gia vào đường đọc.

Bảng lớn hơn pool: mỗi lần quét vẫn chạm đĩa 577 trang

Bảng seq_pk nặng 131,7 MB, lớn hơn buffer pool 128 MB một chút. Vì không bao giờ vừa trọn, buffer pool không thể giữ hết; phần tràn ra phải nạp lại từ đĩa ở mỗi lần quét. Quét toàn bảng ba lần liên tiếp, đo delta đọc vật lý và thời gian mỗi lần:

pool = 128 MB  (seq_pk 131 MB > pool: không vừa)
  lần 1:  577 trang chạm đĩa   183 ms
  lần 2:  577 trang chạm đĩa   195 ms
  lần 3:  577 trang chạm đĩa   187 ms

Con số 577 lặp lại y hệt ở cả ba lần là điểm mấu chốt: cache ấm cỡ nào cũng không cứu được, vì chỗ thiếu luôn ở đó. Một bảng chỉ nhỉnh hơn buffer pool vài phần trăm vẫn trả cái giá đọc đĩa trên mọi lần quét — không có điểm "đủ ấm" để thoát.

Ảnh chụp output thật nền tối đo buffer pool trên mysql 8, một bảng warmtest 46 MB vừa pool 128 MB quét ấm đọc vật lý tăng 0 trang đọc logic tăng 4036 hit ratio truy vấn 100%, hai bảng seq_pk 131 MB lớn hơn pool 128 MB quét ba lần liên tiếp lần nào cũng 577 trang chạm đĩa thời gian 183 rồi 195 rồi 187 ms con số 577 lặp y hệt vì chỗ thiếu luôn ở đó cache ấm không cứu được, ba nới innodb_buffer_pool_size lên 256 MB để seq_pk vừa khít lần 1 cold 577 trang chạm đĩa 181 ms nạp nốt phần còn thiếu lần 2 warm 0 trang chạm đĩa 128 ms lần 3 warm 0 trang chạm đĩa 134 ms, bốn hit ratio toàn cục Innodb_buffer_pool_reads 9316 trên Innodb_buffer_pool_read_requests 10.756.254 bằng 99,9%, kết luận vừa pool thì quét lặp chạm đĩa 0 trang không vừa thì mọi lần quét vẫn chạm đĩa thêm RAM để tập làm việc vừa buffer pool là cách rẻ nhất bỏ đĩa khỏi đường đọc

Hình 2: bảng vừa pool quét ấm chạm đĩa 0 trang; bảng lớn hơn pool chạm đĩa 577 trang mỗi lần, cho tới khi nới pool để bảng vừa khít thì tụt về 0.

Nới pool qua ngưỡng: 577 trang tụt về 0

Giờ nới innodb_buffer_pool_size từ 128 MB lên 256 MB — không khởi động lại, InnoDB đổi cỡ pool khi đang chạy. Bảng seq_pk 131 MB giờ vừa khít trong 256 MB. Quét lại ba lần:

pool = 256 MB  (seq_pk 131 MB < pool: giờ vừa)
  lần 1 (cold):  577 trang chạm đĩa   181 ms   ← nạp nốt phần còn thiếu
  lần 2 (warm):    0 trang chạm đĩa   128 ms
  lần 3 (warm):    0 trang chạm đĩa   134 ms

Đúng một lần nạp cuối cùng ở lần 1, rồi từ lần 2 trở đi 0 trang chạm đĩa, thời gian tụt từ 185 ms xuống 128 ms. Cùng một bảng, cùng một câu lệnh, chỉ đổi một tham số cấu hình: truy vấn đi từ "chạm đĩa mãi mãi" sang "không bao giờ chạm đĩa sau lần đầu". Đây là lý do khuyến nghị kinh điển đặt buffer pool đủ lớn để chứa tập dữ liệu làm việc — không phải để chứa toàn bộ CSDL, mà để chứa phần thực sự được truy vấn.

Cần nói thẳng một giới hạn của phép đo: file dữ liệu ở đây nằm trên tmpfs (RAM), nên "đọc vật lý" vẫn là đọc từ RAM, và khoảng cách thời gian 185 so với 128 ms hẹp hơn nhiều so với đĩa thật. Nhưng số trang chạm đĩa thì chính xác — đó là hành vi thật của InnoDB. Trên SSD hay đĩa quay, cùng 577 trang chạm đĩa mỗi lần quét sẽ giãn khoảng cách thời gian ra hàng chục tới hàng trăm lần.

Một lần quét lớn không xoá sạch cache

Khi dựng phép đo, có một chuyện đáng chú ý: cố ý quét bảng 131 MB để đẩy warmtest ra khỏi pool đã không thành công — warmtest vẫn nằm nguyên trong RAM. Đó không phải lỗi, mà là một thiết kế quan trọng của InnoDB. Buffer pool dùng LRU chia hai vùng: danh sách young chứa trang nóng hay dùng, danh sách old chứa trang vừa nạp. Trang đến từ một lần quét bảng lớn được đưa thẳng vào vùng old, và nếu không được dùng lại ngay thì bị tái dụng trong nội bộ vùng old — không chạm tới vùng young. Nhờ vậy một câu báo cáo nặng đọc cả bảng một lần không hất tập dữ liệu nóng của các truy vấn thường xuyên ra khỏi cache. Không có cơ chế này, mỗi lần quét lớn sẽ làm nguội toàn bộ pool và mọi truy vấn sau đó đều chậm.

Ba ý mang về

  1. Buffer pool quyết định truy vấn chạm đĩa bao nhiêu, và hit ratio là thước đo. Phân biệt đọc logic (read_requests, phục vụ từ RAM) và đọc vật lý (reads, nạp từ đĩa); toàn cục đo được 9.316 trên 10,76 triệu, hit ratio ~99,9%.
  2. Bảng vừa pool thì quét lặp chạm đĩa 0 trang; bảng lớn hơn pool thì mọi lần quét vẫn chạm đĩa. warmtest 46 MB vừa pool 128 MB cho đọc vật lý 0 khi quét ấm; seq_pk 131 MB không vừa cho đúng 577 trang chạm đĩa ở mọi lần quét — cache ấm không cứu được chỗ thiếu.
  3. Nới innodb_buffer_pool_size để tập làm việc vừa pool là đòn bẩy rẻ nhất. Nới từ 128 lên 256 MB để seq_pk vừa khít khiến đọc vật lý tụt từ 577 mỗi lần xuống 0 sau lần nạp đầu, thời gian 185 → 128 ms. Và nhờ LRU hai vùng, một lần quét lớn không xoá sạch cache của phần còn lại.

Nguồn

Phần sau mổ xẻ chính cấu trúc B+tree: vì sao một index trên bảng hàng triệu dòng chỉ sâu vài tầng, mỗi lần tra khóa chỉ đọc vài trang. Đo số tầng cây và số trang phải đọc khi bảng lớn dần, để thấy vì sao độ sâu tăng cực chậm so với số hàng.