Why InnoDB Double Write Matters: MySQL vs Oracle Recovery Mechanisms

InnoDB Double Write Overview

Since MySQL 5.5.5, InnoDB has been the default storage engine. One of its key features is the double‑write buffer, which protects data from loss during power failures or crashes by writing modified pages twice: first to a 2 MB double‑write buffer in the shared tablespace, then to the final data files.

The buffer is divided into two units: 120 pages for bulk dirty‑page flushing and 8 pages for single‑page flushes. This design allows background threads to flush pages without blocking foreground operations.

How Double Write Works

When a dirty page is ready, MySQL copies it into the double‑write buffer using memcpy, writes the 1 MB buffer to the shared tablespace twice, and then calls fsync to ensure durability. Because the two writes are sequential, the overhead is modest (about 5 % according to Percona tests).

After the buffer is persisted, the pages are written to their final locations, which involves random writes and incurs a higher cost.

Monitoring Double Write Activity

show status like '%dbl%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Innodb_dblwr_pages_written | 23196544 |
| Innodb_dblwr_writes        | 4639373  |
+----------------------------+----------+

In the example above, the ratio of pages written to writes is roughly 5:1, indicating a low frequency of data changes.

Parallel Double Write in Percona Server

Percona Server 5.7 introduces the innodb_parallel_doublewrite_path parameter, allowing parallel flushing of dirty pages:

innodb_parallel_doublewrite_path = xb_doublewrite

A corresponding 30 MB file (e.g., xb_doublewrite) is created on disk to hold the parallel buffers.

Oracle’s Approach to Partial Writes

Oracle also experiences partial writes, but handles them differently. During hot backup (open‑database backup), Oracle records a full copy of a block before modification. If a crash occurs, the stored copy can be used to reconstruct the damaged block.

These “fractured blocks” arise when the block header and tail SCN values become inconsistent. Oracle mitigates the issue with commands such as:

ALTER TABLESPACE ... BEGIN BACKUP;
-- perform backup
ALTER TABLESPACE ... END BACKUP;

For non‑RMAN backups, the BEGIN BACKUP and END BACKUP statements ensure a complete block image is logged before changes.

Oracle Block Types

Data block (default 8 KB) – SELECT file#, name, block_size FROM v$datafile; Redo block – size equals OS block size; query with SELECT lebsz FROM x$kccle; Control‑file block – SELECT block_size FROM v$controlfile; Since Oracle 11g, redo logs include a blocksize attribute (default 512 bytes), which is used during recovery.

Checkpoint and Recovery

Oracle relies on checkpoints recorded in the control file. After a crash, the database mounts, reads the latest checkpoint, and applies redo logs to bring the data files to a consistent state (crash recovery). The control file’s SCN (System Change Number) plays the same role as MySQL’s LSN.

Comparing MySQL and Oracle Recovery Mechanisms

Both systems use redo logs, but MySQL also maintains a binary log (binlog) at the server level to support replication and point‑in‑time recovery across multiple storage engines. Oracle’s redo log combines the functions of MySQL’s binlog and redo.

In MySQL, a transaction commit writes to both the binlog and the InnoDB redo log, coordinated by a two‑phase commit. Oracle, lacking pluggable engines, uses only redo.

Storage Layer Considerations

On modern SSDs, double write introduces extra write amplification and wear. Tests show an 8‑10 % performance penalty for CPU‑bound workloads and up to 34 % for I/O‑bound workloads, with a corresponding increase in SSD wear.

File‑system characteristics also matter. Most filesystems use 4 KB sectors; older 512‑byte sectors can cause partial I/O issues, especially in emulation mode where logical writes are 512 bytes but physical writes are 4 KB.

Practical Takeaways for DBAs

Enable and monitor the double‑write buffer to safeguard against power loss.

Consider innodb_parallel_doublewrite_path on Percona Server for better throughput.

Understand Oracle’s hot‑backup workflow and fractured‑block handling.

Align checkpoint settings ( innodb_fast_shutdown, innodb_lru_scan_depth) with workload characteristics.

Account for SSD wear when double write is active; evaluate trade‑offs between data safety and performance.

By studying both MySQL’s InnoDB double‑write mechanism and Oracle’s redo‑based recovery, DBAs can make informed decisions about configuration, performance tuning, and data‑integrity strategies.