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

On Wed, May 7, 2014 at 4:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2014-05-07 13:51:57 -0700, Jeff Janes wrote:
>> On Wed, May 7, 2014 at 11:38 AM, Andres Freund <andres@2ndquadrant.com>wrote:
>>
>> > On 2014-05-07 13:32:41 -0500, Merlin Moncure wrote:
>> > >
>> > > *) raising shared buffers does not 'give more memory to postgres for
>> > > caching' -- it can only reduce it via double paging
>> >
>> > That's absolutely not a necessary consequence. If pages are in s_b for a
>> > while the OS will be perfectly happy to throw them away.
>> >
>>
>> Is that an empirical observation?
>
> Yes.
>
>> I've run some simulations a couple years
>> ago, and also wrote some instrumentation to test that theory under
>> favorably engineered (but still plausible) conditions, and couldn't get
>> more than a small fraction of s_b to be so tightly bound in that the kernel
>> could forget about them.  Unless of course the entire workload or close to
>> it fits in s_b.
>
> I think it depends on your IO access patterns. If the whole working set
> fits into the kernel's page cache and there's no other demand for pages
> it will stay in. If you constantly rewrite most all your pages they'll
> also stay in the OS cache because they'll get written out. If the churn
> in shared_buffers is so high (because it's so small in comparison to the
> core hot data set) that there'll be dozens if not hundreds clock sweeps
> a second you'll also have no locality.
> It's also *hugely* kernel version specific :(

right.  This is, IMNSHO, exactly the sort of language that belongs in the docs.

merlin