pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: PostgresSQL Hackers Mailing List <pgsql-hackers@postgresql.org>
Cc: pgsql-advocacy@postgresql.org
Subject: Re: [HACKERS] Changing the default configuration (was Re:
Date: Thu, 13 Feb 2003 01:12:17 -0500 (EST)
Message-ID: <200302130612.h1D6CHc14823@candle.pha.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.44.0302121912150.2811-100000@peter.localdomain>

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



view thread (65+ messages)  latest in thread

Message-ID: <200302130612.h1D6CHc14823@candle.pha.pa.us>
Permalink:  ../200302130612.h1D6CHc14823@candle.pha.pa.us/
Also on:    postgresql.org/message-id/200302130612.h1D6CHc14823@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pgman@candle.pha.pa.us, peter_e@gmx.net, tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com, pgsql-hackers@postgresql.org, pgsql-advocacy@postgresql.org
  Subject: Re: [HACKERS] Changing the default configuration (was Re:
  In-Reply-To: <200302130612.h1D6CHc14823@candle.pha.pa.us>
  References: <Pine.LNX.4.44.0302121912150.2811-100000@peter.localdomain> <200302130612.h1D6CHc14823@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox