Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

InnoDB warning: page_cleaner 1000ms intended loop took longer, settings might not be optimal

ErrorInnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal.

Short answer

The page cleaner threads flush dirty pages about once a second. The warning means a pass took much longer than that, so flushing is falling behind your disk. The usual levers are innodb_lru_scan_depth (scan less per pass), innodb_io_capacity to match real disk speed, and a faster disk. Change one setting at a time.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the current flushing settings and the size of the buffer pool, so you know what each pass has to scan.

    SHOW VARIABLES WHERE Variable_name IN
      ('innodb_lru_scan_depth','innodb_io_capacity','innodb_io_capacity_max',
       'innodb_page_cleaners','innodb_buffer_pool_instances','innodb_adaptive_flushing');
  2. 2Match what you see, then jump to its fix

    1. 1The warning shows up with a large buffer pool or several buffer pool instancesinnodb_lru_scan_depth is too high for the diskFix
    2. 2Flushing is slow and disk utilisation sits at its limit during the warningsinnodb_io_capacity does not match what the storage can really doFix
    3. 3innodb_adaptive_flushing is OFF in the first-step outputAdaptive flushing is switched offFix

What does the error look like?

  • InnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal.
  • The warning appears in the error log during write-heavy load or backups

Cause 1 of 3

innodb_lru_scan_depth is too high for the disk

1Check

The page cleaner scans this many pages per buffer pool instance, once per second. The manual says a value significantly higher than necessary can hurt performance, and that a write-heavy workload that saturates I/O should lower it, especially with a large buffer pool.

SHOW VARIABLES LIKE 'innodb_lru_scan_depth';

2Fix

Lower it in the server configuration and restart, then watch the log. Move in small steps.

# my.cnf
[mysqld]
innodb_lru_scan_depth = 256

3Verify

The warning stops appearing under the same load.

SHOW VARIABLES LIKE 'innodb_lru_scan_depth';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

innodb_io_capacity does not match what the storage can really do

1Check

innodb_io_capacity tells InnoDB how much I/O it may use for background flushing, shared equally among buffer pool instances. If flushing falls behind, InnoDB may exceed it up to innodb_io_capacity_max.

SHOW VARIABLES LIKE 'innodb_io_capacity%';

2Fix

Set innodb_io_capacity to roughly the IOPS your storage can sustain, as low as practical without letting background work fall behind. The manual suggests twice that value as a starting point for innodb_io_capacity_max, which cannot be lower than innodb_io_capacity. It can be changed at runtime with SET GLOBAL.

# my.cnf
[mysqld]
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
# or at runtime: SET GLOBAL innodb_io_capacity = 1000;

3Verify

Compare the warning frequency before and after one change, under similar load.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Adaptive flushing is switched off

1Check

Adaptive flushing adjusts the flush rate to the speed of redo log generation and is on by default. It matters most when the redo log is close to full.

SHOW VARIABLES LIKE 'innodb_adaptive_flushing';

2Fix

Turn it back on unless you have measured a reason to keep it off.

# my.cnf
[mysqld]
innodb_adaptive_flushing = ON

3Verify

The variable shows ON after the restart.

SHOW VARIABLES LIKE 'innodb_adaptive_flushing';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Set innodb_io_capacity from measured disk speed, not from a blog or another server.
  • Change one flushing variable at a time and keep a note of the result.
  • Watch the error log during backups and bulk loads, which are the usual triggers.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.