Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers - Mailing list pgsql-hackers

From Merlin Moncure
Subject Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Date
Msg-id CAHyXU0yTrFE92DtPo5Rf2Y-1kLmY-4bV1DgPm-WipdFZqD27FQ@mail.gmail.com
Whole thread Raw
In response to Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-hackers
On Tue, Sep 10, 2013 at 11:39 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Sep 9, 2013 at 6:29 PM, Bruce Momjian <bruce@momjian.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



pgsql-hackers by date:

Previous
From: Jeff Janes
Date:
Subject: Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Next
From: Heikki Linnakangas
Date:
Subject: Re: Strange hanging bug in a simple milter