Re: [HACKERS] Changing the default configuration (was Re: - Mailing list pgsql-advocacy

Peter Eisentraut wrote:
> Tom Lane writes:
>
> > Well, as I commented later in that mail, I feel that 1000 buffers is
> > a reasonable choice --- but I have to admit that I have no hard data
> > to back up that feeling.
>
> I know you like it in that range, and 4 or 8 MB of buffers by default
> should not be a problem.  But personally I think if the optimal buffer
> size does not depend on both the physical RAM you want to dedicate to
> PostgreSQL and the nature and size of the database, then we have achieved
> a medium revolution in computer science. ;-)

I have thought about this and I have an idea.  Basically, increasing the
default values may get us closer, but it will discourage some to tweek,
and it will cause problems with some OS's that have small SysV params.

So, my idea is to add a message at the end of initdb that states people
should run the pgtune script before running a production server.

The pgtune script will basically allow us to query the user, test the OS
version and perhaps parameters, and modify postgresql.conf with
reasonable values.  I think this is the only way to cleanly get folks
close to where they should be.

For example, we can ask them how many rows and tables they will be
changing, on average, between VACUUM runs.  That will allow us set the
FSM params.  We can ask them about using 25% of their RAM for shared
buffers.  If they have other major apps running on the server or have
small tables, we can make no changes.  We can basically ask them
questions and use that info to set values.

We can even ask about sort usage maybe and set sort memory.  We can even
control checkpoint_segments this way if they say they will have high
database write activity and don't worry about disk space usage.  We may
even be able to compute some random page cost estimate.

Seems a script is going to be the best way to test values and assist
folks in making reasonable decisions about each parameter.  Of course,
they can still edit the file, and we can ask them if they want
assistance to set each parameter or leave it alone.

I would restrict the script to only deal with tuning values, and tell
people they still need to review that file for other useful parameters.

Another option would be to make a big checklist or web page that asks
such questions and computes proper values, but it seems a script would
be easiest.  We can even support '?' which would explain why the
question is being ask and how it affects the value.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073