Be careful with innodb_io_capacity! (with standard HDDs)

A beautiful picture is sometimes better than words :

With standard HDDs (here in RAID 10), the innodb_io_capacity variable may have non expected effects.

You can see on this picture the result of a test on one of my server with only the replication thread activated. The value of the innodb_io_capacity variable was the single modification during the period.

So, don’t touch this parameter without a strong reason…

[EDIT1 2013-01-08]: I know 30k is a too high value for innodb_io_capacity, I used this value to make the graphe really eloquent.
Read this excellent post from Chris for more details : http://www.chriscalender.com/?p=201

[EDIT2 2014-09-12]: Tuning this variable is of course needful in some cases. Do your own tests




Share the love!

Subscribe to RSS feed or by email to automatically receive real-time notifications
Oracle, MySQL, and InnoDB are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners

13 Comments

  1. Yes, the innodb_io_capacity should be tuned to the number of IOPS your disk array can sustain. Or actually, the number of IOPS you want MySQL to consume (because you might not want that to be 100% of disk capacity).

    Conventional disks generally can’t do 30000 IOPS. If you tune innodb_io_capacity to 30000, the InnoDB write threads will throw more data at the disks every second that they can possible handle, and you’ll get I/O queueing.

    This is why when we change config variables we call it “tuning” instead of “increasing.” :-)

    Reply
    • Totally agree with you, I used 30000 for this example because it was really eloquent.

      And the documentation is a bit vague again :
      “Although you can specify a very high number, in practice such large values cease to have any benefit; for example, a value of one million would be considered very high.”

      In reality, a high value may cause a very dangerous behavior for performance…

      That’s why I would like to share about that.

      Reply
  2. Optimal tuning for this is normally as high as required for the background work (change buffer merging, flushing and purging) to keep up and no higher. Substantially higher than required just increases background thread i/o rates, particularly for flushing, without benefit. It’s easy for optimal to be say 100, the old default, instead of 200, the current default, or higher. Just depends on the workload and how much data changing it’s doing.

    Views are my own, for an official Oracle view, consult a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle

    Reply
    • “Substantially higher than required just increases background thread i/o rates, particularly for flushing, without benefit.”
      I’m not agree with that in my case (I can’t say it’s always true), substantially higher value cause an undesirable behavior (not only no benefit)

      Reply
  3. Hi Cedric,

    30K might be too high for traditional disks. You might not even want to go that high even if you have SSDs. Considering the fact that you are running with 4 15K disks in RAID 10, a good value in that case would be 500. Note that there are cases where you want to tune innodb_io_capacity. For example InnoDB flushing depends on the value of io_capacity, as it checks innodb_io_capacity to see how much of the pages should be flushed. If you are running stock MySQL server, then tuning this variable is important if you want to prevent stalls caused by flushing storms. So while I do not agree that tuning this variable in general is bad, setting any value too high would be bad anyhow :) However, if you tune it properly, it can provide you with some benefits.

    Reply
    • I know 30K is too high (read my last comments) and I’ve never said that tuning this variable in general is bad.
      I’m exactly on the same logic as you, tune properly is the best way for any variables.

      Reply
  4. Cédric, the title of this post is “Don’t play with innodb_io_capacity! (with standard HDDs)”. Call me crazy, but that seems like you are saying that one shouldn’t tune this variable at all if using rotating disks. Its a poor, alarmist, attention getting headline.

    Reply
    • William, If you read only the title maybe you can understand that!
      But, “don’t play” doesn’t mean that you shouldn’t tune this variable.
      On the contrary, it means that we shouldn’t do as we wish with this variable, that’s why I used the word “play”
      Hope you understand my approach

      Reply
  5. Well, if that’s the definition of play you are using, then I’d recommend that no one play with any MySql config variables.

    Reply

Leave a Comment.