Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 1:44 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Oct 9, 2013 at 01:34:21PM -0400, Robert Haas wrote:
>> And quite frankly I don't think I really believe the auto-tuning
>> formula has much chance of being right in the first place. It's
>> generally true that you're going to need to increase work_mem if you
>> have more memory and decrease it work_mem if you have more
>> connections, but it also depends on a lot of other things, like the
>> complexity of the queries being run, whether all of the connection
>> slots are actually routinely used, and whether you've really set
>> shared_buffers to 25% of your system's total memory, which many people
>> do not, especially on Windows. I think we're just going to create the
>> false impression that we know what the optimal value is when, in
>> reality, that's far from true.
>
> I disagree. There is nothing preventing users from setting their own
> values, but I think auto-tuning will be make people who don't change
> values more likely to be closer to an optimal values. We can't
> auto-tune to a perfect value, but we can auto-tune closer to a perfect
> value than a fixed default. Yes, auto-tuned values are going to be
> worse for some users, but I believe they will be better for most users.
>
> Having really bad defaults so everyone knows they are bad really isn't
> user-friendly because the only people who know they are really bad are
> the people who are tuning them already. Again, we need to think of the
> typical user, not us.

I think a typical user will be happier if we simply raise the default
rather than stick in an auto-tuning formula that's largely wishful
thinking. You're welcome to disagree, but you neither quoted nor
responded to my points about the sorts of scenarios in which that
might cause surprising and hard-to-debug results.

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