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