Re: Overhauling GUCS

On Wed, 4 Jun 2008, Andrew Dunstan wrote:

> Tom Lane wrote:
>> * Can we build a "configuration wizard" to tell newbies what settings
>> they need to tweak?
>
> That would trump all the other suggestions conclusively. Anyone good at 
> expert systems?

Sigh.  I guess we need to start over again.

Last year around this time, there was one of the recurring retreads of 
this topic named "PostgreSQL Configuration Tool for Dummies": 
http://archives.postgresql.org/pgsql-performance/2007-06/msg00386.php

Josh Berkus pointed out that he already had the "expert system" part of 
this problem solved pretty well with a spreadsheet:

http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc (that's in 
the OpenOffice Calc format if you don't know the extension)

That particular spreadsheet has more useful tuning suggestions in this 
area than 99.9% of PostgreSQL users have or will ever know.  You can 
nitpick the exact recommendations, but the actual logic and thinking 
involved is pretty well solved.  It could use a touch of tweaking and 
modernization but it's not too far off from being as good as you're likely 
to get at making guesses without asking the user too many questions. 
There is one ugly technical issue, that you can't increase shared_buffers 
usefully in many situations because of SHMMAX restrictions, and that issue 
will haunt any attempt to be completely automatic.

Where Josh got hung up, where I got hung up, where Lance Campbell stopped 
at with his Dummies tool, and what some unknown number of other people 
have been twarted by, is that taking that knowledge and turning it into a 
tool useful to users is surprisingly difficult.  The reason for that is 
the current postgresql.conf file and how it maps internally to GUC 
information isn't particularly well suited to automated generation, 
analysis, or updates.  I think Josh got lost somewhere in the parsing the 
file stage.  The parts I personally got stuck on were distinguishing 
user-added comments from ones the system put in, plus being completely 
dissatisfied with how lossy the internal GUC process was (I would like a 
lot more information out of pg_settings than are currently there). 
Lance's helper tool was hobbled by the limitations of being a simple web 
application.

That's the background to Josh's proposal.  It has about an 80% overlap 
with what I was working on suggesting, which is why I jumped on his 
bandwagon so fast.  The outline at 
http://wiki.postgresql.org/wiki/GUCS_Overhaul includes the superset of our 
respective thinking on the first step here toward straightening out this 
mess, further expanded with observations made in this thread.

I would respectively point out that comments about the actual tuning 
itself have no bearing whatsoever on this proposal.  This is trying to 
nail down all the features needed to support both doing an initial 
generation and subsequent incremental improvements to the postgresql.conf 
file, while also reducing some redundancy in the code itself.  Reducing 
the scope to only handling initial generation would make this a smaller 
task.  But it happens to fall out that the work required to cut down on 
the redundancy and that required to better support incremental updates as 
well happen to be almost the same.  Josh's stated agenda is to get this 
right in one swoop, with only one version worth of disruption to the 
format, and that goal is served better IMHO as well by addressing all 
these changes as one batch.

I will attempt to resist further outbursts about non-productive comments 
here, and each time I am tempted instead work on prototyping the necessary 
code I think this really needs instead.

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