Understanding InnoDB - Buffer Pool Flushing

InnoDB Page Cleaner Thread

The InnoDB Page Cleaner Thread is an InnoDB background thread that flushes dirty pages from the InnoDB Buffer Pool to disk. Prior MySQL 5.6 this action was performed by the InnoDB Master Thread.

SQL> SELECT thread_id, name, type
  FROM performance_schema.threads
 WHERE name LIKE 'thread/innodb/page%'
    OR name LIKE 'thread/innodb/srv_master%';
+-----------+-----------------------------------+------------+
| thread_id | name                              | type       |
+-----------+-----------------------------------+------------+
|        14 | thread/innodb/page_cleaner_thread | BACKGROUND |
|        19 | thread/innodb/srv_master_thread   | BACKGROUND |
+-----------+-----------------------------------+------------+

The number of InnoDB Page Cleaner Threads is defined by the server variable innodb_page_cleaners:

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_page_cleaners';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_page_cleaners | 1     |
+----------------------+-------+

MySQL currently considers a number of Page Cleaner Threads bigger than Buffer Pool Instances not as useful.

The actual number of dirty InnoDB pages can be shown with the command:

SQL> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 1412  |
+--------------------------------+-------+

InnoDB Buffer Pool Flushing

The rate of flush (from Buffer Pool) and merge (from Change Buffer) operations is defined by the innodb_io_capacity variable:

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_io%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+

The innodb_io_capacity is the total capacity for all InnoDB Buffer Pool Instances together.

The server variable innodb_flush_neighbors controls how the InnoDB Page Cleaner Thread flushes other dirty pages from the same InnoDB extent.

Literature: MariaDB / MySQL

0 means: No other dirty pages are flushed from the Buffer Pool.
1 means: Contiguous dirty pages in the same InnoDB extent are flushed from the InnoDB Buffer Pool.
2 means: Flush dirty pages in the same InnoDB extent from the InnoDB Buffer Pool.