MySQL 8.0 Doublewrite Mechanism Causes LOAD Performance Degradation and How to Fix It
The article explains how MySQL 8.0’s new doublewrite buffer implementation dramatically slows LOAD performance on a 1 GB file, identifies the innodb_doublewrite_pages default setting as the root cause, and shows that increasing it to 64 restores speeds comparable to MySQL 5.7.
When loading a 1 GB file into MySQL, the LOAD operation finishes in under three minutes on version 5.7 but takes more than an hour on version 8.0, even though both versions run on the same virtual machine with identical configuration snapshots.
After comparing the configuration files of 5.7 and 8.0, the only significant difference affecting performance is the doublewrite mechanism introduced in MySQL 8.0.20, where the doublewrite buffer was moved from the shared tablespace to a separate file and new control parameters were added.
The relevant doublewrite parameters are shown in the original table (innodb_doublewrite_dir, innodb_doublewrite_pages, etc.). The documentation advises not to modify most of them, but the default value of innodb_doublewrite_pages equals innodb_write_io_threads (default 4), which is unusually low for heavy‑load scenarios.
In MySQL 5.7 the doublewrite buffer is flushed in batches of 64 pages, while in 8.0.20+ the batch size is controlled by innodb_doublewrite_pages . With the default of 4, the number of fsync operations during a LOAD increases sixteen‑fold, causing the severe slowdown.
Changing innodb_doublewrite_pages from 4 to 64 restores LOAD performance to the level observed on MySQL 5.7.
The article also provides a concise explanation of MySQL doublewrite: modified pages are first written to an in‑memory doublewrite buffer, then flushed sequentially to a dedicated file; after the buffer is safely persisted, the pages are written to their final data files, allowing recovery from crashes using the buffer or redo log.
Additional Q&A sections cover why redo logs cannot directly recover corrupted pages, whether doublewrite significantly reduces write performance, and how to monitor doublewrite activity using the status variables Innodb_dblwr_pages_written and innodb_dblwr_writes .
Reference diagrams (included as images) compare the InnoDB architecture of MySQL 5.7 and 8.0, highlighting the relocation of the doublewrite buffer.
References: MySQL 5.7 InnoDB Architecture – https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html MySQL 8.0 InnoDB Architecture – https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
YunZhu Net Technology Team
Technical practice sharing from the YunZhu Net Technology Team
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.