Yếu tố căn bản cần biết khi tuning InnoDB trong MySQL

Khương PhạmSeptember 21, 2022

Yếu tố căn bản cần biết khi tuning InnoDB trong MySQL

Xem nhanh

Lời mở đầu

MySQL là một ứng dụng cơ sở dữ liệu mã nguồn mở tạo ra cấu trúc có ý nghĩa và khả năng truy cập cho một lượng lớn dữ liệu. Tuy nhiên, khi khối lượng dữ liệu tăng lên thì sẽ gặp các vấn đề về hiệu suất. Bài viết này sẽ cũng cấp cho các bạn cái nhìn tổng quan về Tuning MySQL.

Các nguyên tắc cơ bản cần lưu ý khi tuning MySQL

Sử dụng công cụ để đo lại sự thay đổi sau mỗi lần đổi config (Monitoring tool)
- Điều này rất quan trọng vì để mình xem lại kết quả, và thu thập thông tin để tiếp tục điều chỉnh giá trị của option để tìm giá trị cho phù hợp.
Chỉ nên thay đổi một option ở một thời điểm
- Nếu thay đổi nhiều option ở một thời điểm, chúng ta sẽ không đo được sự thay đổi của từng option.
- Thay đổi nhiều option nhiều khi chúng sẽ ảnh hưởng đến nhau, dẫn đến tình trạng thay đổi nhiều option không tốt bằng thay đổi một option.
Thay đổi giá trị của option từ từ trong quá trình tuning
- Từ từ thay đổi lên chứ không nên thay đổi quá so với lần đầu, vì lúc đó có thể rất tệ.
Hiểu được option đó làm gì
- Phải hiểu được option thì chúng ta sẽ biết rằng option đó có hữu ích không và biết những ảnh hưởng sẽ xảy ra sau khi điều chỉnh chúng hay không.

Điều kiện cần cho việc tuning MySQL

Để tuning được MySQL, chúng ta nên tìm hiểu về các phần dưới đây

Tổng quan về flow của InnoDB.
Tìm hiểu các option hay gặp phải trong quá trình tuning (Phần này sẽ được viết ở bài viết khác).

Các khái niệm cơ bản cần nắm

Buffer pool

Là một vùng nhớ chính để InnoDB cache table và index khi nó được truy cập. Buffer pool cho phép dữ liệu được sử dụng thường xuyên được lấy trực tiếp từ bộ nhớ, giúp tăng tốc độ xử lý. Thông thường, ở các máy chủ chuyên dụng, 80% bộ nhớ sẽ dùng cho buffer pool.

Data trong buffer pool được lưu trữ dưới dạng các page. Mỗi page có kích cỡ 16KB, có hai trạng thái là dirty và clean

Dirty page: Là những page có data trong đó thay đổi và chưa được flush xuống disk.
Clean page: Là những page không có data thay đổi hoặc có data thay đổi những vừa được flush xuống disk.

Để tăng hiệu quả cho việc đọc dữ liệu lớn, buffer pool chia thành các page chứa nhiều dòng. Để quản lý cache, buffer pool được implement dưới dạng linked list của các trang. Dữ liệu ít sử dụng sẽ được sẽ được xóa khỏi cache sử dụng thuật toán "least recently used" (LRU). Biết cách tận dụng buffer pool để giữ những dữ liệu đã truy cập trong bộ nhớ là một điều quan trọng trong việc tuning MySQL.

Thuật toán least recently used (LRU) của buffer pool

Buffer pool sử dụng thuật toán LRU để xác định page nào cần xóa để nhường chỗ cho page mới. Trước giải thích thuật toán chúng ta hãy nhìn qua hình vẽ dưới đây để có cái nhìn tổng quan hơn

Khi cần thêm chỗ để thêm 1 page mới vào buffer pool, page nào ít sử dụng nhất sẽ bị loại bỏ và một page mới sẽ được thêm vào giữa danh sách. "Midpoint insertion" chia Buffer Pool list thành 2 sublist dưới đây:

Ở phần đầu là "new sublist" là những page được truy cập gần đây.
Ở phần cuối là "old sublist" là những page cũ truy cập ít hơn.

Mặc định thuật toán hoạt động như sau:

3/8 buffer pool dành cho “old sublist”.
“Mid point” của danh sách là ranh giới của đuôi “new sublist” và phần đầu của “old sublist”.
Khi InnoDB đọc một page và đưa nó vào buffer pool, trước tiên nó sẽ chèn vào “Mid point”.
Khi một page được truy cập bất luận nó ở “new sublist” hay “old sublist”, thì nó sẽ được chuyển qua phần đầu “new sublist”.
Page nào ít được sử dụng sẽ được đẩy về cuối danh sách và bị xóa khỏi danh sách nếu cần khoảng trống cho việc thêm page mới. Page bị xóa khỏi buffer pool từ “old sublist”.

Mặc định, page được lấy khi khi chúng ta thực hiện các truy vấn đọc sẽ được thêm ngay vào “new sublist”, điều này có nghĩa nó ở buffer pool lâu hơn. Ví dụ quét bảng cho mysqldump hoặc sử dụng các câu truy vấn SELECT không có mệnh đề WHERE nó có thể đưa một lượng lớn dữ liệu vào buffer pool và loại bỏ một lượng lớn dữ liệu cũ mặc dù dữ liệu mới này chưa chắc đã được sử dụng lại. Ở tình huống này sẽ làm cho các page sử dụng thường xuyên bị đưa vào “old sublist” và có thể bị đá ra ngoài. Cho nên khi viết truy vấn đọc chúng ta hãy cố gắng tối ưu nó đặc biệt đừng quên mệnh đề WHERE.

Log buffer

Là một vùng lưu trữ trong bộ nhớ lưu các thông tin thay đổi để ghi xuống file dưới ổ cứng. Kích thước mặc định là 16MB.

Doublewrite buffer

Là 1 vùng lưu trữ mà InnoDB dùng để ghi page được đẩy qua từ buffer pool trước khi ghi chúng vào vị trí thích hợp trong tablespace. Khi đưa các page từ buffer pool qua tablespace file có thể gặp tình huống mà dữ liệu đưa xuống ổ cứng không được toàn vẹn (cúp điện, lỗi trong quá trình fragment). Lúc này InnoDB sẽ tìm một bản sao tại đây để thực hiện khôi phục dữ liệu.

Tổng quan về InnoDB data flow

Hiện hầu hết các hệ thống sử dụng MySQL đều dùng engine InnoDB, do đó chúng ta nên dành thời gian cho việc để xem sơ qua các option của InnoDB, đặc biệt là hai option cần phải chỉnh sửa ở hầu hết các hệ thống production đó là: buffer pool size và redo log size.

Trước khi xem xét về việc chỉnh sửa các option như thế nào cho hợp lý, chúng ta nên xem qua flow giữa buffer pool, tablespace và redo log. Hình dưới đây thể hiện một flow đơn giản giữa chúng:

Khi một query lấy dữ liệu, nó luôn đọc ở buffer pool. Nếu không có trong buffer pool thì nó sẽ lấy ở tablespace. Buffer pool được chia thành hai danh sách con “new sublist” và “old sublist” như ở phía trên ta đã đề cập và cả hai danh sách con này đều sử dụng thuật toán LRU để xác định page nào bị xóa để nhường chỗ cho page mới.
Khi một query update dữ liệu, sẽ có hai luồng sau đây
1. Ghi xuống redo log: Để giảm dung lượng của redo log, InnoDB chỉ ghi sự thay đổi chứ không phải toàn bộ page vào bộ nhớ là log buffer sau đó được flush redo log nằm trên ổ cứng. Mặc định redo log sẽ chứa ít nhất hai file (ib_logfile0, ib_logfile1). Các file redo log được sử dụng theo kiểu vòng tròn, việc ghi bắt đầu từ một file cho đến lúc đầy thì InnoDB sẽ tiếp tục với file tiếp theo, khi đến file cuối cùng thì InnoDB sẽ quay trở lại file đầu.
2. Ghi vào buffer pool: InnoDB sẽ ghi sự thay đổi vào đây và được đánh dấu là “Dirty”, sau đó được flush qua doublewrite buffer rồi gọi "fsynced" để chuyển qua các file của tablespace.