Patch: Innodb-IO

Patch description here

The number of background I/O threads for read prefetch requests.

The number of background I/O threads for writing dirty pages from the buffer pool.

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 of read-ahead feature.

“read-ahead for random access” is removed from Plugin 1.0.5. So also XtraDB ignores it after 1.0.5.

The number of disk I/O operations InnoDB assumes the server can perform per second. InnoDB assumes by default that the server can perform 100 I/O per second, which is not always true. The patch adjusts InnoDB's behavior to consume more I/O resources when it's told they are available.

Control the added feature adaptive checkpointing(*). 'none':disable adaptive checkpointing, 'reflex':enable adaptive checkpointing (the first method), 'estimate':enable adaptive checkpointing (the new method (experimental))

• 'reflex': This behavior is similar to 'innodb_max_dirty_pages_pct' flushing. The difference is this method starts to flush blocks constantly and contiguously based on the oldest modified age (it is not flushed yet). If the age exceeds 1/2 of the max age capacity, starts the weak contiguous flushing. If exceeds 3/4, starts the strong flushing. The strength can be adjusted by the variable innodb_io_capacity. In other words, we must tune innodb_io_capacity for the 'reflex' method working correctly.

• 'estimate': If the oldest modified age exceeds 1/2 of the max age capacity, starts to flush blocks every seconds. The number of the blocks is determined by [number of modified blocks], [LSN progress speed] and [average age of all modified blocks]. So, this behavior is independent from the variable innodb_io_capacity. In other words, we don't need to tune innodb_io_capacity.

attention: innodb_adaptive_checkpoint needs larger transaction log files, in some cases. (innodb_adaptive_checkpoint makes the limit of modified age lower) So, about twice length of transaction log files may be safe.

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.

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

This parameter is startup parameter. If the value is set lower than the half of innodb_buffer_pool_size, it is used as maximum size of insert buffer. Using the too small value (e.g. 0) is not recommended for performance reasons. 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.)

attention: Someone may miss to take the meaning of “very fast storage” above. It means much faster than HDD based storage (any RAID storages). It is like the battery-backed huge dynamic memory array etc… So, it is not recommended to set the value for performance, if the storage doesn't have ram-level speed/response, the performance will drop down.

This is parameter for additional tuning the amount of insert buffer processing by background thread. Sometimes, justinnodb_io_capacity is insufficient to tune the insert buffer.

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.

attention: This option is not needed after XtraDB-1.0.5, because the problem is solved already.

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: fix_broken_group_commit)

0 (default): disable the feature. max_checkpoint_age_async can be controlled by this value. It is not needed to shrink innodb_log_file_size to tune recovery time.

Added next 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
...