Re: Overhauling GUCS - Mailing list pgsql-hackers

On Sun, 1 Jun 2008, Peter Eisentraut wrote:

> Josh Berkus wrote:
>> 1. Most people have no idea how to set these.
> Could you clarify this?  I can't really believe that people are incapable of
> editing a configuration file.

The big problem isn't the editing, it's knowing what to set the 
configuration values to.

This is not to say that editing a configuration file should be considered 
reasonable.  Any GUCS overhaul should include a design goal of being able 
to completely manage the configuration system using, say, pgadmin (the 
"manage settings via port access" part that Josh already mentioned). 
This is why I was suggesting additions aimed at assimilating all the 
things that are in the postgresql.conf file.

Joshua has been banging a drum for a while now that all this data needs to 
get pushing into the database itself.  The GUCS data is clearly structured 
like a database table.  Josh's suggested changes are basically adding all 
the columns needed to it in order to handle everything you'd want to do to 
the table.  If you think of it in those terms and make it possible to 
manipulate that data using the tools already available for updating 
tables, you'll open up the potential to add a whole new class of 
user-friendly applications for making configuration easier to manage.

However, I don't fully agree with taking that idea as far as Joshua has 
suggested (only having the config data in the database), because having 
everything in a simple text file that can be managed with SCM etc. has 
significant value.  It's nice to allow admins to be able to make simple 
changes with just a file edit.  It's nice that you can look at all the 
parameters in one place and browse them.  However, I do think that the 
internal database representation must be capable of holding everything in 
the original postgresql.conf file and producing an updated version of the 
file, either locally or remotely, as needed.

>> 4. We don't seem to be getting any closer to autotuning.
> True.  But how does your proposal address this?

The idea that Josh's suggestions are working toward is simplying the 
construction of tools that operate on the server configuration file, so 
that it's easier to write an autotuning tool.  Right now, writing such a 
tool in a generic way gets so bogged down just in parsing/manipulating the 
postgresql.conf file that it's hard to focus on actually doing the tuning 
part.  If we go back to his original suggestion:
http://wiki.postgresql.org/wiki/GUCS_Overhaul
>> Add a script called pg_generate_conf to generate a postgresql.conf 
>> based on guc.c and command-line switches (rather than 
>> postgresql.conf.sample)

It's an easy jump from there to imagine a pg_generate_conf that provide a 
"wizard" interface to update a configuration file.  I forsee a little GUI 
or web app that connects to a server on port 5432, finds out some basic 
information about the server, and gives something like this:

Parameter        Current    Recommended    Change?
shared_buffers        32MB    1024MB        [X]
effective_cache_size    128MB    3000MB        [ ]
work_mem        1MB    16MB        [ ]

Josh has the actual brains behind such an app all planned out if you look 
at his presentations, but without the larger overhaul it's just not 
possible to make the implementation elegant.

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