Let’s take a closer look at MySQL Flush Cache in InnoDB. Bobcares, as a part of our MySQL Support Services offers solutions to every query that comes our way.

MySQL Flush Cache In InnoDB

The flushing of dirty pages from the buffer cache is one of the activities that InnoDB carries out in the background. The term “dirty pages” refers to modified pages that have not yet been written to the data files on the disc. A background thread in InnoDB known as the InnoDB Page Cleaner Thread in MySQL flushes old pages from the InnoDB Buffer Pool to disc. The InnoDB Master Thread performs this action for versions prior to MySQL 5.6.

However, the  page cleaner threads perform buffer pool flushing from MySQL 5.7 onwards. The innodb_page_cleaners  variable, which has a default value of 4, regulates the number of page cleaner threads. However, InnoDB page cleaners are automatically set to the same value as innodb_buffer_pool_instances if the number of page cleaner threads is more than the number of buffer pool instances.

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 server variable innodb_page_cleaners controls the number of InnoDB Page Cleaner Threads:

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

Several Page Cleaner Threads that are larger than Buffer Pool Instances are currently not seen as beneficial by MySQL. To display  the precise number of dirty InnoDB pages, use the command:

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

Flushing the InnoDB Cache Pool in MySQL

The innodb_io_capacity variable controls the frequency of flush (from Buffer Pool) and merge (from Change Buffer) operations:

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

The total capacity of all InnoDB Buffer Pool Instances constitutes the innodb_io_capacity. The InnoDB Page Cleaner Thread wipes other dirty pages from the same InnoDB extent under the direction of the server variable innodb_flush_neighbors.

Be careful that if there is a delay in flushing, the pace of buffer pool flushing may be greater than InnoDB’s allotted I/O capacity, as specified by the innodb_io_capacity setting. In such cases, the iinnodb_io_capacity_max value specifies an upper limit on I/O capacity to prevent a spike in I/O activity from using up the server’s whole I/O capacity.

Innodb_io_capacity parameter affects all instances of the buffer pool. Buffer pool instances receive equal I/O capacity during the flushing of dirty pages.

[Need assistance with another issue? We’re available 24/7.]

Conclusion

In this article, we have provided the details from our Tech team about the MySQL Flush Cache in InnoDB.