Re: Spread checkpoint sync

On Thu, Feb 10, 2011 at 10:30 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> 3) The existing write spreading code in the background writer needs to be
> overhauled, too, before spreading the syncs around is going to give the
> benefits I was hoping for.

I've been thinking about this problem a bit. It strikes me that the
whole notion of a background writer delay is probably wrong-headed.
Instead of having fixed-length cycles, we might want to make the delay
dependent on whether we're actually keeping up. So during each cycle,
we decide how many buffers we want to clean, and we write 'em. Then
we go to sleep. When we wake up again, we figure out whether we kept
up. If the number of buffers we wrote during the prior cycle was more
than the required number, then we'll sleep longer the next time, up to
some maximum; if we we didn't write enough, we'll reduce the sleep.

Along with this, we'd want to change the minimum rate of writing
checkpoint buffers from 1 per cycle to 1 for every 200 ms, or
something like that.

We could even possibly have a system where backends wake the
background writer up early if they notice that it's not keeping up,
although it's not exactly clear what a good algorithm would be.
Another thing that would be really nice is if backends could somehow
let the background writer know when they're using a
BufferAccessStrategy, and somehow convince the background writer to
write those buffers out to the OS at top speed.

> I want to make this problem go away, but as you can see spreading the sync
> calls around isn't enough.  I think the main write loop needs to get spread
> out more, too, so that the background writer is trying to work at a more
> reasonable pace.  I am pleased I've been able to reproduce this painful
> behavior at home using test data, because that much improves my odds of
> being able to isolate its cause and test solutions.  But it's a tricky
> problem, and I'm certainly not going to fix it in the next week.

Thanks for working on this. I hope we get a better handle on it for 9.2.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company