Một số options quan trọng của InnoDB buffer pool trong MySQL

Khương PhạmJune 30, 2023

Một số options quan trọng của InnoDB buffer pool trong MySQL

Xem nhanh

Ở bài trước chúng ta đã tìm hiểu Yếu tố căn bản cần biết khi tuning InnoDB trong MySQL. Dưới đây chúng ta sẽ đi sâu hơn về một số options trong InnoDB buffer pool

InnoDB buffer pool là gì

Buffer pool là nơi để InnoDB cache table và index, đây là một thành phần quan trọng của kiến trúc InnoDB, nó giúp tăng tốc độ truy xuất dữ liệu bằng cách giảm số lần truy xuất dữ liệu từ ổ đĩa bằng cách:

Nếu page đã có trong buffer pool MySQL sẽ đọc trực tiếp từ bộ nhớ mà không cần phải đọc từ ổ đĩa, điều này sẽ giúp tăng tốc độ truy xuất dữ liệu.
Nếu page không có trong buffer pool, MySQL sẽ phải đọc từ ổ đĩa và lưu vào buffer pool trước khi trả về kết quả. Việc đọc dữ liệu từ ổ đĩa sẽ tốn nhiều thời gian hơn so với đọc từ bộ nhớ nên nó sẽ làm chậm tốc độ truy xuất dữ liệu.

Vì tất cả các request đều đi qua buffer pool, nên nó trở thành một phần rất quan trọng trong việc nâng cao hiệu suất của MySQL.

Một số options quan trọng của InnoDB buffer pool

innodb_buffer_pool_size

Là option quan trọng nhất trong InnoDB buffer pool dùng để xác định kích thước của buffer pool trong bộ nhớ với mặc định là 128MB.
Cần được cấu hình sao cho đủ lớn để chứa các page cần thiết và đủ nhỏ để tránh lãng phí tài nguyên bộ nhớ
Nếu kích thước quá nhỏ thì làm giảm hiệu suất hệ thống do cơ chế di chuyển của page giữa ổ đĩa và buffer pool đó là:
- Page không được sử dụng và phải ghi vào ổ đĩa để giải phóng bộ nhớ
- Page không có sẵn ở buffer pool và phải đọc từ ổ đĩa để đưa vào buffer pool
Nếu kích thước quá lớn cũng sẽ gây ra một số vấn đề dưới đây
- Sử dụng thừa tài nguyên hệ thống
- Ảnh hưởng đến các ứng dụng khác đang chạy trên hệ thống
- Có thể dẫn đến trì trệ hoặc treo cục bộ cho một số truy vấn. Nếu một truy vấn yêu cầu sử dụng bộ nhớ lớn hơn giá trị innodb_buffer_pool_size, InnoDB sẽ phải sử dụng bộ nhớ swap để đáp ứng yêu cầu này. Và khi đó, hiệu suất truy vấn có thể bị giảm đi đáng kể và thời gian phản hồi của truy vấn có thể tăng lên.
Nên đặt kích thước phù hợp với dung lượng RAM có sẵn trên hệ thống, thường sẽ lấy giá trị khoảng 60-80% tổng số RAM trên hệ thống.

innodb_buffer_pool_instances

Option này xác định số lượng instance của các buffer pool được tạo ra, nó sẽ chia nhỏ buffer pool thành các phần bằng nhau. Mỗi instance hoạt động độc lập với nhau và sử dụng một phần của buffer pool.
Trước đây chỉ có một buffer pool duy nhất cho toàn bộ dữ liệu InnoDB trong hệ thống. Trong trường hợp có nhiều truy vấn chạy song song do các máy chủ sử dụng CPU có nhiều lõi (core). Điều này dẫn tới việc tranh chấp mutex khi truy cập dữ liệu trong buffer pool. Một trong những giải pháp để giảm tranh chấp là cho phép chia buffer pool ra thành nhiều instance. Nếu khối lượng công việc song song nhiều thì càng phải có nhiều instance để giúp giảm tranh chấp. Việc tạo ra nhiều instance sẽ cho phép các truy vấn truy cập đến các buffer pool được phân tán trên các instance khác nhau, giúp phân bổ tài nguyên bộ nhớ hiệu quả hơn và giảm thời gian chờ đợi khi nhiều kết nối cùng truy xuất vào cơ sở dữ liệu từ đó giúp cho việc tăng hiệu suất hệ thống
Nếu tất cả truy vấn từ các page khác nhau thì tăng instance sẽ hiệu quả. Ngược lại nếu tất cả truy vấn đều yêu cầu dữ liệu trong cùng một page thì việc tăng nhiều instance không có lợi ích gì.
Không nên để dung lượng của mỗi buffer pool quá nhỏ, hãy để cho mỗi instance có dung lượng ít nhất là 1 Gigabite. Dung lượng của mỗi instance sẽ được tính bởi công thức: innodb_buffer_pool_size / innodb_buffer_pool_instances
Nên tính toán sao cho số lượng bộ nhớ buffer pool phù hợp với số lượng CPU sử dụng trên hệ thống để mỗi instance có thể được xử lý bởi một luồng CPU riêng.
Giá trị mặc định là 1, nghĩa là chỉ sử dụng một instance. Tuy nhiên, nếu hệ thống có nhiều lõi CPU hoặc bộ nhớ lớn, chúng ta có thể tăng giá trị này để cải thiện hiệu suất của hệ thống. Giá trị khuyến nghị bằng số lượng lõi của CPU. Không nên cấu hình tham số này quá thấp, nó sẽ làm giảm hiệu suất của hệ thống.

innodb_lru_scan_depth

Option này chỉ định số lượng page sẽ được quét qua trong mỗi lần quét LRU để tìm các dirty page có thể flush trên mỗi buffer pool instance. Tiến trình này sẽ được chạy background mỗi giây.
Giá trị mặc định của option là 1024, theo như tài liệu của MySQL thì nếu đặt giá trị nhỏ hơn giá trị mặc định thường phù hợp với hầu hết với các hệ thống.
Nếu tăng giá trị innodb_lru_scan_depth có nghĩa là số lượng page được quét mỗi lần nhiều hơn trong mỗi lần chạy, do đó
- Số lượng page xử lý trong mỗi lần quét nhiều hơn đo đó CPU tốn tài nguyên hơn gây ảnh hưởng đến hiệu suất chung của hệ thống
- Số page được lưu lại nhiều hơn và không được loại bỏ khỏi buffer pool đúng lúc do đó phải tốn nhiều bộ nhớ hơn nhưng có thể cải thiện hiệu suất truy vấn
Nếu giảm giá trị innodb_lru_scan_depth thì số lượng page được quét trong mỗi lần quét LRU ít hơn, do đó
- Tốn ít CPU hơn
- Tốn ít bộ nhớ hơn để lưu trữ các page, tuy nhiên vì ít page trong buffer pool hơn nên tăng số lần đọc từ đĩa do đó làm giảm hiệu suất truy vấn
- Nếu quá thấp thì dẫn đến tình trạng các page đọc/ghi thường xuyên làm cho tăng CPU và sử dụng bộ nhớ không hiệu quả
Giá trị thường được khuyến nghị cho innodb_lru_scan_depth
- Khoảng từ 100 đến 200 cho hệ thống với bộ nhớ đệm nhỏ (ví dụ: dưới 1GB).
- Khoảng 200 đến 1000 cho hệ thống với bộ nhớ đệm lớn hơn (ví dụ: trên 1GB)
- Nếu hệ thống ghi nhiều thì xem xét giảm giá trị này
- Nếu hệ thống đọc nhiều thì xem xét tăng giá trị này
- Tuy nhiên, điều quan trọng là thử nghiệm và điều chỉnh giá trị này dựa trên thực tế hệ thống của bạn. Nên bắt đầu với một giá trị thấp và tăng dần cho đến khi đạt được hiệu suất tốt nhất cho ứng dụng cụ thể của bạn.

innodb_io_capacity

Option này qui định số lượng I/O tối đa mà hệ thống InnoDB có thể xử lý trong một khoảng thời gian nhất định, nó ảnh hưởng đến hiệu suất của các hoạt động đọc và ghi trên ổ đĩa.
Nếu bị vấn đề về hiệu suất I/O (ví dụ như các hoạt động đọc/ghi trên ổ đĩa chậm chạp) và hệ thống của bạn có tài nguyên đủ, điều chỉnh tham số này tăng lên có thể cải thiện hiệu suất. Tuy nhiên, nếu tài nguyên hệ thống hạn chế hoặc không có vấn đề về hiệu suất I/O, việc điều chỉnh tham số này có thể không mang lại lợi ích đáng kể và thậm chí có thể gây ra tác động tiêu cực.
Chúng ta phải đánh giá khả năng của hệ thống phải có I/O đáp ứng được với giá trị innodb_io_capacity

innodb_io_capacity_max

Option này qui định giá trị tối đa cho innodb_io_capacity. Giới hạn này giúp đảm bảo chúng ta không thể cấu hình một giá trị quá cao cho innodb_io_capacity, gây ra tải quá mức hoặc tài nguyên bị lãng phí.
Thông thường, giá trị mặc định của innodb_io_capacity_max là 2000, tuy nhiên nếu gặp vấn đề về I/O thì chúng ta nên xem xét tăng giá trị này lên với lưu ý
- Đánh giá hệ thống có thể hỗ trợ được I/O bằng với innodb_io_capacity_max hay không
- Ví dụ: Với hệ thống MySQL/InnoDB có một ổ đĩa SSD nhanh và bạn gặp vấn đề về hiệu suất I/O trong khi xử lý tải công việc đọc lớn. Trong ngữ cảnh này, chúng ta có thể tăng giá trị innodb_io_capacity_max từ giá trị mặc định là 2000 lên thành 5000 hoặc 10000. Sau khi thực hiện thay đổi, bạn thực hiện thử nghiệm và đánh giá tốc độ đọc, thời gian đáp ứng và tài nguyên hệ thống để xác định xem việc tăng giá trị innodb_io_capacity_max có cải thiện hiệu suất I/O hay không.

innodb_flush_method

Option này xác định phương pháp flush các page từ bộ buffer pool xuống ổ đĩa. Khi InnoDB ghi dữ liệu, thường sẽ lưu trữ page vào buffer pool trước. Tuy nhiên, để đảm bảo tính an toàn dữ liệu, các page này cần được ghi xuống ổ đĩa.
Một số khuyến nghị cho việc thiết lập innodb_flush_method
- Giá trị mặc định "fsync":
  - Dữ liệu được đồng bộ từ buffer pool xuống ổ đĩa. Nó được đảm bảo tính an toàn của dữ liệu.
  - Đây là giá trị được khuyến nghị trong hầu hết các trường hợp, đặc biệt khi tính an toàn dữ liệu là ưu tiên hàng đầu.
- Giá trị "O_DIRECT":
  - Sử dụng giá trị này khi muốn tối ưu hiệu suất đọc/ghi dữ liệu bằng cách tránh đệm tại cấp độ hệ điều hành.
  - Điều này có thể cải thiện hiệu suất truy vấn, đặc biệt là trong các ứng dụng có tải cao.
  - Tuy nhiên, việc sử dụng O_DIRECT yêu cầu hệ điều hành hỗ trợ tính năng này.
- Tránh sử dụng "O_DSYNC" và "ALL_O_DIRECT" trong hầu hết các trường hợp:
  - Mặc dù O_DSYNC và ALL_O_DIRECT có hiệu suất cao hơn, nhưng chúng có rủi ro về tính an toàn dữ liệu
  - Sử dụng những giá trị này chỉ khi đã thực hiện các biện pháp bảo vệ dữ liệu khác, như sao lưu thường xuyên hoặc có các giải pháp phòng ngừa sự mất mát dữ liệu khác.

Lưu ý

Các giá trị cụ thể của các options là khác nhau, tùy thuộc vào phiên bản MySQL/InnoDB và cấu hình hệ thống của bạn. Do đó điều quan trọng nhất là thực hiện một cách cẩn thận dựa trên thử nghiệm các giá trị và đánh giá hiệu suất để đảm bảo rằng hệ thống vẫn hoạt động tốt đồng thời đáp ứng yêu cầu của công việc.

Xem hiệu suất của InnoDB buffer pool thông qua chỉ số

Các chỉ số sau có thể được xem thông qua các công cụ như MySQL Enterprise Monitor, MySQL Performance Schema hoặc MySQL sys schema. Ngoài ra, ta cũng có thể sử dụng lệnh SHOW ENGINE INNODB STATUS để kiểm tra trạng thái hiện tại của InnoDB buffer pool

innodb_buffer_pool_reads: Đếm số lần các page phải được đọc từ đĩa vì không tìm thấy trong bộ nhớ đệm. Việc đọc page từ ổ đĩa sẽ tốn nhiều thời gian hơn so với việc truy cập page trong bộ nhớ đệm. Do đó nếu innodb_buffer_pool_reads càng thấp thì hiệu suất của hệ thống sẽ càng tốt.
innodb_buffer_pool_read_requests: Đếm số lần yêu cầu đọc page từ buffer pool. Nếu giá trị càng cao, điều đó cho thấy rằng hầu hết các yêu cầu đọc đã được giải quyết bằng cách truy cập trực tiếp vào bộ nhớ đệm InnoDB, điều này giúp cải thiện hiệu suất của hệ thống.
innodb_buffer_pool_pages_data: Cho biết số page hiện tại trong buffer pool. Chỉ số này càng cao thì càng tốt, vì nó cho biết tỷ lệ lượng dữ liệu được lưu trữ trong buffer pool.
innodb_buffer_pool_write_requests: Cho biết số lần yêu cầu ghi một page vào buffer pool. Chỉ số này càng cao thì càng tốt, vì nó cho biết buffer pool đang được sử dụng hiệu quả để phục vụ yêu cầu ghi dữ liệu.
innodb_buffer_pool_wait_free: Cho biết số lần chờ để giải phóng page trong buffer pool để giải phóng bộ nhớ. Chỉ số này càng thấp thì càng tốt, vì nó cho biết buffer pool đang có đủ bộ nhớ để giữ các page và không cần phải giải phóng bộ nhớ quá thường xuyên.
innodb_buffer_pool_wait_read: Cho biết số lần chờ để đọc page vào buffer pool. Chỉ số này càng thấp thì càng tốt, vì nó cho biết buffer pool đang đáp ứng nhanh chóng các yêu cầu đọc dữ liệu.
innodb_buffer_pool_wait_write: Cho biết số lần chờ để ghi các page vào buffer pool. Chỉ số này càng thấp thì càng tốt, vì nó cho biết buffer pool đang đáp ứng nhanh chóng các yêu cầu ghi dữ liệu.
Hit rate: là tỷ lệ giữa số lần các page được tìm thấy trong bộ nhớ đệm và tổng số lần truy cập bộ nhớ đệm
- Công thức: Hit Rate = 100 * (1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)), với innodb_buffer_pool_reads và innodb_buffer_pool_read_requests có thể được lấy từ câu query sau SELECT variable_name, variable_value FROM sys.metrics WHERE variable_name IN ('innodb_buffer_pool_reads', 'innodb_buffer_pool_read_requests')
- Nếu hit rate cao (gần 100%), điều này cho thấy rằng hệ thống đang hoạt động tốt và đáp ứng tốt các yêu cầu truy vấn.
- Ngược lại, nếu hit rate thấp (dưới 90%), điều này có thể cho thấy rằng kích thước của InnoDB buffer pool quá nhỏ hoặc yêu cầu truy vấn đang quá tải, dẫn đến việc đọc dữ liệu từ đĩa nhiều hơn, dẫn đến giảm hiệu suất của hệ thống.