By default, every time InnoDB flushes a 16 KB page to disk, it writes it twice. First into the doublewrite files, then into its real place in the table. That first copy is the doublewrite buffer.
It's there for one bad moment: the power goes out halfway through a page write. The disk is left with a page that's half old, half new (a "torn page"). The redo log can't fix that on its own, because redo describes changes to a page, and it needs a whole page to apply them to. With a clean copy sitting in the doublewrite file, InnoDB repairs the page on restart.
Everyone says it "doubles your writes". We wanted the real numbers, including the middle setting MySQL added in 8.0.30, DETECT_ONLY. So we ran it.
The setup
- MySQL 8.4.13 in Docker: 4 CPUs, 6 GB memory limit, 1 GB buffer pool,
O_DIRECT, 2 GB redo log, binary log off,innodb_flush_log_at_trx_commit=1. - sysbench
oltp_write_only, 8 tables of 1 million rows. That's about twice the buffer pool, so InnoDB is flushing pages all the time. - For each mode: restart, 60 s warm-up, then 90 s runs at 1, 8, 32 and 64 threads.
- Then a second pass: one 90 s run per mode at 32 threads, counting the bytes the container sent to its block device.
- The machine: a 16 vCPU AMD EPYC cloud VM that also runs other services. The data folder sits on XFS in a loop file on the VM's virtual disk. Keep that in mind for the speed numbers.
Bytes: ON writes about twice as much
The second pass, 32 threads, 90 seconds each:
| Mode | Transactions | Pages flushed | Doublewrite copies | Redo log | Disk writes | Per transaction |
|---|---|---|---|---|---|---|
| ON (default) | 56,795 | 41,578 (681 MB) | 681 MB | 207 MB | 1,563 MB | 27.5 KB |
| DETECT_ONLY | 68,899 | 46,042 (754 MB) | metadata only | 267 MB | 1,006 MB | 14.6 KB |
| OFF | 81,992 | 50,056 (820 MB) | none | 344 MB | 1,105 MB | 13.5 KB |
- The disk counter agrees with InnoDB's own counters: for ON, pages + doublewrite copies + redo = 1,569 MB, and the disk saw 1,563 MB.
- Per flushed page: 37.6 KB hit the disk with ON, about 22 KB with DETECT_ONLY or OFF (the 16 KB page plus its share of redo).
- DETECT_ONLY costs almost nothing in bytes. It writes a little metadata about each page, not the page itself.
- Trap:
Innodb_dblwr_pages_writtenstill goes up in DETECT_ONLY (46,042 here), even though no page contents were written. Don't read it as bytes. - Another trap:
Innodb_data_writtenleft the doublewrite copies out in our runs. ON at 1 thread: it said 733 MB, the real total was about 1.3 GB. UseInnodb_pages_written× 16 KB, or measure at the disk.
Speed: lost in the noise on this disk
The first pass, all four thread counts. "Written" is what InnoDB counted per transaction: pages, doublewrite copies and redo.
| Threads | ON: TPS / p95 | DETECT_ONLY: TPS / p95 | OFF: TPS / p95 | Written per transaction (ON / DETECT / OFF) |
|---|---|---|---|---|
| 1 | 133 / 14.7 ms | 108 / 19.3 ms | 100 / 21.9 ms | 110 / 62 / 67 KB |
| 8 | 682 / 23.5 ms | 417 / 47.5 ms | 647 / 24.8 ms | 30 / 21 / 16 KB |
| 32 | 754 / 116.8 ms | 906 / 95.8 ms | 1,092 / 75.8 ms | 28 / 16 / 15 KB |
| 64 | 1,235 / 145.0 ms | 1,070 / 164.4 ms | 842 / 204.1 ms | 24 / 15 / 16 KB |
- At 32 threads, OFF beat ON in both passes (1,092 vs 754 TPS, then 911 vs 631), with DETECT_ONLY in between.
- But at 1 and 64 threads, ON came out on top. And the same setting twice (ON, 32 threads) gave 754 and then 631 TPS.
- That swing is bigger than anything doublewrite does. The disk is shared, and other work on it moves the numbers more than this setting does.
- So on a busy cloud disk, don't expect turning doublewrite off to be a clear speed win. What you reliably get is fewer bytes written.
What it means for SSD wear
- Say you run 500 transactions a second like these, all day. ON: 500 × 86,400 × 27.5 KB ≈ 1.19 TB a day. OFF: about 0.58 TB.
- A 1 TB SSD rated for 600 TBW lasts about 1.4 years at that rate with ON, about 2.8 years with OFF. That counts only what MySQL writes, not the extra writing the SSD does inside itself.
- Our test flushes a lot, because the data is twice the buffer pool and the updates are random. If your hot data fits in memory, fewer pages get flushed per transaction and the gap shrinks.
- To size it for your own server:
Innodb_pages_writtenper second × 16 KB is exactly the amount doublewrite adds.
When OFF is safe
- ZFS. It's copy-on-write: a page is never overwritten in place, so a torn write leaves the old block whole. The usual setup is
recordsize=16Kfor the data folder. - Storage that promises 16 KB atomic writes, end to end. Some cloud setups document this (AWS has "torn write prevention" for 16 KiB writes on some instance types). Only count on it if your provider documents it for your exact instance, volume and file system.
- A database you'd rebuild anyway: a replica you can re-clone, a test or CI database.
- Not safe: ext4 or XFS on a normal SSD or cloud disk. The disk writes in 4 KB (or 512 byte) units, and a 16 KB page is four of them.
- Btrfs: copy-on-write too, but only if you haven't switched CoW off for the data folder (
chattr +Cornodatacow, which many database guides suggest). With CoW off, pages are overwritten in place again.
DETECT_ONLY: the middle setting
- Writes only metadata to the doublewrite file, not the page.
- After a crash, InnoDB can tell that a page was torn and stops with an error, instead of quietly running on a broken page.
- It can't repair the page. You restore from a backup or rebuild from a replica.
- Good fit: storage you believe is atomic, where you still want a tripwire. In our run it wrote about the same bytes as OFF.
Check and change it
- The files live in the data folder:
#ib_16384_0.dblwr(8 MB) and#ib_16384_1.dblwr(16 MB) on our server. innodb_doublewrite_dircan put them on another, faster disk. That moves the extra writes, it doesn't remove them.
Switching at runtime, on MySQL 8.4.13:
So ON and DETECT_ONLY swap on the fly. Going to or from OFF means changing my.cnf and restarting (the same error comes back if you try to turn it on while it's OFF).
Our take
- On ext4 or XFS on SSDs and cloud disks, keep it ON. Half the bytes isn't worth a page you can't get back.
- On ZFS, or storage documented as 16 KB atomic: OFF, or DETECT_ONLY if you want the tripwire.
- Worried about SSD wear? Look at pages flushed first. A buffer pool that holds your hot data cuts both copies at once.
Postgres has the same problem and solves it differently: it writes the whole page into the WAL the first time it changes after a checkpoint (full_page_writes). We measured that side in a SELECT that wrote 485 MB of WAL. For another MySQL write path, see one big transaction that stops every commit. And to find which statements do most of the writing, paste performance_schema digest output into statementsRanker (MySQL tab).

