Re: PostgreSQL 8.4 performance tuning questions

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