增加缓冲池大小Increase buffer pool size

当表数据缓存在 InnoDB 缓冲池中时，可以通过查询重复访问它，而不需要任何磁盘 I/O。使用 innodb_buffer_pool_size 选项指定缓冲池的大小。此内存区域非常重要，因此通常建议将 innodb_buffer_pool_size 配置为系统内存的 50% 到 75%。有关详细信息，请参阅第 10.12.3.1 节“MySQL 如何使用内存”。When table data is cached in the InnoDB buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O. Specify the size of the buffer pool with the innodb_buffer_pool_size option. This memory area is important enough that it is typically recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory. For more information see, Section 10.12.3.1, “How MySQL Uses Memory”.

调整冲洗方式Adjust the flush method

在某些版本的 GNU/Linux 和 Unix 中，使用 Unix fsync() 调用和类似方法将文件刷新到磁盘的速度出奇地慢。如果数据库写入性能存在问题，请使用设置为 O_DSYNC 的 innodb_flush_method 参数进行基准测试。In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

配置操作系统刷新阈值Configure a threshold for operating system flushes

默认情况下，当InnoDB创建一个新的数据文件，例如新的日志文件或表空间文件时，文件会先完全写入操作系统缓存，然后才刷新到磁盘，这可能会导致大量磁盘写入活动同时发生。为了强制从操作系统缓存进行更小、周期性的数据刷新，您可以使用innodb_fsync_threshold变量来定义一个阈值，以字节为单位。当达到字节阈值时，操作系统缓存的内容会刷新到磁盘。默认值 0 会强制使用默认行为，即只有在文件完全写入缓存后才将数据刷新到磁盘。By default, when InnoDB creates a new data file, such as a new log file or tablespace file, the file is fully written to the operating system cache before it is flushed to disk, which can cause a large amount of disk write activity to occur at once. To force smaller, periodic flushes of data from the operating system cache, you can use the innodb_fsync_threshold variable to define a threshold value, in bytes. When the byte threshold is reached, the contents of the operating system cache are flushed to disk. The default value of 0 forces the default behavior, which is to flush data to disk only after a file is fully written to the cache.

在多个 MySQL 实例使用相同存储设备的情况下，指定阈值来强制进行较小的定期刷新可能会很有用。例如，创建新的 MySQL 实例及其关联的数据文件可能会导致磁盘写入活动大幅激增，从而影响使用相同存储设备的其他 MySQL 实例的性能。配置阈值有助于避免写入活动激增。Specifying a threshold to force smaller, periodic flushes may be beneficial in cases where multiple MySQL instances use the same storage devices. For example, creating a new MySQL instance and its associated data files could cause large surges of disk write activity, impeding the performance of other MySQL instances that use the same storage devices. Configuring a threshold helps avoid such surges in write activity.

使用 fdatasync() 而不是 fsync()Use fdatasync() instead of fsync()

在支持 fdatasync() 系统调用的平台上，innodb_use_fdatasync 变量允许对操作系统使用 fdatasync() 而不是 fsync()脸红。 fdatasync() 系统调用不会刷新对文件元数据的更改，除非后续数据检索需要，从而提供潜在的性能优势。On platforms that support fdatasync() system calls, the innodb_use_fdatasync variable permits using fdatasync() instead of fsync() for operating system flushes. An fdatasync() system call does not flush changes to file metadata unless required for subsequent data retrieval, providing a potential performance benefit.

innodb_flush_method 设置的子集，例如 fsync、 O_DSYNC 和 O_DIRECT 使用 fsync() 系统调用。使用这些设置时，innodb_use_fdatasync 变量适用。A subset of innodb_flush_method settings such as fsync, O_DSYNC, and O_DIRECT use fsync() system calls. The innodb_use_fdatasync variable is applicable when using those settings.

在 Linux 上将 noop 或截止时间 I/O 调度程序与本机 AIO 结合使用Use a noop or deadline I/O scheduler with native AIO on Linux

InnoDB 使用 Linux 上的异​​步 I/O 子系统（本机 AIO）来执行数据文件页面的预读和写入请求。此行为由 innodb_use_native_aio 配置选项控制，该选项默认启用。对于本机 AIO，I/O 调度程序的类型对 I/O 性能影响更大。通常，建议使用 noop 和 Deadline I/O 调度程序。进行基准测试以确定哪个 I/O 调度程序可以为您的工作负载和环境提供最佳结果。有关详细信息，请参阅第 17.8.6 节“在 Linux 上使用异步 I/O”。InnoDB uses the asynchronous I/O subsystem (native AIO) on Linux to perform read-ahead and write requests for data file pages. This behavior is controlled by the innodb_use_native_aio configuration option, which is enabled by default. With native AIO, the type of I/O scheduler has greater influence on I/O performance. Generally, noop and deadline I/O schedulers are recommended. Conduct benchmarks to determine which I/O scheduler provides the best results for your workload and environment. For more information, see Section 17.8.6, “Using Asynchronous I/O on Linux”.

在 Solaris 10 上针对 x86_64 架构使用直接 I/OUse direct I/O on Solaris 10 for x86_64 architecture

在 x86_64 架构 (AMD Opteron) 的 Solaris 10 上使用 InnoDB 存储引擎时，对 InnoDB 相关文件使用直接 I/O，以避免降低 InnoDB 性能。要对用于存储 InnoDB 相关文件的整个 UFS 文件系统使用直接 I/O，请使用 forcedirectio 选项挂载它；请参阅mount_ufs(1M)。 （Solaris 10/x86_64 上默认不使用此选项。）要将直接 I/O 仅应用于 InnoDB 文件操作而不是整个文件系统，请设置 innodb_flush_method = O_DIRECT。通过此设置，InnoDB 调用 directio() 而不是 fcntl() 来进行数据文件的 I/O（而不是日志文件的 I/O）。When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O for InnoDB-related files to avoid degradation of InnoDB performance. To use direct I/O for an entire UFS file system used for storing InnoDB-related files, mount it with the forcedirectio option; see mount_ufs(1M). (The default on Solaris 10/x86_64 is not to use this option.) To apply direct I/O only to InnoDB file operations rather than the whole file system, set innodb_flush_method = O_DIRECT. With this setting, InnoDB calls directio() instead of fcntl() for I/O to data files (not for I/O to log files).

在 Solaris 2.6 或更高版本中使用原始存储来存储数据和日志文件Use raw storage for data and log files with Solaris 2.6 or later

在任何 Solaris 2.6 及更高版本和任何平台 (sparc/x86/x64/amd64) 上使用具有较大 innodb_buffer_pool_size 值的 InnoDB 存储引擎时，请进行基准测试使用原始设备或单独的直接 I/O UFS 文件系统上的 InnoDB 数据文件和日志文件，使用前面所述的 forcedirectio 挂载选项。 （如果要对日志文件进行直接 I/O，则必须使用挂载选项，而不是设置 innodb_flush_method。）Veritas 文件系统 VxFS 的用户应使用 convosync=direct 挂载选项。When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks with InnoDB data files and log files on raw devices or on a separate direct I/O UFS file system, using the forcedirectio mount option as described previously. (It is necessary to use the mount option rather than setting innodb_flush_method if you want direct I/O for the log files.) Users of the Veritas file system VxFS should use the convosync=direct mount option.

不要将其他 MySQL 数据文件，例如MyISAM表的数据文件，放在直接 I/O 文件系统上。可执行文件或库不得放置在直接 I/O 文件系统上。Do not place other MySQL data files, such as those for MyISAM tables, on a direct I/O file system. Executables or libraries must not be placed on a direct I/O file system.

使用额外的存储设备Use additional storage devices

额外的存储设备可用于设置 RAID 配置。有关相关信息，请参阅第 10.12.1 节“优化磁盘 I/O”。Additional storage devices could be used to set up a RAID configuration. For related information, see Section 10.12.1, “Optimizing Disk I/O”.

或者，InnoDB 表空间数据文件和日志文件可以放置在不同的物理磁盘上。有关详细信息，请参阅以下部分：Alternatively, InnoDB tablespace data files and log files can be placed on different physical disks. For more information, refer to the following sections:

考虑非旋转存储Consider non-rotational storage

非旋转存储通常为随机 I/O 操作提供更好的性能；以及用于顺序 I/O 操作的循环存储。当跨旋转和非旋转存储设备分布数据和日志文件时，请考虑主要在每个文件上执行的 I/O 操作类型。Non-rotational storage generally provides better performance for random I/O operations; and rotational storage for sequential I/O operations. When distributing data and log files across rotational and non-rotational storage devices, consider the type of I/O operations that are predominantly performed on each file.

面向随机 I/O 的文件通常包括file-per-table 和通用表空间数据文件、撤消表空间 文件和临时表空间 文件。面向顺序 I/O 的文件包括 InnoDB 系统表空间 文件、双写文件和日志文件，例如 二进制日志 文件和 重做日志 文件。Random I/O-oriented files typically include file-per-table and general tablespace data files, undo tablespace files, and temporary tablespace files. Sequential I/O-oriented files include InnoDB system tablespace files, doublewrite files, and log files such as binary log files and redo log files.

使用非旋转存储时查看以下配置选项的设置：Review settings for the following configuration options when using non-rotational storage:

确保您的操作系统启用了 TRIM 支持。它通常默认启用。Ensure that TRIM support is enabled for your operating system. It is typically enabled by default.

增加 I/O 容量以避免积压Increase I/O capacity to avoid backlogs

如果吞吐量由于 InnoDB 检查点 操作而周期性下降，请考虑增加 innodb_io_capacity 配置选项的值。较高的值会导致更频繁的刷新，避免积压的工作导致吞吐量下降。If throughput drops periodically because of InnoDB checkpoint operations, consider increasing the value of the innodb_io_capacity configuration option. Higher values cause more frequent flushing, avoiding the backlog of work that can cause dips in throughput.

如果刷新不落后，则降低 I/O 容量Lower I/O capacity if flushing does not fall behind

如果系统在 InnoDB 刷新 操作方面没有落后，请考虑降低 innodb_io_capacity 配置的值选项。通常，您可以将此选项值设置为尽可能低的值，但不要太低，以免导致吞吐量周期性下降，如前面项目符号中所述。在典型情况下，您可以降低选项值，您可能会在 SHOW ENGINE INNODB STATUS 的输出中看到如下组合：If the system is not falling behind with InnoDB flushing operations, consider lowering the value of the innodb_io_capacity configuration option. Typically, you keep this option value as low as practical, but not so low that it causes periodic drops in throughput as mentioned in the preceding bullet. In a typical scenario where you could lower the option value, you might see a combination like this in the output from SHOW ENGINE INNODB STATUS:

在 Fusion-io 设备上存储系统表空间文件Store system tablespace files on Fusion-io devices

您可以通过将包含双写存储区域的文件存储在支持原子写入的 Fusion-io 设备上来利用与双写缓冲区相关的 I/O 优化。 （doublewrite 缓冲区存储区域驻留在 doublewrite 文件中。请参见第 17.6.4 节“Doublewrite 缓冲区”。）当 doublewrite 存储区域文件放置在支持原子写入的 Fusion-io 设备上时，doublewrite 缓冲区会自动禁用，并且 Fusion-io 原子写入将用于所有数据文件。此功能仅在 Fusion-io 硬件上受支持，并且仅针对 Linux 上的 Fusion-io NVMFS 启用。要充分利用此功能，建议将 innodb_flush_method 设置为 O_DIRECT。You can take advantage of a doublewrite buffer-related I/O optimization by storing the files that contain the doublewrite storage area on Fusion-io devices that support atomic writes. (The doublewrite buffer storage area resides in doublewrite files. See Section 17.6.4, “Doublewrite Buffer”.) When doublewrite storage area files are placed on Fusion-io devices that support atomic writes, the doublewrite buffer is automatically disabled and Fusion-io atomic writes are used for all data files. 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.

禁用压缩页面的日志记录 Disable logging of compressed pages

使用InnoDB表压缩功能时，重新压缩页面会写入重做日志。此行为由 innodb_log_compressed_pages 控制，默认情况下启用该功能以防止在恢复期间使用不同版本的 zlib 压缩算法时可能发生的损坏。如果您确定 zlib 版本不会更改，请禁用 innodb_log_compressed_pages 以减少修改压缩数据的工作负载生成重做日志。When using the InnoDB table compression feature, images of re-compressed pages are written to the redo log when changes are made to compressed data. This behavior is controlled by innodb_log_compressed_pages, which is enabled by default to prevent corruption that can occur if a different version of the zlib compression algorithm is used during recovery. If you are certain that the zlib version is not subject to change, disable innodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.