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

On 8 October 2013 17:13, Bruce Momjian <bruce@momjian.us> wrote:

> Patch applied with a default of 4x shared buffers.  I have added a 9.4
> TODO that we might want to revisit this.

I certainly want to revisit this patch and this setting.

How can we possibly justify a default setting that could be more than
physical RAM?

The maximum known safe value is the setting of shared_buffers itself,
without external knowledge. But how can we possibly set it even that
high?

Does anyone have any evidence at all on how to set this? How can we
possibly autotune it?

I prefer the idea of removing "effective_cache_size" completely, since
it has so little effect on workloads and is very frequently
misunderstood by users. It's just dangerous, without being useful.

Why do we autotune the much more important synch scan threshold, yet
allow tuning of e_c_s?

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.

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