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 starting with 10.2.2
In MariaDB 10.2.2 and later, it is possible to use multiple InnoDB page cleaner threads.
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
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, only a single InnoDB page cleaner thread is supported.
Page Flushing with Multi-threaded Flush Threads
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
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.
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.
SET GLOBAL innodb_io_capacity=20000;
[mariadb] ... innodb_io_capacity=20000
The maximum amount of I/O capacity available to InnoDB in an emergency can be 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;
[mariadb] ... innodb_io_capacity_max=20000