This patch is conjugation of Yasufumi's IO patches (control_io-threads.patch, control_flush_and_merge_and_read.patch, adaptive_flush.patch).
The patch adds next configuration parameters:
- innodb_read_io_threads (default 1) - the number of background IO threads for read requests.
- innodb_write_io_threads (default 1) - the number of background IO 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 of read-ahead feature. (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 that server can perform 100 i/o per second, which is not always true. The patch adjusts InnoDB behavior to consume more IO 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: fix_broken_group_commit)
The patch adds next information into SHOW INNODB STATUS to confirm the checkpointing activity:
- max checkpoint age
- current age of oldest page modification which have not been flushed to disk yet.
- 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.
