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

On 2014-05-07 13:51:57 -0700, Jeff Janes wrote:
> On Wed, May 7, 2014 at 11:38 AM, Andres Freund <andres(at)2ndquadrant(dot)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 :(

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services