Re: Volunteer to build a configuration tool

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)