effective_cache_size less than shared_buffers

Should we log a warning at startup when effective_cache_size is less
than shared_buffers?

-Kevin

On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote:
> Should we log a warning at startup when effective_cache_size is less
> than shared_buffers?

I would say no. Although I could see an argument for the default
effective_cache_size always being the same size as shared_buffers.

Joshua D. Drake

>
> -Kevin
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:
> On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote:
>> Should we log a warning at startup when effective_cache_size is less
>> than shared_buffers?
>
> I would say no. Although I could see an argument for the default
> effective_cache_size always being the same size as shared_buffers.

That's certainly not what we've meant historically by ECS. Generally
it's been the size of shared_buffers *and* the FS cache. If it were
just the size of shared_buffers, then we wouldn't need a 2nd setting,
would we?

--Josh

On Wed, 2009-02-25 at 17:04 -0800, Josh Berkus wrote:
> Joshua D. Drake wrote:
> > On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote:
> >> Should we log a warning at startup when effective_cache_size is less
> >> than shared_buffers?
> >
> > I would say no. Although I could see an argument for the default
> > effective_cache_size always being the same size as shared_buffers.
>
> That's certainly not what we've meant historically by ECS. Generally
> it's been the size of shared_buffers *and* the FS cache. If it were
> just the size of shared_buffers, then we wouldn't need a 2nd setting,
> would we?

We can't determine the size of the FS cache. We can determine the size
of the shared_buffers. The idea here is to eliminate one of those by
default PostgreSQL is slow issues. Since we are already using X amount
of shared_buffers we know we have at least X amount of cache.

Sincerely,

Joshua D. Drake

>
> --Josh
>
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Thu, Feb 26, 2009 at 1:33 AM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> On Wed, 2009-02-25 at 17:04 -0800, Josh Berkus wrote:
>
>> Joshua D. Drake wrote:
>>
>> > I would say no. Although I could see an argument for the default
>> > effective_cache_size always being the same size as shared_buffers.
>>
>> That's certainly not what we've meant historically by ECS.

> Since we are already using X amount
> of shared_buffers we know we have at least X amount of cache.

That's not what you wrote, at least how it was understood. It sounds
like you're in violent agreement.

> We can't determine the size of the FS cache.

Hence why we have the parameter.

> We can determine the size
> of the shared_buffers. The idea here is to eliminate one of those by
> default PostgreSQL is slow issues.

Well we won't eliminate any problems unless we actually override the
effective_cache_size setting by clipping it to shared_buffers. I don't
really see much of a problem doing that. The only case where that
would annoy someone was if they're intentionally understating
effective_cache_size to push the planner into avoiding nested loops
and I doin't think it's a powerful enough knob to be very likely used
that way.

--
greg

Greg,

>
> Well we won't eliminate any problems unless we actually override the
> effective_cache_size setting by clipping it to shared_buffers. I don't
> really see much of a problem doing that. The only case where that
> would annoy someone was if they're intentionally understating
> effective_cache_size to push the planner into avoiding nested loops
> and I doin't think it's a powerful enough knob to be very likely used
> that way.
>

My experience from PostgreSQL on Windows: effective_cache_size should
reflect the value of "system cache" from task manager. shared_buffers (on
windows) should be rather small.

My real-workload-tests (no benchmarks, real usage of DB-Server) showed that
big shared buffers on Windows have a negative effect on PostgreSQL
performance. I have found no explanation WHY it is this way.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
EuroPython 2009 will take place in Birmingham - Stay tuned!