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

On Fri, Sep 13, 2013 at 10:08 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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).

This is a very important point: the 8gb cap is also to high. We have
a very high transaction rate server here that exploded at 32GB, was
downgraded to 2GB ran fine, then upgraded to 4GB (over my strenuous
objection) and exploded again.

The stock documentation advice I probably needs to be revised to so
that's the lesser of 2GB and 25%. I'm more and more coming around to
the opinion that in terms of shared buffers we have some major
problems that manifest in high end servers. So +1 to your point,
although I'm still ok with the auto-setting on the basis that for very
high end servers most of the setting end up being manually tweaked
anyways. We do need to be cautious though; it's not impossible that
improvements to buffers system might cause the 25% advice to revised
upwards in the not-to-near future if some of the problems get solved..

merlin