The danger of setting this value too high is that there will be no memory left for the operating system and some MySQL-subsystems that rely on filesystem cache such as binary logs, and InnoDB’s transaction logs.

innodb_log_file_size – defaults to 48M. High write throughput systems may want to increase this to allow background checkpoint activities to smooth out over longer periods of time, leading to improved performance. Values up to 4G are quite safe. Historically, the downside of operating with a large log file was increased crash recovery times, but this has vastly improved in 5.5 and 5.6.

If you do not have a hardware RAID controller, or with some SANs O_DIRECT may lead to worse performance. The manual and Bug #54306 clarify this in more detail.

innodb_flush_method – defaults to fdatasync. if you are using a hardware RAID controller, you may want to set this to O_DIRECT . This prevents the “double buffering” effect of when reading InnoDB buffer pool pages there is both a copy in InnoDB and in operating system’s filesystem cache.

innodb_flush_neighbors – defaults to 1. This setting should be set to 0 (disabled) on SSDs which do not have any performance gains with sequential IO. In some hardware configurations it may also be beneficial to disable with RAID, because logically sequential blocks are not guaranteed to be physically sequential.

innodb_io_capacity and innodb_io_capacity_max – these settings influence how much background work InnoDB will do per second. In a previous post I described how most write IO (other than writing InnoDB’s log) is backgrounded. If you have knowledge of your hardware (and how many operations per second it can perform) it makes sense to consume this capacity rather than let it remain idle.

The analogy I like to use here is like a flight taking off with empty seats – sometimes it is better to fill them with people who were destined for later flights just in case there is bad weather ahead. i.e. it is better to get rid of all background work at the earliest convenience to reduce liabilities.

Some simple math: 200 IOPS per disk that can write, so a RAID10 controller with 10 disks = (10/2) * 200 = 1000. I say “simple math”, because a RAID controller will often will be able to provide additional merging and effectively increase the number of IOPS you will be capable of. In the case of SSDs, IOPS count will likely be in the thousands.

The risk of setting this too high is that you do not want background work to be so overwhelming it competes with foreground work for disk capacity. Historically there was some risk of setting this too high that InnoDB would hold an internal lock leading to decreased performance (my understanding is that this is greatly improved in 5.6).