Re: 7 hrs for a pg_restore?

On Wed, 20 Feb 2008, Pavan Deolasee wrote:

> Date: Wed, 20 Feb 2008 14:31:09 +0530
> From: Pavan Deolasee <pavan.deolasee@gmail.com>
> To: Jeff Davis <pgsql@j-davis.com>
> Cc: Douglas J Hunley <doug@hunley.homeip.net>,
>      pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] 7 hrs for a pg_restore?
>
> On Feb 19, 2008 11:53 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> >
> >
> > Keep in mind, if you have several GB worth of indexes, they take up
> > basically no space in the logical dump (just the "CREATE INDEX" command,
> > and that's it). But they can take a lot of processor time to build up
> > again, especially with localized text.
> >
> >
>
> I think it would be interesting if we can build these indexes in parallel.
> Each index build requires a seq scan on the table. If the table does
> not fit in shared buffers, each index build would most likely result
> in lots of IO.
>
> One option would be to add this facility to the backend so that multiple
> indexes can be built with a single seq scan of the table. In theory, it
> should be possible, but might be tricky given the way index build works
> (it calls respective ambuild method to build the index which internally
> does the seq scan).
>
> Other option is to make pg_restore multi-threaded/processed. The
> synchronized_scans facility would then synchronize the multiple heap
> scans. ISTM that if we can make pg_restore mult-processed, then
> we can possibly add more parallelism to the restore process.
>
> My two cents.
>
> Thanks,
> Pavan
>
>
That'd be great! Maybe an option to pg_restore to spawn AT MOST n
processes (1 per CPU)
my .02 Euro
--
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)