MySQL DELAY_KEY_WRITE Option
By Pete Freitag
Another performance option in MySQL is the
DELAY_KEY_WRITE option. According to the MySQL documentation the option makes index updates faster because they are not flushed to disk until the table is closed.
Note that this option applies only to MyISAM tables,
You can enable it on a table by table basis using the following SQL statement:
ALTER TABLE sometable DELAY_KEY_WRITE = 1;
This can also be set in the advanced table options in the MySQL Query Browser.
This performance option could be handy if you have to do a lot of update, because you can delay writing the indexes until tables are closed. So frequent updates to large tables, may want to check out this option.
Ok, so when does MySQL close tables?
That should have been your next question. It looks as though tables are opened when they are needed, but then added to the table cache. This cache can be flushed manually with
FLUSH TABLES; but here's how they are closed automatically according to the docs:
- When the cache is full and a thread tries to open a table that is not in the cache.
- When the cache contains more than
table_cacheentries and a thread is no longer using a table.
FLUSH TABLES;is called.
DELAY_KEY_WRITEis enabled, this means that the key buffer for tables with this option are not flushed on every index update, but only when a table is closed. This speeds up writes on keys a lot, but if you use this feature, you should add automatic checking of all MyISAM tables by starting the server with the
--myisam-recoveroption (for example,
So if you do use this option you may want to flush your table cache periodically, and make sure you startup using the myisam-recover option.
MySQL DELAY_KEY_WRITE Option was first published on August 11, 2005.
If you like reading about mysql, performance, delay_key_write, or caching then you might also like:
- The MySQL Query Cache
- Cache Template in Request Setting Explained
- 9 Ways to Save Bandwidth on your RSS Feed
- MySQL Optimization Hints