innodb_doublewrite

innodb_doublewrite 变量控制双写缓冲区是否启用。在大多数情况下，它默认是启用的。要禁用双写缓冲区，将 innodb_doublewrite 设置为 OFF。如果您更关心性能而非数据完整性，例如在进行基准测试时，可以考虑禁用双写缓冲区。The innodb_doublewrite variable controls whether the doublewrite buffer is enabled. It is enabled by default in most cases. To disable the doublewrite buffer, set innodb_doublewrite to 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.

innodb_doublewrite 支持 DETECT_AND_RECOVER 和 DETECT_ONLY 设置。innodb_doublewrite supports DETECT_AND_RECOVER and DETECT_ONLY settings.

DETECT_AND_RECOVER 设置与 ON 设置相同。使用此设置时，双写缓冲区将完全启用，数据库页面内容会写入双写缓冲区，在恢复过程中访问该缓冲区以修复未完成的页面写入。The DETECT_AND_RECOVER setting is the same as the ON setting. 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_ONLY 设置时，仅将元数据写入双写缓冲区。数据库页面内容不会写入双写缓冲区，恢复过程也不会使用双写缓冲区来修复未完成的页面写入。此轻量级设置仅用于检测未完成的页面写入。With the DETECT_ONLY setting, only metadata is written to the doublewrite 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.

MySQL 支持对 innodb_doublewrite 设置进行动态更改，用于启用双写缓冲区，可在 ON、DETECT_AND_RECOVER 和 DETECT_ONLY 之间切换。MySQL 不支持在启用双写缓冲区的设置与 OFF 之间或反之进行动态更改。MySQL supports dynamic changes to the innodb_doublewrite setting that enables the doublewrite buffer, between ON, DETECT_AND_RECOVER, and DETECT_ONLY. MySQL does not support dynamic changes between a setting that enables the doublewrite buffer and OFF or vice versa.

如果双写缓冲区位于支持原子写入的 Fusion-io 设备上，双写缓冲区将自动禁用，并且数据文件写入将改为使用 Fusion-io 的原子写入。然而，请注意 innodb_doublewrite 设置是全局的。当双写缓冲区被禁用时，它对于所有数据文件都是禁用的，包括那些不在 Fusion-io 硬件上的文件。此功能仅在 Fusion-io 硬件上受支持，并且仅在 Linux 上的 Fusion-io NVMFS 上启用。为了充分利用此功能，建议将 innodb_flush_method 设置为 O_DIRECT。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.

innodb_doublewrite_dir

innodb_doublewrite_dir 变量定义了 InnoDB 创建双写文件的目录。如果未指定目录，双写文件将创建在 innodb_data_home_dir 目录中，如果未指定则默认为数据目录。The innodb_doublewrite_dir variable 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.

理想情况下，doublewrite 目录应放置在最快的存储介质上。Ideally, the doublewrite directory should be placed on the fastest storage media available.

innodb_doublewrite_filesinnodb_doublewrite_files

innodb_doublewrite_files 变量定义了 doublewrite 文件的数量，默认为 2。默认情况下，每个缓冲池实例会创建两个 doublewrite 文件：一个用于 flush 列表的 doublewrite 文件和一个用于 LRU 列表的 doublewrite 文件。The innodb_doublewrite_files variable defines the number of doublewrite files, which defaults to 2. By default, two doublewrite files are created for each buffer pool instance: A flush list doublewrite file and an LRU list doublewrite file.

flush 列表 doublewrite 文件用于从缓冲池 flush 列表中刷出的页面。flush 列表 doublewrite 文件的默认大小为 InnoDB 页面大小 * doublewrite 页面字节数。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.

LRU 列表双写文件用于从缓冲池 LRU 列表中刷新的页面。它还包含用于单页刷新的槽。LRU 列表双写文件的默认大小为 InnoDB 页面大小 * (双写页面数 + (512 / 缓冲池实例数))，其中 512 是为单页刷新保留的槽总数。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.

至少有两个双写文件。双写文件的最大数量是缓冲池实例数量的两倍。（缓冲池实例的数量由 innodb_buffer_pool_instances 变量控制。）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.)

双写文件名具有以下格式：#ib_page_size_file_number.dblwr（在 DETECT_ONLY 设置下为 .bdblwr）。例如，对于具有 16KB InnoDB 页面大小和单个缓冲池的 MySQL 实例，将创建以下双写文件：Doublewrite file names have the following format: #ib_page_size_file_number.dblwr (or .bdblwr with the DETECT_ONLY setting). For example, the following doublewrite files are created for a MySQL instance with an InnoDB pages size of 16KB and a single buffer pool:

#ib_16384_0.dblwr
#ib_16384_1.dblwr

innodb_doublewrite_files 变量用于高级性能调整。默认设置对于大多数用户来说应该是合适的。The innodb_doublewrite_files variable is intended for advanced performance tuning. The default setting should be suitable for most users.

innodb_doublewrite_pagesinnodb_doublewrite_pages

innodb_doublewrite_pages 变量控制每个线程的最大双写页面数。此变量用于高级性能调优。默认值对于大多数用户来说应当是合适的。The innodb_doublewrite_pages variable controls the maximum number of doublewrite pages per thread. This variable is intended for advanced performance tuning. The default value should be suitable for most users.