Date: Fri, 6 Jun 2008 15:22:58 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: Peter Eisentraut <peter_e@gmx.net>
cc: pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>
Subject: Re: Overhauling GUCS
In-Reply-To: <200806061435.01196.peter_e@gmx.net>
Message-ID: <Pine.GSO.4.64.0806061500450.7804@westnet.com>
References: <48409D1E.3070208@agliodbs.com>
	<Pine.GSO.4.64.0806041045520.2258@westnet.com>
	<29525.1212613052@sss.pgh.pa.us> <200806061435.01196.peter_e@gmx.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 6 Jun 2008, Peter Eisentraut wrote:

> - What settings do "newbies" (or anyone else) typically need to change?
> Please post a list.
> - What values would you set those settings to?  Please provide a description
> for arriving at a value, which can later be transformed into code.  Note that
> in some cases, not even the documentation provides more than handwaving help.

Josh's spreadsheet at 
http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc provides 
five different models for setting the most critical parameters based on 
different types of workloads.  Everyone can quibble over the fine tuning, 
but having a good starter set of reasonable settings for these parameters 
is a solved problem.  It's just painful to build a tool to apply the 
available expert knowledge that is already around.

> - If we know better values, why don't we set them by default?

Because there's not enough information available; the large differences 
between how you tune for different workloads is one example.  Another is 
that people tune for peak and projected activity rather than just what's 
happening right now.  Every model suggested for a tuning wizard recognizes 
you need to ask some set of questions to nail things down.  I continue to 
repeat in broken-record style, exactly what a tuning tool will ask about 
and what settings it will suggest is not important, and getting into that 
is an entirely different discussion (one that gets hashed out every single 
day on pgsql-performance).  The fact that writing such a tool is harder 
than it should be is the issue here.

> Another orthogonal stumbling block on the way to making all of this 
> automatic is that the surely criticial shared_buffers setting will in 
> any useful configuration require messing around with kernel settings 
> that no PostgreSQL tool can really help with.

Yes.  So?  All you can do is point this out to users.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD