A smaller default postgresql.conf

Peter Eisentraut wrote:

I seem to recall that there was general support for installing a smaller
default postgresql.conf file with only, say, a dozen parameters mentioned for
initial tuning. The complete file can stay as a sample. Any objections to
that? (Let's not discuss quite yet exactly which parameters are the chosen
ones.)

i think this would make sense as long as this small file tells users
where to find the "full story".
generally i would say that this would be a step into the right direction.

alternatively we could use some sort of "#include" mechanism to split
"most important" and "not so important".

hans

--
Cybertec Sch�nig & Sch�nig GmbH
PostgreSQL Solutions and Support
Gr�hrm�hlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

Hans-Juergen Schoenig wrote:

alternatively we could use some sort of "#include" mechanism to split
"most important" and "not so important".

We already have an "include" mechanism.

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

Alvaro Herrera wrote:

Hans-Juergen Schoenig wrote:

alternatively we could use some sort of "#include" mechanism to split
"most important" and "not so important".

We already have an "include" mechanism.

Using that to include a file that's full of comments anyway (which is
all that's left in postgresql.conf at this time, I'm sure) just seems.
Well. Sub-optimal.

//Magnus

On Tue, 19 Aug 2008 17:11:49 +0200
Magnus Hagander <magnus@hagander.net> wrote:

Alvaro Herrera wrote:

Hans-Juergen Schoenig wrote:

alternatively we could use some sort of "#include" mechanism to
split "most important" and "not so important".

We already have an "include" mechanism.

Using that to include a file that's full of comments anyway (which is
all that's left in postgresql.conf at this time, I'm sure) just seems.
Well. Sub-optimal.

Yes but part of this idea is valid. The fact is the majority of the
postgresql.conf parameters don't need to be in there by default. It
just makes the file an intimidating mess for newbies and I am not
talking about just n00bs but also people coming from other environments
such as MSSQL.

I believe we could probably break the conf down to a reasonable 2 dozen
or less parameters. The rest should just be documented in our
documentation and call it good. We even have static URLs for this (I
seem to have dejavu with this as I am pretty sure I have had this
discussion already).

Joshua D> Drake

//Magnus

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua Drake <jd@commandprompt.com> writes:

Magnus Hagander <magnus@hagander.net> wrote:

Using that to include a file that's full of comments anyway (which is
all that's left in postgresql.conf at this time, I'm sure) just seems.
Well. Sub-optimal.

Yes but part of this idea is valid. The fact is the majority of the
postgresql.conf parameters don't need to be in there by default. It
just makes the file an intimidating mess for newbies and I am not
talking about just n00bs but also people coming from other environments
such as MSSQL.

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation. For novices they aren't
even adequately duplicative.

regards, tom lane

On Tue, 19 Aug 2008 12:12:16 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joshua Drake <jd@commandprompt.com> writes:

Magnus Hagander <magnus@hagander.net> wrote:

Yes but part of this idea is valid. The fact is the majority of the
postgresql.conf parameters don't need to be in there by default. It
just makes the file an intimidating mess for newbies and I am not
talking about just n00bs but also people coming from other
environments such as MSSQL.

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?

I guess it would depend on what initdb puts into it. I don't really
have a problem ripping out all extra stuff as it would help force
people to read the docs but would we still have 150 parameters?. From a
friendly perspective it would make sense to tone it down to the key
parameters such as shared_buffers, listen_address, work_mem etc...

We don't need (for example) to have autovacuum in there by default as it
is always on and configured reasonably at this point. If they need to
change autovacuum they should be reading the docs about it first;
the same with bgwriter, fsync, async_commit etc...

I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation. For novices they aren't
even adequately duplicative.

I can't argue with this. :)

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Tom,

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation. For novices they aren't
even adequately duplicative.

Well, that doesn't help unless we either provide a .conf generation tool
(something I favor) or docs somewhere which explain which are the
variables to be the most concerned with instead of making users read
through all 218 of them.

Attached is the postgresql.conf.simple I used in my presentaiton. It
has an egregious math error in it (see if you can find it) but should
give you the general idea.

--Josh

Joshua Drake <jd@commandprompt.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?

I guess it would depend on what initdb puts into it.

Per the code:

max_connections
shared_buffers
max_fsm_pages (slated to die anyway in 8.4)
lc_messages
lc_monetary
lc_numeric
lc_time
datestyle
default_text_search_config

The first three of those are derived from probing the SHMMAX setting,
and the rest are from the initdb-time locale settings.

regards, tom lane

On Tue, 19 Aug 2008 12:48:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joshua Drake <jd@commandprompt.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into
it)?

I guess it would depend on what initdb puts into it.

Per the code:

max_connections
shared_buffers
max_fsm_pages (slated to die anyway in 8.4)
lc_messages
lc_monetary
lc_numeric
lc_time
datestyle
default_text_search_config

The first three of those are derived from probing the SHMMAX setting,
and the rest are from the initdb-time locale settings.

When I first started to reply I had a list of another dozen or so we
should add but in reality as I think about it; we need only one more
parameter. If we add listen_addresses and a link to the documention for
the rest, I would +1 this.

I was thinking about the apache conf and it is riddled with
documentation, lots and lots of text. I find that either I am irritated
with how much documentation there is (because I already understand the
directive I am working with) or I am frustrated because it doesn't
adequately explain the dependencies.

If we move to the above route, we end up in an environment with a
single source for "official" documentation and we can always point to
that.

Sincerely,

Joshua D. Drake

regards, tom lane

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Josh Berkus <josh@agliodbs.com> wrote:

Attached is the postgresql.conf.simple I used in my presentaiton. It

has an egregious math error in it (see if you can find it) but should

give you the general idea.

Well, this sure looks scary:

# maintenance_work_mem = 256MB #webserver with 2GB RAM

But I'm amazed by this, too:

# max_connections = 700 # web application database

How many CPUs and spindles are you assuming there?

My testing and experience suggest applications should use no more than
4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a
connection pool with queuing capability is required for best
performance with a large number of users?

-Kevin

Joshua Drake <jd@commandprompt.com> writes:

If we move to the above route, we end up in an environment with a
single source for "official" documentation and we can always point to
that.

Yeah, the fundamental point here is whether or not postgresql.conf
should be trying to serve as part of our system documentation. I'm
inclined to think that any comments in it should be more about why these
particular values have been set, and not "here are some values you might
like to twiddle". So initdb might emit

# Set by initdb from probing kernel limits 2008-08-11
max_connections = 100
shared_buffers = 32MB

# Set by initdb from its locale environment: LANG = en_US
lc_messages = en_US
lc_monetary = en_US
[etc]

I'm really not in favor of having comments in the conf file that try to
tell you about stuff you might want to set, much less why. That task
properly belongs to some kind of introductory chapter in the SGML docs.
Novice DBAs are unlikely even to *find* the config file, let alone look
inside it, if there's not an introductory chapter telling them about
Things They Ought To Do.

regards, tom lane

On Tue, Aug 19, 2008 at 1:17 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

Josh Berkus <josh@agliodbs.com> wrote:

But I'm amazed by this, too:

# max_connections = 700 # web application database

How many CPUs and spindles are you assuming there?

My testing and experience suggest applications should use no more than
4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a
connection pool with queuing capability is required for best
performance with a large number of users?

Agreed, with this many concurrent users, I would expect severe lock
contention on the ProcArrayLock. Similarly, if this were heavily
updated, WAL-related locks would likely become another significant
bottleneck.

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Tue, 19 Aug 2008 12:17:46 -0500
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

Well, this sure looks scary:

# maintenance_work_mem = 256MB #webserver with 2GB RAM

I would agree. 2GB isn't that much memory as it is and that is a fairly
heft amount of maintenance_work_mem. This isn't the days when vacuum
ran via cron at 2am anymore. Autovacuum will fire at any time.

But I'm amazed by this, too:

# max_connections = 700 # web application database

How many CPUs and spindles are you assuming there?

My testing and experience suggest applications should use no more than
4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a
connection pool with queuing capability is required for best
performance with a large number of users?

I just did the math on this and I would say you are correct. I had
never really evaluated in the way you just had but based on some of our
larger installs (32cores, 100 spindles) your math works.

Noting that he actually states it is a webserver connecting there
should absolutely be a pool in front of PostgreSQL.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tue, 19 Aug 2008 13:22:34 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'm really not in favor of having comments in the conf file that try
to tell you about stuff you might want to set, much less why. That
task properly belongs to some kind of introductory chapter in the
SGML docs. Novice DBAs are unlikely even to *find* the config file,
let alone look inside it, if there's not an introductory chapter
telling them about Things They Ought To Do.

I would be willing to work up a patch that does as you suggest.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?

Well, my original implementation of GUC had an empty default configuration
file, which was later craptaculated to its current form based on seemingly
popular demand. I am very happy to work back toward the empty state, and
there appears to be growing support for that.

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

I'm really not in favor of having comments in the conf file that try to
tell you about stuff you might want to set, much less why. That task
properly belongs to some kind of introductory chapter in the SGML docs.
Novice DBAs are unlikely even to *find* the config file, let alone look
inside it, if there's not an introductory chapter telling them about
Things They Ought To Do.

Ugh, you are heading in the wrong direction. The configuration file
should be well documented: moving the documentation further away
from it is the wrong idea, especially if it means firing up a web
browser to do so. As link is fine, and recommended, but a "bare"
configuration file would be far, far worse than the mess we have today.
I like Josh B's version a lot. It's not perfect (I'd add a URL for
each config for example), but it's a great start. Text space is cheap, and having
a consistent, well-documented, easy-to-read conf file is something
worth shooting for.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200808191511
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkirGyEACgkQvJuQZxSWSsgsvwCdH6Hb4KOj47j/Zceb26FgEQUM
J2gAoKE19rLhMpgP17EdJIuUVoKQ7H3u
=//eH
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:

I like Josh B's version a lot. It's not perfect (I'd add a URL for
each config for example), but it's a great start.

Josh B's approach is great until people start making changes that are
unrelated to (or perhaps even contradictory to) his comments. And then
it's just a recipe for confusion. I would far rather see his text as
part of the SGML docs.

regards, tom lane

On Tue, 19 Aug 2008 19:12:47 -0000
"Greg Sabino Mullane" <greg@turnstep.com> wrote:

Ugh, you are heading in the wrong direction. The configuration file
should be well documented: moving the documentation further away
from it is the wrong idea, especially if it means firing up a web
browser to do so.

It is impossible to document the postgresql.conf file in a manner that
is truly useful without firing up the reading material in the first
place.

Even with Josh's improvements there are too many variables and we are
just going to have a bunch of people breaking stuff and then
complaining that, "well it was suggested in the postgresql.conf".

a URL
for each config for example), but it's a great start. Text space is
cheap, and having a consistent, well-documented, easy-to-read conf
file is something worth shooting for.

I have yet to find one; anywhere.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:

moving the documentation further away from it is the wrong idea,
especially if it means firing up a web browser to do so.

I can see that argument, but I think we can quite simply solve it if we
provide a plain-text version of the configuration chapter of the
documentation. You can easily grep that in a second window and don't have to
be in-your-face to users who just want to edit the settings.

Text space is cheap,

I'd offer the alternative theory that anything that is longer than one screen
is overwhelming and unwieldy.