The InnoDB doublewrite buffer was implemented to recover from half-written pages. This can happen when there's a power failure while InnoDB is writing a page to disk. On reading that page, InnoDB can discover the corruption from the mismatch of the page checksum. However, in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy.

Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk will InnoDB write the page to the final destination. When recovering, InnoDB scans the double write buffer and for each valid page in the buffer checks if the page in the data file is valid too.

Although data is written twice, the doublewrite buffer does not require twice as much I/O, as data is written to the buffer in a large sequential chunk with a single fsync() call. There is extra time consumed however, and the effect becomes visible with fast storage and a heavy write load.

Doublewrite buffer settings

MariaDB until 10.0

Before MariaDB 10.0, the doublewrite buffer can be moved to a different drive, reducing contention on random reads. Use the innodb_doublewrite_file system variable to specify a location. Since the doublewrite buffer is mostly sequential writes, a traditional HDD is a better choice than SSD. This variable has been removed in MariaDB 10.0.

To turn off the doublewrite buffer, set the innodb_doublewrite system variable to 0. This is not recommended for production systems. An alternative option is atomic writes - see FusionIO DirectFS atomic write support for details.


Comments loading...