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

On Wed, Sep 11, 2013 at 3:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> 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.
>
> Agreed. I think we should take the patch as-is, and spend the rest of
> the 9.4 dev cycle arguing about 3x vs. 4x.
>
> ;-)

I'm happy with that option, but I think the larger point here is that
this only has a hope of being right if you're setting shared_buffers
to 25% of system memory. And more and more, people are not doing
that, because of the other recommendation, not much discussed here, to
cap shared_buffers at about 8GB. Systems whose total memory is far
larger than 32GB are becoming quite commonplace, and only figure to
become moreso. So while I don't particularly object to this proposal,
it would have had a lot more value if we'd done it 5 years ago.

Now the good news is that right now the default is 128MB, and under
any of these proposals the default will go up, quite a bit. Default
shared_buffers is now 128MB, so we're looking at raising the default
to at least 384MB, and for people who also tune shared_buffers but
might not bother with effective cache size, it'll go up a lot more.
That's clearly a move in the right direction even if the accuracy of
the formula is suspect (which it is).

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