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.

innodb_max_dirty_pages_pct

The variable specifies the maximum percentage of unwritten (dirty) pages in the . If this percentage is exceeded, flushing will take place.

innodb_max_dirty_pages_pct_lwm

The 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 are no separate background flushing so long as:

• the share of dirty pages does not exceed

• the last checkpoint age (LSN difference since the latest checkpoint) does not exceed (minus some safety margin)

• the is not running out of space, which could trigger eviction flushing

To make flushing more eager, set to a higher value, for example SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;

Page Flushing with Multiple InnoDB Page Cleaner Threads

The system variable makes it possible to use multiple InnoDB page cleaner threads. It is deprecated and ignored now as the original reasons for splitting the buffer pool have mostly gone away.

The number of InnoDB page cleaner threads can be configured by setting the system variable. The system variable can be set in a server in an prior to starting up the server:

The system variable can be changed dynamically with :

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

Page Flushing with a Single InnoDB Page Cleaner Thread

Since 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

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

The 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 system variable. However, it is also recommended to use your own benchmarks to find a suitable value for your particular application.

Note

InnoDB's multi-thread flush feature is deprecated. 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 system variable. This system variable can be changed dynamically with :

This system variable can also be set in a server in an prior to starting up the server:

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

This system variable can also be set in a server in an prior to starting up the server:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}