InnoDB warning: page_cleaner 1000ms intended loop took longer, settings might not be optimal
InnoDB: 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.
Fix it fast
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');2Match what you see, then jump to its fix
- 1
The warning shows up with a large buffer pool or several buffer pool instancesinnodb_lru_scan_depth is too high for the diskFix - 2
Flushing is slow and disk utilisation sits at its limit during the warningsinnodb_io_capacity does not match what the storage can really doFix - 3
innodb_adaptive_flushing is OFF in the first-step outputAdaptive flushing is switched offFix
- 1
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 = 2563Verify
The warning stops appearing under the same load.
SHOW VARIABLES LIKE 'innodb_lru_scan_depth';Confirmed by dev.mysql.com
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
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 = ON3Verify
The variable shows ON after the restart.
SHOW VARIABLES LIKE 'innodb_adaptive_flushing';Confirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix