Re: pg_dump and pg_restore with multiple streams does Not seem to improve overall times

On Fri, May 1, 2015 at 11:49 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
wrote:

> On Fri, May 1, 2015 at 9:16 AM, Susan K. McClure <smcclure(at)rice(dot)edu>
> wrote:
>
>> Running postgresql 9-4 on REHL 7 system. I am trying to speed up
>> pg_dump and pg_restore by
>> using a postgresql.conf with various performance options set, and the
>> --jobs option to force multiple
>> streams. But various tests, with various "--jobs=" numbers only achieve
>> at most a 1 minute improvement
>> in elapsed time versus doing pg_dump or pg_restore with no "--jobs"
>> option and no postgresql.conf with performance
>> options. Am I missing some key option(s) to improve things??
>>
>> The DB in question is ~25GB. The processor has 24 Cpus, 12 cores
>>
>> I have tried with "--jobs = 8, 12, and 20" with little or no discernible
>> improvements.
>>
>
> So have you tried 2 jobs first? I'd see how 1, 2, 3, 4 etc work. See if 2
> is faster than 1, then 3 faster than 2 etc.
>
> Most of the time, unless you've got a really fast IO subsystem increasing
> the --jobs doesn't make a big difference as a lot of the work is
> sequential. Also on restores I think the extra jobs part only kicks in for
> index builds.
>
>
Also depends how many tables you have and how big they are. Each job
process can only do one table at a time, so if all your data is
concentrated in 2-3 tables and the rest are fairly small, you're not going
to see much of an improvement on dumps or restores.

Keith