Re: PostgreSQL 8.4 performance tuning questions

[Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>]

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