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

From Robert Haas
Subject Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Date
Msg-id CA+TgmoYyrQXADHJiB9nSzoVkx9sLtXNJHhcX80_xXq-GcPngHg@mail.gmail.com
Whole thread Raw
In response to Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers  (Josh Berkus <josh@agliodbs.com>)
Responses Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
List pgsql-hackers
On Wed, Sep 11, 2013 at 3:40 PM, Josh Berkus <josh@agliodbs.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



pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: Custom Plan node
Next
From: Cédric Villemain
Date:
Subject: Re: PostgreSQL 9.3 beta breaks some extensions "make install"