InnoDB Disk I/O Performance

You have developed an optimal database design (logical & physical), indexing methods and SQL(s), but still your database performance is slow due to disk I/O, The following below are few direct methods of tuning MySQL disk I/ for performance:

• Confirm it’s not CPU –  Check CPU usage, if the workload is more than 90% then bottleneck is also CPU
• Innodb_buffer_pool_size – If table data is cached in the InnoDB buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O.  You can specify the size of the buffer pool with system variable innodb_buffer_pool_size. The typically recommended value for innodb_buffer_pool_size is 70 to 80 percent of available system memory but need not to be larger than the database size.
• Optimal flush method – Default InnoDB flush method is fsync() ; In some versions of GNU/Linux and Unix, This makes write performance unacceptably slow. We encourage you to benchmark the write performance by setting system variable innodb_flush_method to O_DSYNC .
• Tuning the I/O capacity for performance – Increase the innodb_io_capacity only if you find throughput is affected, Higher values causes more frequent flushing, avoiding the backlog of work that can cause dips in throughput. You can consider reducing innodb_io_capacity by monitoring InnoDB throughput from the following matrices of SHOW ENGINE INNODB STATUS command :
• • 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.
• Invest in non-rotational storage – Random I/O operations perform optimally in Non-rotational storage. Random I/O oriented files include general data files (both tables and indexes) , undo tablespace files and temporary tablespace files.
• • Review settings for the following configuration options when using non-rotational storage:
    • Innodb_checksum_algorithm – The crc32 option uses a faster checksum algorithm and is recommended for fast storage systems.
    • Innodb_flush_neighbors – This option optimizes I/O for rotational storage devices. Disable it for non-rotational storage or a mix of rotational and 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_io_capacity_max – 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.
    • Innodb_log_compressed_pages – If redo logs are on non-rotational storage, consider disabling this option to reduce logging.
    • Innodb_log_file_size – If redo logs are on non-rotational storage, configure this option to maximize caching and write combining.
    • Innodb_page_size – Consider using a page size that matches the internal sector size of the disk. Early-generation SSD devices often have a 4k sector size. 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.
    • Binlog_row_image – If binary logs are on non-rotational storage and all tables have primary keys, consider setting this option to minimal to reduce logging.
• Store system tablespace files on Fusion-io devices – To get the advantage of doublewrite buffer-related I/O optimization, you can store 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.
• Disable logging of compressed pages – If you are using InnoDB table compression feature, images of re-compressed pages are written to the redo log when changes are made to compressed data. This behaviour is controlled by innodb_log_controlled pages, which is enabled by default to prevent corruption that can occur if a different version of zlib compression algorithm is used during recovery. Once confirmed that zlib version will not change, you can disable innodb_log_compressed pages to reduce redo log generation for workloads that modify compressed data.