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

On 2014-05-06 17:43:45 +0100, Simon Riggs wrote:
> On 6 May 2014 15:17, Andres Freund <andres@2ndquadrant.com> wrote:
> 
> >> Lets fix e_c_s at 25% of shared_buffers and remove the parameter
> >> completely, just as we do with so many other performance parameters.
> >
> > That'd cause *massive* regression for many installations. Without
> > significantly overhauling costsize.c that's really not feasible. There's
> > lots of installations that use relatively small s_b settings for good
> > reasons. If we fix e_c_s to 25% of s_b many queries on those won't use
> > indexes anymore.
> 
> "many queries" can't be correct.

It is.

> All this changes is the cost of
> IndexScans that would use more than 25% of shared_buffers worth of
> data. Hopefully not many of those in your workload. Changing the cost
> doesn't necessarily prevent index scans either. And if there are many
> of those in your workload AND you run more than one at same time, then
> the larger setting will work against you. So the benefit window for
> such a high setting is slim, at best.

Why? There's many workloads where indexes are larger than shared buffers
but fit into the operating system's cache. And that's precisely what
effective_cache_size is about.
Especially on bigger machines shared_buffers can't be set big enough to
actually use all the machine's memory. It's not uncommon to have 4GB
shared buffers on a machine with 512GB RAM... It'd be absolutely
disastrous to set effective_cache_size to 1GB for an analytics workload.

> I specifically picked 25% of shared_buffers because that is the point
> at which sequential scans become more efficient and use the cache more
> efficiently. If our cost models are correct, then switching away from
> index scans shouldn't hurt at all.

More often than not indexes are smaller than the table size, so this
argument doesn't seem to make much sense.

Greetings,

Andres Freund

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