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

On 6 May 2014 18:08, Josh Berkus <josh@agliodbs.com> wrote:
> On 05/06/2014 08:41 AM, Simon Riggs wrote:
>> On 6 May 2014 15:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Simon Riggs <simon@2ndQuadrant.com> writes:
>>>> 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.
>>>
>>> Apparently, you don't even understand what this parameter is for.
>>> Setting it smaller than shared_buffers is insane.
>>
>> You know you can't justify that comment and so do I. What workload is
>> so badly affected as to justify use of the word insane in this
>> context?
>
> Most of them?  Really?

I didn't use the word "most" anywhere. So not really clear what you are saying.


> I have to tell you, your post sounds like you've missed out on the last
> 12 years of PostgreSQL query tuning.  Which is a little shocking
> considering where you've spent that 12 years.
I read the code, think what to say and then say what I think, not
rely on dogma.

I tried to help years ago by changing the docs on e_c_s, but that's
been mostly ignored down the years, as it is again here.


>> I can read code. But it appears nobody apart from me actually does, or
>> at least understand the behaviour that results.
>
> So, break it down for us: explain how we'll get desirable query plans
> out of the current code if:
>
> (1) Table & Index is larger than shared_buffers;
> (2) Table & Index is smaller than RAM;
> (3) Selectivity is 0.02
> (4) ECS is set lower than shared_buffers

Is that it? The above use case is the basis for a default setting??

It's a circular argument, since you're assuming we've all followed
your advice of setting shared_buffers to 25% of RAM, which then
presumes a large gap between (1) and (2). It also ignores that if ECS
is set low then it increases the cost, but does not actually preclude
index scans larger than that setting. It also ignores that if your
database fits in RAM, your random_page_cost setting is wrong and
lowering that appropriately will increase the incidence of index scans
again.

You should also include

(5) You're only running one query at a time (which you know, how?)
(6) You don't care if you flush your cache for later queries
(7) You've got big tables yet are not partitioning them effectively

> I think the current cost math does a pretty good job of choosing the
> correct behavior if ECS is set correctly.  But if it's not, no.
>
> If I'm wrong, then you've successfully found a bug in our costing math,
> so I'd love to see it.

Setting it high generates lovely EXPLAINs for a single query, but do
we have any evidence that whole workloads are better off with higher
settings? And that represents the general case?

And it makes sense even if it makes it bigger than actual RAM??

If you assume that you can use all of that memory, you're badly wrong.
Presumably you also set work_mem larger than shared_buffers, since
that will induce exactly the same behaviour and have the same
downsides. (Large memory usage for single query, but causes cache
churn, plus problems if we try to overuse RAM because of concurrent
usage).

In the absence of  performance measurements that show the genuine
effect on workloads, I am attempting to make a principle-based
argument. I suggested 25% of shared_buffers because we already use
that as the point where other features cut in to minimise cache churn.
I'm making the argument that if *that* setting is the right one to
control cache churn, then why is it acceptable for index scans to
churn up even bigger chunks of cache?

In case it wasn't clear, I am only suggesting 25% of shared_buffers
for large settings, not for micro-configurations. My proposal to
remove the setting completely was a rhetorical question, asking why we
have a setting for this parameter and yet no tunables for other
things.

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