MySQL Bugs: #48341: innodb_io_capacity set to high by default in InnoDB plugin

Bug #48341	innodb_io_capacity set to high by default in InnoDB plugin
Submitted:	27 Oct 2009 7:45	Modified:	29 Oct 2009 0:38
Reporter:	Oli Sennhauser	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	1.0.4	OS:	Any
Assigned to:	Inaam Rana	CPU Architecture:	Any
Tags:	DEFAULT, innodb, innodb_io_capacity, plugin, value

Description:
According to the FB people the default value of innodb_io_capacity (200) is set to high. This causes twice the amount of I/O compared to 5.0 (and possibly 5.1 as well).

http://www.facebook.com/note.php?note_id=165914135932

How to repeat:
Setting up two replicas of the same master, one running 5.0.84 and one running 5.1.38+1.0.4 showed the 5.1 server writing about 2x as much to disk, and having a little trouble keeping up the master. Mark helped identify the insert buffer cache as the likely culprit. SHOW INNODB STATUS showed 5.1 only had 10 pages in the insert buffer and had a 1:1 insert to merge ratio, while 5.0 had over 16k pages and was getting a 4:1 reduction in merges. Merging 4x as many pages into the secondary indexes was definitely the problem.

In 5.0 the number of merges performed per background IO loop was hardcoded to 5% of 100 IOPS. 5.1 has made this 5% of a variable number of IOPS, configured with the innodb_io_capacity variable. The default value is 200, allowing the insert buffer to drain twice as fast. Changing innodb_io_capacity to 100 to match the 5.0 behavior allowed the insert buffer to grow and reduce the number of merges to be on parity with 5.0. This brought the write load down to similar levels as 5.0 and things seem to be humming along fine.

Suggested fix:
Set it by default back to 100.

Thank you to FB folks for noticing this.

innodb_io_capacity controls not only the ibuf batch size but also the flushing rate of dirty pages. If the above situation is encountered then the workaround is to set the value of innodb_io_capacity to a lower value. A default value of 200 is already pretty conservative (it means a server can perform 200 IOPs). We'll make a note in the plugin documentation describing correlation between innodb_io_capacity and ibuf batch size.

At this point we don't think changing the default for innodb_io_capacity is needed particularly because that will also affect the flushing rate of dirty pages.

Who is to say "a default value of 200 is already pretty conservative"?

Is this documented somewhere, or do you have any basic benchmarks to prove this?

From the research I've done, 200 seems a bit high for most standard disk drives.

Just see the IOPS benchmarks here:

http://en.wikipedia.org/wiki/IOPS#Examples
http://storagearchitect.blogspot.com/2008/09/how-many-iops.html

Chris, please see documentation bug #52393 and the request in bug #55818 for the ability to set innodb_io_capacity below 100. Also see bug #59214.

It's impossible to set one value that's always right but we can document what influences the need for the setting and how it might usefully be adjusted to strike the best balance.

I generally want to start as low as possible (currently 100) and only increase when there's a demonstrated need to do so.

It's worth checking the version and using the most recent version possible within each major release. More specifically, in 5.5 there were some tweaks done to various things in the release or two after GA that reduced some flushing work, see the release notes for more.

It's also worth having a look at 5.6 and the recent 5.5 release notes to see some of the adjustments that help to make the server better at adjusting its own settings. Things like increasing the change buffer merge rate when it becomes too full, so it is less likely to get completely full and lose its ability to greatly speed up inserts.

All of these things are the subject of lots of discussion and investigation, specifically including insert buffer merge rate, flushing and purging.

The server defaults are a compromise. The changes in 5.5 and 5.6 are making that compromise better and it might be possible to reduce innodb_io_capacity in 5.6 defaults if we can work through everything needed to keep it lower and have the server automatically increase what it does when necessary. SSDs are one of the challenges there, since they have high io capacities but are available in very inexpensive systems.