调整刷新脏页处理innodb_io_capacity

   在5.1.X版本中，最多只会刷新100个脏页到磁盘、合并20个插入缓冲，即使磁盘有能力处理更多的请求，只能会处理这么多，这样在更新量较大的时候，脏页刷新就可能跟不上，导致性能下降。

   但在5.5.X版本里，innodb_io_capacity参数可以动态调整刷新脏页的数量，这在一定程度上解决了这一问题。

   innodb_io_capacity默认是200，单位是页，该参数的设置大小取决于硬盘的IOPS，即每秒每秒的输入输出量(或读写次数)。

  可以动态调整参数:set global innodb_io_capacity=2000;

  磁盘配置与innodb_io_capacity参数值

innodb_io_capacity磁盘配置200单盘SAS/SATA2000SAS*12  RAID  105000SSD50000FUSION-IO

13.7.7.11. Controlling the Master Thread I/O Rate

The master thread in InnoDB is a thread that performs various tasks in the background. Most of these tasks are I/O related, such as flushing dirty pages from the buffer cache or writing changes from the insert buffer 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 storage engine 1.0.4, a new configuration parameter indicates the overall I/O capacity available to InnoDB. The new parameterinnodb_io_capacity should be set to approximately the number of I/O operations that the system can perform per second. The value depends on your system configuration. Wheninnodb_io_capacity is set, the master threads estimates the I/O bandwidth available for background tasks based on the set value. Setting the value to100 reverts to the old behavior.

You can set the value of innodb_io_capacity to any number 100 or greater, and the default value is200. You can set the value of this parameter in the MySQL option file (my.cnf ormy.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.