Robert, Tom:
On 05/06/2014 03:28 PM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I basically think the auto-tuning we've installed for
>> effective_cache_size is stupid. Most people are going to run with
>> only a few GB of shared_buffers, so setting effective_cache_size to a
>> small multiple of that isn't going to make many more people happy than
>> just raising the value - say from the current default of 128MB to, oh,
>> 4GB - especially because in my experience queries aren't very
>> sensitive to the exact value; it just has to not be way too small. I
>> bet the number of PostgreSQL users who would be made happy by a much
>> higher hard-coded default is not too different from the number that
>> will be made happy by the (completely unprincipled) auto-tuning.
>
> There is a lot to be said for that argument, especially considering
> that we're not even really happy with the auto-tuning mechanism,
> never mind the behavior it's trying to implement.
Right, the decisive question with this patch is: does it improve things
over what would have happened with most users anyway?
Based on the users I deal with ... which skew rather strongly EC2 web
applications and one-off data warehouses ... most users don't set
effective_cache_size *at all* until they hire me or chat me up on IRC.
This means that ECS is running with the default of 128MB, which means
that Postgres is seriously underestimating the probability of cached
data on most machines today.
The users I deal with are a lot more likely to have set shared_buffers
themselves, based on the 25% conventional wisdom (or based on some other
advice). And, when they want to tinker with pushing index usage, they
change random_page_cost instead, sometimes to silly values (like 0.5). So, based solely on the users I deal with, I
wouldfind automatically
setting effective_cache_size to 3X or 4X shared_buffers to be a benefit
compared to leaving it at its current fixed low default.
Other people on this list deal with different kinds of users, so if
people work with a class of users where a default of 4 X shared_buffers
would be definitely a worse idea than the current default, then please
speak up.
ECS is definitely a "weak knob", as Robert says, but it's *good* that
it's a weak knob. This means that we can make large adjustments in it
without introducing a lot of highly variable behavior ... instead of
random_page_cost, which does.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com