Re: Checkpoint throttling issues

On 2015-10-22 09:52:25 -0400, Robert Haas wrote:
> On Mon, Oct 19, 2015 at 6:10 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > 1) The progress passed to CheckpointWriteDelay() will often be wrong -
> > it's calculated as num_written / num_to_write, but num_written is only
> > incremented if the buffer hasn't since independently been written
> > out. That's bad because it mean's we'll think we're further and
> > further behind if there's independent writeout activity.
> >
> > Simple enough to fix, we gotta split num_written into num_written
> > (for stats purposes) and num_processed (for progress).
> >
> > This is pretty much a bug, but I'm a slightly worried about
> > backpatching a fix because it can have a rather noticeable
> > behavioural impact.
>
> I think this is an algorithmic improvement, not a bug fix.

progress, passed to CheckpointWriteDelay, is defined as:
* 'progress' is an estimate of how much of the work has been done, as a
* fraction between 0.0 meaning none, and 1.0 meaning all done.

passing a potentially wildly inaccurate value (like close to 0, because
most buffers have since been displaced) seems more like a bug than an
algorithmic improvement... I can't see how that could have been
intentional.

But I'm now wild on backpatching these, so it's probably a moot
distinction.

> > I think the sleep time should be computed adaptively based on the
> > number of buffers remaining and the remaining time. There's probably
> > better formulations, but that seems like an easy enough improvement
> > and considerably better than now.
>
> One thing to keep in mind here is that somebody did work a few years
> ago to reduce the number of wake-ups per second that PostgreSQL
> generates when idle.

As far as I can see checkpointing hasn't been touched in the course of
that work. For power usage it's probably not bad to complete checkpoints
faster than necessary and then sleep for longer :/. So it might be good
to have an upper limit to the sleep time.

> I like the idea of an adaptive sleep time.

Good. I do too ;)

Greetings,

Andres Freund