Re: Reducing pg_dump & pg_restore times

Hi!

Thanks so much for the responses. The system configuration is something like
this-

Linux, PG 8.1, ext3, RAID 1 setup with two controllers

The machine has 4 processors with 32 Gig of RAM.

Thanks!
Shivkumar

On 10/27/06, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> wrote:
>
> Ron Johnson wrote:
> > On 10/26/06 01:20, Chris wrote:
> >>> Coder At Heart wrote:
> >>>> Hi!
> >>>>
> >>>> Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc,
> 32
> >>>> G RAM machine for a 60GB database.
> >>> Please always cc the mailing list.
> >>>
> >>> I've never played with databases that large so I don't know if that's
> >>> good or bad.. others on the list will be able to offer
> advice/suggestions.
> >
> > RAM certainly helps, but pg_dump is single-threaded, and, by
> > definition, backing up and restoring 60GB of data is *extremely* IO
> > constrained.
>
> well from what I have seen is that on large databases and a sufficiently
> fast disk-io subsystem actually CPU(or rather the speed of a single
> core) starts to be the bottleneck with current postgresql versions.
> That is true for both COPY and to a much greater effect index creation
> (I have seen restores that took days and most of that was purely index
> creation).
> 8.2 has improved considerably on that due to the massive improvments in
> the external sorting code but it will still be bottleneck by the
> single-process nature of psql.
>
> >
> > So,
> > - What OS?
> > - What version of PG?
> > - what kind of disk system does the DB live on?
> > - How many controllers?
> > - What kind of tape drive?
> > - Is it on it's own SCSI controller?
>
> in addition to that basic information we need there are:
>
> *) increasing maintainance_work_mem and work_mem massively
> *) increasing the number of checkpoint segments
> *) disabling fsync during the load
>
>
> Stefan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>