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

Re: Feature Request --- was: PostgreSQL Performance Tuning

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning
Date: 2007-04-30 11:48:24
Message-ID: Pine.GSO.4.64.0704292241270.1296@westnet.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Guillaume CottenceauDate: 2007-04-30 13:05:06
Subject: Re: Query performance problems with partitioned tables
Previous:From: Andreas HaumerDate: 2007-04-30 11:43:52
Subject: Query performance problems with partitioned tables

pgsql-general by date

Next:From: Jim NasbyDate: 2007-04-30 12:42:44
Subject: Re: Disadvantages on having too many page slots?
Previous:From: Oleg BartunovDate: 2007-04-30 10:55:56
Subject: Re: Stemming not working with tsearch2() function

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