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)
