Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Optimizing InnoDB Disk I/O

8.5.7 Optimizing InnoDB Disk I/O

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.

  • 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. 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, “How MySQL Uses Memory”.

  • Adjust the flush method

    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.

  • Use a noop or deadline I/O scheduler with native AIO on Linux

    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 14.9.7, “Using Asynchronous I/O on Linux”.

  • Use direct I/O on Solaris 10 for x86_64 architecture

    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).

  • Use raw storage for data and log files with Solaris 2.6 or later

    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 earlier. (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.

    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

    Additional storage devices could be used to set up a RAID configuration. 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:

  • 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.

    Random I/O-oriented files are typically file-per-table data 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.

    Review settings for the following configuration options when using non-rotational storage:

    • innodb_io_capacity

      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.

    • innodb_log_file_size

      If redo logs are on non-rotational storage, configure this option to maximize caching and write combining.

    Early-generation SSD devices often have a 4k sector size, but some newer devices have a 16k sector size. The default InnoDB page size is 16k. Keeping the page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

    Ensure that TRIM support is enabled for your operating system. It is typically enabled by default.

  • Increase I/O capacity to avoid backlogs

    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.

  • Lower I/O capacity if flushing does not fall behind

    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:

    • 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.)

    • Log sequence number - Last checkpoint is at less than 7/8 or ideally less than 6/8 of the total size of the InnoDB log files.

User Comments
  Posted by Scott Simmons on September 4, 2016
and if on Solaris 11 or using Solaris 10 with ZFS keep your InnoDB pool cache very small, as you have block layer caching handled by ARC which is more efficient for performance. Something like 2-4G would be enough.
Sign Up Login You must be logged in to post a comment.