MySQL 8.0 Performance Regression: Fix InnoDB Doublewrite Pages Default

MySQL 8.0 Performance Regression Caused by InnoDB Doublewrite Pages Default Change​
A critical performance regression in MySQL 8.0 (especially versions 8.0.20+) has been identified, where operations like ALTER TABLE ... FORCE (used to rebuild tables) experience ​3x slower execution​ compared to MySQL 5.7. The root cause stems from a parameter change introduced in MySQL 8.0.20: the default value of innodb_doublewrite_pages was reduced from ​128​ (in 5.7) to ​4.

Key Details:

1. ​Impact of the Parameter Change:
   • innodb_doublewrite_pages controls the number of pages flushed per batch during InnoDB’s doublewrite mechanism.
   • A lower value (4) increases I/O operations, reduces doublewrite buffer efficiency, and elevates disk synchronization costs.
2. ​Historical Context:
   • MySQL 5.7 implicitly used a default equivalent to 128 for this parameter, ensuring better write performance.
   • The regression was confirmed in 2024 but not addressed in MySQL 8.0 due to its LTS "stability" commitment. It was fixed in MySQL 8.4.0 by resetting the default to 128.
3. ​Symptoms and Affected Scenarios:
   • Slowdowns are most noticeable during:
     • Table rebuilds via ALTER TABLE ... ENGINE=InnoDB or ALTER TABLE ... FORCE.
     • DDL operations with large data page writes.
     • High-concurrency environments with frequent write contention.
   • Monitoring metrics like Innodb_dblwr_pages_written and Innodb_dblwr_writes will show elevated I/O activity.
4. ​Solutions:
   • ​Temporary Fix for MySQL 8.0: Set innodb_doublewrite_pages=128 in your configuration file (requires a server restart).
   • ​Long-Term Recommendation: Upgrade to ​MySQL 8.4+​​ to leverage the corrected default.

‍