🚨 MySQL Error: 'InnoDB: page_cleaner: 1000ms intended loop took ... ms' — Cause, Fix, and Troubleshooting Guide

Overview

This is a warning in the MySQL error log, not a client-facing SQL error, but it signals real I/O pressure:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal. (flushed=1523, during the time.)

The page cleaner thread is supposed to run one flushing loop per second. When a loop takes far longer than 1000ms, InnoDB is falling behind on writing dirty pages from the buffer pool to disk. Left unaddressed, this causes write stalls, latency spikes, and — at the extreme — the server blocking user writes until flushing catches up.

Symptoms

• Recurring page_cleaner: 1000ms intended loop took N ms lines in the error log (N well above 1000).
• Periodic write latency spikes or brief stalls, often correlated with checkpoints.
• High Innodb_buffer_pool_pages_dirty and a growing checkpoint age.
• Slow commits under heavy write bursts.

Common Root Causes

1. innodb_io_capacity set too low for the storage

The default io_capacity (200) assumes slow disks. On SSD/NVMe the cleaner is throttled below what the hardware can do, so it cannot flush fast enough during bursts.

SHOW VARIABLES LIKE 'innodb_io_capacity%';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_io_capacity           | 200   |
| innodb_io_capacity_max       | 2000  |
+------------------------------+-------+

2. Buffer pool too small or dirty-page ratio too high

A small buffer pool churns dirty pages faster than they flush; a high innodb_max_dirty_pages_pct lets dirt accumulate until a panic flush.

3. Write bursts exceeding disk throughput

A batch import or a hot write path outruns the disk’s real IOPS.

4. Single page-cleaner thread bottleneck

One cleaner thread cannot drive enough concurrent I/O against fast storage.

How to diagnose

Check flushing pressure and dirty pages:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';
SHOW ENGINE INNODB STATUS\G   -- see "LOG" section: checkpoint age vs. log capacity

Review the tuning knobs:

SHOW VARIABLES LIKE 'innodb_io_capacity%';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct%';
SHOW VARIABLES LIKE 'innodb_page_cleaners';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Measure real disk throughput (iostat -x 1) to know your true ceiling before raising io_capacity.

Fixes

Raise innodb_io_capacity/..._max to match measured storage IOPS (SSD/NVMe example):

SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL innodb_io_capacity_max = 4000;

Persist and add more cleaner threads and a right-sized buffer pool:

[mysqld]
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_page_cleaners = 4
innodb_buffer_pool_size = 24G          # ~60-75% of RAM on a dedicated box
innodb_max_dirty_pages_pct = 75
innodb_flush_neighbors = 0             # 0 for SSD/NVMe

innodb_page_cleaners and innodb_buffer_pool_size require a restart. Enlarge the redo log so checkpoints are less frequent:

[mysqld]
innodb_redo_log_capacity = 4G          # MySQL 8.0.30+

What to watch out for

• Do not set innodb_io_capacity above what iostat shows the disk can sustain — over-provisioning starves user I/O and makes stalls worse.
• innodb_flush_neighbors=1 helps spinning disks but hurts SSDs by writing extra pages; set it to 0 on flash.
• More page cleaners than CPU cores or than buffer-pool instances yields no benefit.
• A too-small redo log forces aggressive flushing (sharp checkpoints); size it for your write rate.
• Occasional single warnings during a big import are normal; persistent, high-N warnings under steady load are the real signal.

Related

• ERROR 1038: Out of sort memory — another memory/IO tuning issue surfaced by heavy queries.
• ERROR 1205: Lock wait timeout exceeded — write stalls from slow flushing can amplify lock contention.
• ERROR 1114: The table is full — a related capacity/tuning symptom under write pressure.