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: BA5FEC30-926B-4FC5-BF17-E1BECE1B6BDD@decibel.org (view raw or flat)
Thread:
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      http://enterprisedb.com      512.569.9461 (cell)



In response to

Responses

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-2014 The PostgreSQL Global Development Group