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: | Raw Message | Whole Thread | 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 |