Date: Mon, 30 Apr 2007 07:48:24 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning
In-Reply-To: <200704271059.50265.josh@agliodbs.com>
Message-ID: <Pine.GSO.4.64.0704292241270.1296@westnet.com>
References: 
 <OFC96167D5.6BB63AC2-ON652572C9.003B5263-652572C9.003E4142@lntinfotech.com>
	<20070427143023.GA2422@mathom.us> <46323744.5020703@drivefaster.net>
	<200704271059.50265.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 27 Apr 2007, Josh Berkus wrote:

> *Everyone* wants this.  The problem is that it's very hard code to write
> given the number of variables

There's lots of variables, and there are at least three major ways to work 
on improving someone's system:

1) Collect up data about their system (memory, disk layout), find out a 
bit about their apps/workload, and generate a config file based on that.

2) Connect to the database and look around.  Study the tables and some 
their stats, make some estimates based on what your find, produce a new 
config file.

3) Monitor the database while it's doing its thing.  See which parts go 
well and which go badly by viewing database statistics like pg_statio. 
From that, figure out where the bottlenecks are likely to be and push more 
resources toward them.  What I've been working on lately is exposing more 
readouts of performance-related database internals to make this more 
practical.

When first exposed to this problem, most people assume that (1) is good 
enough--ask some questions, look at the machine, and magically a 
reasonable starting configuration can be produced.  It's already been 
pointed out that anyone with enough knowledge to do all that can probably 
spit out a reasonable guess for the config file without help.  If you're 
going to the trouble of building a tool for offering configuration advice, 
it can be widly more effective if you look inside the database after it's 
got data in it, and preferably after it's been running under load for a 
while, and make your recommendations based on all that information.

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