Re: pg_restore --multi-thread

On Fri, Feb 20, 2009 at 09:22:58AM -0800, Joshua D. Drake wrote:
> On Fri, 2009-02-20 at 09:33 -0500, Andrew Dunstan wrote:
>
> > The short answer is that we don't know yet. There is anecdotal evidence
> > that the number of CPUs on the server is a good place to start, but we
> > should be honest enough to say that this is a new feature and we are
> > still gathering information about its performance. If you want to give
> > some advice, then I think the best advice is to try a variety of
> > settings to see what works best for you, and if you have a good set of
> > figures report it back to us.
>
> There has been some fairly heavy testing and research that caused the
> patch in the first place. The thread is here:
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg00695.php
>
> It is a long thread. The end was result was the fastest restore time for
> 220G was performed with 24 threads with an 8 core box. It came in at 3.5
> hours.
>
> http://archives.postgresql.org/pgsql-hackers/2008-02/msg01092.php
>
> It is important to point out that this was a machine with 50 spindles.
> Which is where your bottleneck is going to be immediately after solving
> the CPU bound nature of the problem.
>
> So although the CPU question is easily answered, the IO is not. IO is
> extremely variable in its performance.
>
> Sincerely,
>
> Joshua D. Drake
>
I also ran some tests against a more modest system that was still
showing a performance improvement at (number-of-cores * 2):

http://archives.postgresql.org/pgsql-hackers/2008-11/msg01399.php

I think that a good starting point for any use should be the number
of cores given these two data points.

Cheers,
Ken