Home > Admin > Don’t play with innodb_io_capacity! (with standard HDDs)

Don’t play 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…
(Of course 30000 was a bit snooty)

[MAJ 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

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment

You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric

Categories: Admin
  1. January 7th, 2013 at 16:15 | #1

    It’s been said that a value of 1000 is appropriate for high end SATA drives; care to test?

    • January 7th, 2013 at 17:37 | #2

      Hum, the best thing to do is to test for your configuration.
      This variables is dynamic, so, it’s quite easy.

  2. January 7th, 2013 at 16:30 | #3

    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.” :-)

    • January 7th, 2013 at 17:44 | #4

      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.

  3. January 7th, 2013 at 20:56 | #5

    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

    • January 7th, 2013 at 22:58 | #6

      “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)

  4. January 8th, 2013 at 10:25 | #7

    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.

    • January 8th, 2013 at 10:56 | #8

      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.

  5. January 8th, 2013 at 10:59 | #9

    Ok then I think we are on the same page :)

  6. William
    January 8th, 2013 at 17:55 | #10

    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.

    • January 8th, 2013 at 18:16 | #11

      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

  7. William
    January 8th, 2013 at 19:00 | #12

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

  8. martin
    January 30th, 2013 at 09:08 | #13

    which graphing do you use? you have nice graph for io_caqpacity.

  1. No trackbacks yet.