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

Re: dbt-2 tuning results with postgresql-8.3.5

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Mark Wong <markwkm(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Selena Deckelmann <selenamarie(at)gmail(dot)com>, Gabrielle Roth <gorthx(at)gmail(dot)com>
Subject: Re: dbt-2 tuning results with postgresql-8.3.5
Date: 2008-12-22 08:59:42
Message-ID: Pine.GSO.4.64.0812220335320.11013@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, 20 Dec 2008, Mark Wong wrote:

> Here are links to how the throughput changes when increasing 
> shared_buffers: http://pugs.postgresql.org/node/505 My first glance 
> takes tells me that the system performance is quite erratic when 
> increasing the shared_buffers.

If you smooth that curve out a bit, you have to throw out the 22528MB 
figure as meaningless--particularly since it's way too close to the cliff 
where performance dives hard.  The sweet spot looks to me like 11264MB to 
17408MB.  I'd say 14336MB is the best performing setting that's in the 
middle of a stable area.

> And another series of tests to show how throughput changes when 
> checkpoint_segments are increased: http://pugs.postgresql.org/node/503 
> I'm also not what to gather from increasing the checkpoint_segments.

What was shared_buffers set to here?  Those two settings are not 
completely independent, for example at a tiny buffer size it's not as 
obvious there's a win in spreading the checkpoints out more.  It's 
actually a 3-D graph, with shared_buffers and checkpoint_segments as two 
axes and the throughput as the Z value.

Since that's quite time consuming to map out in its entirety, the way I'd 
suggest navigating the territory more efficiently is to ignore the 
defaults altogether.  Start with a configuration that someone familiar 
with tuning the database would pick for this hardware:  8192MB for 
shared_buffers and 100 checkpoint segments would be a reasonable base 
point.  Run the same tests you did here, but with the value you're not 
changing set to those much larger values rather than the database 
defaults, and then I think you'd end with something more interesting. 
Also, I think the checkpoint_segments values >500 are a bit much, given 
what level of recovery time would come with a crash at that setting. 
Smaller steps from a smaller range would be better there I think.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Laszlo NagyDate: 2008-12-22 10:11:30
Subject: Re: rebellious pg stats collector (reopened case)
Previous:From: Mark WongDate: 2008-12-22 08:50:20
Subject: Re: dbt-2 tuning results with postgresql-8.3.5

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