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
InnoDBbuffer 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_sizeoption. 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 188.8.131.52, “The InnoDB Buffer Pool”.
In some versions of GNU/Linux and Unix, flushing files to disk with the Unix
InnoDBuses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the
innodb_flush_methodparameter set to
When using the
InnoDBstorage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O for
InnoDB-related files, to avoid degradation of
InnoDBperformance. To use direct I/O for an entire UFS file system used for storing
InnoDB-related files, mount it with the
mount_ufs(1M). (The default on Solaris 10/x86_64 is not to use this option.) To apply direct I/O only to
InnoDBfile operations rather than the whole file system, set
innodb_flush_method = O_DIRECT. With this setting,
fcntl()for I/O to data files (not for I/O to log files).
When using the
InnoDBstorage engine with a large
innodb_buffer_pool_sizevalue on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks with
InnoDBdata files and log files on raw devices or on a separate direct I/O UFS file system, using the
forcedirectiomount option as described earlier. (It is necessary to use the mount option rather than setting
innodb_flush_methodif you want direct I/O for the log files.) Users of the Veritas file system VxFS should use the
Do not place other MySQL data files, such as those for
MyISAMtables, 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 9.12.3, “Optimizing Disk I/O” for additional low-level I/O tips.
If throughput drops periodically because of
InnoDBcheckpoint operations, consider increasing the value of the
innodb_io_capacityconfiguration option. Higher values cause more frequent flushing, avoiding the backlog of work that can cause dips in throughput.
If the system is not falling behind with
InnoDBflushing operations, consider lowering the value of the
innodb_io_capacityconfiguration 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:
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_pctof 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.)
Log sequence number - Last checkpointis at less than 7/8 or ideally less than 6/8 of the total size of the
As of MySQL 5.7.4, 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
Because the doublewrite buffer setting is global, doublewrite buffering is also disabled for data files residing on non-Fusion-io hardware.
InnoDBconfiguration options to consider when tuning I/O-bound workloads include