Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 10:30 AM, Bruce Momjian <bruce@momjian.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
dependson 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