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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
