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

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

Responses

Browse pgsql-admin by date

  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