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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
