I have a MySQL server running with DELAY_KEY_WRITE=1 enabled on a table that represents each day (all MYISAM). Data gets inserted into each day and some updates occur on today and previous days. Delayed key writes is a must as the server does a lot of heavy writing. These tables are several GB each.
As we are running with delayed key writes any updates to these tables may get delayed and a server crash would take a long time to recover the tables. I don’t mind the current day being corrupted (in the event of a crash) but I would like to ensure that the other days updates are written. I have tried an ALTER TABLE … DELAY_KEY_WRITE=0 on the older tables but this also takes a long time and is not really viable.
I could periodically flush the tables to disk, however this flushes the dirty and clean blocks and blows away the key buffer. It also causes a delay on the server while the flushing takes place.
Is there a way I can stop the server delaying key writes to these older tables?
Is there any way that I can flush the dirty blocks from the key_buffer (ideally on a table basis) without flushing the clean ones and periodically blowing away the key_buffer cache?
If we don’t flush at all, is there a way I can tell if there are any pending delayed writes for these tables?
I have also noticed that if I do not flush the tables, then when I shut the server down there is a large delay (sometimes minutes) while it seems to be flushing the delayed blocks to disk. The size of the delayed blocks appears to be close to the size of my key_buffer.
If anyone has any thoughts, they would be greatly appreciated.