From: | Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Matthew Wakeling <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: PostgreSQL 8.4 performance tuning questions |
Date: | 2009-07-30 18:14:19 |
Message-ID: | 4A71E2FB.3070500@kaltenbrunner.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Since the dump to custom format ran longer than the full pg_dump
>> piped directly to psql would have taken, the overall time to use this
>> technique is clearly longer for our databases on our hardware.
>
> Hmmm ... AFAIR there isn't a good reason for dump to custom format to
> take longer than plain text dump, except for applying compression.
> Maybe -Z0 would be worth testing? Or is the problem that you have to
> write the data to a disk file rather than just piping it?
I always dump with -Z0(and compress afterwards or even in a pipe to get
two cores busy) because otherwise custom dump times are simply ridiculous.
However Kevin is on something here - on the typical 4-8 core box I
tested I managed to an around cores/2 speedup for the restore which
means that for a pure upgrade or testing similiar to what kevin is doing
custom dumps + parallel restore might result in no win or even a loss.
On on of our datasets I did some benchmarking a while ago (for those who
attended bruce pg_migrator talk @pgcon these are same numbers):
* 150GB Database (on-disk - ~100GB as a plain text dump)
time to dump(-C0): 120min
time to restore(single threaded): 180min
time to restore(-j 16): 59min
however the problem is that this does not actually mean that parallel
restore shaves you ~120min in dump/restore time because you get the
following real runtimes:
plain text dump + single threaded restore in a pipe: 188min
custom dump to file + parallel restore: 179min
this is without compression, with the default custom dump + parallel
restore is way slower than the simple approach on reasonable hardware.
Stefan
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2009-07-30 18:14:30 | Re: PostgreSQL 8.4 performance tuning questions |
Previous Message | Kevin Grittner | 2009-07-30 17:35:13 | Re: PostgreSQL 8.4 performance tuning questions |