Basic Details
In MySQL's InnoDB storage engine, the innodb_page_cleaners configuration variable specifies the number of page cleaner threads. These threads are responsible for flushing dirty pages from the buffer pool to disk.
Releem automatically tunes innodb_page_cleaners and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding of innodb_page_cleaners by reading our detailed variable documentation.
Releem automatically tunes innodb_page_cleaners and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding of innodb_page_cleaners by reading our detailed variable documentation.
innodb_page_cleaners – Usage
When records in the InnoDB buffer pool (an in-memory cache for data and indexes) are modified, they become "dirty". These modified pages in the buffer pool need to be written or "flushed" back to the physical disk at some point to maintain data integrity and free up space in the buffer pool for other records.
Role of Page Cleaner Threads
Page cleaner threads are responsible for this flushing process. They work in the background, identifying dirty pages and writing them back to disk, ensuring that the number of dirty pages remains below a specified threshold (defined by the innodb_max_dirty_pages_pct variable).
The Need for Multiple Page Cleaner Threads
Before the introduction of innodb_page_cleaners, there was only one page cleaner thread in InnoDB. As the demands on databases grew, especially with systems having many CPUs and high write loads, the single page cleaner thread could become a bottleneck, unable to flush dirty pages to disk quickly enough. This could cause various problems, including stalls in the system when the number of dirty pages exceeds the desired percentage.
To address this, starting from MySQL 5.7.5, the InnoDB storage engine allowed for the possibility of multiple page cleaner threads to improve the flushing of dirty pages. The innodb_page_cleaners variable was introduced to control the number of these threads.
Default Value
By default, MySQL sets the innodb_page_cleaners value to 4. However, the actual number of page cleaner threads will be the lesser of innodb_page_cleaners and innodb_buffer_pool_instances.
Role of Page Cleaner Threads
Page cleaner threads are responsible for this flushing process. They work in the background, identifying dirty pages and writing them back to disk, ensuring that the number of dirty pages remains below a specified threshold (defined by the innodb_max_dirty_pages_pct variable).
The Need for Multiple Page Cleaner Threads
Before the introduction of innodb_page_cleaners, there was only one page cleaner thread in InnoDB. As the demands on databases grew, especially with systems having many CPUs and high write loads, the single page cleaner thread could become a bottleneck, unable to flush dirty pages to disk quickly enough. This could cause various problems, including stalls in the system when the number of dirty pages exceeds the desired percentage.
To address this, starting from MySQL 5.7.5, the InnoDB storage engine allowed for the possibility of multiple page cleaner threads to improve the flushing of dirty pages. The innodb_page_cleaners variable was introduced to control the number of these threads.
Default Value
By default, MySQL sets the innodb_page_cleaners value to 4. However, the actual number of page cleaner threads will be the lesser of innodb_page_cleaners and innodb_buffer_pool_instances.
innodb_page_cleaners – Configuration
The innodb_page_cleaners variable can be configured using the command line (>= MariaDB 10.3.3 ONLY) or set at startup using the configuration file:
Command Line Configuration:
mysqld> set global innodb_page_cleaners = XX
Replace XX with a value to suit your database needs. To verify that the variable has been changed:
mysqld> show global variables like ‘innodb_page_cleaners’
Configuration File:
[mysqld]
innodb_page_cleaners variable = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
Command Line Configuration:
mysqld> set global innodb_page_cleaners = XX
Replace XX with a value to suit your database needs. To verify that the variable has been changed:
mysqld> show global variables like ‘innodb_page_cleaners’
Configuration File:
[mysqld]
innodb_page_cleaners variable = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
innodb_page_cleaners – Considerations
For optimal performance, it's a common practice to match the number of innodb_page_cleaners with the number of innodb_buffer_pool_instances. However, there isn't a one-size-fits-all value, and the optimal number of page cleaner threads can vary based on the specific workload and hardware of the system.
Increasing the number of page cleaner threads can help in handling high write loads, but it might not always lead to a linear improvement in performance. There's a point where adding more threads can lead to diminishing returns, and in some cases, it might even degrade performance due to increased contention. Hence, tuning should be based on monitoring, testing, and iterative adjustments.
Automatically Configure the Best Value for innodb_page_cleaners
Don't get lost in the maze of tweaking the value of innodb_page_cleaners or relying solely on MySQL's default settings. Turn to Releem.
Increasing the number of page cleaner threads can help in handling high write loads, but it might not always lead to a linear improvement in performance. There's a point where adding more threads can lead to diminishing returns, and in some cases, it might even degrade performance due to increased contention. Hence, tuning should be based on monitoring, testing, and iterative adjustments.
Automatically Configure the Best Value for innodb_page_cleaners
Don't get lost in the maze of tweaking the value of innodb_page_cleaners or relying solely on MySQL's default settings. Turn to Releem.
This powerful database configuration tool constantly assesses server performance, pinpointing the most efficient setting for over 44 variables, innodb_page_cleaners being one of them. With Releem, configuration adjustments are a breeze — there's no intensive effort from your side. Experience optimized resource allocation, reduced latency, and enhanced query performance with Releem by your side.