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

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