This page is a combination of Yasufumi's I/O changes (control_io-threads.patch, control_flush_and_merge_and_read.patch, adaptive_flush.patch).

It adds these configuration parameters:

• innodb_read_io_threads (default 1) - the number of background I/O threads for read requests.
• innodb_write_io_threads (default 1) - the number of background I/O threads for writing dirty pages from the buffer pool.

• innodb_read_ahead (default 'both') - control native read-ahead behavior of InnoDB. 'none':disable read-ahead, 'random':enable read-ahead for random access only, 'linear':enable read-ahead for sequential access only, 'both':enable both read-ahead features. (the setting by number [0-3] as v1.0 is also acceptable in v1.1~)

• innodb_io_capacity (default 100) - number of disk IOPs the server can do. InnoDB by default assumes the server can perform 100 I/O per second, which is not always true. This adjusts InnoDB behavior to consume more I/O resources. [number of HDD of the RAID] * 100 may be recommended as the start value of tuning.

• innodb_adaptive_checkpoint (default 0) - control the added feature adaptive checkpointing(*). 0:disable adaptive checkpointing, 1:enable adaptive checkpointing. attention: innodb_adaptive_checkpoint needs larger transaction files, in some cases. (innodb_adaptive_checkpoint makes the limit of modified age lower)

(The following parameters are implemented after v1.1)

• innodb_flush_neighbor_pages (default 1) - When the dirty page are flushed (written to datafile), this parameter determines whether the neighbor pages in the datafile are also flushed at the same time or not. If you use the storage which don't have “head seek delay” (e.g. SSD or enough Write-Buffered), 0 may show better performance. 0:disable, 1:enable

• innodb_ibuf_max_size (default [the half of innodb_buffer_pool_size](bytes)) - This parameter is startup parameter. If the lower value is set than the half of innodb_buffer_pool_size, it is used as maximum size of insert buffer. To restrict to the too small value (e.g. 0) is not recommended for performance. If you don't like the insert buffer growing bigger, you should use the following parameters instead. (* If you use very fast storage, small value (like several MB) may show better performance.)

• innodb_ibuf_accel_rate (default 100(%)) - This parameter is additional tuning the amount of insert buffer processing by background thread. Sometimes, only innodb_io_capacity is insufficient to tune the insert buffer.

• innodb_ibuf_active_contract (default 0) - By default (same to normal InnoDB), the each user threads do nothing about contracting the insert buffer until the insert buffer reaches its maximum size. 1 makes the each user threads positive to contract the insert buffer as possible in asynchronous.

• innodb_enable_unsafe_group_commit (default 0) - 1 forces group commit even if with binlog. If 1 is set, the orders of InnoDB-transaction log and binlog may not be same. In this case, if you use some hot-backup of InnoDB, the snapshot may not correspond to any binlog position. (some inconsistencies may occur.)(discussion as reference: Why group commit is broken after 5.0 with binlog?)

(The following change to an existing system variable is implemented in version 5.1.49-11.3)

• innodb_flush_log_at_trx_commit_session (default 3) - This change implements a session-level version of the existing MySQL global system variable innodb_flush_log_at_trx_commit. This allows a session to override the global setting when either more strict or less strict commit control is required by the session than the global level setting is providing. The session variable can be set to the same values (0, 1, 2) as the global variable, with the same meanings. It can also be set to an additional value (3), which causes the session to ignore innodb_flush_log_at_trx_commit_session and operate according to innodb_flush_log_at_trx_commit (the global setting) instead.

This change adds the following information into SHOW INNODB STATUS to confirm the checkpointing activity:

1. max checkpoint age
2. current age of oldest page modification which have not been flushed to disk yet.
3. current age of the last checkpoint

...
---
LOG
---
Log sequence number 0 1059494372
Log flushed up to   0 1059494372
Last checkpoint at  0 1055251010
Max checkpoint age  162361775
Modified age        4092465
Checkpoint age      4243362
0 pending log writes, 0 pending chkp writes
...

(*) adaptive checkpointing

InnoDB flushes dirty blocks of buffer pool constantly. And normally, the checkpoint is done at oldest page modification at the time passively (This is called ”fuzzy checkpointing”). When the checkpoint age grows near to the max checkpoint age (determined by total of transaction log files' length), InnoDB tries to keep the checkpoint age away from the max by ad-hoc flushing many dirty blocks. But if there are many update per second and many blocks which are almost same modification age, huge number of flushing and stallings may be caused.

adaptive checkpointing reinforces the constant flushing activity along the rate of [modified age / max checkpoint age]. It might avoid or soften the impact of the such huge flushing or stallings.