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

On Wed, May 7, 2014 at 3:18 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> If we believe that 25% of shared_buffers worth of heap blocks would
> flush the cache doing a SeqScan, why should we allow 400% of
> shared_buffers worth of index blocks?

I think you're comparing apples and oranges. The 25% threshold is
answering the question "How big does a sequential scan have to be
before it's likely to flush so much so much unrelated data out of
shared_buffers that it hurts the performance of other things running
on the system?". So it's not really about whether or not things will
*fit* in the cache, but rather a judgement about at what point caching
that stuff is going to be less value than continuing to cache other
things. Also, it's specifically a judgement about shared_buffers, not
system memory.

But effective_cache_size is used to estimate the likelihood that an
index scan which accesses the same heap or index block twice will
still be in cache on the second hit, and thus need to be faulted in
only once. So this *is* a judgment about what will fit - generally
over a very short time scale. And, since bringing a page into
shared_buffers from the OS cache is much less expensive than bringing
a page into memory from disk, it's really about what will fit in
overall system memory, not just shared_buffers.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company