Description:
At http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_io_capacity is the claim that innodb_io_capacity is "The maximum number of I/O operations per second that InnoDB will perform". It isn't a maximum number of I/O operations per second for InnoDB:
1. It regulates only the background thread activities such as flushing modified pages from the buffer pool and performing insert buffer merges. It does not affect the foreground threads that perform most I/O for select and other operations.
2. The amount of work done by the background tasks is variable and set to base values in each place where I/O is done. The innodb_io_capacity is used by multiplying those values by innodb_io_capacity / 100. The amount of work is not measured in I/O operations per second at any point, InnoDB just uses base values that assumed that a server has one disk drive that could for both foreground and background operations combined handle 100 I/O operations per second.
The optimal value varies but in general it should be set just high enough so that the background threads do not get a long way behind in their work, but low enough so that they don't cause so much insert buffer merging and page flushing that the caching those parts of the server do becomes useless. If you set it too high you can in effect make the insert buffer and dirty page caching useless and greatly increase the amount of avoidable I/O that your server does.
The innodb.com version of the description at http://www.innodb.com/doc/innodb_plugin-1.0/innodb-performance.html#innodb-performance-thr... is better than our current one:
"The master thread in InnoDB is a thread that performs various tasks in the background. Most of these tasks are I/O related like flushing of the dirty pages from the buffer cache or writing the buffered inserts to the appropriate secondary indexes. The master thread attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the free I/O bandwidth available and tune its activities to take advantage of this free capacity. Historically, InnoDB has used a hard coded value of 100 IOPs (input/output operations per second) as the total I/O capacity of the server.
Beginning with InnoDB Plugin 1.0.4, a new configuration parameter is introduced to indicate the overall I/O capacity available to InnoDB. The new parameter innodb_io_capacity should be set to approximately the number of I/O operations that the system can perform per second. The value will of course depend on your system configuration. When innodb_io_capacity is set, the master threads estimates the I/O bandwidth available for background tasks based on the set value. Setting the value to 100 reverts to the old behavior.
You can set the value of innodb_io_capacity to any number 100 or greater, and the default value is 200. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege."
I've deliberately avoided saying set it to the maximum number of I/O operations the server can perform because that is often far too high on systems that have very capable disk systems. Setting it to the maximum I/O rate they can handle destroys the usefulness of some of the caches and greatly increases avoidable I/O. Setting it just high enough to avoid getting behind is a better way of picking an appropriate value.
How to repeat:
Not applicable.
Suggested fix:
Update the documentation after any required discussion.
