MySQL Forums
Forum List  »  Performance

delay_key_write with midpoint insertion strategy (key_cache_division_limit)
Posted by: Eric Jensen
Date: May 20, 2008 10:37AM

The simplicity of MyISAM with delay_key_write is wonderful, but in high-write applications the Key_blocks_not_flushed can become such a large percentage (> 90%) of the Key_blocks_used that every cache miss requires not only a disk read to pull the needed block into memory, but also a write to flush the dirty blocks being evicted to disk. This severely degrades read performance.

What solutions are people using to deal with this problem? If I flush my tables, this problem obviously goes away for a while and read performance improves, but I can't afford a flush downtime in an online server.

I'm thinking of trying a very low key_cache_division_limit so that I basically turn my key caches into LFU strategy, but I'm not clear on how this interacts with delay_key_write. If I set key_cache_division_limit < 50 and increased key_cache_age_threshold, for example, I could guarantee that more of my "cache" is actually used for caching and not just filling up with dirty blocks, right? I would still have the problem of cache misses causing writes from the warm subchain, but at least I would increase my hit ratio...

Anyone have knowledge / experience with this? Thanks,
eric

Options: ReplyQuote


Subject
Views
Written By
Posted
delay_key_write with midpoint insertion strategy (key_cache_division_limit)
5124
May 20, 2008 10:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.