Re: pg_restore taking 4 hours!

Josh Berkus <josh@agliodbs.com> writes:
> Not as much, but it's still a good idea to serialize the load.   With too few
> segments, you get a pattern like:

> Fill up segments
> Write to database
> Recycle segments
> Fill up segments
> Write to database
> Recycle segments
> etc.

Actually I think the problem is specifically that you get checkpoints
too often if either checkpoint_timeout or checkpoint_segments is too
small.  A checkpoint is expensive both directly (the I/O it causes)
and indirectly (because the first update of a particular data page
after a checkpoint causes the whole page to be logged in WAL).  So
keeping them spread well apart is a Good Thing, as long as you
understand that a wider checkpoint spacing implies a longer time to
recover if you do suffer a crash.

I think 8.0's bgwriter will considerably reduce the direct cost of
a checkpoint (since not so many pages will be dirty when the checkpoint
happens) but it won't do a thing for the indirect cost.

            regards, tom lane