If you follow best practices for database design and tuning techniques for SQL operations, but your database is still slow due to heavy disk I/O activity, consider these disk I/O optimizations. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound.
如果您遵循数据库设计和SQL操作调优技术的最佳实践，但由于磁盘I/O活动很大，数据库仍然很慢，那么请考虑这些磁盘I/O优化。如果Unix top工具或Windows任务管理器显示您的工作负载的CPU使用率小于70%，那么您的工作负载可能是磁盘受限的。
Increase buffer pool size
增加缓冲池大小
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.
当表数据缓存在InnoDB缓冲池时，可以通过查询重复访问，而不需要任何磁盘I/O。使用innodb_buffer_pool_size选项指定缓冲池的大小。
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.
这个内存区域非常重要，因此通常建议将innodb_buffer_pool_size配置为系统内存的50%到75%。
For more information see, Section 8.12.4.1, “How MySQL Uses Memory”.
Adjust the flush method
调整flush方法
In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) 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.
在一些版本的GNU/Linux和Unix中，用Unix的fsync()调用(InnoDB默认使用)和类似的方法将文件刷新到磁盘的速度非常慢。如果数据库写性能有问题，可以将innodb_flush_method参数设置为O_DSYNC进行基准测试。
Use a noop or deadline I/O scheduler with native AIO on Linux
在Linux上对本机AIO使用noop或deadline I/O调度器
InnoDB uses the asynchronous I/O subsystem (native AIO) on Linux to perform read-ahead and write requests for data file pages.
InnoDB使用Linux上的异步I/O子系统(native AIO)对数据文件页执行预读和写请求。
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.
该行为由innodb_use_native_aio配置选项控制，该选项默认是启用的。对于本机AIO, I/O调度器的类型对I/O性能有更大的影响。
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.
一般建议使用noop和deadline I/O调度器。执行基准测试以确定哪个I/O调度器为您的工作负载和环境提供了最佳结果。
For more information, see Section 14.8.7, “Using Asynchronous I/O on Linux”.
Use direct I/O on Solaris 10 for x86_64 architecture
对于x86_64架构，在Solaris 10上使用直接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.
在Solaris 10 for x86_64架构(AMD Opteron)上使用InnoDB存储引擎时，InnoDB相关文件使用直接I/O，避免InnoDB性能下降。
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).
要对整个用于存储innodb相关文件的UFS文件系统使用直接I/O，可以使用forcedirectio选项挂载它;看到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 10/x86_64上的默认不使用此选项。)如果要只对InnoDB文件操作应用直接I/O，而不对整个文件系统应用直接I/O，可以设置innodb_flush_method = O_DIRECT。通过这个设置，InnoDB会调用directio()而不是fcntl()，用于I/O到数据文件(而不是I/O到日志文件)
Use raw storage for data and log files with Solaris 2.6 or later
在Solaris 2.6或更高版本中，对数据和日志文件使用原始存储
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.
当使用InnoDB存储引擎,通过innodb_buffer_pool_size价值任何Solaris 2.6的发布,和任何平台(sparc / x86 / x64 / amd64),进行基准InnoDB数据文件和日志文件原始设备或在一个单独的直接I / O UFS文件系统,使用forcedirectio挂载选项如前所述。(如果您想要对日志文件进行直接I/O，则需要使用mount选项，而不是设置innodb_flush_method。)Veritas文件系统VxFS的用户应该使用convosync=direct mount选项。
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.
不要将其他MySQL数据文件，例如MyISAM表的数据文件，放在直接I/O文件系统上。可执行文件或库不能放在直接I/O文件系统上。
Use additional storage devices
使用额外的存储设备
Additional storage devices could be used to set up a RAID configuration.
：可以使用额外的存储设备来设置RAID配置。
For related information, see Section 8.12.2, “Optimizing Disk I/O”.
Alternatively, InnoDB tablespace data files and log files can be placed on different physical disks. For more information, refer to the following sections:
另外，InnoDB表空间数据文件和日志文件也可以放在不同的物理磁盘上。欲了解更多信息，请参阅以下章节:
Consider non-rotational storage
考虑non-rotational存储
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.
Non-rotational存储通常为随机I/O操作提供更好的性能;以及用于顺序I/O操作的旋转存储。当在旋转和非旋转存储设备上分布数据和日志文件时，请考虑主要在每个文件上执行的I/O操作类型。
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 (due to doublewrite buffering and change buffering) and log files such as binary log files and redo log files.
面向I/ o的随机文件通常包括每个表文件和普通表空间数据文件、undo表空间文件和临时表空间文件。顺序的I/ o文件包括InnoDB系统表空间文件(由于doublewrite缓冲和change缓冲)和日志文件，如二进制日志文件和重做日志文件。
Review settings for the following configuration options when using non-rotational storage:
当使用非旋转存储时，检查以下配置选项的设置:
-
The
crc32option uses a faster checksum algorithm and is recommended for fast storage systems. -
Optimizes I/O for rotational storage devices. Disable it for non-rotational storage or a mix of rotational and non-rotational storage.
-
The default setting of 200 is generally sufficient for a lower-end non-rotational storage device. For higher-end, bus-attached devices, consider a higher setting such as 1000.
-
The default value of 2000 is intended for workloads that use non-rotational storage. For a high-end, bus-attached non-rotational storage device, consider a higher setting such as 2500.
-
If redo logs are on non-rotational storage, consider disabling this option to reduce logging. See Disable logging of compressed pages.
-
If redo logs are on non-rotational storage, configure this option to maximize caching and write combining.
-
Consider using a page size that matches the internal sector size of the disk. Early-generation SSD devices often have a 4KB sector size. Some newer devices have a 16KB sector size. The default
InnoDBpage size is 16KB. Keeping the page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk. -
If binary logs are on non-rotational storage and all tables have primary keys, consider setting this option to
minimalto reduce logging.
Ensure that TRIM support is enabled for your operating system. It is typically enabled by default.
确保为您的操作系统启用了TRIM支持。它通常在默认情况下是启用的。
Increase I/O capacity to avoid backlogs
增加I/O容量以避免积压
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.
如果由于InnoDB检查点操作导致吞吐量周期性下降，可以考虑增加innodb_io_capacity配置选项的值。较高的值会导致更频繁的刷新，从而避免可能导致吞吐量下降的工作积压。
Lower I/O capacity if flushing does not fall behind
如果不延迟刷新，则降低I/O容量
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:
如果系统没有落后于InnoDB刷新操作，可以考虑降低innodb_io_capacity配置选项的值。通常情况下，您将这个选项值保持为实用值，但不要太低，否则会导致前面提到的周期性吞吐量下降。在一个典型的场景中，你可以降低选项的值，你可能会在SHOW ENGINE INNODB STATUS的输出中看到这样的组合:
-
History list length low, below a few thousand.
-
历史名单长度较低，低于几千
-
Insert buffer merges close to rows inserted.
-
插入缓冲区合并接近插入的行。
-
Modified pages in buffer pool consistently well below innodb_max_dirty_pages_pct of the buffer pool. (Measure at a time when the server is not doing bulk inserts; it is normal during bulk inserts for the modified pages percentage to rise significantly.)
-
缓冲池中的修改页始终低于缓冲池的innodb_max_dirty_pages_pct。(在服务器不做批量插入时进行度量;在批量插入修改页面时，百分比会显著上升，这是正常的。)
-
Log sequence number - Last checkpointis at less than 7/8 or ideally less than 6/8 of the total size of theInnoDBlog files. -
日志序号-最后一个检查点小于InnoDB日志文件总大小的7/8或理想情况下小于6/8
Store system tablespace files on Fusion-io devices
在Fusion-io设备上存放系统表空间文件
You can take advantage of a doublewrite buffer-related I/O optimization by storing system tablespace files (“ibdata files”) on Fusion-io devices that support atomic writes. In this case, doublewrite buffering (innodb_doublewrite) 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.
您可以通过在支持原子写的Fusion-io设备上存储系统表空间文件(“ibdata文件”)来利用doublewrite缓冲区相关的I/O优化。在这种情况下，doublewrite缓冲(innodb_doublewrite)被自动禁用，所有数据文件都使用Fusion-io原子写。该特性仅在Fusion-io硬件上支持，且仅在Linux下的Fusion-io NVMFS中启用。为了充分利用这个特性，推荐O_DIRECT的innodb_flush_method设置。
Note
Because the doublewrite buffer setting is global, doublewrite buffering is also disabled for data files residing on non-Fusion-io hardware.
因为doublewrite缓冲区设置是全局的，所以对于非fusion -io硬件上的数据文件，doublewrite缓冲区也被禁用。
Disable logging of 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.
当使用InnoDB表压缩特性时，当对压缩数据进行更改时，重新压缩页的映像被写入重做日志。此行为由innodb_log_compressed_pages控制，默认情况下，innodb_log_compressed_pages是启用的，以防止在恢复过程中使用不同版本的zlib压缩算法时可能发生的破坏。如果您确定zlib版本不会发生变化，可以禁用innodb_log_compressed_pages，以减少修改压缩数据的工作负载的重做日志生成。
76
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
