Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

On Tue, Jan  8, 2013 at 08:40:44PM -0500, Andrew Dunstan wrote:
>
> On 01/08/2013 08:08 PM, Tom Lane wrote:
> >Robert Haas <robertmhaas@gmail.com> writes:
> >>On Tue, Jan 8, 2013 at 7:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>>...  And I don't especially like the idea of trying to
> >>>make it depend directly on the box's physical RAM, for the same
> >>>practical reasons Robert mentioned.
> >>For the record, I don't believe those problems would be particularly
> >>hard to solve.
> >Well, the problem of "find out the box's physical RAM" is doubtless
> >solvable if we're willing to put enough sweat and tears into it, but
> >I'm dubious that it's worth the trouble.  The harder part is how to know
> >if the box is supposed to be dedicated to the database.  Bear in mind
> >that the starting point of this debate was the idea that we're talking
> >about an inexperienced DBA who doesn't know about any configuration knob
> >we might provide for the purpose.
> >
> >I'd prefer to go with a default that's predictable and not totally
> >foolish --- and some multiple of shared_buffers seems like it'd fit the
> >bill.
>
> +1. That seems to be by far the biggest bang for the buck. Anything
> else will surely involve a lot more code for not much more benefit.

I have developed the attached patch which implements an auto-tuned
effective_cache_size which is 4x the size of shared buffers.  I had to
set effective_cache_size to its old 128MB default so the EXPLAIN
regression tests would pass unchanged.

I considered a new available_ram variable but that just gives us another
variable, and in a way shared_buffers is a fixed amount, while
effective_cache_size is an estimate, so I thought driving everything
from shared_buffers made sense.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +