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

On 2013-09-11 12:53:29 -0400, Bruce Momjian wrote:
> On Wed, Sep 11, 2013 at 12:43:07PM -0300, Alvaro Herrera wrote:
> > Bruce Momjian escribió:
> >
> > > > 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.
> >
> > The other argument I see for the 3x value is that it is a compromise.
> > People with really large servers will want to increase it; people with
> > very small servers will want to reduce it.
>
> Yes, you could make the argument that 2x is the right default,
> especially considering work_mem.

I think that most of the arguments in this thread drastically
overestimate the precision and the effect of effective_cache_size. The
planner logic behind it basically only uses it to calculate things
within a single index scan. That alone shows that any precise
calculation cannot be very meaningful.
It also does *NOT* directly influence how the kernel caches disk
io. It's there to guess how likely it is something is still cached when
accessing things repeatedly.

I think nearly all practical experience shows that setting it smaller is
more likely to cause problems than setting it too low. We shouldn't be
too skimpy here.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services