Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 10:30 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Josh Berkus suggested here that work_mem and maintenance_work_mem could
> be auto-tuned like effective_cache_size:
>
> http://www.postgresql.org/message-id/50ECCF93.3060101@agliodbs.com

I think that this is unlikely to work out well. effective_cache_size
is a relatively unimportant parameter and the main thing that is
important is not to set it egregiously too low. The formula we've
committed is probably inaccurate in a large number of case, but it
doesn't really matter, because it doesn't do that much in the first
place.

The same cannot be said for work_mem. Setting it too low cripples
performance; setting it too high risks bringing the whole system down.
Putting an auto-tuning formula in place that depends on the values
for multiple other GUCs is just asking for trouble. Just to give a
few example, suppose that a user increases shared_buffers. Magically,
work_mem also increases, and everything works great until a load spike
causes the system to start swapping, effectively dead in the water.
Or suppose the user increases max_connections; all of their query
plans change, probably getting worse. The value of the auto-tuning
has got to be weighed against the risk of unintended consequences and
user confusion, which IMHO is pretty high in this case.

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 think what is really needed is not so much to auto-tune work_mem as
to provide a more sensible default. Why not just change the default
to 4MB and be done with it?

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