X-Original-To: pgsql-advocacy@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4B244474E42; Mon, 17 Feb 2003 21:49:48 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id 79C2047580B; Mon, 17 Feb 2003 21:49:46 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id h1I2nVW06457; Mon, 17 Feb 2003 21:49:31 -0500 (EST) From: Bruce Momjian Message-Id: <200302180249.h1I2nVW06457@candle.pha.pa.us> Subject: Re: [HACKERS] Changing the default configuration (was Re: In-Reply-To: <200302130612.h1D6CHc14823@candle.pha.pa.us> To: Bruce Momjian Date: Mon, 17 Feb 2003 21:49:31 -0500 (EST) Cc: Peter Eisentraut , Tom Lane , Merlin Moncure , PostgresSQL Hackers Mailing List , pgsql-advocacy@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/86 X-Sequence-Number: 844 People seemed to like the idea: Add a script to ask system configuration questions and tune postgresql.conf. --------------------------------------------------------------------------- Bruce Momjian wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- 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