How to force InnoDB Buffer Pool flushing

InnoDB tries to keep pages in Buffer Pool to be fast. If a page is changed by a DML statement (INSERT, UPDATE, DELETE) this change will be done in InnoDB Buffer Pool and not directly on disk. But those changed InnoDB pages residing in InnoDB Buffer Pool must be flushed sooner or later to disk to become persistent. This is done by the InnoDB background writer thread(s) (default 4).

InnoDB flushes the dirty pages with a pace of innodb_io_capactiy (default 200) pages/s. This variable should be set depending on the rate you are dirtying pages and on the capacity of your I/O system. 1 single server HDD has an I/O capacity of about 200 IOPS, a SSD between 1000 and 50000 IOPS.
The rate of dirtying pages depends on the number of DML statements and the locality of the changes in your database blocks (random vs. sequential, AUTO_INCREMENT vs. UUID).

Keeping many dirty pages in InnoDB Buffer Pool is good from performance point of view. But in certain cases you want to have the number of dirty pages small or even close to zero. This case is during Backups done with MEB. LVM snapshots should in theory not have this problem (otherwise MySQL/InnoDB would not be crash-safe) and MariaDB Backup (mariabackup) and Percona Xtrabackup (xtrabackup) can deal with the problem yet. See: Mariabackup - Concurrent DDL and Backup Issues.

With the new optimized (without redo logging) DDL operations in MySQL 5.7 we were running into the following problems with MEB:

ERROR: InnoDB: An optimized(without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.

MEB will not be able take a consistent backup. Retry the backup operation.

We found out, that systems with a smaller InnoDB Buffer Pool had this problem much less frequent than systems with a bigger InnoDB Buffer Pool. Thus we came to the idea instead of shrinking the InnoDB Buffer Pool to reduce the number of dirty pages in the Buffer Pool.

This is similar to a Checkpoint how it is called in other RDBMS. But I found that the term checkpoint does not always mean the exact same thing in different RBDMS. Further details you can find in Literature.

To force a Checkpoint you can lower the variable innodb_max_dirty_pages_pct variable to 0 before doing a MEB backup and then increasing it again to its original value after the MEB backup. This should reduce the probability of running into the error mentioned above.
Reducing innodb_max_dirty_pages_pct to 0 will possibly lead to an I/O burst on the disk. So lowering the value could be done in smaller decrements.

An other possibility, which is probably much less intrusive, would be to increase innodb_io_capacity to a higher value. But this only works if your I/O system is capable to deal with the higher amount of I/O.

Literature

• OracleFAQs - Checkpoint
• Database Checkpoints (SQL Server)
• SQLShack - Database Checkpoints enhancements SQL Server 2016
• Checkpoints in DBMS
• PostgreSQL Checkpoint
• InnoDB Redo Log - Checkpoints
• More on Checkpoints in InnoDB MySQL 8
• MySQL checkpoint
• MySQL fuzzy checkpointing
• InnoDB Checkpoints
• How InnoDB Performs a Checkpoint
• The relationship between Innodb Log checkpointing and dirty Buffer pool pages
• InnoDB fuzzy checkpoints