The doublewrite buffer is a storage area where
InnoDB writes pages flushed from the buffer
pool before writing the pages to their proper positions in the
InnoDB data files. If there is an operating
system, storage subsystem, or unexpected mysqld
process exit in the middle of a page write,
InnoDB can find a good copy of the page from
the doublewrite buffer during crash recovery.
Although data is written twice, the doublewrite buffer does not
require twice as much I/O overhead or twice as many I/O
operations. Data is written to the doublewrite buffer in a large
sequential chunk, with a single
fsync() call to
the operating system (except in the case that
innodb_flush_method is set to
Prior to MySQL 8.0.20, the doublewrite buffer storage area is
located in the
InnoDB system tablespace. As of
MySQL 8.0.20, the doublewrite buffer storage area is located in
The following variables are provided for doublewrite buffer configuration:
innodb_doublewritevariable controls whether the doublwrite buffer is enabled. It is enabled by default in most cases. To disable the doublewrite buffer, set
OFF. Consider disabling the doublewrite buffer if you are more concerned with performance than data integrity, as may be the case when performing benchmarks, for example.
From MySQL 8.0.30,
DETECT_AND_RECOVERsetting is the same as the
ONsetting. With this setting, the doublewrite buffer is fully enabled, with database page content written to the doublewrite buffer where it is accessed during recovery to fix incomplete page writes.
DETECT_ONLYsetting, only metadata is written to the doublwrite buffer. Database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only.
From MySQL 8.0.30, you can change the
innodb_doublewritesetting dynamically between settings that enable the doublewrite buffer, which include
DETECT_AND_ONLY. Changing the setting dynamically from one that enables the doublewrite buffer to
OFFor vice versa is not supported.
If the doublewrite buffer is located on a Fusion-io device that supports atomic writes, the doublewrite buffer is automatically disabled and data file writes are performed using Fusion-io atomic writes instead. However, be aware that the
innodb_doublewritesetting is global. When the doublewrite buffer is disabled, it is disabled for all data files including those that do not reside on Fusion-io hardware. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an
innodb_doublewrite_dirvariable (introduced in MySQL 8.0.20) defines the directory where
InnoDBcreates doublewrite files. If no directory is specified, doublewrite files are created in the
innodb_data_home_dirdirectory, which defaults to the data directory if unspecified.
A hash symbol '#' is automatically prefixed to the specified directory name to avoid conflicts with schema names. However, if a '.', '#'. or '/' prefix is specified explicitly in the directory name, the hash symbol '#' is not prefixed to the directory name.
Ideally, the doublewrite directory should be placed on the fastest storage media available.
innodb_doublewrite_filesvariable defines the number of doublewrite files. By default, two doublewrite files are created for each buffer pool instance: A flush list doublewrite file and an LRU list doublewrite file.
The flush list doublewrite file is for pages flushed from the buffer pool flush list. The default size of a flush list doublewrite file is the
InnoDBpage size * doublewrite page bytes.
The LRU list doublewrite file is for pages flushed from the buffer pool LRU list. It also contains slots for single page flushes. The default size of an LRU list doublewrite file is the
InnoDBpage size * (doublewrite pages + (512 / the number of buffer pool instances)) where 512 is the total number of slots reserved for single page flushes.
At a minimum, there are two doublewrite files. The maximum number of doublewrite files is two times the number of buffer pool instances. (The number of buffer pool instances is controlled by the
Doublewrite file names have the following format:
DETECT_ONLYsetting). For example, the following doublewrite files are created for a MySQL instance with an
InnoDBpages size of 16KB and a single buffer pool:
innodb_doublewrite_filesvariable is intended for advanced performance tuning. The default setting should be suitable for most users.
innodb_doublewrite_pagesvariable (introduced in MySQL 8.0.20) controls the maximum number of doublewrite pages per thread. If no value is specified,
innodb_doublewrite_pagesis set to the
innodb_write_io_threadsvalue. This variable is intended for advanced performance tuning. The default value should be suitable for most users.
innodb_doublewrite_batch_sizevariable (introduced in MySQL 8.0.20) controls the number of doublewrite pages to write in a batch. This variable is intended for advanced performance tuning. The default value should be suitable for most users.
As of MySQL 8.0.23,
encrypts doublewrite file pages that belong to encrypted
tablespaces (see Section 15.13, “InnoDB Data-at-Rest Encryption”).
Likewise, doublewrite file pages belonging page-compressed
tablespaces are compressed. As a result, doublewrite files can
contain different page types including unencrypted and
uncompressed pages, encrypted pages, compressed pages, and pages
that are both encrypted and compressed.