Tổng hợp config options cho MariaDB được sử dụng để tunning (p1)

Bizfly Cloud

1556

03-01-2024

Trong quá trình phát triển và vận hành ứng dụng, hiệu năng của database đem lại luôn luôn ảnh hưởng đến hiệu năng của ứng dụng. Để nâng cao hiệu năng của database, chúng ta thường chú ý đến 2 vấn đề:

• Năng lực của phần cứng
• Năng lực của database engine

Tuy nhiên thì năng lực của database engine vẫn luôn là vấn đề mà chúng ta cần phải lưu ý nhiều nhất bởi nếu năng lực của phần cứng có tốt đến đâu đi nữa mà database engine không tận dụng được hết thì vẫn là một thứ bỏ đi.

Tùy vào đặc thù và nhu cầu của mỗi ứng dụng, chúng ta sẽ có các config options khác nhau để cấu hình. Sau đây là một vài MariaDB config options để tunning cùng với tác dụng của chúng.

Làm thế nào để kiểm tra kết quả tunning trên MariaDB config options?

Trước khi chúng ta đi đến với các options để tunning, chúng ta nên biết cách để benchmark kết quả của việc thay đổi cấu hình để kiểm nghiệm.

Mình đã thực hiện benchmark dựa trên node database với cấu hình 16 CPU/ 64 GB RAM và ổ cứng lưu trữ dữ liệu là HDD.

Đưới đây là kết quả khi chạy lần đầu tiên

Mục tiêu tối ưu các MariaDB config options dựa trên đâu?

Đối với database engine, chúng ta có rất nhiều config options để tunning. Nhưng mình chỉ nói đến các option có liên quan như sau:

caching data

caching query

InnoDB does all its caching in a the buffer pool, whose size is controlled by innodb_buffer_pool_size. By default it contains 16KB data and index blocks from the open tables (see innodb_page_size), plus some maintenance overhead.

Các options cải thiện caching, query

Theo như nội dung của docs MariaDB

Để tối ưu cho MariaDB config options thì có thể làm như sau: You should set innodb_buffer_pool_size to about 80% of your memory. The goal is to ensure that 80 % of your working set is in memory!

bởi vì: The InnoDB buffer pool is a key component for optimizing MariaDB. It stores data and indexes, and you usually want it as large as possible so as to keep as much of the data and indexes in memory, reducing disk IO, as main bottleneck.

The buffer pool attempts to keep frequently-used blocks in the buffer, and so essentially works as two sublists, a new sublist of recently-used information, and an old sublist of older information. By default, 37% of the list is reserved for the old list.

When new information is accessed that doesn't appear in the list, it is placed at the top of the old list, the oldest item in the old list is removed, and everything else bumps back one position in the list. When information is accessed that appears in the old list, it is moved to the top the new list, and everything above moves back one position.

Các biến cần đặc biệt chú ý bao gồm:

- innodb_buffer_pool_size

- innodb_log_file_size

- innodb_flush_method

- innodb_thread_sleep_delay

- innodb_adaptive_max_sleep_delay

- innodb_buffer_pool_instances

- innodb_max_dirty_pages_pct_lwm

- innodb_read_ahead_threshold

- innodb_thread_concurrency

chúng ta có thể đọc chi tiết rõ hơn ở đây:

InnoDB buffer size sẽ bị mất khi service bị shutdown và khi init. Để có thể dump lại buffer thì có thể cấu hình ON cho:

- innodb_buffer_pool_dump_at_shutdown

- innodb_buffer_pool_load_at_startup

Một trong các yếu tố gây ảnh hưởng đến hiệu năng của database cũng có thể kể đến là tốc độ ghi dữ liệu vào ổ cứng. Tùy thuộc vào nhu cầu của ứng dụng, chúng ta có thể điều chỉnh cấu hình của innodb_flush_method với các giá trị như sau

• O_SYNC: requires that any write operations block until all data and all metadata have been written to persistent storage. 
•  O_DSYNC: like O_SYNC, except that there is no requirement to wait for any metadata changes which are not necessary to read the just-written data. In practice, O_DSYNC means that the application does not need to wait until ancillary information (the file modification time, for example) has been written to disk. Using O_DSYNC instead of O_SYNC can often eliminate the need to flush the file inode on a write.

read more in this description

Các MariaDB config options cải thiện số lượng transactions/s

Số lượng transactions/s cũng là một tham số mà chúng ta cần quan tâm, bởi nó có tầm ảnh hưởng lớn đến database vì nó quy định cho một tập hợp các thao tác mà ở đó hoặc là các thao tác đó được thực hiện thành công, hoặc là không một thao tác nào được thực hiện thành công cả

Điều chỉnh innodb_file_per_table = OFF | 0:

Đây là option thấy khá rõ sự khác biệt về số transaction/s khi chạy sysbench. Vai trò: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).

Điều chỉnh

3 biến này sẽ tối ưu khi query dữ liệu, một số tài liệu thì cho rằng nó sẽ bi bottleneck nhưng cũng có thể sử dụng được trong đa số các trường hợp. Effect thấy rõ được bằng cách thực hiện như sau trên database:

nếu chúng ta disable với các giá trị là 0 thì mỗi lần query trên sẽ đều mất một khoảng thời gian để query nhưng ngược lại, query sẽ được cache với cùng câu lệnh trên.

Kết quả

Khi adjust như trên cũng đồng nghĩa với việc tăng được số lượng query. Cụ thể sử dụng trên disk HDD như sau:

và kết quả sau khi tunning, ta thu được như sau

Ngoài các config options trên, MariaDB vẫn cung cấp cho chúng ta nhiều options khác mà tùy vào nhu cầu ứng dụng để có thể cấu hình phù hợp. Mình sẽ giới thiệu vào thời gian tới.