MySQL Bugs: #55818: innodb_io_capacity can't be set below 100

Bug #55818	innodb_io_capacity can't be set below 100
Submitted:	7 Aug 2010 6:23
Reporter:	James Day	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.49	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If you try to set innodb_io_capacity to a value lower than 100 you receive a warning like this:

[Warning] option 'innodb-io-capacity': unsigned value 50 adjusted to 100

When tuning production systems it's a little more common for me to want to set innodb_io_capacity below 100 than it is to want to set it to higher values than 200. I want to do both, just lower is more often of interest than higher.

The reason to go lower is to increase the scope for write combining in modified pages in the buffer pool, after verifying that:

1. There is no purge lag issue.
2. The insert buffer/change buffer merging is not lagging.
3. The modified page percentage for the buffer pool is not at innodb_max_dirty_pages_pct and either not likely to get there, or is harmless for the workload if it does.
4. The server is under I/O stress and likely to benefit from the potential for reduced I/O from higher write combining levels.
5. There is potential that the innodb_io_capacity is causing more modified page flushing from the buffer pool than necessary for the workload.

This combination is increasingly common on servers with large buffer pools, notably for replication slaves, where there is little prospect of the single replication thread being able to cause the background threads to have any trouble keeping up with the workload. In such cases the master is often more powerful and has the advantage of being able to use multiple threads to make changes, so it's more often a challenge to help the slave keep up than it is to increase it to help the background threads.

On the master or standalone servers, the opposite may be true, particularly with servers that have disk systems that can support very high I/O rates, such as disk arrays or arrays of SSDs.

How to repeat:
Add innodb_io_capacity = 50 to my.cnf and start mysqld. Look in the error log file for the warning.

Suggested fix:
Change the minimum permitted value of innodb_io_capacity from 100 to 20. Then document how to diagnose whether it's possibly too low or if there's a chance that reducing it may help performance.

James,

I am not convinced that by lowering innodb_io_capacity < 100 we stand to gain much. For example, if we are dealing with a replication slave where it periodic dips due to sharp checkpointing do not matter and if we set the following:

* disable adaptive_flushing
* make dirty page %age very high (I think max allowed is 95%)
* make log file size maximum (equal to 4G) to reduce number of checkpoints
* and we have innodb_io_capacity = 100

then master thread behavior will most likely be:

* do nothing for 10 seconds (unless dirty page ratio is hit)
* after every ten seconds if the server has performed less than 200 IO ops in past 10 seconds then flush 100 pages
* 200 IO ops are calculated as number of IOs to logfile + page reads + page writes
* if dirty page ratio > 70 then flush 100 pages else flush 10 pages

In short if a server is doing more than 200 IO ops per 10 seconds and the dirty ratio is less than 70% than innodb will flush 10 pages every 10 seconds i.e.: 1 IOPs. Otherwise it will flush 100 pages per 10 seconds i.e.: 10 IOPs.

I doubt going further down then that can help us.