Re: Overhauling GUCS

On Mon, 18 Aug 2008, Michael Nacos wrote:

> Having done a SELECT * FROM pg_settings, all the information you need
> seems to be there...

See http://archives.postgresql.org/pgsql-hackers/2008-06/msg00209.php You
sound like you're at rung 2 on the tool author ladder I describe there,
still thinking about the fun part of tuning but not yet aware of the
annoying postgresql.conf management issues that show up in the field that
motivate many of the GUCS changes suggested. Coping with user and
system-generated comments is one difficult part that people normally don't
consider, dealing with bad settings the server won't start with is
another.

I did make one mistake in that message, which is that the "context" field
of pg_settings already exposes when a setting can be changed. And it is
possible to get the value for a setting as entered by the admin by joining
pg_settings against what current_setting returns, which is one part of
handling the import/change/export cycle while keeping useful units intact.

> Maybe I'm being over-simplistic here, but the important bit is knowing
> how you should tune stuff - and this is what I'm hoping to learn through
> this process.

The tuning references at the bottom of
http://wiki.postgresql.org/wiki/GUCS_Overhaul provide more detail here
than anyone has been able to automate so far. There's certainly room to
improve on the suggestions there with some introspection of the database,
I'm trying to stay focused on something to help newbies whose settings are
completely wrong first.

> As soon as there is a tuning strategy published, a number of tools will
> certainly follow.

Josh Berkus published one in 2005 and zero such tools have been produced
since then, even though it looked to him then (like it does to you now and
like it did to me once) that such a tool would easily follow:
http://pgfoundry.org/docman/?group_id=1000106

The bright side here is that you don't have to waste time tinkering in
this area to find out where the dead ends are like Josh and I
independantly did.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD