Re:Disaster: MySQL 5.5 Flushing
by jbellis on Sunday September 18, 2011 @11:17PM
(#3012080)
As much as I like pg, I don't think there's anything magical it can do to fix this when the database is given a write-heavy workload such that the [sequential] commitlog appends outstrip the ability to do the [random] b-tree updates. Which is a pretty easy situation to set up on modern hardware, really.
When faced with this situation it seems to me you have three choices at a high level:
- throttle writes closely to your random i/o limit, eliminating your ability to accomodate brief write spikes
- throttle writes less aggressively, accommodating brief spikes but forcing stalls under sustained load as seen here
- don't throttle at all and postpone catching up until commitlog replay on restart, if necessary
When faced with this situation it seems to me you have three choices at a high level:
- throttle writes closely to your random i/o limit, eliminating your ability to accomodate brief write spikes
- throttle writes less aggressively, accommodating brief spikes but forcing stalls under sustained load as seen here
- don't throttle at all and postpone catching up until commitlog replay on restart, if necessary
None is without downside.