| From: | "Susan K(dot) McClure" <smcclure(at)rice(dot)edu> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org |
| Subject: | pg_dump and pg_restore with multiple streams does Not seem to improve overall times |
| Date: | 2015-05-01 15:16:21 |
| Message-ID: | em5e30e63a-661c-44ad-8996-601c945efc8b@oyster-creek |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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
...
fsync = off # dump/restore Perf
Valuecheckpoint_segments = 60 # 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(at)rice(dot)edu
713.348.4852
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Ribe | 2015-05-01 15:22:11 | Re: pg_dump and pg_restore with multiple streams does Not seem to improve overall times |
| Previous Message | Stephen Frost | 2015-05-01 15:11:29 | Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0 |