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.
(FWIW - I am still building this box, it is Not in production yet, so no real users/DB activity )
My postgresql.conf has these options for (hopefully) pg_dump and pg_restore improvements:
=================
work_mem = 1GB # dump/restore Perf Value
#maintenance_work_mem = 2048MB # min 1MB
maintenance_work_mem = 1GB # dump/restore Perf Value
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
#maintenance_work_mem = 2048MB # min 1MB
maintenance_work_mem = 1GB # dump/restore Perf Value
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
...
fsync = off # dump/restore Perf Valuecheckpoint_segments = 60 # dump/restore Perf Value
checkpoint_warning = 60s # dump/restore Perf Value
......
checkpoint_warning = 60s # dump/restore Perf Value
......
autovacuum = off # dump/restore Perf Value..
=============================
Any thoughts on what else I might try to improve things ?
Thanks for your time,
Susan
shared_buffers = 4096MB
Susan K. McClure
smcclure@xxxxxxxx
713.348.4852
