public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim Nasby <[email protected]>
To: Greg Smith <[email protected]>
Cc: [email protected]
Subject: Re: Volunteer to build a configuration tool
Date: Mon, 25 Jun 2007 19:19:01 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<op.tt7mn0m9cigqcu@apollo13>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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 [email protected]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
view thread (33+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Volunteer to build a configuration tool
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox