X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 7B1CFB43162 for ; Wed, 9 Jul 2003 23:31:01 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 22776-08 for ; Wed, 9 Jul 2003 20:30:29 -0300 (ADT) Received: from perrin.int.nxad.com (internal.ext.nxad.com [69.1.70.251]) by svr1.postgresql.org (Postfix) with ESMTP id 81309B430AF for ; Wed, 9 Jul 2003 20:30:29 -0300 (ADT) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id 9C3622105A; Wed, 9 Jul 2003 16:30:31 -0700 (PDT) Date: Wed, 9 Jul 2003 16:30:31 -0700 From: Sean Chittenden To: Michael Mattox Cc: pgsql-performance@postgresql.org Subject: Re: Moving postgresql.conf tunables into 2003... Message-ID: <20030709233031.GF72567@perrin.int.nxad.com> References: <20030703190502.GR72567@perrin.int.nxad.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 3849 3760 1AFE 7B17 11A0 83A6 DD99 E31F BC84 B341 X-Web-Homepage: http://sean.chittenden.org/ User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/158 X-Sequence-Number: 2451 > I don't have much to add because I'm pretty new to Postgres and have > been soliciting advice here recently, but I totally agree with > everything you said. I don't mind if it's in the postgres.conf file > or in a faq that is easy to find, I just would like it to be in one > place. A good example of the need for this is when I was tuning > "effective_cache" I thought that was creating a cache for Postgres > when in fact as it was pointed out to me, it's just hinting to > postgres the size of the OS cache. Lots of ways for people to get > really confused here. I looked through the src/doc/runtime.sgml for a good place to stick this and couldn't find a place that this seemed appropriate, but on FreeBSD, this can be determined with a great deal of precision in a programmatic manner: echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" The same OID is available via C too. It'd be slick if PostgreSQL could tune itself (on FreeBSD) at initdb time with the above code. If Linux exports this info via /proc and can whip out the appropriate magic, even better. An uncommented out good guess that shows up in postgresql.conf would be stellar and quite possible with the use of sed. Maybe an initdb switch could be added to have initdb tune the config it generates? If a -n is added, have it generate a config and toss it to stdout? case `uname` in "FreeBSD") echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" ;; *) echo "Unable to automatically determine the effective cache size" >> /dev/stderr ;; esac -sc -- Sean Chittenden