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

On Wed, Sep 11, 2013 at 09:18:30AM -0400, Bruce Momjian wrote:
> On Tue, Sep 10, 2013 at 03:08:24PM -0700, Josh Berkus wrote:
> > Merlin,
> > 
> > > I vote 4x on the basis that for this setting (unlike almost all the
> > > other memory settings) the ramifications for setting it too high
> > > generally aren't too bad.  Also, the o/s and temporary memory usage as
> > > a share of total physical memory has been declining over time
> > 
> > If we're doing that, then we should change our general advice on this
> > setting as well.
> 
> Uh, what general advice?  I don't see 4x mentioned anywhere.
> 
> > Another argument in favor: this is a default setting, and by default,
> > shared_buffers won't be 25% of RAM.
> 
> So, are you saying you like 4x now?

Here is an arugment for 3x.  First, using the documented 25% of RAM, 3x
puts our effective_cache_size as 75% of RAM, giving us no room for
kernel, backend memory, and work_mem usage.  If anything it should be
lower than 3x, not higher.

Second, if the machine is not a dedicated machine, and supposed 10% of
RAM is used for shared_buffers, 4x would put effective cache size at 40%
of RAM, which again seems too high, considering others are using the
machine and filling the kernel cache.  3x also seems too high, but
acceptable at 30% of RAM.

I basically can't imagine a case where you set shared_buffers to a
reasonable value and would still have 4x of that available for kernel
cache.

Finally, for those who like the idea of 4x, you can think of
shared_buffers (1x) + effective_cache_size (3x) as totalling 4x.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +