initdb profiles

Andrew Dunstan wrote:

But I wondered if it might not be a good idea to allow
an option to initdb which would provide a greater possible range of
settings for max_connections, shared_buffers and so on. For example,
we might offer a profile which is very conservative for memory bound

That reminds me of an identical proposal that was rejected years ago...

machines, medium size for a development platform, large for a server
running with other server processes, and huge for a decdicated box,
and then provide some heuristics that initdb could apply. We'd have

And before long we'll have 750 profiles...

to let all of these degrade nicely, so that even if the user select
the machine hog setting, if we find we can only do something like the
tiny setting that's what s/he would get. Also, we might need to have

And degrading nicely was a feature that we removed a long time ago. Now
you get what you ask for.

some tolerably portable way of finding out about machine resources.

And that doesn't exist.

And power users will still want to tube things more. But it might
help to alleviate our undeserved reputation for poor performance if
we provide some help to start off at least in the right ballpark.

And mind reading devices are not yet available.

So it's doesn't look all that good.

All jokes aside, tuning aids are surely needed, but letting initdb guess
the required profile isn't going to do it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

All jokes aside, tuning aids are surely needed, but letting initdb guess
the required profile isn't going to do it.

The idea was in fact to allow the user to provide additional information
to allow initdb to make better guesses than it currently does.

cheers

andrew

Andrew Dunstan wrote:

The idea was in fact to allow the user to provide additional
information to allow initdb to make better guesses than it currently
does.

There's certainly going to be opposition to making initdb an interactive
tool.

The other problem is that no one has ever managed to show that it is
possible to derive reasonable settings from a finite set of questions
presented to the user, plus perhaps from a reasonably portable system
analysis. If you can do that, that would be a cool tool in its own
right. And then you could call that from initdb or not depending on
taste.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:

All jokes aside, tuning aids are surely needed, but letting initdb guess
the required profile isn't going to do it.

initdb is really the wrong place for this anyway, because in many
situations (RPM installations for instance) initdb is run behind the
scenes with no opportunity for user interaction. We should be doing
our best to remove options from initdb, not add them.

I think Andrew has a good point that we need to work more on making
configuration tuning easier ... but initdb isn't the place.

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

All jokes aside, tuning aids are surely needed, but letting initdb guess
the required profile isn't going to do it.

initdb is really the wrong place for this anyway, because in many
situations (RPM installations for instance) initdb is run behind the
scenes with no opportunity for user interaction. We should be doing
our best to remove options from initdb, not add them.

I think Andrew has a good point that we need to work more on making
configuration tuning easier ... but initdb isn't the place.

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep rise
we have seen in typically available memory at the low end? We currently
try {100, 50, 40, 30, 20, 10} for connections and {1000, 900, 800, 700,
600, 500, 400, 300, 200, 100, 50} for buffers. I think there's arguably
a good case for trying numbers several (4 maybe?) times this large in
both categories. Out own docs state that the default number of shared
buffers is low for efficient use, and it would be nice to try to allow
one connection per standard allowed apache client (default is 256
non-threaded and 400 threaded, I think).

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep rise
we have seen in typically available memory at the low end?

I can't see any particular harm in having initdb try somewhat-larger
values ... but how far does that really go towards fixing the issues?

Personally, the default value I currently see as far too tight is
max_fsm_pages. I'd rather see initdb trying to push that up if it's
able to establish shared_buffers and max_connections at their current
maxima.

... it would be nice to try to allow
one connection per standard allowed apache client (default is 256
non-threaded and 400 threaded, I think).

That's a mostly independent consideration, but it seems fair enough.
Can we check the exact values rather than relying on "I think"?

regards, tom lane

Andrew Dunstan wrote:

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep
rise we have seen in typically available memory at the low end?

There is a compromise that I think we cannot make. For production
deployment, shared buffers are typically sized at about 10% to 25% of
available phyiscal memory. I don't think we want to have a default
installation of PostgreSQL that takes 10% or more of memory just like
that. It just doesn't look good.

So the question whether initdb should by default consider up to 1000 or
up to 4000 buffers is still worth discussion, but doesn't solve the
tuning issue to a reasonable degree.

What I would like to see is that initdb would end with saying that the
system is not really tuned and that I should run pg-some-program to
improve that. pg-some-program would analyze my system, ask me a few
questions, and then output a suggested configuration (or apply it right
away). Again, the challenge is to write that program.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

heuristics that initdb could apply. We'd have to let all of these
degrade nicely, so that even if the user select the machine hog setting,
if we find we can only do something like the tiny setting that's what
s/he would get. Also, we might need to have some tolerably portable way
of finding out about machine resources. And power users will still want
to tube things more. But it might help to alleviate our undeserved
reputation for poor performance if we provide some help to start off at
least in the right ballpark.

I think we should just do what MySQL does and include:

postgresql.conf
postgresql-large.conf
postgresql-huge.conf

Chris

What I would like to see is that initdb would end with saying that the
system is not really tuned and that I should run pg-some-program to
improve that. pg-some-program would analyze my system, ask me a few
questions, and then output a suggested configuration (or apply it right
away). Again, the challenge is to write that program.

Perhaps at the end of initdb it would say would you like
to run the PostgreSQL configuration program?

Which would be a wizard that would ask 10 or so questions
and automatically configure us based on those questions?

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:

What I would like to see is that initdb would end with saying that the
system is not really tuned and that I should run pg-some-program to
improve that.

Perhaps at the end of initdb it would say would you like
to run the PostgreSQL configuration program?

You're both assuming that the output of initdb goes someplace other
than /dev/null ...

I do agree with trying to create a "configuration wizard" program,
but I think having initdb advertise it will be of only marginal use.

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep rise
we have seen in typically available memory at the low end?

I can't see any particular harm in having initdb try somewhat-larger
values ... but how far does that really go towards fixing the issues?

Personally, the default value I currently see as far too tight is
max_fsm_pages. I'd rather see initdb trying to push that up if it's
able to establish shared_buffers and max_connections at their current
maxima.

Ok. how would the logic go? Just have a function that runs max_fsm_pages
checks after we call test_connections() and test_buffers(), or should
there be some interplay between those settings? As I understand it, the
current setting would consume all of 120,000 bytes of shared memory, so
there could well be lots of head room.

... it would be nice to try to allow
one connection per standard allowed apache client (default is 256
non-threaded and 400 threaded, I think).

That's a mostly independent consideration, but it seems fair enough.
Can we check the exact values rather than relying on "I think"?

That's my reading of
http://httpd.apache.org/docs/2.0/mod/mpm_common.html#maxclients

Peter Eisentraut <peter_e@gmx.net> writes:

There is a compromise that I think we cannot make. For production
deployment, shared buffers are typically sized at about 10% to 25% of
available phyiscal memory. I don't think we want to have a default
installation of PostgreSQL that takes 10% or more of memory just like
that. It just doesn't look good.

The fundamental issue there is "box dedicated to (one instance of)
Postgres" versus "box serves multiple uses". If you don't know what
fraction of the machine resources you're supposed to take up, it's
difficult to be very smart. I think that we have to default to a
socially friendly "don't eat the whole box" position ...

regards, tom lane

Peter Eisentraut wrote:

Andrew Dunstan wrote:

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep
rise we have seen in typically available memory at the low end?

There is a compromise that I think we cannot make. For production
deployment, shared buffers are typically sized at about 10% to 25% of
available phyiscal memory. I don't think we want to have a default
installation of PostgreSQL that takes 10% or more of memory just like
that. It just doesn't look good.

I have a single instance of apache running on this machine. It's not
doing anything, but even so it's consuming 20% of physical memory. By
contrast, my 3 postmasters are each consuming 0.5% of memory. All with
default settings. I don't think we are in any danger of looking bad for
being greedy. If anything we are in far greater danger of looking bad
from being far too conservative and paying a performance price for that.
There's nothing magical about the numbers we use.

So the question whether initdb should by default consider up to 1000 or
up to 4000 buffers is still worth discussion, but doesn't solve the
tuning issue to a reasonable degree.

True, but that doesn't mean it's not worth doing anyway.

cheers

andrew

Hello All,

Please allow me to put a disclaimer, I am no serious PG hacker,
but would it be possible to allow for a simple config script to be run
(which would work even via /etc/init.d) which could be used to generate a
config file for initdb, which initdb could read and do its thing ?

This script could say do you wish to do a manual adjustment or
accept the default values, and then initdb could feed off that file. Does
this create too much work or is it disadvantageous.

Cheers,

Aly.

On Wed, 7 Sep 2005, Andrew Dunstan wrote:

Peter Eisentraut wrote:

Andrew Dunstan wrote:

I accept the "run from init.d" argument. So then, is there a case for
increasing the limits that initdb works with, to reflect the steep
rise we have seen in typically available memory at the low end?

There is a compromise that I think we cannot make. For production
deployment, shared buffers are typically sized at about 10% to 25% of
available phyiscal memory. I don't think we want to have a default
installation of PostgreSQL that takes 10% or more of memory just like that.
It just doesn't look good.

I have a single instance of apache running on this machine. It's not doing
anything, but even so it's consuming 20% of physical memory. By contrast, my 3
postmasters are each consuming 0.5% of memory. All with default settings. I
don't think we are in any danger of looking bad for being greedy. If anything
we are in far greater danger of looking bad from being far too conservative
and paying a performance price for that. There's nothing magical about the
numbers we use.

So the question whether initdb should by default consider up to 1000 or up
to 4000 buffers is still worth discussion, but doesn't solve the tuning
issue to a reasonable degree.

True, but that doesn't mean it's not worth doing anyway.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

On Thu, Sep 08, 2005 at 09:54:59AM +0800, Christopher Kings-Lynne wrote:

I think we should just do what MySQL does and include:

postgresql.conf
postgresql-large.conf
postgresql-huge.conf

I do that, in the package of PG I distribute with my application. I
tell the user that they should use it in the installation documentation,
as part of the installation script, in the performance tuning documentation
in the maintenance documentation, in the runbook, on the website and
in the applications online help.

I also mention it to customers by phone, email and on occasion IRC or IM
when they're about to install it or have just installed it. I also mention
it to them any time they call about performance problems.

These are technically literate customers working for large ISPs, with
significant local sysadmin and DBA support, so the concept is not beyond them.

Yet when I ssh in to one of their servers only about 1 in 3 is running
with anything other than the default postgresql.conf.

Just a data point. If it works, most people won't think to fix it.

Cheers,
Steve

Steve Atkins wrote:

These are technically literate customers working for large ISPs, with
significant local sysadmin and DBA support, so the concept is not beyond them.

Yet when I ssh in to one of their servers only about 1 in 3 is running
with anything other than the default postgresql.conf.

Just a data point. If it works, most people won't think to fix it.

That's why I think we need a range of measures, one of which would be to
use better defaults in initdb. We are using the same numbers that were
there at least 2 releases ago, IIRC. But the world has changed some in
that time.

cheers

andrew

On 2005-09-08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

initdb is really the wrong place for this anyway, because in many
situations (RPM installations for instance) initdb is run behind the
scenes with no opportunity for user interaction. We should be doing
our best to remove options from initdb, not add them.

Running initdb behind the scenes is a proven dangerous practice; why
encourage it?

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Folks,

Help on the Configurator is actively solicited. I really think this is a
better solution for this problem.
http://www.pgfoundry.org/projects/configurator

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

Folks,

Help on the Configurator is actively solicited. I really think this is a
better solution for this problem.
http://www.pgfoundry.org/projects/configurator

I don't agree, for several reasons.

1. Steve has already told us most of his clients just go with the defaults
2. We don't have to pick a winner; improving initdb wouldn't obviate the
need for configurator
3. It's a cop-out. I think there's a reasoable expectation that we will
by default use some settings that work reasonably in typical cases.
Inviting people to use an add-on tool to tune postgres after initdb is
the sort of thing that gets postgres a bad name. We need to find some
sort of sweet spot between being machine hogs and being so conservative
that out of the box we run like a dog for typical users. Initdb already
has adaptive rules - look at the source - and Tom suggests adding
another set for max_fsm_pages. All I'm doing is to suggest that we need
to tweak those.

cheers

andrew