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

[Keith <keith@xxxxxxxxxxx>]

On Fri, May 1, 2015 at 11:49 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Fri, May 1, 2015 at 9:16 AM, Susan K. McClure <smcclure@xxxxxxxx> 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