X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 13807CD211 for ; Sun, 20 Jul 2003 19:20:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 37959-01 for ; Sun, 20 Jul 2003 19:20:42 +0000 (GMT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 71E56CC305 for ; Sun, 20 Jul 2003 16:20:03 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id h6KJKQL10578; Sun, 20 Jul 2003 15:20:26 -0400 (EDT) From: Bruce Momjian Message-Id: <200307201920.h6KJKQL10578@candle.pha.pa.us> Subject: Re: Moving postgresql.conf tunables into 2003... In-Reply-To: <20030709233031.GF72567@perrin.int.nxad.com> To: Sean Chittenden Date: Sun, 20 Jul 2003 15:20:26 -0400 (EDT) Cc: Michael Mattox , pgsql-performance@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 amavisd-new at postgresql.org X-Archive-Number: 200307/312 X-Sequence-Number: 2605 Keep in mind that if we auto-tune, we will only be able to do it for some platforms, so we will need a table that shows which settings are autotuned for each platform. --------------------------------------------------------------------------- Sean Chittenden wrote: > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- 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