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

On Fri, Sep 13, 2013 at 11:07 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-09-13 10:50:06 -0500, Merlin Moncure wrote:
>> The stock documentation advice I probably needs to be revised to so
>> that's the lesser of 2GB and 25%.
>
> I think that would be a pretty bad idea. There are lots of workloads
> where people have postgres happily chugging along with s_b lots bigger
> than that and see benefits.
> We have a couple people reporting mostly undiagnosed (because that turns
> out to be hard!) problems that seem to be avoided with smaller s_b. We
> don't even remotely know enough about the problem to make such general
> recommendations.

I happen to be one of those "couple" people. Load goes from 0.1 to
500 without warning then back to 0.1 equally without warning.
Unfortunately the server is in a different jurisdiction such that it
makes deep forensic analysis impossible. I think this is happening
more and more often as postgres is becoming increasingly deployed on
high(er) -end servers. I've personally (alone) dealt with 4-5
confirmed cases and there have been many more. We have a problem.

But, to address your point, the "big s_b" benefits are equally hard to
quantify (unless your database happens to fit in s_b) -- they mostly
help high write activity servers where the write activity fits a very
specific pattern. But the risks of low s_b (mostly slightly higher
i/o and query latency) are much easier to deal with than high s_b
(even if less likely); random inexplicable server stalls and other
weird manifestations. My "stock" advice remains to set to max 2gb
until having a reason (of which there can be many) to set otherwise.

merlin