Optimizing InnoDB Disk I/O

忘是亡心i 2022-08-27 13:58 87阅读 0赞

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html

### Optimizing `InnoDB` Disk I/O ###

If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. 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.

*  When table data is cached in the `InnoDB` buffer pool, it can be processed over and over by queries without requiring any disk I/O. Specify the size of the buffer pool with the[`innodb_buffer_pool_size`][innodb_buffer_pool_size] option. This memory area is important enough that busy databases often specify a size approximately 80% of the amount of physical memory. For more information, see[Section 8.9.1, “The `InnoDB` Buffer Pool”][Section_8.9.1_ _The _InnoDB_ Buffer Pool].
 *  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`][innodb_flush_method] parameter set to `O_DSYNC`.
 *  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`][innodb_flush_method]. With this setting,`InnoDB` calls `directio()` instead of`fcntl()` for I/O to data files (not for I/O to log files).
 *  When using the `InnoDB` storage engine with a large[`innodb_buffer_pool_size`][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 earlier. (It is necessary to use the mount option rather than setting[`innodb_flush_method`][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.
    
    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.
 *  If you have additional storage devices available to set up a RAID configuration or symbolic links to different disks,[Section 8.11.3, “Optimizing Disk I/O”][Section_8.11.3_ _Optimizing Disk I_O] for additional low-level I/O tips.

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文与MySQL数据库 “性能优化”主题有关。

[innodb_buffer_pool_size]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
[Section_8.9.1_ _The _InnoDB_ Buffer Pool]: http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
[innodb_flush_method]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method
[Section_8.11.3_ _Optimizing Disk I_O]: http://dev.mysql.com/doc/refman/5.5/en/disk-issues.html