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

On Tue, Sep 10, 2013 at 11:39 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Mon, Sep 9, 2013 at 6:29 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Thu, Sep 5, 2013 at 09:02:27PM -0700, Josh Berkus wrote:
>>> On 09/05/2013 03:30 PM, Merlin Moncure wrote:
>>>
>>> >> Standard advice we've given in the past is 25% shared buffers, 75%
>>> >> effective_cache_size. Which would make EFS *3X* shared_buffers, not 4X.
>>> >> Maybe we're changing the conventional calculation, but I thought I'd
>>> >> point that out.
>>> >
>>> > This was debated upthread.
>>>
>>> Actually, no, it wasn't. Tom threw out a suggestion that we use 4X for
>>> historical reasons. That's all, there was no discussion.
>>>
>>> So, my point stands: our historical advice has been to set EFS to 75% of
>>> RAM. Maybe we're changing that advice, but if so, let's change it.
>>> Otherwise 3X makes more sense.
>>
>> So, what do we want the effective_cache_size default to be? 3x or 4x?
>> We clearly state:
>>
>> If you have a dedicated database server with 1GB or more of RAM,
>> a reasonable starting value for shared_buffers is 25% of the
>> memory in your system. There are some workloads where even
>>
>> If we make the default 4x, that means that people using the above
>> suggestion would be setting their effective_cache_size to 100% of RAM?
>> If we go with 4x, which I believe was the majority opinion, what shall
>> we answer to someone who asks about this contradiction?
>
> I vote for 3x. The past defaults had a different ratio, but we are
> changing things to make them better, not to leave them the same. We
> should change it be consistent with the advice we have long given.
> Sure, 3 is not a power of 2, but I usually root for the underdog.

I vote 4x on the basis that for this setting (unlike almost all the
other memory settings) the ramifications for setting it too high
generally aren't too bad. Also, the o/s and temporary memory usage as
a share of total physical memory has been declining over time
(meaning, that if you have a 256gb memory server and follow the advice
to set to 64gb, your memory for caching is approximately 64gb).

merlin