InnoDB Page Flushing

Page Flushing with InnoDB Page Cleaner Threads

InnoDB page cleaner threads flush dirty pages from the InnoDB buffer pool. These dirty pages are flushed using a least-recently used (LRU) algorithm.

Page Flushing with Multiple InnoDB Page Cleaner Threads

MariaDB 10.2.2 - 10.5.1

The innodb_page_cleaners system variable was added in MariaDB 10.2.2, and makes it possible to use multiple InnoDB page cleaner threads. It is deprecated and ignored from MariaDB 10.5.1, as the original reasons for for splitting the buffer pool have mostly gone away.

The number of InnoDB page cleaner threads can be configured by setting the innodb_page_cleaners system variable. This system variable can be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
innodb_page_cleaners=8

In MariaDB 10.3.3 and later, this system variable can also be changed dynamically with SET GLOBAL. For example:

SET GLOBAL innodb_page_cleaners=8;

This system variable's default value is either 4 or the configured value of the innodb_buffer_pool_instances system variable, whichever is lower.

Page Flushing with a Single InnoDB Page Cleaner Thread

In MariaDB 10.2.1 and before, and from MariaDB 10.5.1, when the original reasons for splitting the buffer pool have mostly gone away, only a single InnoDB page cleaner thread is supported.

Page Flushing with Multi-threaded Flush Threads

MariaDB 10.1.0 - 10.3.2

InnoDB's multi-thread flush feature was first added in MariaDB 10.1.0. It was deprecated in MariaDB 10.2.9 and removed in MariaDB 10.3.2.

In MariaDB 10.3.1 and before, InnoDB's multi-thread flush feature can be used. This is especially useful in MariaDB 10.1, which only supports a single page cleaner thread.

InnoDB's multi-thread flush feature can be enabled by setting the innodb_use_mtflush system variable. The number of threads cane be configured by setting the innodb_mtflush_threads system variable. This system variable can be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
innodb_use_mtflush = ON
innodb_mtflush_threads = 8

The innodb_mtflush_threads system variable's default value is 8. The maximum value is 64. In multi-core systems, it is recommended to set its value close to the configured value of the innodb_buffer_pool_instances system variable. However, it is also recommended to use your own benchmarks to find a suitable value for your particular application.

InnoDB's multi-thread flush feature was deprecated in MariaDB 10.2.9 and removed from MariaDB 10.3.2. In later versions of MariaDB, use multiple InnoDB page cleaner threads instead.

Configuring the InnoDB I/O Capacity

Increasing the amount of I/O capacity available to InnoDB can also help increase the performance of page flushing.

The amount of I/O capacity available to InnoDB can be configured by setting the innodb_io_capacity system variable. This system variable can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL innodb_io_capacity=20000;

This system variable can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
innodb_io_capacity=20000

The maximum amount of I/O capacity available to InnoDB in an emergency defaults to either 2000 or twice innodb_io_capacity, whichever is higher, or can be directly configured by setting the innodb_io_capacity_max system variable. This system variable can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL innodb_io_capacity_max=20000;