Re: pg_start_backup() takes too long

On Mon, 2008-09-29 at 13:39 +0400, Ivan Zolotukhin wrote:

> This is all not about checkpoints. As I've mentioned in the first
> message, even right after manual run of CHECKPOINT command in psql
> pg_start_backup() takes same time (~10 minutes).

As explained, there's not very much going on apart from the checkpoint
and that can be problematic.

What version are you running?
What are your checkpoint_timeout and checkpoint_completion_target
settings?

My guesses are 8.3, 20 minutes, and default.

pg_start_backup() doesn't do an immediate checkpoint, it does a smooth
one, so doing a CHECKPOINT beforehand should make no difference in 8.3.

(Looks at code...)

I'm surprised that checkpoint smoothing moves slowly even when it has so
little to do. ISTM checkpoint completion target should set its write
rate according to the thought that if shared_buffers were all dirty it
would write them out in checkpoint_timeout *
checkpoint_completion_target seconds. However, what it does is write
them *all* out in that time, no matter how many dirty blocks there are.
If there is just a few blocks to write, we take the *same* time to write
them as if it were all dirty. Which looks fairly ludicrous to me, but
the only time that is possible in current code is pg_start_backup()
since no other code requests a checkpoint exactly when you ask, but
slowly. It makes more sense to have a constant write rate during
checkpoint, or at very least a minimum rate during checkpoint.

IMHO the checkpoint smoothing code is wrong, but since it only shows
itself for pg_start_backup() I think people will say we should change
that instead. If we do, then we'll get people saying "how come
pg_start_backup() causes such a performance drop?" because we start
doing an immediate checkpoint. The idea of this is that "online backup"
should have as little effect as possible on normal running. So I suggest
we change the checkpoint code instead.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support