MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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
O_DIRECT_NO_FSYNC
).
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
doublewrite files.
The following variables are provided for doublewrite buffer configuration:
The innodb_doublewrite
variable controls whether the doublwrite buffer is enabled. It
is enabled by default in most cases. To disable the
doublewrite buffer, set
innodb_doublewrite
to 0 or
start the server with
--skip-innodb-doublewrite
. You might
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.
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_doublewrite
setting 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_flush_method
setting
of O_DIRECT
is recommended.
The innodb_doublewrite_dir
variable (introduced in MySQL 8.0.20) defines the directory
where InnoDB
creates doublewrite files. If
no directory is specified, doublewrite files are created in
the innodb_data_home_dir
directory, 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.
The innodb_doublewrite_files
variable 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 InnoDB
page 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 InnoDB
page 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
innodb_buffer_pool_instances
variable.)
Doublewrite file names have the following format:
#ib_
.
For example, the following doublewrite files are created for
an MySQL instance with an page_size
_file_number
.dblwrInnoDB
pages size
of 16KB and a single buffer pool:
#ib_16384_0.dblwr #ib_16384_1.dblwr
The innodb_doublewrite_files
variable is intended for advanced performance tuning. The
default setting should be suitable for most users.
The innodb_doublewrite_pages
variable (introduced in MySQL 8.0.20) controls the number of
maximum number of doublewrite pages per thread. If no value is
specified,
innodb_doublewrite_pages
is
set to the
innodb_write_io_threads
value. This variable is intended for advanced performance
tuning. The default value should be suitable for most users.
The
innodb_doublewrite_batch_size
variable (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, InnoDB
automatically
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.