Re: Overhauling GUCS

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD