Re: pg_restore taking 4 hours! - Mailing list pgsql-performance

From Tom Lane
Subject Re: pg_restore taking 4 hours!
Date
Msg-id 27280.1102965664@sss.pgh.pa.us
Whole thread Raw
In response to Re: pg_restore taking 4 hours!  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: pg_restore taking 4 hours!
Next
From: Alvaro Nunes Melo
Date:
Subject: Re: Similar tables, different indexes performance