From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: Volunteer to build a configuration tool
Date: Wed, 20 Jun 2007 02:45:27 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0706200224430.16657@westnet.com> (raw)
In-Reply-To: <7621.1182318230@sss.pgh.pa.us>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E53@SAB-DC1.sab.uiuc.edu>
<1182298719.4482.133.camel@ipso.snappymail.ca>
<Pine.GSO.4.64.0706200040001.7594@westnet.com>
<7621.1182318230@sss.pgh.pa.us>
On Wed, 20 Jun 2007, Tom Lane wrote:
> I think what would be much more useful in the long run is some serious
> study of the parameters themselves. For instance, random_page_cost is a
> self-admitted oversimplification of reality.
If I could figure out who would sponsor such a study that's what I'd be
doing right now. I have studies on many of the commit-related parameters
I'll have ready in another few days, those are straightforward to map out.
But you know what I have never found? A good benchmark that demonstrates
how well complicated queries perform to run studies on things like
random_page_cost against. Many of the tuning knobs on the query optimizer
seem very opaque to me so far, and I'm not sure how to put together a
proper test to illuminate their operation and map out their useful range.
Here's an example of one of the simplest questions in this area to
demonstate things I wonder about. Let's say I have a properly indexed
database of some moderate size such that you're in big trouble if you do a
sequential scan. How can I tell if effective_cache_size is in the right
ballpark so it will do what I want to effectively navigate that? People
back into a setting for that parameter right now based on memory in their
system, but I never see anybody going "since your main table is X GB
large, and its index is Y GB, you really need enough memory to set
effective_cache_size to Z GB if you want queries/joins on that table to
perform well".
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com
Subject: Re: Volunteer to build a configuration tool
In-Reply-To: <Pine.GSO.4.64.0706200224430.16657@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox