Skip site navigation (1) Skip section navigation (2)

Re: Volunteer to build a configuration tool

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Greg Smith <gsmith(at)gregsmith(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Volunteer to build a configuration tool
Date: 2007-06-26 00:19:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-docspgsql-performance
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(at)nasby(dot)net
EnterpriseDB      512.569.9461 (cell)

In response to


pgsql-docs by date

Next:From: Gregory StarkDate: 2007-06-26 08:01:59
Subject: Re: Volunteer to build a configuration tool
Previous:From: Greg SmithDate: 2007-06-23 19:28:17
Subject: Re: Volunteer to build a configuration tool

pgsql-performance by date

Next:From: Stephen FrostDate: 2007-06-26 00:33:39
Subject: Re:
Previous:From: Ed TyrrillDate: 2007-06-26 00:09:58
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group