Re: Overhauling GUCS

Magnus and I had the fortune to share a plane flight when leaving pgCon.
This gave us a chance to discuss my ideas around reforming PostgreSQL
configuration at some length. Here's what we came up with (Magnus will
presumably correct the parts I got wrong):

Currently, PostgreSQL,conf and our set of GUC configurations suffer from
4 large problems:

1. Most people have no idea how to set these.
2. The current postgresql.conf file is a huge mess of 194 options, the
vast majority of which most users will never touch.
3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
and the settings.sgml), which are only synched with each other manually.
4. We don't seem to be getting any closer to autotuning.

We think all of these are solvable for 8.4. We also think that it's
vitally important than any overhaul of the GUCS to be completed in one
version to minimize the pain involved.

Here's a list of the things we want to change. It's all a package and
should make sense if you take all the changes as a whole.

1) Add several pieces of extra information to guc.c in the form of extra
"gettext" commands: default value, subcategory, long description,
recommendations, enum lists.
2) Incorporate this data into pg_settings
3) Delete postgresql.conf.sample
4) 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), which would be called automatically by initdb.

For (4), pg_generate_conf would take the following switches:
-b , --basic = short conf file, listing only the 15-18 most commonly
changed options
-a , --advanced = conf file listing all 196+ options
-t, --terse = conf file lists only category headings and actual
settings, no comments
-n, --normal = conf file has category and subcategory settings, with
short desc comments
-v, --verbose = conf file lists full descriptions and recommendations in
comments with each option
-c "option = value" set specific option to specific value in the file
-f "filename" = take options and values from file "filename"

The default would be "-b, -n" with specific settings for shared_buffers
and wal_sync_method. The current postgresql.conf is a lot more like an
"-a, -v" file.

This would help us in the following ways:

A. there would now only be 2 places to maintain GUCS lists, the docs and
guc.c.
B. by having a generated postgresql.conf and an easy way to generate it,
writing autoconfiguration scripts (as well as shortcuts like SET
PERSISTENT) become vastly easier.
C. Most users would deal only with a limited set of 15-20 configuration
variables.

There's obviously some refinements needed, but what do people think of
the above general idea?

On Fri, 30 May 2008, Josh Berkus wrote:

> 1) Add several pieces of extra information to guc.c in the form of extra
> "gettext" commands: default value, subcategory, long description,
> recommendations, enum lists.
> 2) Incorporate this data into pg_settings

When you last brought this up in February (I started on a long reply to
http://archives.postgresql.org/pgsql-hackers/2008-02/msg00759.php that I
never quite finished) the thing I got stuck on was how to deal with the
way people tend to comment in these files as they change things.

One problem I didn't really see addressed by the improvements you're
suggesting is how to handle migrating customized settings to a new version
(I'm talking about 8.4->9.0 after this is in place, 8.3->8.4 is a whole
different problem). It would be nice to preserve "history" of what people
did like in your examples (which look exactly like what I find myself
running into in the field). Now, that will get a lot easier just by
virtue of having a smaller config file, but I think that adding something
into pg_settings that allows saving user-added commentary would be a nice
step toward some useful standardization on that side of things. It would
make future automated tools aimed at parsing and generating new files, as
part of things like version upgrades, a lot easier if there was a standard
way such comments were handled in addition to the raw data itself.

The other thing I'd like to see make its way into pg_settings, so that
tools can operate on it just by querying the database, is noting what file
the setting came from so that you can track things like include file
usage. I think with those two additions (comments and source file
tracking) it would even be concievable to clone a working facsimile of
even a complicated postgresql.conf file set remotely just by reading
pg_settings.

While a bit outside of the part you're specifically aiming to improve
here, if you could slip these two additions in I think it would be a boon
to future writers of multi-server management tools as well.

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

On Fri, 2008-05-30 at 17:34 -0700, Josh Berkus wrote:

> There's obviously some refinements needed, but what do people think of
> the above general idea?

My initial reaction is that this is too much change, though I agree with
many of the points and understand it is well intended.

We have many supporters, though 90% of them seldom touch the database
from one release to the next. Many are dismayed that every time they do
we've fiddled with the knobs so some don't work anymore, some are
missing or renamed and there's a few new ones. So if they don't know
what they're doing it is frequently because we keep moving the
goalposts.

We should also consider that most people with multiple databases are
running multiple versions of PostgreSQL. The main reason for that is
that we keep changing the behaviour of SQL between releases, so even if
you had a magic superfast upgrade utility, in perhaps 50% of cases they
won't use it because they have to do a full application re-test, which
costs time and money.

Trying to be a Postgres DBA is hard when each new release is configured
differently to the last one and we have a major release about 3-5 more
frequently than Oracle/SQLServer. That is probably largest source of
questions and annoyance from the students on the courses I run.

So my viewpoint is that we should be aggressively adding new features,
yet be conservative in changing existing behaviour: provide options for
behaves-like-previous-release and keep the administrative interfaces as
similar as possible between releases.

If you wish to make changes, I would suggest that you simply reorder
some of the parameters in the .conf file, so that the ones you think are
important are grouped at the top.

Another suggestion would be to allow a #include style interface within
postgresql.conf. We can then do this:

#include standard_postgresql.conf

# now we put Josh's favourite GUCs as overrides on the above
...

That keeps things simple because the standard_postgresql.conf looks
almost identical to what people are used to. It also provides a new
feature, allowing people to include site-wide defaults for various
settings to allow easy implementation of local policy. It also
demonstrates a best practice approach to managing GUCs.

Some other problems I see with GUCs

* It's not possible to set one parameter depending upon the setting of
another.

* It's always unclear which GUCs can be changed, and when. That is much
more infrequently understood than the meaning of them.

* We should rename effective_cache_size to something that doesn't sound
like it does what shared_buffers does

* There is no config verification utility, so if you make a change and
then try to restart and it won't, you are in trouble.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> On Fri, 2008-05-30 at 17:34 -0700, Josh Berkus wrote:

> We have many supporters, though 90% of them seldom touch the database
> from one release to the next. Many are dismayed that every time they do
> we've fiddled with the knobs so some don't work anymore, some are
> missing or renamed and there's a few new ones.

I certainly agree we don't want to make it more difficult.

>
> So if they don't know
> what they're doing it is frequently because we keep moving the
> goalposts.

No, its because they don't read the docs. We are not talking about
dumping SQL here :) I don't feel it is correct to not perform such a
needed cleanup for fear that some user can't be bothered to read the
documentation.

> We should also consider that most people with multiple databases are
> running multiple versions of PostgreSQL. The main reason for that is
> that we keep changing the behaviour of SQL between releases, so even if
> you had a magic superfast upgrade utility, in perhaps 50% of cases they
> won't use it because they have to do a full application re-test, which
> costs time and money.

This could certainly be a problem.

>
> Trying to be a Postgres DBA is hard when each new release is configured
> differently to the last one and we have a major release about 3-5 more
> frequently than Oracle/SQLServer. That is probably largest source of
> questions and annoyance from the students on the courses I run.

That is the nature of the beast though. We are still learning,
improving, engineering. It's part of progress of the database. I would
suggest that your students (which is what I tell mine) not upgrade every
release but instead try hanging out for 3 years per release.

>
> So my viewpoint is that we should be aggressively adding new features,
> yet be conservative in changing existing behaviour: provide options for
> behaves-like-previous-release and keep the administrative interfaces as
> similar as possible between releases.
>

I agree with this in principle but not on this argument.

> If you wish to make changes, I would suggest that you simply reorder
> some of the parameters in the .conf file, so that the ones you think are
> important are grouped at the top.

IMO, the only settings that should be in the postgresql.conf are the
ones that require a restart. The rest should be gathered from pg_settings.

>
> Another suggestion would be to allow a #include style interface within
> postgresql.conf. We can then do this:
>
> #include standard_postgresql.conf
>
> # now we put Josh's favourite GUCs as overrides on the above
> ...

I think that could get very messy but Apache allows this. It isn't
exactly a new idea and I wouldn't fight against it.

> Some other problems I see with GUCs
>
> * It's not possible to set one parameter depending upon the setting of
> another.

That is getting a bit more complicated than I think we need. Unless I am
misunderstanding what you are saying.

>
> * It's always unclear which GUCs can be changed, and when. That is much
> more infrequently understood than the meaning of them.
>

This is a definite problem. Even the docs are a bit difficult on this
one. It should be a nice simple grid :) or like I said above, only goes
in the conf if requires a restart.

Hmm,

SET effective_cache_size = '5000MB';
WARNING: You must issue a reload to the database for this to take effect.

> * We should rename effective_cache_size to something that doesn't sound
> like it does what shared_buffers does

Hmmm, I wonder if it is not the other way around. Although I know that
one thing I run into is that a lot of people think effective_cache is an
allocation.

shared_buffer_alloc?
shared_mem?

>
> * There is no config verification utility, so if you make a change and
> then try to restart and it won't, you are in trouble.
>

+1 +1

Sincerely,

Joshua D. Drake

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Many are dismayed that every time they do we've fiddled with the
> knobs so some don't work anymore, some are missing or renamed and
> there's a few new ones.
...
> * We should rename effective_cache_size

*raises eyebrow*

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200805311114
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhBa3UACgkQvJuQZxSWSsg6pACgu2EJOyzj+ik79QwzdTTfi8Qj
aW0AoNayz4sniFVFxnQFgnxh7dPB6QOD
=Uyap
-----END PGP SIGNATURE-----

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> 4 large problems:

> 1. Most people have no idea how to set these.
> 2. The current postgresql.conf file is a huge mess of 194 options, the
> vast majority of which most users will never touch.
> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> and the settings.sgml), which are only synched with each other manually.
> 4. We don't seem to be getting any closer to autotuning.

The proposal doesn't actually solve any of those problems.

> Here's a list of the things we want to change. It's all a package and
> should make sense if you take all the changes as a whole.

> 1) Add several pieces of extra information to guc.c in the form of extra
> "gettext" commands: default value, subcategory, long description,
> recommendations, enum lists.
> 2) Incorporate this data into pg_settings
> 3) Delete postgresql.conf.sample
> 4) 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), which would be called automatically by initdb.

I disagree with doing any of this. It doesn't result in any useful
reduction in maintenance effort, and what it does do is make it
impossible to keep control over the detailed layout, formatting,
commenting etc in a sample postgresql.conf. Nor do I think that
"generate a whole config file from scratch" is going to be a useful
behavior for tuning problems --- how will you merge it with what
you had before?

regards, tom lane

On May 31, 2008, at 09:23, Tom Lane wrote:

>> 1. Most people have no idea how to set these.
>> 2. The current postgresql.conf file is a huge mess of 194 options,
>> the
>> vast majority of which most users will never touch.
>> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
>> and the settings.sgml), which are only synched with each other
>> manually.
>> 4. We don't seem to be getting any closer to autotuning.
>
> The proposal doesn't actually solve any of those problems.

It solves #2 at least.

> I disagree with doing any of this. It doesn't result in any useful
> reduction in maintenance effort, and what it does do is make it
> impossible to keep control over the detailed layout, formatting,
> commenting etc in a sample postgresql.conf. Nor do I think that
> "generate a whole config file from scratch" is going to be a useful
> behavior for tuning problems --- how will you merge it with what
> you had before?

I'd love to see these issues resolved. The current postgresql.conf is
way over the top. Might you have a better idea?

Thanks,

David

Simon, Tom, Greg,

> Now, that will get a lot easier just by
> virtue of having a smaller config file, but I think that adding something
> into pg_settings that allows saving user-added commentary would be a nice
> step toward some useful standardization on that side of things. It would
> make future automated tools aimed at parsing and generating new files, as
> part of things like version upgrades, a lot easier if there was a standard
> way such comments were handled in addition to the raw data itself.

Hmmm. What about a COMMENT ON SETTING? That seems like it would serve the
purpose ... and make preserving user comments easier than the current
file-conversion approach.

> The other thing I'd like to see make its way into pg_settings, so that
> tools can operate on it just by querying the database, is noting what file
> the setting came from so that you can track things like include file
> usage. I think with those two additions (comments and source file
> tracking) it would even be concievable to clone a working facsimile of
> even a complicated postgresql.conf file set remotely just by reading
> pg_settings.

Hmmm. Any idea how to do this? It sounds like a good idea to me.

> So my viewpoint is that we should be aggressively adding new features,
> yet be conservative in changing existing behaviour: provide options for
> behaves-like-previous-release and keep the administrative interfaces as
> similar as possible between releases.

It's my viewpoint based on a lot of user feedback that the current
postgresql.conf is fundamentally broken and a major roadblock to PostgreSQL
adoption. This was a point with which there was pretty much universal
agreement when I talked with people at pgCon. That is, I beleive that you're
arguing for keeping .conf stable for the 5% of users who understand it and
ignoring the 95% of users who are baffled by it.

At this point, I think we are the only major SQL database without some form of
basic autotuning for the most significant settings (certainly MySQL, Oracle,
and SQL Server have it); we've been able to coast through that because
autotuning features are new in other DBs, but it's going to start to hurt us
pretty soon.

Now, I do believe that we should plan any GUCS overhaul to happen in one
postgresql version rather than phasing it in over multiple versions so that
administrators only need to get used to a new way once.

> > Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> > 4 large problems:
> >
> > 1. Most people have no idea how to set these.
> > 2. The current postgresql.conf file is a huge mess of 194 options, the
> > vast majority of which most users will never touch.
> > 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> > and the settings.sgml), which are only synched with each other manually.
> > 4. We don't seem to be getting any closer to autotuning.
>
> The proposal doesn't actually solve any of those problems.

OK, let me draw some lines:
1 & 2) by not having the settings be defined in a 500-line file, new users
would no longer be baffled by scores of settings which probably don't concern
them, trying to find the handful of settings which do.

3) We'd consolidate the GUC lists down from 3 places to 2, which is one less
area to synchronize. Magnus and I looked to see if it might be possible to
generate the docs from the same list, but it's not practical.

4) By shifting from a model where postgresql.conf is document-formatted and
hand-edited to one where it's machine generated, it becomes vastly easier to
write simple utilities to manage these settings. Right now, the big
"obstacle" to things like SET PERSISTENT is "how to we preseve the
hand-edited comments in the file" -- and the answer is we *don't.*

> I disagree with doing any of this. It doesn't result in any useful
> reduction in maintenance effort, and what it does do is make it
> impossible to keep control over the detailed layout, formatting,
> commenting etc in a sample postgresql.conf.

Have you *looked* at postgresql.conf.sample lately, Tom? It's a disaster.
Maintenance is already difficult, and becoming more so as we add settings.

Further, you and Simon seem to think that the current "narrative docs inside
the conf file" format has some kind of value which makes things easier for
DBAs. I don't believe it does, and I have yet to meet a *single* new
PostgreSQL user who wasn't confused and intimidated by the file.

> Nor do I think that
> "generate a whole config file from scratch" is going to be a useful
> behavior for tuning problems --- how will you merge it with what
> you had before?

Who's merging? I don't think you get the proposal. The whole "hand-edited"
approach to postgresql.conf should go away. It's not useful, it's not
educational, and it doesn't make PostgreSQL easy to manage.

Further, the lack of an easy way to manage settings via port access to
PostgreSQL is a significant inhibitor to adopting PostgreSQL in environments
with large numbers of servers. See prior arguments by the CPANEL folks about
why they don't support PostgreSQL, which is in turn a major reason why
PostgreSQL web hosting is hard to find.

I agree that editing the data about settings in the guc.c file is not ideal;
Magnus and I discussed that mainly because we wanted to preserve the
translation framework with gettext strings. If someone can think of a better
way to do this part, I'm all ears.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

"David E. Wheeler" <david(at)kineticode(dot)com> writes:

> I'd love to see these issues resolved. The current postgresql.conf is way over
> the top. Might you have a better idea?

I don't think fiddling with surface issues like the formatting of the
postgresql.conf is productive. Hiding parameters because you don't think
beginners need them is only going to frustrate those people who do need to
adjust them.

What might be productive is picking up a group of parameters and thinking hard
about what they mean in terms of user-visible real-world effects. If they can
be recast in terms of behaviour the user wants instead of internal
implementation details then that would translate into a massive simplification
as well as being easier to explain to users.

I think we do a pretty good job of this already. Witness things like
effective_cache_size -- imagine if this were "nested_loop_cache_hit_rate" for
example, good luck figuring out what to set it to.

The vacuum cost delay factors are probably ripe for such a recast though. I
think we need just one parameter "vacuum_io_bandwidth" or something like that.
The bgwriter parameters might also be a candidate but I'm less certain.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:

> It's my viewpoint based on a lot of user feedback that the current
> postgresql.conf is fundamentally broken and a major roadblock to PostgreSQL
> adoption. This was a point with which there was pretty much universal
> agreement when I talked with people at pgCon.

Actually as a new DBA when I was first starting out with Postgres I found it
very convenient to have all the common parameters in one place where I could
just uncomment and adjust them. Instead of having to search through
documentation and find the default value from which

> 1 & 2) by not having the settings be defined in a 500-line file, new users
> would no longer be baffled by scores of settings which probably don't concern
> them, trying to find the handful of settings which do.

I'm not sure how an empty file is any less "baffling" than one listing the
default value for parameters they don't need yet.

> 3) We'd consolidate the GUC lists down from 3 places to 2, which is one less
> area to synchronize. Magnus and I looked to see if it might be possible to
> generate the docs from the same list, but it's not practical.

This seems like a trivial gain and one which is unlikely to outweigh the pain
of having to massage the info into C data structures.

> 4) By shifting from a model where postgresql.conf is document-formatted and
> hand-edited to one where it's machine generated, it becomes vastly easier to
> write simple utilities to manage these settings. Right now, the big
> "obstacle" to things like SET PERSISTENT is "how to we preseve the
> hand-edited comments in the file" -- and the answer is we *don't.*

What this sounds like is a sly way to try to get rid of postgresql.conf
entirely and replace it with parameters stored in the database so admins would
adjust the parameters using an SQL syntax rather than a text file.

There are pros and cons of such a system but I think for newbie admins that
would be a thousand times *more* baffling. You would have to learn new
commands and have no holistic view of what parameters had been set, what
related parameters might exist. You also have no way to keep the file in a
version control system or sync across servers etc.

> Have you *looked* at postgresql.conf.sample lately, Tom? It's a disaster.
> Maintenance is already difficult, and becoming more so as we add settings.

What difficulties?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On May 31, 2008, at 12:36, Gregory Stark wrote:

> What this sounds like is a sly way to try to get rid of
> postgresql.conf
> entirely and replace it with parameters stored in the database so
> admins would
> adjust the parameters using an SQL syntax rather than a text file.
>
> There are pros and cons of such a system but I think for newbie
> admins that
> would be a thousand times *more* baffling. You would have to learn new
> commands and have no holistic view of what parameters had been set,
> what
> related parameters might exist. You also have no way to keep the
> file in a
> version control system or sync across servers etc.

FWIW, this has not been a barrier to MySQL adoption.

Best,

David

Josh Berkus wrote:
> Currently, PostgreSQL,conf and our set of GUC configurations suffer from
> 4 large problems:

As we have talked about it before, you know that I agree that the GUC system
could use some improvements. But I'm a bit surprised about some of your
assessments.

> 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.

> 2. The current postgresql.conf file is a huge mess of 194 options, the
> vast majority of which most users will never touch.

My opinion has always been that we should provide a default file with only the
essential options instead of all of them. I see this as a the major problem,
because people are overwhelmed and consequently don't set anything.

> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> and the settings.sgml), which are only synched with each other manually.

While this is not ideal, I can't really see who this is a major problem, at
least from the perspective of the user.

> 4. We don't seem to be getting any closer to autotuning.

True. But how does your proposal address this?

On May 31, 2008, at 15:32, Peter Eisentraut 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.

I've been using PostgreSQL on and off, mostly on, for almost 10 years.
I still have no idea what 75% of those settings in postgresql.conf
mean or are for. There are an overwhelming number of them. I know that
5-8 of them I always touch, thanks largely to assistance now and then
from Josh Berkus, but the rest are just complexity to me. I don't
doubt that the vast majority of them are useful in one situation or
another, but unless I'm in one of those situations, I really don't
need to see them there and be confused by them.

Does that help?

>> 2. The current postgresql.conf file is a huge mess of 194 options,
>> the
>> vast majority of which most users will never touch.
>
> My opinion has always been that we should provide a default file
> with only the
> essential options instead of all of them. I see this as a the major
> problem,
> because people are overwhelmed and consequently don't set anything.

+1

Best,

David

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

In the interest of constructive criticism, here's some
ways I think postgresql.conf could be improved.

* A weak +1 to the #include idea. I'm much more inclined to simply
add a new section to the bottom of the file and use version
control, but I could see ways in which include would be useful.

* Rearrange the settings to put the more "common" ones at
the top, even if it means messing up the categories a bit.
Having 'bonjour_name' at the top, and 'default_statistics_target'
buried way down below is crazy. The sections should be
looked at from a clean perspective: do we need them at all? Are their
better ways to arrange things? What order should they be in?

* Much more verbose comments. The abovementioned default_statistics_target
is a very important settings, but there is zero explanation in the file
of what it is. The only thing we're told is that it ranges from 10 - 1000.
We can do better than that. Users would absolutely love it if each item
had a clear explanation, and it would be well worth a slightly increased
file size. See the postfix main.cf file for a good example of such.

* Remove the confusing "commented out is default" bit entirely. Simply
set each value explicitly. Why should new users have to confront this?:

# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.

* Lose the tabbed indenting, which looks bad, especially for multi-line
comments. Just use spaces.

* Create a tool, or at least a best practices, for controlling and tracking
changes to the file.

* Put some doc URLs in the file, at the minimum per major section. At the
very bare minimum, a real URL at the top.

* Indicate which values can be changed per-session or per-role.

* Fix the disparity between the syntax in the file and the SET interface.
For example, work_mem = 16MB works in the conf file, but you have to write
SET work_mem = '16MB'. Easiest is probably just to quote everything in the conf.

* Lose the post-value, end-of-line comments, they just get in the way when making
changes and make the file harder to read by contributing to the wrap problem.

* I'm tempted by the argument of creating a separate file for the obscure
settings, but I think it would be too much pain, and nobody would ever agree on
which settings are 'important' and which are 'obscure'.

* It might be nice to mention other ways to reload the file, such as
'service postgresql reload', or whatever Windows uses.

* The word 'paramters' is still misspelled. :)

* That whole sentence about changing the parameters as command-line
options needs to go away.

* Since the executable is now named "postgres" (thank goodness we got
rid of "postmaster"), the file should be named 'postgres.conf'. This would
also be a way to quickly distinguish 'old' vs 'new' style conf files if
we end up making major changes to it.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200805311911
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhB39sACgkQvJuQZxSWSshahQCg4V5QsO34HOhUDoPzT7STcR45
V5UAoPQxkmuk/oCYirTKxMAhV+Kh8Ytz
=7Lgk
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:
>
> * A weak +1 to the #include idea. I'm much more inclined to simply
> add a new section to the bottom of the file and use version
> control, but I could see ways in which include would be useful.
>
>
>
>

We already have include directives, and have had since 8.2.

Thus spake the docs:

"In addition to parameter settings, the postgresql.conf file can contain
/include directives/, which specify another file to read and process as
if it were inserted into the configuration file at this point. Include
directives simply look like:

include 'filename'

If the file name is not an absolute path, it is taken as relative to the
directory containing the referencing configuration file. Inclusions can
be nested."

cheers

andrew

Greg Sabino Mullane wrote:

> * Much more verbose comments. The abovementioned default_statistics_target
> is a very important settings, but there is zero explanation in the file
> of what it is. The only thing we're told is that it ranges from 10 - 1000.
> We can do better than that. Users would absolutely love it if each item
> had a clear explanation, and it would be well worth a slightly increased
> file size. See the postfix main.cf file for a good example of such.

I kind of agree with this but actually think we should have the bare
minimum comments in the file. Why? Because our documentation links are
static. Each setting should have the URL to the full documentation on a
particular setting.

>
> * Create a tool, or at least a best practices, for controlling and tracking
> changes to the file.
>

This I disagree with. There are plenty of tools to handle this should
someone really want to. SVN, CVS, parrot, etc... Let systems management
be the domain of systems management.

>
> * Put some doc URLs in the file, at the minimum per major section. At the
> very bare minimum, a real URL at the top.
>

Hah! See above :)

>
> * Indicate which values can be changed per-session or per-role.
>

Agreed. Along with this although offtopic for this post is a grid in the
docs that are explicit about this.

>
> * Fix the disparity between the syntax in the file and the SET interface.
> For example, work_mem = 16MB works in the conf file, but you have to write
> SET work_mem = '16MB'. Easiest is probably just to quote everything in the conf.

Agreed.

>
> * I'm tempted by the argument of creating a separate file for the obscure
> settings, but I think it would be too much pain, and nobody would ever agree on
> which settings are 'important' and which are 'obscure'.
>

Actually I could buy into this. There really are only about a dozen must
change settings (if that). I could see something like:

Memory settings:

network etc/network.conf
include etc/memory.conf
logging etc/logging.conf

etc...

>
> * It might be nice to mention other ways to reload the file, such as
> 'service postgresql reload', or whatever Windows uses.
>

I think a url to the docs is a better idea here.

>
> * The word 'paramters' is still misspelled. :)
>

Heh.

> * Since the executable is now named "postgres" (thank goodness we got
> rid of "postmaster"), the file should be named 'postgres.conf'. This would
> also be a way to quickly distinguish 'old' vs 'new' style conf files if
> we end up making major changes to it.

It was never postmaster.conf (that I can recall). I don't see the issue
here. Consider apache... It isn't apache.conf.

I think postgresql.conf (as that is the name of the software) makes sense.

Sincerely,

Joshua D. Drake

Can I express one's user view:

Greg Sabino Mullane writes:
> * Much more verbose comments. The abovementioned default_statistics_target
> is a very important settings, but there is zero explanation in the file
> of what it is. The only thing we're told is that it ranges from 10 - 1000.
> We can do better than that. Users would absolutely love it if each item
> had a clear explanation, and it would be well worth a slightly increased
> file size. See the postfix main.cf file for a good example of such.
>
Absolutely agreed :-). Total list of GUC and fully explanation each
of them is sufficient.
For "easy start", it might be a several postgresql.conf with
distribution, like mysql doing it,
or generate postgresql.conf by wizard-dialog before initdb starting, for
example.
But I think that all GUCs must present in configuration file.

Thanks.

Gregory Stark wrote:
>
> I think we do a pretty good job of this already. Witness things like
> effective_cache_size -- imagine if this were "nested_loop_cache_hit_rate" for
> example, good luck figuring out what to set it to.

I think either of these are fine if we describe how to measure
them. Ideally if we had a GUC that said "log_nested_loop_cache_hit_rate"
that enabled some timing code (understandably with lots of overhead) that
made an attempt to measure the hit rate, it'd be easier to figure out
than the effective cache size, no?

> The vacuum cost delay factors are probably ripe for such a recast though. I
> think we need just one parameter "vacuum_io_bandwidth" or something like that.

+1; though perhaps the inverse of that is more useful. When my
machines are idle I'd be happy if they vacuum more. Wouldn't
we be better served specifying the I/O bandwidth of each device/tablespace
and letting vacuum use whatever portion would be otherwise idle?

> The bgwriter parameters might also be a candidate but I'm less certain.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Andrew Dunstan wrote:

> We already have include directives, and have had since 8.2.

Heh, thanks - which proves how useless they are to me. :)

Joshua Drake wrote:

> I kind of agree with this but actually think we should have the bare
> minimum comments in the file. Why? Because our documentation links are
> static. Each setting should have the URL to the full documentation on a
> particular setting.

Ugh, why so much context switching? Put the docs next to the setting. URLs
are nice but not necessary. If you are arguing for minimum comments in
conf files, please make a patch for pg_hba.conf ;)

>> * Create a tool, or at least a best practices, for controlling and tracking
>> changes to the file.

> This I disagree with. There are plenty of tools to handle this should
> someone really want to. SVN, CVS, parrot, etc... Let systems management
> be the domain of systems management.

Well, perhaps just a note in the docs at least that one might want to put
postgresql.conf in version control. I've seen people not doing so more often
than you would think. Perhaps because they are DBAs and not sysadmins? I also
meant a tool to do things like verify that the changes are valid, as someone
else mentioned elsewhere in this thread.

>> * It might be nice to mention other ways to reload the file, such as
>> 'service postgresql reload', or whatever Windows uses.

> I think a url to the docs is a better idea here.

Good point. Maybe a sort of "DBA basics" page in the docs is warranted for
things like this.

>> * Since the executable is now named "postgres" (thank goodness we got
>> rid of "postmaster"), the file should be named 'postgres.conf'. This would
>> also be a way to quickly distinguish 'old' vs 'new' style conf files if
>> we end up making major changes to it.

> It was never postmaster.conf (that I can recall). I don't see the issue
> here. Consider apache... It isn't apache.conf.

Not saying it ever was postmaster.conf: just that I'm glad we finally
changed the name. As for the Apache project, the httpd executable reads the
httpd.conf file. Hence, one might expect the postgres executable to read a
postgres.conf file.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200806011656
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhDEJMACgkQvJuQZxSWSsgeogCfT0g69NDoxyWGiWmDcB3PxH8h
wJ8AnjzssA7aIk0rBdJzL+bB5vSQSeBV
=lgZG
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160

> Joshua Drake wrote:
>
>> I kind of agree with this but actually think we should have the bare
>> minimum comments in the file. Why? Because our documentation links are
>> static. Each setting should have the URL to the full documentation on a
>> particular setting.
>
> Ugh, why so much context switching? Put the docs next to the setting. URLs
> are nice but not necessary. If you are arguing for minimum comments in
> conf files, please make a patch for pg_hba.conf ;)

Hah! Well I don't know that a minimum of comments is what I am arguing
as much as not too much comments. The comments in general in the
postgresql.conf are useless unless you have previous knowledge. I really
think that if we take advantage of the fact that we have static URLs
that life would be easier overall.

>
>>> * Create a tool, or at least a best practices, for controlling and tracking
>>> changes to the file.
>
>> This I disagree with. There are plenty of tools to handle this should
>> someone really want to. SVN, CVS, parrot, etc... Let systems management
>> be the domain of systems management.
>
> Well, perhaps just a note in the docs at least that one might want to put
> postgresql.conf in version control.

I could certainly buy into this. No reason we can't help people better
administrators. I would suggest a link to a static wiki page (on
wiki.pg) that would link to each option?

> I've seen people not doing so more often
> than you would think. Perhaps because they are DBAs and not sysadmins? I also
> meant a tool to do things like verify that the changes are valid, as someone
> else mentioned elsewhere in this thread.

pg_ctl -D data check?

I would +1 that. Including (in later releases):

WARNING: You specify 66536 for shared buffers but you only have 131072
of memory. Consider decreasing the parameter.

Obviously we would need more non math friendly wording.

>
>>> * It might be nice to mention other ways to reload the file, such as
>>> 'service postgresql reload', or whatever Windows uses.
>
>> I think a url to the docs is a better idea here.
>
> Good point. Maybe a sort of "DBA basics" page in the docs is warranted for
> things like this.
>

Yeah I could buy into this.

>>> * Since the executable is now named "postgres" (thank goodness we got
>>> rid of "postmaster"), the file should be named 'postgres.conf'. This would
>>> also be a way to quickly distinguish 'old' vs 'new' style conf files if
>>> we end up making major changes to it.
>
>> It was never postmaster.conf (that I can recall). I don't see the issue
>> here. Consider apache... It isn't apache.conf.
>
> Not saying it ever was postmaster.conf: just that I'm glad we finally
> changed the name. As for the Apache project, the httpd executable reads the
> httpd.conf file. Hence, one might expect the postgres executable to read a
> postgres.conf file.

Maybe, but I think I would need more convincing.

Sincerely,

Joshua D. Drake

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Greg Sabino Mullane wrote:
>> Ugh, why so much context switching? Put the docs next to the setting. URLs
>> are nice but not necessary. If you are arguing for minimum comments in
>> conf files, please make a patch for pg_hba.conf ;)

> Hah! Well I don't know that a minimum of comments is what I am arguing
> as much as not too much comments. The comments in general in the
> postgresql.conf are useless unless you have previous knowledge. I really
> think that if we take advantage of the fact that we have static URLs
> that life would be easier overall.

Yeah ... this thread started out with the idea of reducing duplication,
so how did we arrive at wanting to duplicate the SGML documentation into
postgresql.conf.sample? I think we need to integrate, not duplicate.

(pg_hba.conf.sample is a pretty good example of what not to do, I think
... and it used to be even worse ...)

regards, tom lane

On Mon, Jun 2, 2008 at 5:46 AM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:

>> I've seen people not doing so more often
>> than you would think. Perhaps because they are DBAs and not sysadmins? I
>> also
>> meant a tool to do things like verify that the changes are valid, as
>> someone
>> else mentioned elsewhere in this thread.
>
> pg_ctl -D data check?
>
> I would +1 that.

I would also really like to see that - though I'd also like to see an
SQL interface so we can check a config before saving when editing via
pgAdmin or similar.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Sun, 1 Jun 2008, Joshua D. Drake wrote:

> Well I don't know that a minimum of comments is what I am arguing as
> much as not too much comments.

Josh's proposal included making three levels of documentation-level
comments available: terse, normal, and verbose. The verbose comment
level probably should include a web link to full documentation. The way
the comments litter the existing file, the status quo that's called normal
mode in this proposal, is IMHO a complete mess. Most use cases I can
think of want either no comments or really verbose ones, the limited
commentary in the current sample postgresql.conf seems a middle ground
that's not right for anybody.

The key thing thing here in my mind is that it should be possible to
transform between those three different verbosity levels without losing
any settings or user-added comments. They're really just different views
on the same data, and which view you're seeing should be easy to change
without touching the data.

I just extracted the original design proposal and some of the relevent
follow-up in this thread, made some additional suggestions, and put the
result at http://wiki.postgresql.org/wiki/GUCS_Overhaul I think reading
that version makes it a bit clearer what the proposed overhaul is aiming
for.

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

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

Greg Smith wrote:
>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

>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.

Depending on the complexity you want to have inside the generator, one
could imagine a middle ground solution like:

include "database-generated.conf"
include "local-overrides.conf"

Where the "database-generated.conf" does not necessarily needs a lot of
comments.

>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.

IMHO Greg's response is the most comprehensive and well-thought-through
contribution in the whole GUC thread.
--
Sincerely,
Stephen R. van den Berg.

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> Joshua has been banging a drum for a while now that all this data needs to
> get pushing into the database itself.

This is, very simply, not going to happen. Shall we go over the reasons
why not, one more time?

1. Quite a few of the GUC parameters are needed *before* one can ever
read the database; in particular the ones about file locations and
shared memory sizing.

2. Suppose you change a parameter in a way that breaks the DB (eg,
set shared_buffers to a value larger than your kernel allows) and
try to restart. Database doesn't start. If the parameter can only
be changed back within an operating database, you're hosed.

I have no objection to providing alternative ways to edit the
configuration data, but the primary source of the settings is
going to continue to be an editable text file. Any proposals for
alternatives-to-a-text-editor have to work within that reality.

regards, tom lane

Tom Lane wrote:
>Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> Joshua has been banging a drum for a while now that all this data needs to
>> get pushing into the database itself.

>This is, very simply, not going to happen. Shall we go over the reasons
>why not, one more time?

>1. Quite a few of the GUC parameters are needed *before* one can ever
>read the database; in particular the ones about file locations and
>shared memory sizing.

Obviously.

>2. Suppose you change a parameter in a way that breaks the DB (eg,
>set shared_buffers to a value larger than your kernel allows) and
>try to restart. Database doesn't start. If the parameter can only
>be changed back within an operating database, you're hosed.

That's why those parameters will always have to be read from a textfile.
Which doesn't prohibit that textfile to be generated from within the
database (once up and running). And yes, if someone changes the
parameter through the DB, then makes the DB write out the configfile,
then restarts the DB remotely, and the change didn't work out (i.e. DB
doesn't start, or crashes/hangs), he is hosed and needs to log in and change
the textfile with a texteditor, no way around it.

>I have no objection to providing alternative ways to edit the
>configuration data, but the primary source of the settings is
>going to continue to be an editable text file. Any proposals for
>alternatives-to-a-text-editor have to work within that reality.

I think everyone is aware of that.
--
Sincerely,
Stephen R. van den Berg.

Simon Riggs wrote:
>
> Some other problems I see with GUCs
>
> * It's not possible to set one parameter depending upon the setting of
> another.
>

To me this is more critical.. Most people I have seen will increase one
or few but not all parameters related to memory which can result in loss
of performance and productivity in figuring out.

What happened to AvailRAM setting and base all memory gucs on that.
Ideally PostgreSQL should only create one big memory pool and allow all
other variables to change runtime via dba or some tuner process or
customized application as long as total is less than the allocated
shared_memory and local_memory settings. (This will also reduce the need
of restarting Postgres if a value needs to be changed)

-Jignesh

> * It's always unclear which GUCs can be changed, and when. That is much
> more infrequently understood than the meaning of them.
>
> * We should rename effective_cache_size to something that doesn't sound
> like it does what shared_buffers does
>
> * There is no config verification utility, so if you make a change and
> then try to restart and it won't, you are in trouble.
>
>

On Mon, 2 Jun 2008, Tom Lane wrote:

> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> Joshua has been banging a drum for a while now that all this data needs to
>> get pushing into the database itself.
>
> This is, very simply, not going to happen.

Right, there are also technical challenges in the way of that ideal. I
was only mentioning the reasons why it might not be the best idea even if
it were feasible. However, I do not see why the limitations you bring up
must get in the way of thinking about how to interact and manage the
configuration data in a database context, even though it ultimately must
be imported and exported to a flat file.

The concerns you bring up again about leaving the database in an
unstartable state are a particularly real danger in the "only has access
to 5432" hosted provider case that this redesign is trying to satisfy. I
added a "Gotchas" section to the wiki page so that this issue doesn't get
forgotten about. The standard way to handle this situation is to have a
known good backup configuration floating around. Adding something in that
area may end up being a hard requirement before remote editing makes
sense.

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

On Mon, 2008-06-02 at 11:59 -0400, Jignesh K. Shah wrote:
>
> Simon Riggs wrote:
> >
> > Some other problems I see with GUCs
> >
> > * It's not possible to set one parameter depending upon the setting of
> > another.
> >
>
> To me this is more critical.. Most people I have seen will increase one
> or few but not all parameters related to memory which can result in loss
> of performance and productivity in figuring out.
>
> What happened to AvailRAM setting and base all memory gucs on that.
> Ideally PostgreSQL should only create one big memory pool and allow all
> other variables to change runtime via dba or some tuner process or
> customized application as long as total is less than the allocated
> shared_memory and local_memory settings. (This will also reduce the need
> of restarting Postgres if a value needs to be changed)

Agreed.

Right now, we can't even do that in code, let alone in config file.

If we had a smart_memory_config = on then we'd be able to say in the
backend:
if (smart_memory_config)
{
other_thing = 0.1 * Nbuffers;
}

but the GUCs are evaluated in alphabetical order, without any way of
putting dependencies between them. So they are notionally orthogonal.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Mon, 2 Jun 2008, Jignesh K. Shah wrote:

> Most people I have seen will increase one or few but not all parameters
> related to memory which can result in loss of performance and
> productivity in figuring out.

If it becomes easier to build a simple tool available to help people tune
their configurations, that should help here without having to do anything
more complicated than that.

> What happened to AvailRAM setting and base all memory gucs on that.

Like some of the other GUC simplification ideas that show up sometimes
(unifying all I/O and limiting background processes based on that total is
another), this is hard to do internally. Josh's proposal has a fair
amount of work involved, but the code itself doesn't need to be clever or
too intrusive. Unifying all the memory settings would require being both
clever and intrusive, and I doubt you'll find anybody who could pull it
off who isn't already overtasked with more important improvements for the
8.4 timeframe.

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

Greg,

> Like some of the other GUC simplification ideas that show up sometimes
> (unifying all I/O and limiting background processes based on that total
> is another), this is hard to do internally.  Josh's proposal has a fair
> amount of work involved, but the code itself doesn't need to be clever
> or too intrusive.  Unifying all the memory settings would require being
> both clever and intrusive, and I doubt you'll find anybody who could
> pull it off who isn't already overtasked with more important
> improvements for the 8.4 timeframe.

Plus, I'm a big fan of "enable an API" rather than "write a feature". I
think that there are people & companies out there who can write better
autotuning tools than I can, and I'd rather give them a place to plug
those tools in than trying to write autotuning into the postmaster.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

to add some complexity to this topic :-) Please note I admit upfront
I am not familiar with every parameter out there, but during my quest
in finding bottleneck while stressing the back-end I find many GUC
parameters with names that show they should be interesting.
I read the comments, the docs (that I know of), go into the source to
learn the parameters and their units. And wondering if altering a
setting would do miracles in my case.

Indeed how can I measure the effect of a new setting? Enhanced
throughput? I learned in the past that fiddling with parameter A in
a database that was not properly setup/used --and there were many of
these--, had side effects in other parts of the engine. Yes, increased
throughput was observed. A new hype created. In the end it turned out
the parameter A was not set correctly at all. That parameter B, once
set to a sane value, cured the wrong behavior, and parameter A was not
optimal at all after the cure. We were just side tracked because we
did not know. Incorrect "knowledge" was borne (parameter A
setting). Throughout the years this database product has matured,
many more parameter realized and much, much more instrumentation been
implemented. It still is quite a challenge to understand what is
happening. But proper analysis is possible indeed. The black box is
much more open now.

One current example: wal_writer_delay. In my team there is an advise
to set this parameter to 100. However, after implementing a counter
(home grown instrumentation) I now know that the background log flush
routine is never called when stressing the database. Therefore I now
think the best setting is 10000 (its maximum) since it does not do
useful work (in my context) and therefore should wake up as little
times as possible. Without this instrumentation I can only guess about
the usability of this parameter and spend many tests in order to get
an impression of its validity to me.

So overhauling the GUC parameters is one step, but adding proper
instrumentation in order to really measure the impact of the new
setting is necessary too. Especially when looking in the direction of
auto tuning. Proper measurement is crucial to enable correct analysis.

Of course I am in favor of doing this with DTrace, however not all
platforms can benefit in that case :-)

--Paul

On 2 jun 2008, at 20:06, Josh Berkus wr
> Greg,
>
>> Like some of the other GUC simplification ideas that show up
>> sometimes
>> (unifying all I/O and limiting background processes based on that
>> total
>> is another), this is hard to do internally. Josh's proposal has a
>> fair
>> amount of work involved, but the code itself doesn't need to be
>> clever
>> or too intrusive. Unifying all the memory settings would require
>> being
>> both clever and intrusive, and I doubt you'll find anybody who could
>> pull it off who isn't already overtasked with more important
>> improvements for the 8.4 timeframe.
>
> Plus, I'm a big fan of "enable an API" rather than "write a
> feature". I
> think that there are people & companies out there who can write better
> autotuning tools than I can, and I'd rather give them a place to plug
> those tools in than trying to write autotuning into the postmaster.
>
> --
> --Josh
>
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Regards,

Paul van den Bogaard

---------------------------------------------------------------------------------------------
Paul van den Bogaard Paul(dot)vandenBogaard(at)sun(dot)com
ISV-E -- ISV Engineering, Opensource Engineering group

Sun Microsystems, Inc phone: +31
334 515 918
Saturnus 1 extentsion:
x (70)15918
3824 ME Amersfoort mobile: +31
651 913 354
The Netherlands fax:
+31 334 515 001

On Tue, 3 Jun 2008, Paul van den Bogaard wrote:

> So overhauling the GUC parameters is one step, but adding proper
> instrumentation in order to really measure the impact of the new setting
> is necessary too.

Correct, but completely off-topic regardless. One problem to be solved
here is to take PostgreSQL tuning from zero to, say, 50% automatic.
Wander the user lists for a few months; the number of completely
misconfigured systems out there is considerable, partly because the
default values for many parameters are completely unreasonable for modern
hardware and there's no easy way to improve on that without someone
educating themselves. Getting distracted by the requirements of the
high-end systems will give you a problem you have no hope of executing in
a reasonable time period.

By all means bring that up as a separate (and much, much larger) project:
"Database Benchmarking and Sensitivity Analysis of Performance Tuning
Parameters" would make a nice PhD project for somebody, and there's
probably a good patent in there somewhere. Even if you had such a tool,
it wouldn't be usable by non-experts unless the mundate GUC generation
issues are dealt with first, and that's where this is at right now.

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

On Sat, May 31, 2008 at 08:36:54PM +0100, Gregory Stark wrote:
> > 4) By shifting from a model where postgresql.conf is document-formatted and
> > hand-edited to one where it's machine generated, it becomes vastly easier to
> > write simple utilities to manage these settings. Right now, the big
> > "obstacle" to things like SET PERSISTENT is "how to we preseve the
> > hand-edited comments in the file" -- and the answer is we *don't.*
>
> What this sounds like is a sly way to try to get rid of postgresql.conf
> entirely and replace it with parameters stored in the database so admins would
> adjust the parameters using an SQL syntax rather than a text file.
>
> There are pros and cons of such a system but I think for newbie admins that
> would be a thousand times *more* baffling. You would have to learn new
> commands and have no holistic view of what parameters had been set, what
> related parameters might exist. You also have no way to keep the file in a
> version control system or sync across servers etc.

It doesn't have to be in the database. I kinda like Oracle's method
here; they default to storing config options in a binary file that
(iirc) you can change via commands, but they also provide a way to turn
that binary file into text (and back). That would allow for a lot of
options when it comes to configuring stuff.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Mon, Jun 02, 2008 at 10:12:06AM -0400, Tom Lane wrote:
> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> > Joshua has been banging a drum for a while now that all this data needs to
> > get pushing into the database itself.
>
> This is, very simply, not going to happen. Shall we go over the reasons
> why not, one more time?
>
<snip>
>
> I have no objection to providing alternative ways to edit the
> configuration data, but the primary source of the settings is
> going to continue to be an editable text file. Any proposals for
> alternatives-to-a-text-editor have to work within that reality.

There's no reason that the server has to deal with a text file. I
completely agree that there must be a method to change settings even if
the database isn't running, but that method does not necessarily need to
be a text file. If we can come up with a standard API for reading and
writing config changes, we (or anyone else) can write any number of
tools to deal with the settings. And once we have an API, we can provide
a SQL interface on top of it.

Instead of focusing on exactly what the 'new postgresql.conf' is going
to look like we should focus on creating a clean configuration API. Once
we have that we can figure out what (if anything) we're doing with the
existing .conf, and what a new one (if it exists) might look like.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! wrote:
>>
>> There's no reason that the server has to deal with a text file. I
>> completely agree that there must be a method to change settings even if
>> the database isn't running, but that method does not necessarily need to
>> be a text file. If we can come up with a standard API for reading and
>> writing config changes, we (or anyone else) can write any number of
>> tools to deal with the settings. And once we have an API, we can provide
>> a SQL interface on top of it.
Once in a lifetime, a man should plant a tree, father a child, and write
an editor... :-)
Hiding the storage of config parameters opaquely behind an API is
something I've been hating for a long time on win32.

When reading this thread, I'm wondering if anybody ever saw a config
file for a complex software product that was easily editable and
understandable. I don't know one. If there was one, it'd be nice to know
it so we can learn from it.

IMHO the best compromise in machine and human readability is an XML
format. It's easily decorateable with comments, easily interpreted and a
pg_settings view could enhance it with even more comments, so an editor
using pgsql functions (to read and write postresql.conf.xml) could be
enabled to supply comprehensive help.

Regards,
Andreas

* Andreas Pflug <pgadmin(at)pse-consulting(dot)de> [080604 10:20]:

> Hiding the storage of config parameters opaquely behind an API is
> something I've been hating for a long time on win32.

;-)

> When reading this thread, I'm wondering if anybody ever saw a config
> file for a complex software product that was easily editable and
> understandable. I don't know one. If there was one, it'd be nice to know
> it so we can learn from it.

PostreSQL, Apache, X.org

They are all easily editable, and "understandable", in the sense that I
understand that I'm supposed to edit the line, changing the value
(following the comments list of accepted values)

They are "less understandable" if you mean that I know the implications
of any change I make. But guess what, having those values inputed
through some other mechanism (like a GUI config file editor, a SQL statement,
or a nice pgadmin-SQL-hiding-interface isn't going to change that part
of "understandable". That part of understandable only comes through
good documentation and reference material, which is universally
applicable to any config method.

> IMHO the best compromise in machine and human readability is an XML
> format. It's easily decorateable with comments, easily interpreted and a
> pg_settings view could enhance it with even more comments, so an editor
> using pgsql functions (to read and write postresql.conf.xml) could be
> enabled to supply comprehensive help.

Well, In my past, I've generally not got around to installing and using
software that reqired me to edit some jumble of XML. Ya, maybe I'm
lucky. And since I've got a lot invested in PG, I'ld be forced to of PG
moved to an XML config, but I'ld be forced to kicking and screaming...

I just *know* that I'ld reload/restart postmaster some time, and the
config file wouldn't be quite correct, and I'ld search for 10 minutes
trying to find the extra (or lack) ", or missing closing /... But maybe
most people are better at parsing XML than me. And that also may be
because I've actively avoided it for so long ;-)

I don't know *how* I'ld decorate my XML config file with comments and
history the way I do my text-based #-commented config files.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
> * Andreas Pflug <pgadmin(at)pse-consulting(dot)de> [080604 10:20]:
>
>
>> Hiding the storage of config parameters opaquely behind an API is
>> something I've been hating for a long time on win32.
>>
>
> ;-)
>
>
>> When reading this thread, I'm wondering if anybody ever saw a config
>> file for a complex software product that was easily editable and
>> understandable. I don't know one. If there was one, it'd be nice to know
>> it so we can learn from it.
>>
>
> PostreSQL, Apache, X.org
>
> They are all easily editable, and "understandable", in the sense that I
> understand that I'm supposed to edit the line, changing the value
> (following the comments list of accepted values)
>
> They are "less understandable" if you mean that I know the implications
> of any change I make. But guess what, having those values inputed
> through some other mechanism (like a GUI config file editor, a SQL statement,
> or a nice pgadmin-SQL-hiding-interface isn't going to change that part
> of "understandable". That part of understandable only comes through
> good documentation and reference material, which is universally
> applicable to any config method.
>

Right. On the editing side, a column "link" in pg_settings that can be
used to construct an URL to postgresql.org/docs/xxx#yyy could help
creating editors that support the user. Whatever a text config file will
look like, you need to know exactly which parameter to use and where to
locate it; even structuring parameters won't help too much for the
typical starter task "I installed pgsql, what to do next".
>
>> IMHO the best compromise in machine and human readability is an XML
>> format. It's easily decorateable with comments, easily interpreted and a
>> pg_settings view could enhance it with even more comments, so an editor
>> using pgsql functions (to read and write postresql.conf.xml) could be
>> enabled to supply comprehensive help.
>>
>
> Well, In my past, I've generally not got around to installing and using
> software that reqired me to edit some jumble of XML. Ya, maybe I'm
> lucky. And since I've got a lot invested in PG, I'ld be forced to of PG
> moved to an XML config, but I'ld be forced to kicking and screaming...
>
> I just *know* that I'ld reload/restart postmaster some time, and the
> config file wouldn't be quite correct, and I'ld search for 10 minutes
> trying to find the extra (or lack) ", or missing closing /... But maybe
> most people are better at parsing XML than me. And that also may be
> because I've actively avoided it for so long ;-)
>
Well I'm an XML evangelist either. But the usual "commenting out a
parameter will reset it to default on reload, no?" caveat isn't funny
either, or duplicate parameter settings scattered throughout your file.
This may be avoided by *preferably* editing the parameters through pgsql
itself; the current postgresql.conf file format isn't too machine write
friendly (as I know since I wrote the pgadmin config file editor). But
having a config file that can't be used with simple editors at all is a
nightmare.

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Well I'm an XML evangelist either. But the usual "commenting out a
> parameter will reset it to default on reload, no?" caveat isn't funny
> either, or duplicate parameter settings scattered throughout your file.

Surely everyone who is opining on this thread knows that we fixed that
in 8.3.

regards, tom lane

On Jun 3, 2008, at 20:48, Greg Smith wrote:

> Correct, but completely off-topic regardless. One problem to be
> solved here is to take PostgreSQL tuning from zero to, say, 50%
> automatic. Wander the user lists for a few months; the number of
> completely misconfigured systems out there is considerable, partly
> because the default values for many parameters are completely
> unreasonable for modern hardware and there's no easy way to improve
> on that without someone educating themselves. Getting distracted by
> the requirements of the high-end systems will give you a problem you
> have no hope of executing in a reasonable time period.

Exactly. The issue is that application developers, who are not DBAs,
have no idea how to tune PostgreSQL, and postgresql.conf is daunting
and confusing. So they use a different database that's "faster".

I think that right now postgresql.conf is designed for full-time DBAs,
rather than folks who might want to target PostgreSQL for an
application they're developing. We want to attract the latter
(without, of course, any expense with the former). Changing how
configuration works so that it's easier to understand and, if
possible, at least partly automatically tunable would go a long way
towards making PostgreSQL friendlier for developers, IMHO.

Best,

David

On Jun 4, 2008, at 07:19, Andreas Pflug wrote:

> IMHO the best compromise in machine and human readability is an XML
> format. It's easily decorateable with comments, easily interpreted
> and a pg_settings view could enhance it with even more comments, so
> an editor using pgsql functions (to read and write
> postresql.conf.xml) could be enabled to supply comprehensive help.

I hope that was a joke…

Best,

David

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Exactly. The issue is that application developers, who are not DBAs,
> have no idea how to tune PostgreSQL, and postgresql.conf is daunting
> and confusing. So they use a different database that's "faster".

Changing some of our defaults would go a long way as well.

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhGzCYACgkQvJuQZxSWSsi2AQCfZFa8PBZ16ljeFNmhY/YlkxF9
8KcAnjciYml1icuuxSPiKIyeE8YrVxOA
=q4Cf
-----END PGP SIGNATURE-----

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Exactly. The issue is that application developers, who are not DBAs,
> have no idea how to tune PostgreSQL, and postgresql.conf is daunting
> and confusing. So they use a different database that's "faster".

I take it you haven't looked at mysql's configuration file lately.

They aren't actually in any better shape than we are, except that
they supply several "preconfigured" sample files for people to choose
from.

regards, tom lane

On Jun 4, 2008, at 11:22, Tom Lane wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> Exactly. The issue is that application developers, who are not DBAs,
>> have no idea how to tune PostgreSQL, and postgresql.conf is daunting
>> and confusing. So they use a different database that's "faster".
>
> I take it you haven't looked at mysql's configuration file lately.

I'm not much into MySQL, but in the work I've done with it, I've had
to create /etc/my.cnf myself. There *is* no configuration file
configuring MySQL until that file is created, is there? So there is no
configuration to learn at first. I'm not saying that this is
necessarily admirable -- it's kind of the opposite end of the spectrum
(PostgreSQL: "Here is every configuration tweak you could ever
possibly want, have fun!" vs MySQL: "There is no configuration until
you need one, then you have to find the docs for it."

> They aren't actually in any better shape than we are, except that
> they supply several "preconfigured" sample files for people to choose
> from.

Which would be a good start, if nothing else…

Best,

David

David E. Wheeler wrote:
> On Jun 4, 2008, at 11:22, Tom Lane wrote:
>
>> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>>> Exactly. The issue is that application developers, who are not DBAs,
>>> have no idea how to tune PostgreSQL, and postgresql.conf is daunting
>>> and confusing. So they use a different database that's "faster".
>>
>> I take it you haven't looked at mysql's configuration file lately.
>
> I'm not much into MySQL, but in the work I've done with it, I've had
> to create /etc/my.cnf myself. There *is* no configuration file
> configuring MySQL until that file is created, is there? So there is no
> configuration to learn at first. I'm not saying that this is
> necessarily admirable -- it's kind of the opposite end of the spectrum
> (PostgreSQL: "Here is every configuration tweak you could ever
> possibly want, have fun!" vs MySQL: "There is no configuration until
> you need one, then you have to find the docs for it."

Tell me how that's better.

If that's what you want, simply remove all the comment lines from your
config file. Problem solved.

>
>> They aren't actually in any better shape than we are, except that
>> they supply several "preconfigured" sample files for people to choose
>> from.
>
> Which would be a good start, if nothing else…

It's been suggested in the past. It is highly debatable that it would
actually be an advance.

cheers

andrew

On Jun 4, 2008, at 12:48, Andrew Dunstan wrote:

>> I'm not much into MySQL, but in the work I've done with it, I've
>> had to create /etc/my.cnf myself. There *is* no configuration file
>> configuring MySQL until that file is created, is there? So there is
>> no configuration to learn at first. I'm not saying that this is
>> necessarily admirable -- it's kind of the opposite end of the
>> spectrum (PostgreSQL: "Here is every configuration tweak you could
>> ever possibly want, have fun!" vs MySQL: "There is no configuration
>> until you need one, then you have to find the docs for it."
>
> Tell me how that's better.
>
> If that's what you want, simply remove all the comment lines from
> your config file. Problem solved.

I didn't say it was better. The point is that it seems to be less
confusing to non-DBAs.

>> Which would be a good start, if nothing else…
>
> It's been suggested in the past. It is highly debatable that it
> would actually be an advance.

Agreed; we've bandied around some better ideas here. Well, I haven't,
I've just kibbitzed. But we can surely do better.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> I'm not much into MySQL, but in the work I've done with it, I've had
> to create /etc/my.cnf myself. There *is* no configuration file
> configuring MySQL until that file is created, is there? So there is no
> configuration to learn at first.

Postgres will start happily with an empty configuration file, too.
What's your point?

regards, tom lane

2008/6/4 David E. Wheeler <david(at)kineticode(dot)com>:
> On Jun 3, 2008, at 20:48, Greg Smith wrote:
>
>> Correct, but completely off-topic regardless. One problem to be solved
>> here is to take PostgreSQL tuning from zero to, say, 50% automatic. Wander
>> the user lists for a few months; the number of completely misconfigured
>> systems out there is considerable, partly because the default values for
>> many parameters are completely unreasonable for modern hardware and there's
>> no easy way to improve on that without someone educating themselves.
>> Getting distracted by the requirements of the high-end systems will give
>> you a problem you have no hope of executing in a reasonable time period.
>
> Exactly. The issue is that application developers, who are not DBAs, have no
> idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing.
> So they use a different database that's "faster".
>

do you thing, so any better config can help? It's not possible. And
you can't tune database without well knowledge of applications that
use database. Any automatic tools are joy for child. But some default
PostgreSQL parameters are not optimal.

> I think that right now postgresql.conf is designed for full-time DBAs,
> rather than folks who might want to target PostgreSQL for an application
> they're developing. We want to attract the latter (without, of course, any
> expense with the former). Changing how configuration works so that it's
> easier to understand and, if possible, at least partly automatically tunable
> would go a long way towards making PostgreSQL friendlier for developers,
> IMHO.
>
> Best,
>
> David
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Wed, 4 Jun 2008, Andreas Pflug wrote:

> When reading this thread, I'm wondering if anybody ever saw a config file for
> a complex software product that was easily editable and understandable.

I would recommend Apache's httpd.conf as an example of something that's
easy to edit and follow. Like any complex product, the comments in the
configuration file itself can't possibly be sufficient by themselves.
But in general I've found Apache's config file to have enough comments to
jog my memory when I'm editing it while not being overwhelming. They
provide enough detail that when I run into a setting I don't understand
there's enough context provided that it's easy to search for more
information.

Poking around with Google for a bit, here's a reasonable sample:
http://webdav.org/goliath/dav_on_x/apache.conf

> IMHO the best compromise in machine and human readability is an XML format.

If the primary PostgreSQL configuration file becomes XML I will quit
working with the project. I'm not kidding. If you think XML is easy to
generate, edit by hand, and use revision control on, we are at such an
fundamental disagreement that I wouldn't even try and directly argue with
you. Instead I'll quote Eric Raymond:

"The most serious problem with XML is that it doesn't play well with
traditional Unix tools. Software that wants to read an XML format needs an
XML parser; this means bulky, complicated programs."
http://www.catb.org/esr/writings/taoup/html/ch05s02.html#id2907018

Let me suggest the following requirement instead which naturally rules it
out: it should be possible for a DBA-level coder to write a simple shell
script that does something useful with the configuration file in order for
having a text-based configuration to be useful in this context. To give a
simple example, I can write a single line [sed|awk|perl] command that will
let me update the value for one parameter in the current postgresql.conf
file. When you can give me a one-liner that does that on an XML file in
any shell language in that class, then we might have something to talk
about.

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

On Jun 4, 2008, at 13:31, Pavel Stehule wrote:

> do you thing, so any better config can help? It's not possible. And
> you can't tune database without well knowledge of applications that
> use database. Any automatic tools are joy for child. But some default
> PostgreSQL parameters are not optimal.

Agreed. But, speaking as an app developer, I'm child-like in my DBA
abilities. I could use a toy to help me with it. :-)

Best,

David

On Jun 4, 2008, at 13:12, Tom Lane wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> I'm not much into MySQL, but in the work I've done with it, I've had
>> to create /etc/my.cnf myself. There *is* no configuration file
>> configuring MySQL until that file is created, is there? So there is
>> no
>> configuration to learn at first.
>
> Postgres will start happily with an empty configuration file, too.
> What's your point?

That it's less daunting for inexperienced users to start with that.
I'm not talking about how things work, I'm talking about what
configurations are present to start with. That's all.

Best,

David

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Wed, 4 Jun 2008, Andreas Pflug wrote:
>> IMHO the best compromise in machine and human readability is an XML format.

> If the primary PostgreSQL configuration file becomes XML I will quit
> working with the project. I'm not kidding.

I have no particular use for XML in this scenario either, but really
this thread seems to be arguing about mostly-irrelevant details. There
is not anything fundamentally broken about keeping configuration in a
text file, as is proven by the fact that all those other packages do it.

The real problem we need to solve is how to allow newbies to have the
system auto-configured to something that more or less solves their
problems. Putting the config settings in XML does not accomplish that,
and neither does putting them inside the database. It might knock a
day or two off the time needed to develop a tool that actually does
solve the newbie's problem ... but it's unlikely that the effort of
changing Postgres to use some other configuration representation would
get repaid through easier tool development.

So I think we should stop worrying about the file format and think about
these two problems:

* Can we present the config options in a more helpful way (this is 99%
a documentation problem, not a code problem)?

* Can we build a "configuration wizard" to tell newbies what settings
they need to tweak?

regards, tom lane

If I can add my 2 cents as the end user, IMHO having the configuration
file
in the xml format is unnecessary and only makes it less user-friendly.

Thanks,
Michael.

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, June 04, 2008 4:58 PM
To: Greg Smith
Cc: Andreas Pflug; Decibel!; Peter Eisentraut;
pgsql-hackers(at)postgresql(dot)org; Josh Berkus
Subject: Re: [HACKERS] Overhauling GUCS

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Wed, 4 Jun 2008, Andreas Pflug wrote:
>> IMHO the best compromise in machine and human readability is an XML
format.

> If the primary PostgreSQL configuration file becomes XML I will quit
> working with the project. I'm not kidding.

I have no particular use for XML in this scenario either, but really
this thread seems to be arguing about mostly-irrelevant details. There
is not anything fundamentally broken about keeping configuration in a
text file, as is proven by the fact that all those other packages do it.

The real problem we need to solve is how to allow newbies to have the
system auto-configured to something that more or less solves their
problems. Putting the config settings in XML does not accomplish that,
and neither does putting them inside the database. It might knock a
day or two off the time needed to develop a tool that actually does
solve the newbie's problem ... but it's unlikely that the effort of
changing Postgres to use some other configuration representation would
get repaid through easier tool development.

So I think we should stop worrying about the file format and think about
these two problems:

* Can we present the config options in a more helpful way (this is 99%
a documentation problem, not a code problem)?

* Can we build a "configuration wizard" to tell newbies what settings
they need to tweak?

regards, tom lane

On Jun 4, 2008, at 1:57 PM, Tom Lane wrote:

> * Can we build a "configuration wizard" to tell newbies what settings
> they need to tweak?

Probably. Given the demographics of a lot of the newbies is
Windows this likely needs to be a pointy-clicky sort of thing
if it's going to be widely useful.

Doing it in a smart way is likely hard, but even a very simple,
crude one would likely be helpful. Doing it as a standalone
GUI editor / Wizard wouldn't be too hard, but it sounds more
like something that should be integrated in pgadmin, perhaps?

Cheers,
Steve

On Jun 4, 2008, at 13:57, Tom Lane wrote:

> So I think we should stop worrying about the file format and think
> about
> these two problems:
>
> * Can we present the config options in a more helpful way (this is 99%
> a documentation problem, not a code problem)?
>
> * Can we build a "configuration wizard" to tell newbies what settings
> they need to tweak?

Amen, Tom. Nicely put.

Thanks,

David

"Pavel Stehule" wrote:

>2008/6/4 David E. Wheeler <david(at)kineticode(dot)com>:
>>
>> Exactly. The issue is that application developers, who are not DBAs, have no
>> idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing.
>> So they use a different database that's "faster".
>
>do you thing, so any better config can help? It's not possible. And
>you can't tune database without well knowledge of applications that
>use database. Any automatic tools are joy for child. But some default
>PostgreSQL parameters are not optimal.

I think it would be an enourmous help for beginners if they had a "simple"
tuning tool which would tell them which values where altered (and possibly
why) from Postgres' default settings based on some basic information.
Like:
- machine hardware (disk layout, OS, installed memory, etc.)
- application usage (no. of clients, read/write activity, etc)

I don't think that such a tool could tune the database perfectly (or even very
good), but at least people new to Postgres would know where to start looking
for tuning it to their needs.

And I am not speaking about end users running an application that uses
Postgres. I talk about application developers like me that port their
application to use Postgres.

Rainer

Tom Lane wrote:

>
> * Can we present the config options in a more helpful way (this is 99%
> a documentation problem, not a code problem)?
>
> * Can we build a "configuration wizard" to tell newbies what settings
> they need to tweak?

It's certainly one thing to create an initial postgresql.conf from
scratch after some inquiry, but a different level of problems to deal
with when offering to change the settings. IMHO initial creation isn't
enough, users will feel even more left alone if there are no tools
helping them further. I guess most users will start tweaking after the
server is already running for a while, with some config already in place.
That's when file format and/or APIs come into play. Preserving comments
and/or using them in a wizard isn't too easy with the current format.

Regards,
Andreas

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?

cheers

andrew

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Tom Lane wrote:
>> * Can we build a "configuration wizard" to tell newbies what settings
>> they need to tweak?

> It's certainly one thing to create an initial postgresql.conf from
> scratch after some inquiry, but a different level of problems to deal
> with when offering to change the settings. IMHO initial creation isn't
> enough, users will feel even more left alone if there are no tools
> helping them further. I guess most users will start tweaking after the
> server is already running for a while, with some config already in place.

Indeed, the wizard should be designed to assist with tweaking an
existing installation. I see no value at all to trying to run it
before/during initdb.

> That's when file format and/or APIs come into play. Preserving comments
> and/or using them in a wizard isn't too easy with the current format.

[ shrug... ] We have already wasted more effort arguing about this
issue than it would take to do something good enough for a wizard
tool. The great thing about simple text formats is that they're pretty
robust. I think all we need to do is comment out any existing
setting(s) of the target variable and add the new setting before the
first such, perhaps with a comment indicating what added it and when.
If the user finds this too ugly, he can tweak it with a text editor.

This is even assuming that the tool needs to edit the file itself,
rather than just give advice. The advice is the hard part, folks;
could we stop obsessing about trivia?

regards, tom lane

On Wed, 4 Jun 2008, Tom Lane wrote:

> The real problem we need to solve is how to allow newbies to have the
> system auto-configured to something that more or less solves their
> problems. Putting the config settings in XML does not accomplish that,
> and neither does putting them inside the database.

The subtle issue here is that what makes sense for the database
configuration changes over time; there's not just one initial generation
and you're done. postgresql.conf files can end up moving from one machine
to another for example. I think something that doesn't recognize that
reality and move toward a "tune-up" capability as well as initial
generation wouldn't be as useful, and that's where putting the settings
inside the database helps so much.

Also, there's a certain elegance to having a optimization tool that works
again either a new installation or an existing one. I personally have
zero interest in a one-shot config generator. It just doesn't solve the
problems I see in the field. Performance starts out just fine even with
the default settings when people first start, and then goes to hell after
the system has been running for a while (and possibly moved to another
machine). By that point nobody wants to mess with their configuration
file unless it's one simple change at a time.

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

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> 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?

How far could we get with the answers to just three questions:

* How many concurrent queries do you expect to have?

* How much RAM space are you willing to let Postgres use?

* How much "overhead" disk space are you willing to let Postgres use?

concurrent queries drives max_connections, obviously, and RAM space
would drive shared_buffers and effective_cache_size, and both of them
would be needed to size work_mem. The third one is a bit weird but
I don't see any other good way to set the checkpoint parameters.

If those aren't enough questions, what else must we ask? Or maybe they
aren't the right questions at all --- maybe we should ask "is this a
dedicated machine or not" and try to extrapolate everything else from
what we (hopefully) can find out about the hardware.

regards, tom lane

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Wed, 4 Jun 2008, Tom Lane wrote:
>> The real problem we need to solve is how to allow newbies to have the
>> system auto-configured to something that more or less solves their
>> problems. Putting the config settings in XML does not accomplish that,
>> and neither does putting them inside the database.

> The subtle issue here is that what makes sense for the database
> configuration changes over time; there's not just one initial generation
> and you're done. postgresql.conf files can end up moving from one machine
> to another for example. I think something that doesn't recognize that
> reality and move toward a "tune-up" capability as well as initial
> generation wouldn't be as useful,

As I just mentioned to someone else, I don't see any point in framing it
as an "initial generation" problem at all. initdb will already give you
settings that work, for some value of "work". The config wizard really
only needs to deal with the case of tuning an existing installation.

> and that's where putting the settings
> inside the database helps so much.

How does it help, pray tell? If you mean being able to see what the
existing settings are, pg_settings already does that.

regards, tom lane

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [080604 20:46]:

> If those aren't enough questions, what else must we ask? Or maybe they
> aren't the right questions at all --- maybe we should ask "is this a
> dedicated machine or not" and try to extrapolate everything else from
> what we (hopefully) can find out about the hardware.

For these three things:
1) max connections/workmem
2) shared buffers/effective cache
3) bgwriter/checkpoint parameters

What are the stats or measures that PostgreSQL produces (or that you
want it too currently doesn't, but you would like it to produce) that
the "masters" (i.e. people who tune PostgreSQL effectively, like you,
Greg, Simon, Robert, Peter, Josh, Jim, etc - sorry if I missed others)
actually use to decide whether to increase or decrease a value?

I tune my postgresql.conf mainly on folklore, and "assimilated
understanding" from reading the lists and blogs... But I haven't come
across (or rather, haven't come across and remembered/bookmarked)
anything that helps someone sample/read any stats or counts to find
bottleneck points which to start tuning.

Stuff like (remembering that I've never had to really work at tuning
because PG has always been "fast enough" for my needs, so take this with
a grain of salt)
* Are backends always writing out dirty buffers because there are no free
ones? This might mean tweaking settings affecting bgwriter.
* Are the evicted buffers ones with really high usage counts? This
might mean an increase shared buffers would help?
* Are we always spilling small amounts of data to disk for sorting? A
a small work_mem increase might help...
* Are all our reads from disk really quick? This probably means OS
pagecache has our whole DB, and means random_page_cost could be
tweaked?

If we could get a definitive list of things like this, or maybe just
comprehensive, or even at least agreed-to-not-be-wrong things to look
at, that would go a long way to documentation *how* to tune PG
effectively, and could lead to any projects that want to tackle
examining a running cluster and suggesting some config changes...

a.
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Jun 4, 2008, at 5:23 PM, Tom Lane wrote:

> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> 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?
>
> How far could we get with the answers to just three questions:
>
> * How many concurrent queries do you expect to have?
>
> * How much RAM space are you willing to let Postgres use?
>
> * How much "overhead" disk space are you willing to let Postgres use?
>
> concurrent queries drives max_connections, obviously, and RAM space
> would drive shared_buffers and effective_cache_size, and both of them
> would be needed to size work_mem. The third one is a bit weird but
> I don't see any other good way to set the checkpoint parameters.
>
> If those aren't enough questions, what else must we ask? Or maybe
> they
> aren't the right questions at all --- maybe we should ask "is this a
> dedicated machine or not" and try to extrapolate everything else from
> what we (hopefully) can find out about the hardware.

I'd be interested in putting together a framework+GUI client to do this
cleanly in a cross-platform (Windows, Linux, Solaris, OS X as a bare
minimum) sort of way, if no-one else already has such a thing.

A framework doesn't get you all the way there, but it makes it a
whole lot easier to work on what base data and information you
need, and how easy it is to map pgsql-performance and #postgresql
gut feel onto something more algorithmic.

Cheers,
Steve

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

On Jun 4, 2008, at 6:28 PM, Greg Smith wrote:
>
>
> 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)

On Jun 4, 2008, at 6:20 PM, Steve Atkins wrote:
> I'd be interested in putting together a framework+GUI client to do
> this
> cleanly in a cross-platform (Windows, Linux, Solaris, OS X as a bare
> minimum) sort of way, if no-one else already has such a thing.

/me makes go together motions, if nobody has any objection

Cheers,
Steve

On Wed, 4 Jun 2008, Aidan Van Dyk wrote:

> * Are backends always writing out dirty buffers because there are no free
> ones? This might mean tweaking settings affecting bgwriter.

What you mean on the first one is "are backends always writing out dirty
buffers becuase there are no *clean* ones"; the server operates with no
*free* buffers as standard operations. Figuring that out is now easy in
8.3 with the pg_stat_bgwriter view.

> * Are the evicted buffers ones with really high usage counts? This
> might mean an increase shared buffers would help?

Evicted buffers must have a 0 usage count. The correct question to ask is
"are buffers never getting high usage counts because they keep getting
evicted too fast?". You can look at that in 8.3 using pg_buffercache,
I've got suggested queries as part of my buffer cache presentation at
http://www.westnet.com/~gsmith/content/postgresql/

> * Are we always spilling small amounts of data to disk for sorting? A
> a small work_mem increase might help...

I was just talking to someone today about building a monitoring tool for
this. Not having a clear way to recommend people monitor use of work_mem
and its brother spilled to disk sorts is an issue right now, I'll whack
that one myself if someone doesn't beat me to it before I get time.

> * Are all our reads from disk really quick? This probably means OS
> pagecache has our whole DB, and means random_page_cost could be
> tweaked?

This is hard to do with low overhead in an OS-independant way. The best
solution available now would use dtrace to try and nail it down. There's
movement in this area (systemtap for Linux, recent discussion at the PGCon
Developer Meeting of possibly needing more platform-specific code) but
it's not quite there yet.

So everything you mentioned is either recently added/documented or being
actively worked on somewhere, and the first two were things I worked on
myself after noticing they were missing. Believe me, I feel the items
that still aren't there, but they're moving along at their own pace.
There's already more tuning knowledge available than tools to help apply
that knowledge to other people's systems, which is why I think a diversion
to focus just on that part is so necessary.

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

* Greg Smith <gsmith(at)gregsmith(dot)com> [080604 22:14]:

> So everything you mentioned is either recently added/documented or being
> actively worked on somewhere, and the first two were things I worked on
> myself after noticing they were missing. Believe me, I feel the items
> that still aren't there, but they're moving along at their own pace.
> There's already more tuning knowledge available than tools to help apply
> that knowledge to other people's systems, which is why I think a diversion
> to focus just on that part is so necessary.

But as an administrator/developer, I don't understand the focus on a new
API for "writing my config" for me... I'ld love a tool that helped me
"analyze" my current running PG database (and yes, that includes getting
*current* settings), and "suggest" config changes, ideally in order that
the tool thinks will make a difference... I can make that change, and
distribute it. That's the easy part. If I really trust the tool then
I'll just blindly run it (depending on it's output format):
$TOOL > $PGDATA/postgresql.conf
in which case, I don't care if it groked any of my previous comments and
cruft. Otherwise, I'll look at it, and integrate some (or all) of the
changes into postgresql.conf using my preferred method of
commenting/SCM/quirks.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Wed, 4 Jun 2008, Aidan Van Dyk wrote:

> I'd love a tool that helped me "analyze" my current running PG database
> (and yes, that includes getting *current* settings), and "suggest"
> config changes

Fine. To demonstrate why the overhaul is needed, let's start designing a
simple tool whose sole purpose in life is to suggest new settings for
shared_buffers and work_mem. Say we want to show people their current
setting and what we'd recommend instead.

I just created a new cluster on my laptop. It has the following in the
postgresql.conf:

shared_buffers = 32MB # min 128kB or max_connections*16kB
# (change requires restart)
#work_mem = 1MB # min 64kB

Say I first start designing such a tool by trying to read the
postgresql.conf file to figure out what these values are set to. In order
to accomplish that, I need to parse the whole file correctly, doing things
like turning "32MB" into the actual numeric value so my program can make
decisions based on its value[1]. This basically requires someone writing
a tuning tool replicate the GUC parsing code, which is crazy; at this
point you've already lost most potential tool authors.

But you're smarter than that; instead you use pg_settings:

psql=# select name,setting,unit from pg_settings where name='shared_buffers' or name='work_mem';
name | setting | unit
----------------+---------+------
shared_buffers | 4096 | 8kB
work_mem | 1024 | kB

Now: what do you tell the user their current value is? The way the
postgresql.conf is parsed into memory is lossy; at this point you don't
know anymore what units where specified in the original file. If someone
sees their current setting shown as "4096" but they know they set it to
"32MB", they'll end up confused unless they understand the whole page size
concept--and if they understood that, they'd automatically be disqualified
from being the type of user our theoretical tool is targeted toward. If
you try and make things more user-friendly by always showing the most
human readable version, what about the person who ended up setting this
parameter because they copied an old config file that recommended setting
it to 4096. You show it to them as "32MB"; they'll also be confused and
blame the tool for being bad.

And even if you work through all that, to give good advice here you have
to know things like that shared_buffers is a server parameter requiring
restart, while work_mem is a per-session parameter. Right now, the only
way to know all that is for tool authors to assemble their own database
and keep it up to date with each release. And you just lost another set
of potential authors with that realization.

Next up, we manage to work through all those issues, and someone happily
follows our advice and gets their file updated with a much larger value
for work_mem. Yeah, we are heroes! Or not. You see, in the config file
we just helpful updated for them was this comment just above that setting:

# OMG don't set this too high or the Linux OOM killer will
# take down the server!

(This is not a theoretical example; I have found variations on that text
in two postgresql.conf files and I vaguely recall Josh mentioned running
into it as well).

And now you just *crashed their server* by giving bad advice that was
clearly against the invaluable comment history in the file already. Oh,
but where are those comments located at? Before the setting? After the
setting? In a group at the top? Who can say? Since there's no strong
standard, people put them all over the place.

I don't know about you, but I'm too busy to spend a bunch of time writing
a tool to work around all these issues knowing it is always going to be
fragile, incomplete, and need updating with every GUC change no matter
what. Instead, reconsider http://wiki.postgresql.org/wiki/GUCS_Overhaul ,
having gotten a taste of the motivation behind those changes, and consider
how this would play out after those improvements.

The approach where you connect to the database and query is the easy path.
There is never a reason to consider parsing postgresql.conf. Anybody who
knows how to write a simple script that connects to a database and reads a
table (basically anyone who's written the database client equivilent of
"hello, world") can feel like a potential tool author.

All the information about the postgresql.conf side of every setting is
retained in case you want to show where they came from, or to generate a
new file that's as similar as possible to the original.

Any recommendations you suggest can be trivially annotated with whether
you need to consider max_connections because it's per-session, and whether
people need to restart the server or can just send it a signal, and that
will continue to be the case in the future with minimal work on the tool
author's part.

The defaults are now available, so that it's easy to figure out what
people changed. That is sometimes handy to include as part of this sort
of analysis, and it's necessary to provide improvements like a "strip the
unnecessary junk out of this file" that many people would like from this
sort of tool.

When you show people that you recommend increasing a value to something
larger, any comments about that setting will be shown and they'll know not
to follow the tool's advice if there's a history there.

This seems like such a better place to be that I'd rather drive toward the
server-side changes necessary to support it rather than fight the
difficult tool creation problems. That's why the "focus on a new API for
'writing my config' for me"; that particular goal is just one part of a
set of revisions that streamline the tool creation process in a not
necessarily obvious way. Unless, of course, you've tried to write a
full-circle config tuning tool, in which case most of the proposed changes
in this overhaul jump right out at you.

[1] In the shared_buffers case, it may be possible to just recommend a
value without caring one bit what the current one is. But for work_mem,
you really need to actually understand the value if you want any real
intelligence that combines that information with the maximum connections,
so that you can compute how much memory is left over for things like
effective_cache_size.

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

Tom Lane wrote:
> * How much "overhead" disk space are you willing to let Postgres use?
>
> ... The third one is a bit weird but
> I don't see any other good way to set the checkpoint parameters.

The way I think about the checkpoint settings is:

1. Set checkpoint_timeout to the max. time you're willing to spend in
recovery in case of crash or power loss.

2. Set checkpoint_segments to a high value. "High" meaning high enough
that you'll never reach it in practice. The purpose is just to keep you
from running out of disk space if something weird happens.

The amount of downtime one is willing to accept in case of power loss is
a good question to ask because it doesn't require any knowledge of how
PostgreSQL works; it can be answered directly from the application
requirements. And if the DBA/developer don't know the answer, he needs
to figure it out, because it's a very important question not only for
the database but in general.

I believe checkpoint_timeout correlates quite well with the max. time
required in recovery. If it took 10 minutes to generate X amount of WAL,
replaying that WAL will need to do at most the same amount of I/O, which
should take roughly the same amount of time, regardless of whether the
I/O was sequential or random. If the system wasn't busy doing updates
during between the checkpoints, it will of course take less.

As with all settings, the tool will need to explain the tradeoff.
Smaller checkpoint_timeout means more checkpointing which means more I/O
which means less average TPS and more WAL generated (because of
full_page_writes).

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Guys,

A configuration wizard would be nice, but it would be a good start to
add a section to the manual on how to do the basic tuning. AFAICS we
don't have one. Clear instructions on how to set the few most important
settings like shared_buffers and checkpoint_timeout/segments would
probably be enough, with a link to the main configuration section that
explains the rest of the settings.

If people don't read the manual, we can add a link to it from
postgresql.conf.sample, add a screen to the Windows installer suggesting
to read it, or even open postgresql.conf in Notepad.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, 5 Jun 2008, Heikki Linnakangas wrote:

> A configuration wizard would be nice, but it would be a good start to add a
> section to the manual on how to do the basic tuning. AFAICS we don't have
> one. Clear instructions on how to set the few most important settings like
> shared_buffers and checkpoint_timeout/segments would probably be enough, with
> a link to the main configuration section that explains the rest of the
> settings.

It hasn't gelled yet but I'm working on that. Most of the text needed is
now linked to at http://wiki.postgresql.org/wiki/Performance_Optimization

I already talked with Chris Browne about merging his document I put first
in that list with useful pieces from some of mine into one more
comprehensive document on the Wiki, covering everything you mention here.
If we took a snapshot of that when it's done and dumped that into the
manual, I don't think that would be a problem to wrap up before 8.4 is
done. I'd like to include a link to the above performance page in that
section of the manual as well, both so that people are more likely to find
fresh content as well as to give them pointers toward more resources than
the manual can possibly cover.

> If people don't read the manual, we can add a link to it from
> postgresql.conf.sample, add a screen to the Windows installer suggesting
> to read it, or even open postgresql.conf in Notepad.

They don't. Putting pointers toward a relatively simple performance
tuning document a bit more in people's faces might help lower some of the
criticism the project takes over providing low defaults for so many
things.

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

On Thu, Jun 05, 2008 at 01:23:53AM +0200, Rainer Bauer wrote:
> I think it would be an enourmous help for beginners if they had a "simple"
> tuning tool which would tell them which values where altered (and possibly
> why) from Postgres' default settings based on some basic information.
> Like:
> - machine hardware (disk layout, OS, installed memory, etc.)
> - application usage (no. of clients, read/write activity, etc)

It would be possible to make a program that worked like:
# pg_autotune
Detected 4GB memory, 2.3GB free
Measured random_page_cost=2.3
Select expected usage: (d)edicated, (n)ormal, (t)iny
> t
Shared_buffers 64MB
Configuration stored to /etc/postgresql/8.3/postgresql.conf
#

This would probably solve most issues.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> A configuration wizard would be nice, but it would be a good start to
> add a section to the manual on how to do the basic tuning.

+1. If we can't write an explanation of what to do, we certainly aren't
going to be able to implement it in a wizard. Agreeing on what to put
in the manual would also go a long way towards providing an
implementation spec for the wizard ...

regards, tom lane

Greg Smith wrote:

> 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.

What I think this says is that we should be pushing Magnus more to
continue work on the configuration API thing he was designing.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Greg Smith wrote:
>
>> 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.
>
> What I think this says is that we should be pushing Magnus more to
> continue work on the configuration API thing he was designing.

There's some magic in pgAdmin to parse and write the file. You could
take a look a that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:
> Alvaro Herrera wrote:

>> What I think this says is that we should be pushing Magnus more to
>> continue work on the configuration API thing he was designing.
>
> There's some magic in pgAdmin to parse and write the file. You could
> take a look a that.

That's what they want to get rid of. It's a problem that every GUI
writer needs to reimplement that functionality; ISTM this is something
worth having in the backend.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Heikki Linnakangas wrote:
> Alvaro Herrera wrote:
>> Greg Smith wrote:
>>
>>> 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.
>>
>> What I think this says is that we should be pushing Magnus more to
>> continue work on the configuration API thing he was designing.
>
> There's some magic in pgAdmin to parse and write the file. You could
> take a look a that.

Not really. You don't want to go there. We really need a "proper API"
for it, and the stuff in pgAdmin isn't even enough to base one on.

//Magnus

On Thu, 5 Jun 2008, Magnus Hagander wrote:

> We really need a "proper API" for it, and the stuff in pgAdmin isn't
> even enough to base one on.

I would be curious to hear your opinion on whether the GUC overhaul
discussed in this thread is a useful precursor to building such a proper
API.

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

Greg Smith wrote:
> On Thu, 5 Jun 2008, Magnus Hagander wrote:
>
>> We really need a "proper API" for it, and the stuff in pgAdmin isn't
>> even enough to base one on.
>
> I would be curious to hear your opinion on whether the GUC overhaul
> discussed in this thread is a useful precursor to building such a
> proper API.

Since I'm the guy who initially wrote that config file editing stuff, I
feel somehow addressed.
The answer is a clear ABSOLUTELY.

- The current implementation is able to edit the file directly or
through pgsql functions; any format change will affect that function
immediately.
- If documentation is enhanced by adding more comments in the
postgresql.conf file, this won't help the editor because it can't rely
on it to present help and hints to the user. It needs the comments/help
in pg_settings or alike.

Regards,
Andreas

Greg Smith wrote:
> On Thu, 5 Jun 2008, Magnus Hagander wrote:
>
>> We really need a "proper API" for it, and the stuff in pgAdmin isn't
>> even enough to base one on.
>
> I would be curious to hear your opinion on whether the GUC overhaul
> discussed in this thread is a useful precursor to building such a proper
> API.

I must say that I am confused by this thread. What's the discussed GUC
overhaul? Things that I vaguely recall being proposed are (in the order
they came to mind):

(1) Add a lot more comments to each setting
(2) Add documentation links to each setting
(3) Move more frequently used settings to the top of the file
(4) Ship different sample config files
(5) Create an expert system to suggest tuning
(6) Other random ideas (XML, settings in database, others?)

To me, there are two ideas that are doable right now, which are (2) and
(4). (1) seems to be a step backwards in pg_hba.conf experience, and we
would have to maintain duplicate documentation. (3) seems messy. (5)
is a lot of work; do we have volunteers? As for (6), the two examples I
give can be easily dismissed.

(2) and (4) do not seem necessary to get the config API built.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Steve Atkins wrote:
>
> I'd be interested in putting together a framework+GUI client to do this
> cleanly in a cross-platform (Windows, Linux, Solaris, OS X as a bare
> minimum) sort of way, if no-one else already has such a thing.
>
This is a great idea, and I was thinking along the same line. The
framework can provide generic interfaces for the GUI/Web client, and
leave it up to the OS to provide the needed data.

-Robert

Tom Lane wrote:
> This is even assuming that the tool needs to edit the file itself,
> rather than just give advice. The advice is the hard part, folks;
> could we stop obsessing about trivia?
+1. IMHO, the tool doesn't need to worry about generating a prettied
version of postgresql.conf. It should just inform the user about the
appropriate settings or create a postgresql.conf.recommend and have the
user update postgresql.conf himself.

-Robert

On Thu, 5 Jun 2008, Alvaro Herrera wrote:

> I must say that I am confused by this thread. What's the discussed GUC
> overhaul?

http://wiki.postgresql.org/wiki/GUCS_Overhaul

I drop that URL in every other message in hopes that people might start
commenting on it directly if they see it enough; the fact that you're
confused says I may need to keep that up :(

> (1) Add a lot more comments to each setting
> (2) Add documentation links to each setting
> (3) Move more frequently used settings to the top of the file
> (4) Ship different sample config files
> (5) Create an expert system to suggest tuning
> (6) Other random ideas (XML, settings in database, others?)
>
> To me, there are two ideas that are doable right now, which are (2) and
> (4). (1) seems to be a step backwards in pg_hba.conf experience, and we
> would have to maintain duplicate documentation. (3) seems messy. (5)
> is a lot of work; do we have volunteers? As for (6), the two examples I
> give can be easily dismissed.
> (2) and (4) do not seem necessary to get the config API built.

(1) is in that proposal but is strictly optional as something to put in
the configuration file itself. The idea behind (2) is to enable tool
authors to have an easier way to suggest where to head for more
information. I'd like for it to be trivial for a tool to say "Suggested
value for <x> is <y>; see
http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
for more information". I know what most of the settings I tinker with do,
but even I'd like it to be easier to find the right spot in the manual;
for newbies it's vital. You are correct that (2) isn't strictly necessary
here, but it's valuable and will be easier to wrap into this than to bolt
on later.

(3) (4) (5) and (6) were off-topic diversions.

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

Tom Lane wrote:
> If those aren't enough questions, what else must we ask? Or maybe they
> aren't the right questions at all --- maybe we should ask "is this a
> dedicated machine or not" and try to extrapolate everything else from
> what we (hopefully) can find out about the hardware.
>
I think probably a combination of high and low-level questions and make
the low-level (more specific) questions optional since some users may
not be able to provide low-level info. Here's a rough idea of how I
envision this tool should work.

$ pg_config_wizard

Is this machine dedicated to Postgres? (y/n) n (now tool
auto-discovers available HW resources)
Your system has 32GB memory. What percentage do you want to allocate to
Postgres? (1=50%, 2=33%, 3=25%, etc) 1
What type of workload? (OLTP, DSS, etc)
...

At the end, the tool runs for a while checking to see if certain
thresholds are reached to determine which parameters need to be
increased. The tool would change the parameters causing the
bottlenecks, rerun Postgres/workload, and iterate a few times until the
results are satfisfactory. Write the recommended settings to
postgresql.conf.recommend and let the user update postgresql.conf
himself or whatever.

I update my postgresql.conf, rerun the app and see 100% increased in
throughput :-)

-Robert

Tom Lane wrote:
> How far could we get with the answers to just three questions:
>
> * How many concurrent queries do you expect to have?
> * How much RAM space are you willing to let Postgres use?
> * How much "overhead" disk space are you willing to let Postgres use?

+1 to this approach - these are the kinds of questions that
make sense to me when first setting up a new installation.
They sound useful for both large servers and tiny (salesguy
laptop for demos) installations.

> If those aren't enough questions, what else must we ask?

* Perhaps something to guess FSM settings? I think FSM is
tunable I most often get wrong with more painful
consequences (bloat) than other tunables.
My approach is to have cron run database-wide vacuums
even on systems with autovacuum just to see the log
messages about FSM.

* Something to tune vacuum delay? Perhaps:
How much I/O bandwidth can be dedicated to Postgres
background activities?

Steve Atkins wrote:
> ... cross-platform (Windows, Linux, Solaris, OS X as a bare
> minimum)

I wonder how cross-platform the tuning algorithm itself is.

I could also imagine that decisions like "do I let the OS page
cache, or postgres's buffer cache get most of the memory" are
extremely OS dependent.

Also, the configuration tool would be even more useful if it could
offer extra platform-specific advice like "hey, I see you're using
this filesystem. You should be using this journaling option for WAL
and this other one for data", or "on your system you should be using
this fsync method", or "use relatime or noatime when mounting your
disk".

* Greg Smith <gsmith(at)gregsmith(dot)com> [080605 15:17]:
> On Thu, 5 Jun 2008, Alvaro Herrera wrote:
>
> >I must say that I am confused by this thread. What's the discussed GUC
> >overhaul?
>
> http://wiki.postgresql.org/wiki/GUCS_Overhaul
>
> I drop that URL in every other message in hopes that people might start
> commenting on it directly if they see it enough; the fact that you're
> confused says I may need to keep that up :(

I've read it. A couple times now. And I like lots of it.

> >(1) Add a lot more comments to each setting
> >(2) Add documentation links to each setting
> >(3) Move more frequently used settings to the top of the file
> >(4) Ship different sample config files
> >(5) Create an expert system to suggest tuning
> >(6) Other random ideas (XML, settings in database, others?)

> (3) (4) (5) and (6) were off-topic diversions.

But, right from the above mentioned page:

*Goals*

By shifting from a model where postgresql.conf is document-formatted
and hand-edited to one where it's machine generated, it becomes vastly
easier to write simple utilities to manage these settings. Right now,
the big "obstacle" to things like SET PERSISTENT is "how to we
preserve the hand-edited comments in the file" -- and the answer is we
don't.

This little goal leads to:

* By having a generated postgresql.conf and an easy way to generate
it, writing autoconfiguration scripts (as well as shortcuts like SET
PERSISTENT) become vastly easier.

And later:

There needs to be a way to modify the underlying settings and save
that into a new machine-generated postgresql.conf file. Is
implementing SET PERSISTENT sufficient for that?

I think that these parts, seemingly "snuck into" the GUC overhaul
proposal is what people like me a wary of. People like me don't want
to have postgresql.conf be *only* a machine-generated file, which I am
not allowed to edit anymore because next DBA doing a "SET PERSISTANT"
type of command is going to cause postgres to write out something else,
over-writing my carefully documented reason for some particular setting.

But the big issue I have (not that it really matters, because I'm not
one of the ones working on it, so I please don't take this as me telling
anyone what they can or can't do) is that that goal doesn't solve any of
the listed problems stated in the proposal:

1. Most people have no idea how to set these.

2. The current postgresql.conf file is a huge mess of 194 options,
the vast majority of which most users will never touch.

3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
and settings.sgml), which are only synched with each other manually.

4. We don't seem to be getting any closer to autotuning.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Thu, 5 Jun 2008, Aidan Van Dyk wrote:

> People like me don't want to have postgresql.conf be *only* a
> machine-generated file, which I am not allowed to edit anymore because
> next DBA doing a "SET PERSISTANT" type of command is going to cause
> postgres to write out something else, over-writing my carefully
> documented reason for some particular setting.

This is why there's the emphasis on preserving comments as they pass into
the GUC structure and back to an output file. This is one of the
implementation details I haven't fully made up my mind on: how to clearly
label user comments in the postgresql.conf to distinguish them from
verbose ones added to the file. I have no intention of letting manual
user edits go away; what I'm trying to do here (and this part is much more
me than Josh) is make them more uniform such that they can co-exist with
machine edits without either stomping on the other. Right now doing that
is difficult, because it's impossible to tell the default comments from
the ones the users added and the current comment structure bleeds onto the
same lines as the settings.

> But the big issue I have (not that it really matters, because I'm not
> one of the ones working on it, so I please don't take this as me telling
> anyone what they can or can't do) is that that goal doesn't solve any of
> the listed problems stated in the proposal
> 1. Most people have no idea how to set these.

Making it much easier to build recommendation tools is how this helps
here.

> 2. The current postgresql.conf file is a huge mess of 194 options,
> the vast majority of which most users will never touch.

The proposed pg_generate_conf tool includes options to spit out a basic
configuration file instead of the complete one.

> 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf,
> and settings.sgml), which are only synched with each other manually.

The proposal throws away having a separate postgresql.conf file, so that
reduces it from 3 places to 2. That's moving in the right direction

> 4. We don't seem to be getting any closer to autotuning.

If you try to build a tuning tool, these areas end up being the
unnecessarily hard parts.

Thanks for the comments on the proposal. I'm only bothering to respond to
messages like yours now, am deleting all of the continuing attemps to
divert the discussion over to parameter tuning details or expanding the
scope here.

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

On Jun 5, 2008, at 14:47, Greg Smith wrote:

> This is why there's the emphasis on preserving comments as they pass
> into the GUC structure and back to an output file. This is one of
> the implementation details I haven't fully made up my mind on: how
> to clearly label user comments in the postgresql.conf to distinguish
> them from verbose ones added to the file. I have no intention of
> letting manual user edits go away; what I'm trying to do here (and
> this part is much more me than Josh) is make them more uniform such
> that they can co-exist with machine edits without either stomping on
> the other. Right now doing that is difficult, because it's
> impossible to tell the default comments from the ones the users
> added and the current comment structure bleeds onto the same lines
> as the settings.

How about a simple rule, such as that machine-generated comments start
with "##", while user comments start with just "#"? I think that I've
seen such a rule used before. At any rate, I think that, unless you
have some sort of line marker for machine-generated comments, there
will be no way to tell them apart from user comments.

Other possibilities for machine-comments:

## Machine comment
### Machine comment
#! Machine comment
#@ Machine comment
#$ Machine comment
#^ Machine comment
# Machine comment

I actually kinda like "#!". It's distinctive and unlikely to appear in
a user comment. Anyway, just food for thought.

Best,

David

David E. Wheeler wrote:

> How about a simple rule, such as that machine-generated comments start
> with "##", while user comments start with just "#"? I think that I've
> seen such a rule used before. At any rate, I think that, unless you have
> some sort of line marker for machine-generated comments, there will be no
> way to tell them apart from user comments.

FWIW smb.conf uses ; for one purpose and # for the other.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, 5 Jun 2008, Alvaro Herrera wrote:

> FWIW smb.conf uses ; for one purpose and # for the other.

They're actually combining the way UNIX files use # with how Windows INI
files use ; in a config file context, which I personally find a little
weird.

I was already considering keeping user comments as # while making all
system-inserted ones #! ; many people are already used to #! having a
special system-related meaning from its use in UNIX shell scripting which
makes it easier to remember.

I think the next step to this whole plan is to generate a next-gen
postgresql.conf mock-up showing what each of the outputs from the
pg_generate_conf tool might look like to get feedback on that; it will
make what is planned here a bit easier to understand as well.

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

On Jun 5, 2008, at 17:53, Greg Smith wrote:

> I was already considering keeping user comments as # while making
> all system-inserted ones #! ; many people are already used to #!
> having a special system-related meaning from its use in UNIX shell
> scripting which makes it easier to remember.

Oooh, yeah. I hadn't even thought of that! I was just looking at
characters on my keyboard and typing them in to see which ones I
thought were most distinctive. This may be part of the reason I
thought that #! was distinctive. :-)

Best,

David

David E. Wheeler wrote:
> On Jun 5, 2008, at 14:47, Greg Smith wrote:
>
>> This is why there's the emphasis on preserving comments as they pass
>> into the GUC structure and back to an output file. This is one of the
>> implementation details I haven't fully made up my mind on: how to
>> clearly label user comments in the postgresql.conf to distinguish them
>> from verbose ones added to the file. I have no intention of letting
>> manual user edits go away; what I'm trying to do here (and this part
>> is much more me than Josh) is make them more uniform such that they
>> can co-exist with machine edits without either stomping on the other.
>> Right now doing that is difficult, because it's impossible to tell the
>> default comments from the ones the users added and the current comment
>> structure bleeds onto the same lines as the settings.
>
> How about a simple rule, such as that machine-generated comments start
> with "##", while user comments start with just "#"? I think that I've
> seen such a rule used before. At any rate, I think that, unless you have
> some sort of line marker for machine-generated comments, there will be
> no way to tell them apart from user comments.

What comments do we consider machine-generated? Just the ones used to
comment out settings, like

#shared_buffers = 32MB

or something else?

If the automatic tool lets alone all other kind of comments, I think
we're fine. In fact, it wouldn't necessarily need to modify those
comments either, it could simply add a new setting line below that:

#shared_buffers = 32MB
shared_buffers = 1024MB

For extra safety, it could comment out old settings, perhaps with
something like this:

#shared_buffers = 32MB
#shared_buffers = 1024MB # commented out by wizard on 2008-06-05
shared_buffers = 2048MB

This would preserve a full change history in the file. It would become
quite messy after a lo of changes, of course, but a user can trim the
history by hand if he wants to.

Or perhaps we should explicitly mark the settings the tool has
generated, and comment out:

#shared_buffers = 32MB # commented out by wizard on 2008-06-05
shared_buffers = 1024MB # automatically set by wizard on 2008-06-05

That way the tool could safely replace automatically set settings,
without replacing manually set ones without leaving a clear trace of
what happened.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

David E. Wheeler wrote:
>
> How about a simple rule, such as that machine-generated comments start
> with "##", while user comments start with just "#"? I think that I've
> seen such a rule used before. At any rate, I think that, unless you
> have some sort of line marker for machine-generated comments, there
> will be no way to tell them apart from user comments.

Two heretical questions:
Do we need user generated comments at all?
I can't remember ever having used any comment in postgresql.conf.

Why do so many people here insist on editing postgresql.conf as primary
means of changing config params?
Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
postgresql.conf or doing it manually?

Looking around for different approaches, network appliances come to my
mind, e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
- use a command line (using ssh or telnet, eqivalent to psql); WRITE
MEMORY to make the changes survive a reboot.
- use a web interface (or similar tool)
- use tftp to up/download the complete config in and out, editing the
file. User comments will be lost, with the exception of those that have
been applied with special comment commands (equivalent to "comment on").

Regards,
Andreas

"Andreas Pflug" <pgadmin(at)pse-consulting(dot)de> writes:

> Why do so many people here insist on editing postgresql.conf as primary means
> of changing config params?
> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
> postgresql.conf or doing it manually?

no, it's awful.

> Looking around for different approaches, network appliances come to my mind,
> e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
> - use a command line (using ssh or telnet, eqivalent to psql); WRITE MEMORY to
> make the changes survive a reboot.
> - use a web interface (or similar tool)
> - use tftp to up/download the complete config in and out, editing the file.
> User comments will be lost, with the exception of those that have been applied
> with special comment commands (equivalent to "comment on").

And in every major installation I've seen people use the last option. They
treat the original text file which is kept elsewhere -- normally checked into
some revision control system, tracked and managed like source code -- as the
canonical and authoritative version.

The fact that you have to go through an especially complicated and awkward
process to load it onto the switches is just a technical detail to be worked
around. And the fact that somebody could have altered the configuration
directly on the switch and forgotten to write it to the configuration file is
a danger to be worked around with policy, not a benefit.

Essentially what good cisco network admins are doing here is working around
the awkward interface using policy and replacing it with a static text
configuration file kept elsewhere.

Experience with other attempts at automatic tools to edit configuration files
like webmin etc are that editing text files which are also under user control
is just a terrible idea. You can make it almost work but it's always fragile
and can't keep up with the flexibility of free-form text and the creativity of
users.

What I would suggest is going back to the plan when we added include files.
Have an automatically generated config file which is freshly re-generated each
time and doesn't try to preserve any user-formatting. Users then include that
file at the top of postgresql.conf and override any of the settings by setting
them in postgresql.conf after the include.

The important thing here is to separate files which are "under postgres
control" and those "under user control". That doesn't necessarily mean we have
to ban users from touching the automatically generated config file or switch
formats, but it relieves us of any responsibility for maintaining free-form
text.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

Gregory Stark wrote:
> "Andreas Pflug" <pgadmin(at)pse-consulting(dot)de> writes:
>
>
>> Why do so many people here insist on editing postgresql.conf as primary means
>> of changing config params?
>> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
>> postgresql.conf or doing it manually?
>>
>
> no, it's awful.
>
>

So I wonder why you accept it when configuring schemas. What's the big
difference between setting a config param, and creating a table?

And ultimately, the config param file format may well look like an SQL
command file, restricted to SET only.

> And in every major installation I've seen people use the last option. They
> treat the original text file which is kept elsewhere -- normally checked into
> some revision control system, tracked and managed like source code -- as the
> canonical and authoritative version.
>

That's how you'd have to manage the schema sources too, no? Your
comments are lost as well after schema creation scripts are executed,
and manual changes may interfere with that.

Regards,
Andreas

"Andreas Pflug" <pgadmin(at)pse-consulting(dot)de> writes:

> Gregory Stark wrote:
>> "Andreas Pflug" <pgadmin(at)pse-consulting(dot)de> writes:
>>
>>> Why do so many people here insist on editing postgresql.conf as primary means
>>> of changing config params?
>>> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
>>> postgresql.conf or doing it manually?
>>
>> no, it's awful.
>
> So I wonder why you accept it when configuring schemas. What's the big
> difference between setting a config param, and creating a table?

Yes, this is a big impedence mismatch between the old-school DBMS (with
emphasis on the MS part of the acronym) architecture and more modern
approaches. You don't configure your web site with "ALTER PAGE home.html SET
FORM contactus INPUT TYPE SUBMIT" either...

This is partly out of necessity though as those database objects contain
*data*. So merely replacing them with new data objects doesn't give the system
enough information to understand what to do with that data. The DDL commands
which modify the schema give that kind of data mutating instruction.

And I'll note that DBAs go to *great* lengths to do exactly the same kind of
thing I described the CISCO DBAs doing. Usually that means doing an ALTER
command and separately editing a creation script and trying to keep the two in
sync. It's a huge burden.

> And ultimately, the config param file format may well look like an SQL command
> file, restricted to SET only.

So all you have is our existing file except with an additional layer of
quoting to deal with, a useless SET keyword to annoy users, and a file that
you need a bison parser to deal instead of a simple keyword-value syntax that
sed can manipulate.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Gregory Stark wrote:
> So all you have is our existing file except with an additional layer of
> quoting to deal with, a useless SET keyword to annoy users, and a file that
> you need a bison parser
Don't you think that's a little over the top, throwing bison at the
simple task to extend postgresql.conf scanning so it accepts --, /**/
and SET?

> to deal instead of a simple keyword-value syntax that
> sed can manipulate.
>
>
sed seems to be THE killer application... Apart from the fact that sed
easily could identify SET, my answer would be to use psql to modify the
config, not sed.

Text config files are NOT friendly for beginner and mediocre users. IMHO
the current restriction on GUC changes is a major obstacle towards pgsql
tuning tools, e.g. written as a Google SoC project. Graphic tools aren't
too popular at pgsql-hackers, but please contemplate a little how much
pgadmin may have contributed to the pgsql usage boost, esp. on windows.

Regards,
Andreas

Am Mittwoch, 4. Juni 2008 schrieb Aidan Van Dyk:
> > When reading this thread, I'm wondering if anybody ever saw a config
> > file for a complex software product that was easily editable and
> > understandable. I don't know one. If there was one, it'd be nice to know
> > it so we can learn from it.
>
> PostreSQL, Apache, X.org

And note that one of the major advances in X.org over XFree86 was that all the
useless garbage was removed from the configuration file, so that the final
and usable configuration fits on one screen, and you can even write it from
memory if you dare to.

Am Mittwoch, 4. Juni 2008 schrieb Tom Lane:
> * Can we present the config options in a more helpful way (this is 99%
> a documentation problem, not a code problem)?

ack

> * Can we build a "configuration wizard" to tell newbies what settings
> they need to tweak?

Some questions to clarify this:

- What settings do "newbies" (or anyone else) typically need to change?
Please post a list.

- What values would you set those settings to? Please provide a description
for arriving at a value, which can later be transformed into code. Note that
in some cases, not even the documentation provides more than handwaving help.

- If we know better values, why don't we set them by default?

Another orthogonal stumbling block on the way to making all of this automatic
is that the surely criticial shared_buffers setting will in any useful
configuration require messing around with kernel settings that no PostgreSQL
tool can really help with.

Am Donnerstag, 5. Juni 2008 schrieb Tom Lane:
> How far could we get with the answers to just three questions:
>
> * How many concurrent queries do you expect to have?
>
> * How much RAM space are you willing to let Postgres use?
>
> * How much "overhead" disk space are you willing to let Postgres use?

This is surely a good start. We could optimize this even more by saying, disk
space is cheap, so let's just use a much higher default setting for
checkpoint_segments. (If PostgreSQL is installed but not actually used, not
all the space is actually going to be used anyway.) Then, increase
max_connections a bit; that should be OK for most users. Then you are left
with the memory settings, and those need kernel tuning in most cases, so any
automation tool loses. Hmm.

* Andreas Pflug <pgadmin(at)pse-consulting(dot)de> [080606 04:50]:
> David E. Wheeler wrote:
> >
> >How about a simple rule, such as that machine-generated comments start
> >with "##", while user comments start with just "#"? I think that I've
> >seen such a rule used before. At any rate, I think that, unless you
> >have some sort of line marker for machine-generated comments, there
> >will be no way to tell them apart from user comments.
>
> Two heretical questions:
> Do we need user generated comments at all?
> I can't remember ever having used any comment in postgresql.conf.

Well, I have, mainly to leave traces as to what and why I've changed
something from a default/previous value, based on "chagne, hope, and
test" style tuning.

And the one that Greg brought up earlier:
## Don't make this too high, or linux OOM will kill ther server!!!

I'm guessing that comment was put in for a reason too.

> Why do so many people here insist on editing postgresql.conf as primary
> means of changing config params?
> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
> postgresql.conf or doing it manually?

I would guess main for 3 reasons:

1) It's always been that way, it's the traditional "unix" way, and many
admins are comfortable being able to comment/make quick test
changes with simple text files.
2) Their postgresql.conf are distrubuted/synced/generated from central
provisioning/SCM system
3) PostgreSQL the server isn't even running

> Looking around for different approaches, network appliances come to my
> mind, e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
> - use a command line (using ssh or telnet, eqivalent to psql); WRITE
> MEMORY to make the changes survive a reboot.
> - use a web interface (or similar tool)
> - use tftp to up/download the complete config in and out, editing the
> file. User comments will be lost, with the exception of those that have
> been applied with special comment commands (equivalent to "comment on").

And, of course, other server software comes to mind too:
apache, bind, postfix, sendmail, dhcpd, sshd, cron, xinetd... ;-)

a.
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

* Peter Eisentraut <peter_e(at)gmx(dot)net> [080606 08:25]:
> Am Mittwoch, 4. Juni 2008 schrieb Aidan Van Dyk:
> > > When reading this thread, I'm wondering if anybody ever saw a config
> > > file for a complex software product that was easily editable and
> > > understandable. I don't know one. If there was one, it'd be nice to know
> > > it so we can learn from it.
> >
> > PostreSQL, Apache, X.org
>
> And note that one of the major advances in X.org over XFree86 was that all the
> useless garbage was removed from the configuration file, so that the final
> and usable configuration fits on one screen, and you can even write it from
> memory if you dare to.

Ya, and unfortunately, I haven't got my dual-head to work with recent
distro X.org either, probably because I haven't found how how/where/what
to set in Xorg.conf for new version (and my old one doesn't "just work"
on new X.org either). So I've got an old x.org version pinned, even
though I'm pretty sure that a newer X.org is probably better, dispite my
lack of ability to configure it.

Go figure ;-)

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

"Andreas Pflug" <pgadmin(at)pse-consulting(dot)de> writes:

> Gregory Stark wrote:
>> So all you have is our existing file except with an additional layer of
>> quoting to deal with, a useless SET keyword to annoy users, and a file that
>> you need a bison parser
>
> Don't you think that's a little over the top, throwing bison at the simple task
> to extend postgresql.conf scanning so it accepts --, /**/ and SET?

Well you're the one who proposed generalising the simple text file format to
SET commands.

The whole point of a machine-editable format would be to make it more
restricted and predictable, not less so. We had a proposal to do a
postgresql.conf.auto which was automatically generated included from a
free-form postgresql.conf. That file could generated in a simple format
without worrying about formatting, comments, ordering, etc.

> Text config files are NOT friendly for beginner and mediocre users. IMHO the
> current restriction on GUC changes is a major obstacle towards pgsql tuning
> tools, e.g. written as a Google SoC project. Graphic tools aren't too popular
> at pgsql-hackers, but please contemplate a little how much pgadmin may have
> contributed to the pgsql usage boost, esp. on windows.

Like it or not computers actually have to store state when you're done
entering it via the GUI. If you design around the GUI you end up with system
that can *only* be used via a GUI and spend years trying to work around that
(witness Windows which is only now with a lot of effort recovering from that
mistake).

People got all focused on comments and sed, but in fact there are a host of
reasons to want to be able to directly edit the state stored in a simple text
file. You might like to group together related options, for example, perhaps
grouping together the options that your site have to adjust between dev and
production -- and probably have instructions on how to make the transition in
a commented section.

I happen to think being presented with 200 options is less confusing than
being presented with an empty file and being told to go read the documentation
to find out whether I might want to put anything in it. The documentation is a
good reference but isn't good as a "here's what you'll want to check before
you start" guide.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

"Peter Eisentraut" <peter_e(at)gmx(dot)net> writes:

> And note that one of the major advances in X.org over XFree86 was that all the
> useless garbage was removed from the configuration file, so that the final
> and usable configuration fits on one screen, and you can even write it from
> memory if you dare to.

This is a good point, though I think the big advance wasn't in actually
removing the garbage so much as making it irrelevant. That is, the advances
were in making the server actually work automatically with less manual
configuration.

That's always a noble goal. I would be interested in what parameters people
thought we could eliminate from the initial config file because the server
normally gets it right anyways and users shouldn't have to adjust it anyways.

(I'm specifically *not* referring to sections like the kerberos parameters
which even if most users can leave them alone those who do use that feature
*do* need to adjust those parameters because the server does *not* get them
right automatically)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Text config files are NOT friendly for beginner and mediocre users. IMHO
> the current restriction on GUC changes is a major obstacle towards pgsql
> tuning tools, e.g. written as a Google SoC project. Graphic tools aren't
> too popular at pgsql-hackers, but please contemplate a little how much
> pgadmin may have contributed to the pgsql usage boost, esp. on windows.

I grow weary of this thread. I will say it once more: I do not believe
for one instant that the current formatting of postgresql.conf is the
major impediment, or even a noticeable impediment, to producing a useful
configuration wizard. If you wish to prove otherwise, provide a
complete wizard except for the parts that touch the config file, and
I will promise to finish it.

I will not read or respond to any further discussion of changing the
config file format. It's a waste of bandwidth.

regards, tom lane

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> - If we know better values, why don't we set them by default?

The problem is: better for what? In particular, I'm uncomfortable with
any changes in the direction of trying to make Postgres take over the
entire machine by default. I'd want some fairly explicit permission
from the user for that ...

regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> - If we know better values, why don't we set them by default?
>
> The problem is: better for what? In particular, I'm uncomfortable with
> any changes in the direction of trying to make Postgres take over the
> entire machine by default. I'd want some fairly explicit permission
> from the user for that ...

That is where some 80% solution sample config files come in.

Joshua D. Drake

>
> regards, tom lane
>

Am Freitag, 6. Juni 2008 schrieb Tom Lane:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > - If we know better values, why don't we set them by default?
>
> The problem is: better for what? In particular, I'm uncomfortable with
> any changes in the direction of trying to make Postgres take over the
> entire machine by default. I'd want some fairly explicit permission
> from the user for that ...

Yes, those are decisions we are going to have to make, eventually. But recall
the three step process:

1. What values need changing?

2. What to change them to?

3. Could that be the new default value?

I haven't seen a proposal for item 1 yet, so the rest is idle discussion at
this time.

Peter Eisentraut wrote:
> Am Freitag, 6. Juni 2008 schrieb Tom Lane:
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> - If we know better values, why don't we set them by default?
>> The problem is: better for what? In particular, I'm uncomfortable with
>> any changes in the direction of trying to make Postgres take over the
>> entire machine by default. I'd want some fairly explicit permission
>> from the user for that ...
>
> Yes, those are decisions we are going to have to make, eventually. But recall
> the three step process:
>
> 1. What values need changing?

shared_buffers
work_mem
maintenance_work_mem
checkpoint_segments
wal_sync_method
effective_cache_size

> I haven't seen a proposal for item 1 yet, so the rest is idle discussion at
> this time.

I think those cover the biggest low hanging fruit, async_commit is arguable.

Sincerely,

Joshua D. Drake

Am Freitag, 6. Juni 2008 schrieb Joshua D. Drake:
> That is where some 80% solution sample config files come in.

Considering that writing a sample configuration file is trivial, yet I haven't
seen a single one posted in the six or more years of GUC, I have no faith in
this plan until I actually see it implemented.

Peter Eisentraut wrote:
> Am Freitag, 6. Juni 2008 schrieb Joshua D. Drake:
>> That is where some 80% solution sample config files come in.
>
> Considering that writing a sample configuration file is trivial, yet I haven't
> seen a single one posted in the six or more years of GUC, I have no faith in
> this plan until I actually see it implemented.

I fail to see why anyone would bother with the amount of noise on this
thread. If people start leaning toward an actual solution, I am sure the
work will get done.

Joshua D. Drake

On Jun 5, 2008, at 23:08, Heikki Linnakangas wrote:

> What comments do we consider machine-generated? Just the ones used
> to comment out settings, like
>
> #shared_buffers = 32MB
>
> or something else?

Those and documentation comments.

> If the automatic tool lets alone all other kind of comments, I think
> we're fine. In fact, it wouldn't necessarily need to modify those
> comments either, it could simply add a new setting line below that:
>
> #shared_buffers = 32MB
> shared_buffers = 1024MB

Well, we've been talking about having varying levels of documentation
in the comments of the file based on the options passed to the
configuration program. I think that these are the primary concern,
though Greg, please do correct me if I'm mistaken.

> For extra safety, it could comment out old settings, perhaps with
> something like this:
>
> #shared_buffers = 32MB
> #shared_buffers = 1024MB # commented out by wizard on 2008-06-05
> shared_buffers = 2048MB
>
> This would preserve a full change history in the file. It would
> become quite messy after a lo of changes, of course, but a user can
> trim the history by hand if he wants to.

I guess that could be a feature. Personally, I use a vcs system for
that.

Best,

David

On Jun 6, 2008, at 01:50, Andreas Pflug wrote:

> Two heretical questions:
> Do we need user generated comments at all?
> I can't remember ever having used any comment in postgresql.conf.

That's a valid point. I've used comments to note by whom and when when
a setting was changed.

> Why do so many people here insist on editing postgresql.conf as
> primary means of changing config params?
> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as
> sed'ing postgresql.conf or doing it manually?

I think that there has been enough pushback against housing all the
settings in the database, not to mention that it calls for an API,
that just starting with something simpler to parse the file and
rewrite it from the command-line might be a better first step.

> Looking around for different approaches, network appliances come to
> my mind, e.g. Cisco routers and PIX. You have 3 ways to configure a
> pix:
> - use a command line (using ssh or telnet, eqivalent to psql); WRITE
> MEMORY to make the changes survive a reboot.
> - use a web interface (or similar tool)
> - use tftp to up/download the complete config in and out, editing
> the file. User comments will be lost, with the exception of those
> that have been applied with special comment commands (equivalent to
> "comment on").

I think the first option there is the one that's been getting the most
support here.

Best,

David

On Wednesday 04 June 2008 15:48:47 Andrew Dunstan wrote:
> simply remove all the comment lines from your
> config file.

+1. That would clear up a lot of confusion on it's own.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Saturday 31 May 2008 17:34:27 David E. Wheeler wrote:
> On May 31, 2008, at 12:36, Gregory Stark wrote:
> > What this sounds like is a sly way to try to get rid of
> > postgresql.conf
> > entirely and replace it with parameters stored in the database so
> > admins would
> > adjust the parameters using an SQL syntax rather than a text file.
> >
> > There are pros and cons of such a system but I think for newbie
> > admins that
> > would be a thousand times *more* baffling. You would have to learn new
> > commands and have no holistic view of what parameters had been set,
> > what
> > related parameters might exist. You also have no way to keep the
> > file in a
> > version control system or sync across servers etc.
>
> FWIW, this has not been a barrier to MySQL adoption.
>

agreed. using sql like syntax is often a more familiar operation than editing
a text file for DBAs. Consider the number of DBA's that manage other DB's
strictly through graphical interfaces (something we are rather lacking in
ability to do comparativly). consider also that if this is built atop the
current pg_settings, all the normal sql tools can still help... ie.
pg_dump -t if you want to check something into svn.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

* David E. Wheeler <david(at)kineticode(dot)com> [080606 12:22]:

> I guess that could be a feature. Personally, I use a vcs system for
> that.

Bugger... Now we only need to make postgresql check postmaster.conf
into git everytime it makes a change...

;-)

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Fri, 2008-06-06 at 13:07 -0400, Aidan Van Dyk wrote:
> * David E. Wheeler <david(at)kineticode(dot)com> [080606 12:22]:
>
> > I guess that could be a feature. Personally, I use a vcs system for
> > that.
>
> Bugger... Now we only need to make postgresql check postmaster.conf
> into git everytime it makes a change...

Just work with the FAM api to automate it :P

Joshua D. Drake

>
> ;-)
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Bugger... Now we only need to make postgresql check postmaster.conf
> into git everytime it makes a change...

Been there, wrote that. (for postgresql.conf anyway).

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200806061351
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkhJeXQACgkQvJuQZxSWSshS3QCfQMC092pMGm/FiRJkYmu9j68e
VDYAoM051BtIF8Hsemsrs//jSv3dkGel
=otzg
-----END PGP SIGNATURE-----

Gregory Stark wrote:
>
>> Text config files are NOT friendly for beginner and mediocre users. IMHO the
>> current restriction on GUC changes is a major obstacle towards pgsql tuning
>> tools, e.g. written as a Google SoC project. Graphic tools aren't too popular
>> at pgsql-hackers, but please contemplate a little how much pgadmin may have
>> contributed to the pgsql usage boost, esp. on windows.
>>
>
> Like it or not computers actually have to store state when you're done
> entering it via the GUI. If you design around the GUI you end up with system
> that can *only* be used via a GUI and spend years trying to work around that
> (witness Windows which is only now with a lot of effort recovering from that
> mistake).
>

I never advocated a file format that isn't editable any more; au
contraire. And the statement that a GUI configuration contradicts manual
editing is plain wrong, even with most windows software (if written
nicely to the registry, you could dump the key, edit and reload it, or
use regedit).
OTOH, you can't make most windows users happy with a text file version only.

Regards,
Andreas