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

On 05/06/2014 01:38 PM, Simon Riggs wrote:
>> Most of them? Really?
>
> I didn't use the word "most" anywhere. So not really clear what you are saying.

Sorry, those were supposed to be periods, not question marks. As in
"Most of them. Really."

>> 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.

Well, if you're going to buck the conventional wisdom, you need to
provide a factual and numerical basis for your arguments. So far, I
haven't seen you do so, although I'll admit that I haven't read 100% of
hackers traffic. So if you have previously presented benchmarking
results or math, please post a link to the archives.

>> (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??

Are you just going to ask rhetorical questions?

> 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).

That 20% to 25% recommendation had a factual and numerical basis, based
on extensive testing using DBT2 at OSDL. While we have reason to
believe that advice may be somewhat dated, nobody has undertaken the
benchmarking work to create a new advice basis. If you have done so, you
have not shared the results.

> 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?

So? Create a benchmark. Prove that you're right. I'd love to see it,
we're suffering from a serious lack of data here.

> 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.

That makes no sense whatsoever. Again, show me the math.

> 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.

Asking rhetorical questions based on extreme perspectives that you don't
really believe in is *definitionally* trolling. If you're going to make
an argument in favor of different tuning advice, then do it based on
something in which you actually believe, based on hard evidence.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com