Saturday, 6 October 2007

MySQL: delay_key_write is good

If you're using MySQL with the MyISAM engine for a high-writes application, delay_key_write is usually very good.

Let me explain further: delay_key_write is a table option which causes the database NOT to flush the MyISAM key file after every write. This is a really good thing, as if you're doing another write very soon anyway, this is likely to just waste I/O time.

This doesn't sound like a good idea right, because it means that if the power fails (or mysql crashes, or something), then you'll be left with a broken index file? No, it's still a very good idea:
  • delay_key_write does NOT appear to affect the MyISAM data file - that will still be flushed according to the normal policy.
  • If you had delay_key_write off, then a power failure or crash during the index write would cause the same level of corruption.
  • Broken MyISAM index files need to be rebuilt, regardless of how little or how much "brokenness" they actually have.
How do you turn delay_key_write on?

It's a table option, so you have to do it at create time. It's possible to do ALTER TABLE, but that behaves how it normally does under MySQL - i.e. it rebuilds the entire table, just to change one flag. This is of course necessary if you're changing the schema, but delay_key_write shouldn't - sadly, it does.

This means that on a large existing table, the ALTER TABLE needs to be done with care (i.e. out-of-hours or during scheduled down time, if at all).

Another option is to set the variable delay_key_write to "ALL" - however, this is not necessarily a good idea as it applies to all tables (in the entire server) whether you want it to or not.

On infrequently written tables, delay_key_write could increase the chance of index corruption.

Our performance testing cycle for delay_key_write is now over - quite frankly we're amazed by how much performance it gives us for so little effort - it will definitely be going into production.

Our servers are being hurt with heavy write-load which is keeping (at least one thread of) mysql almost permanently in I/O-wait - delay_key_write makes almost all of this go away making inserts much quicker and leaving the server more time for other things. It's likely to become essential once our server workload grows even higher.


Riddla said...

excellent little writeup.

this has helped me out plenty.

thank you kindly!

Jerry said...

The concept of delay_key_write is good for performance. Unfortunately it seems to contribute to recurring MyISAM table corruption under high load. We have observed the problem in both 5.0.33 and 5.1.36. I haven't been able to find where this has been fixed in a newer release.

Ninja said...

does this affect the result of select from another process?

for example, the table is
(a, b, c,d);
we have a index [a,c];

if we do not flush the index, then query like
select c from table where a=1
might produce the wrong result?

Mark Robson said...

No it does not affect the other process; it won't make the server return the wrong results, as it uses the indexes still in the key buffer, even if they are dirty.