Why does MySQL's InnoDB write flushed pages to a doublewrite buffer when it already has a redo log, and when is turning it off acceptable?
answer
- a 16 KB page, smaller atomic writes
- half old, half new
- redo needs an intact page
- write a copy first, then in place
- DETECT_AND_RECOVER versus DETECT_ONLY
basics
~20 sA crash can leave a 16 KB InnoDB page half-written, and redo records cannot be replayed onto a torn page. The doublewrite buffer keeps an intact copy to restore first; disable it only where storage guarantees whole-page writes.
solid answer
~50 sInnoDB pages are 16 KB by default, but the operating system and storage usually write in smaller units, so a power loss or a killed `mysqld` in the middle of a page write can leave a **torn page**: part new, part old, failing its checksum. InnoDB's redo log records describe changes to pages, and replaying them assumes the page on disk is an intact earlier version, so redo alone cannot rebuild a torn page. Before writing flushed pages in place, InnoDB writes them in a sequential batch to the **doublewrite** files and flushes those; during recovery it replaces any torn page from that copy, then applies redo. The cost is far below double the I/O. `innodb_doublewrite` is `ON` by default; turning it `OFF` is acceptable only for throwaway instances such as benchmarks, or on storage proven to write whole pages atomically. In MySQL 8.4 `innodb_doublewrite_files` defaults to 2 and `innodb_doublewrite_pages` to 128.
code
ini · 3 lines[mysqld]
innodb_doublewrite = ON
innodb_doublewrite_dir = /fast-ssd/mysql-dblwrgo deeper
Recall that InnoDB writes pages twice on purpose, first to doublewrite files and then in place, to survive a crash in the middle of a page write.
Explain a torn page, why redo records cannot be applied to one, and the order of writes and flushes that guarantees one intact copy exists.
Judge when innodb_doublewrite=OFF is defensible, know the DETECT_ONLY and DETECT_AND_RECOVER modes and the restart rule, and put the files on fast storage.
Treat disabling doublewrite as a durability decision needing proof of atomic writes from the storage layer, documented and owned, never a tuning tweak.
## The problem: torn pages InnoDB reads and writes data in **pages**, 16 KB by default (`innodb_page_size`). The layers underneath, the filesystem and the device, usually guarantee atomicity only for smaller units. If the operating system crashes, power fails or `mysqld` is killed while a page is being written, the page on disk can end up part new and part old. That is a **torn page**, and InnoDB detects it because the page checksum no longer matches. InnoDB's **redo log**, kept in the `#innodb_redo` directory as `#ib_redoN` files, records changes so that crash recovery can roll the data files forward. But redo records describe *changes to a page*; replaying them assumes the page on disk is a consistent earlier version. A torn page is neither the old version nor the new one, so there is nothing valid to replay onto. Without another copy the page, and possibly the table, is lost, and the usual remedy is a restore from backup. ## How the doublewrite buffer works The **doublewrite buffer** is a storage area on disk, not a memory structure, where InnoDB writes pages flushed from the buffer pool before writing them to their real positions: 1. A batch of dirty pages is written sequentially to a doublewrite file. 2. That write is flushed to durable storage. 3. Only then are the pages written to their proper places in the tablespace files. 4. During crash recovery, InnoDB checks pages; if one is torn, it takes the good copy from the doublewrite file, writes it back and then applies redo as usual. If the crash hits during step 1, the in-place pages are still intact and the doublewrite copy is simply ignored. If it hits during step 3, the doublewrite copy is complete. Either way one good copy exists. Although the data is written twice, the I/O cost is far lower than double: the doublewrite write is one large sequential chunk followed by a single `fsync()` call (except with `innodb_flush_method=O_DIRECT_NO_FSYNC`). ## The files and settings | Variable | MySQL 8.4 default | Role | |---|---|---| | `innodb_doublewrite` | `ON` | `ON`, `OFF`, `DETECT_AND_RECOVER` or `DETECT_ONLY` | | `innodb_doublewrite_dir` | unset: `innodb_data_home_dir`, else the data directory | Where doublewrite files live | | `innodb_doublewrite_files` | `2` | Number of doublewrite files | | `innodb_doublewrite_pages` | `128` | Maximum doublewrite pages per thread | The files are named after the page size, for example `#ib_16384_0.dblwr` and `#ib_16384_1.dblwr`; there are flush-list and LRU-list files. MySQL 8.4 changed two defaults: in 8.0, `innodb_doublewrite_files` was twice `innodb_buffer_pool_instances` and `innodb_doublewrite_pages` followed `innodb_write_io_threads`. The manual's advice is to put `innodb_doublewrite_dir` on the fastest storage available. The modes of `innodb_doublewrite`: - **`ON`** and **`DETECT_AND_RECOVER`** are the same: full page content goes to the doublewrite buffer and recovery uses it to fix incomplete writes. - **`DETECT_ONLY`** writes only metadata. InnoDB can still *detect* a torn page during recovery but cannot *repair* it from the buffer. Its files use a `.bdblwr` extension. - **`OFF`** disables the mechanism entirely. Switching between `ON`, `DETECT_AND_RECOVER` and `DETECT_ONLY` works at runtime; switching to or from `OFF` does not, and needs a restart. ## When turning it off is acceptable The MySQL manual frames the trade-off plainly: disable it if you care more about performance than data integrity, as when running benchmarks. In practice: - **Acceptable:** benchmark rigs, disposable test instances, a bulk load into a fresh instance you will rebuild from source if it crashes. - **Acceptable with proof:** storage that guarantees a page-sized write is atomic. The manual's one built-in case is a flash device family with native atomic writes, on which InnoDB disables doublewrite automatically, and for all data files, since the setting is global. - **Not acceptable:** a production primary on ordinary disks or cloud volumes whose atomic-write guarantee you have not verified. You trade a small, steady write cost for the risk of a page that cannot be recovered after a power loss. Two related settings are often confused with this one. `innodb_flush_log_at_trx_commit` controls how durably each commit's redo is flushed, and so whether the latest commits survive a crash; it does nothing about torn pages. Removing redo log files to speed up startup is something the manual explicitly advises against, even when some data loss is acceptable. The phases of crash recovery in general are a separate topic; what is InnoDB's own is this copy-before-overwrite step that runs before redo.
- Why does the doublewrite buffer not halve InnoDB's write throughput?The doublewrite copy is written as one large sequential chunk and flushed with a single `fsync()` call, after which the pages go to their scattered in-place positions as they would anyway. Sequential writes are cheap compared with the random in-place writes, so the overhead is a fraction of the page write volume, not a doubling of I/O operations.
- What does innodb_doublewrite=DETECT_ONLY give up compared with ON?`DETECT_ONLY` writes metadata, not page content, so it costs less than `ON`. The manual describes it as intended only for detecting incomplete page writes during recovery. What it gives up compared with `ON` is repair, because no page copy exists, so a detected torn page still means restoring from a backup or rebuilding the instance from another copy.
- Can you turn the doublewrite buffer off at runtime on a busy server?No. MySQL supports runtime changes among `ON`, `DETECT_AND_RECOVER` and `DETECT_ONLY`, but not between an enabled setting and `OFF` in either direction. Disabling or re-enabling it fully requires a restart with the new value in the option file.
Rewriting a page of an important ledger: you first copy the new version into a scratch notebook and only then rub out and rewrite the ledger page. If you are interrupted halfway through the ledger page, the notebook still holds a complete copy to finish from; the list of changes you made that day, the redo, is no help on a page that is half erased.
- The redo log can rebuild any page, so doublewrite is redundant.
- The doublewrite buffer is an in-memory cache that disappears on restart.
- Doublewrite doubles InnoDB's write I/O, so disabling it is free performance.
- innodb_flush_log_at_trx_commit=1 makes the doublewrite buffer unnecessary.
- DETECT_ONLY repairs torn pages, just more cheaply than ON.
Assumes MySQL 8.4, which changed the innodb_doublewrite_files default to 2 and innodb_doublewrite_pages to 128.