Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Claudio Freire <klaussfreire(at)gmail(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Date: 2013-01-08 22:23:36
Message-ID: CA+TgmoYpJntEMkE1zWd+MMEXiOaTpdPpbuNKGvcF5DM_2HJ+zA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan 8, 2013 at 9:53 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Tue, Jan 8, 2013 at 11:39 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Reference: http://postgresql.1045698.n5.nabble.com/Simple-join-doesn-t-use-index-td5738689.html
>>
>> This is a pretty common gotcha: user sets shared_buffers but misses
>> the esoteric but important effective_cache_size.  ISTM
>> effective_cache_size should always be >= shared buffers -- this is a
>> soft configuration error that could be reported as a warning and
>> perhaps overridden on the fly.
>
> Not true. If there are many concurrent users running concurrent
> queries against parallel databases, such as some test systems I have
> that contain many databases for many test environments, such a setting
> wouldn't make sense. If a DBA sets it to lower than shared_buffers,
> that setting has to be honored.
>
> Rather, I'd propose the default setting should be "-1" or something
> "default" and "automagic" that works most of the time (but not all).

+1.  I've found that a value of three-quarters of system memory works
pretty well most of the time.  Of course, there's not a single,
portable way of getting that on every platform we support.  If I
remember my last investigation into this area, to use that particular
rule we would probably need at least three paths - one for Windows,
one for System V descendents, and one for BSD descendents.  And there
might still be obscure things that wouldn't be covered.  Of course
this also makes the admittedly unwarranted assumption that the
database owns the box, which could be wrong too, but purposely
lowballing effective_cache_size to discourage index-scan plans seems
unlikely to be a winning strategy.

A cruder heuristic that might be useful is 3 * shared_buffers.  If
people follow the guidance to set shared_buffers around 25% of RAM,
then this will work out to around 75% again.  Of course, many people,
for valid reasons, use smaller values of shared_buffers than that, and
a few use larger ones.  It might still be better than no auto-tuning,
though I wouldn't swear to it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2013-01-08 22:28:33
Subject: Re: [PATCH 2/5] Make relpathbackend return a statically result instead of palloc()'ing it
Previous:From: Stephen FrostDate: 2013-01-08 22:21:54
Subject: Re: Index build temp files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group