Postgres Professional
   

Re: pg_restore taking 4 hours!

From: Tom Lane
Subject: Re: pg_restore taking 4 hours!
Date: ,
Msg-id: 27280.1102965664@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: pg_restore taking 4 hours!  (Josh Berkus)
List: pgsql-performance

Tree view

pg_restore taking 4 hours!  (Rodrigo Carvalhaes, )
 Re: pg_restore taking 4 hours!  (Shridhar Daithankar, )
  Re: [GENERAL] pg_restore taking 4 hours!  (Tom Lane, )
  Re: [GENERAL] pg_restore taking 4 hours!  ("Riccardo G. Facchini", )
  Re: pg_restore taking 4 hours!  (Rodrigo Carvalhaes, )
   Re: pg_restore taking 4 hours!  (Hannu Krosing, )
 Re: pg_restore taking 4 hours!  (Josh Berkus, )
 Re: pg_restore taking 4 hours!  (Andrew McMillan, )
 Re: pg_restore taking 4 hours!  (Vivek Khera, )
  Re: pg_restore taking 4 hours!  (Josh Berkus, )
 Re: pg_restore taking 4 hours!  (Josh Berkus, )
  Re: pg_restore taking 4 hours!  (Tom Lane, )
 Re: pg_restore taking 4 hours!  ("Iain", ) 
Josh Berkus <> 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: Tom Lane
Date:
Subject: Re: Using LIMIT changes index used by planner
Next:
From: Hasnul Fadhly bin Hasan
Date:
Subject: Trying to create multi db query in one large queries