Submitted by jay on December 10, 2009 - 12:35pm

Doing some performance testing on some modern hardware comparing Innodb plugin 1.0.4 with stock Innodb. I'm running a sysbench transactions test (reads and writes) with 200M rows in my table (table size is around 46G, RAM is 16G, buffer pool is set to 12G).

I was puzzled to see the innodb plugin to be decent, but not really as great as I expected, I was doing about ~6100 RW operations a second (individual statements within transactions). Then I compared it to the stock innodb and shockingly I got ~7K ops. I thought about what I tuned that was different in the plugin and came up with the innodb_io_capacity.

Somewhat arbitrarily, I did the following sysbench tests to try to determine a good value to put in innodb_io_capacity.

sysbench --test=fileio prepare
sysbench --test=fileio --num-threads=16 --file-extra-flags=direct --file-test-mode=rndrw run


This gave me a number in the 4k-5k iops total... this is a 12-drive (15k SAS) RAID 1+0 setup. Sounds good, so I stuck 4000 in innodb_io_capacity.

What I noticed about my tests was that the innodb plugin was excessively vigorous about flushing the buffer pool. I rarely say the buffer pool beyond a few percent dirty.


When I realized that maybe innodb thinking it was ok to do all that I/O was my bottleneck, I bumped innodb_io_capacity from 4k down to 1k, and reran my tests: I jumped from ~6k to ~12.2k ops.


So what's the lesson? Well, I *think* some more thought might need to go into tuning innodb_io_capacity. Unless my sysbench test was inappropriate for measuring iops on my filesystem, it doesn't seem wise to give innodb_io_capacity *all* of your io, just a healthy portion.


Trackback URL for this post:

Jay,   What were the other



What were the other plugin settings, particularly innodb_adaptive_flushing. What was the stock version?.



jay's picture

 I have

 I have innodb_adaptive_flushing = true. I'm running mysql 5.1.39, community build.

Jay, you didn't specify your


you didn't specify your I/O subsystem. I assume it's a RAID array, but how many disks ?

As a rough rule of thumb innodb_io_capacity = (nr of disks * 100)


jay's picture

 Re-read the article more

 Re-read the article more carefully, the RAID config is in there

Ah sorry. Value of 1000 makes

Ah sorry. Value of 1000 makes more sense then :)


Comment abuse is not tolerated on this site, besides all the comments are moderated, so don't bother posting comments that are not on topic, only for increasing the SEO of your site, or are outright spam.  If you've got something intelligent to contribute, by all means, post a link to your blog.  

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
High Availability
Global Load Balancing
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile