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

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