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

On Tue, Jan 8, 2013 at 05:23:36PM -0500, Robert Haas wrote:
> > 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.

Agreed. This is similar to the fudge we have about random_page_cost:

Random access to mechanical disk storage is normally much more expensive
than four-times sequential access. However, a lower default is used
(4.0) because the majority of random accesses to disk, such as indexed
reads, are assumed to be in cache. The default value can be thought of
as modeling random access as 40 times slower than sequential, while
expecting 90% of random reads to be cached.

effective_cache_size is impossible to set accurately because you have no
idea what other things might be in the cache, or what other concurrent
queries might be filling the cache. Going with something at least
partly reasonable makes a lot more sense. While we don't know the size
of RAM, we do know the size of shared_buffers, and keying on that for a
default seems like a no-brainer, rather than using 128MB.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +