Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 02C049FB8C2 for ; Tue, 26 Jun 2007 01:30:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96595-05 for ; Tue, 26 Jun 2007 01:30:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 916869FB31E for ; Tue, 26 Jun 2007 01:30:13 -0300 (ADT) Received: from [10.71.3.20] (unknown [12.162.44.130]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id B791B56447; Mon, 25 Jun 2007 23:30:11 -0500 (CDT) In-Reply-To: References: <1182298719.4482.133.camel@ipso.snappymail.ca> <7621.1182318230@sss.pgh.pa.us> <9001.1182323187@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Volunteer to build a configuration tool Date: Mon, 25 Jun 2007 19:19:01 -0500 To: Greg Smith X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/597 X-Sequence-Number: 25630 On Jun 23, 2007, at 2:28 PM, Greg Smith wrote: > On Thu, 21 Jun 2007, Campbell, Lance wrote: >> I have a PostgreSQL database that runs on a dedicated server. The >> server has 24Gig of memory. What would be the max size I would ever >> want to set the shared_buffers to if I where to relying on the OS for >> disk caching approach? It seems that no matter how big your >> dedicated >> server is there would be a top limit to the size of shared_buffers. > > It's impossible to say exactly what would work optimally in this > sort of situation. The normal range is 25-50% of total memory, but > there's no hard reason for that balance; for all we know your apps > might work best with 20GB in shared_buffers and only a relatively > small 4GB left over for the rest of the OS to use. Push it way up > and and see what you get. > > This is part of why the idea of an "advanced" mode for this tool is > suspect. Advanced tuning usually requires benchmarking with as > close to real application data as you can get in order to make good > forward progress. Agreed. EnterpriseDB comes with a feature called "DynaTune" that looks at things like server memory and sets a best-guess at a bunch of parameters. Truth is, it works fine for 90% of cases, because there's just a lot of installations where tuning postgresql.conf isn't that critical. The real issue is that the "stock" postgresql.conf is just horrible. It was originally tuned for something like a 486, but even the recent changes have only brought it up to the "pentium era" (case in point: 24MB of shared buffers equates to a machine with 128MB of memory, give or take). Given that, I think an 80% solution would be to just post small/medium/large postgresql.conf files somewhere. I also agree 100% with Tom that the cost estimators need serious work. One simple example: nothing in the planner looks at what percent of a relation is actually in shared_buffers. If it did that, it would probably be reasonable to extrapolate that percentage into how much is sitting in kernel cache, which would likely be miles ahead of what's currently done. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)