Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL 8.4 performance tuning questions

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 (view raw or flat)
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

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-07-30 18:14:30
Subject: Re: PostgreSQL 8.4 performance tuning questions
Previous:From: Kevin GrittnerDate: 2009-07-30 17:35:13
Subject: Re: PostgreSQL 8.4 performance tuning questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group