InnoDB Page Flushing
- Page Flushing with InnoDB Page Cleaner Threads
- Page Flushing with Multi-threaded Flush Threads
- Configuring the InnoDB I/O Capacity
- See Also
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.
The innodb_max_dirty_pages_pct_lwm variable determines the low-water mark percentage of dirty pages that will enable preflushing to lower the dirty page ratio. The value 0 (the default) means that there will be no separate background flushing so long as:
- the share of dirty pages does not exceed innodb_max_dirty_pages_pct
- the last checkpoint age (LSN difference since the latest checkpoint) does not exceed innodb_log_file_size (minus some safety margin)
- the buffer pool is not running out of space, which could trigger eviction flushing
Note that in MariaDB 10.5.7 and MariaDB 10.5.8 only, flushing was more aggressive, and the page cleaner thread would always run in the background, as long as dirty pages exist in the buffer pool. To make flushing more eager, set to a higher value, for example
SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001; (the default until MariaDB 10.2.1).
Page Flushing with Multiple InnoDB Page Cleaner Threads
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
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
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
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.
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 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;
[mariadb] ... innodb_io_capacity_max=20000