Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 10:45:52AM -0400, Bruce Momjian wrote:
> On Wed, Oct  9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
> >     Effectively, if every session uses one full work_mem, you end up with
> >     total work_mem usage equal to shared_buffers.
> >
> >     We can try a different algorithm to scale up work_mem, but it seems wise
> >     to auto-scale it up to some extent based on shared_buffers.
> >
> >
> > In my experience a optimal value of work_mem depends on data and load, so I
> > prefer a work_mem as independent parameter.
>
> But it still is an independent parameter.  I am just changing the default.
>
> > maintenance_work_mem can depend on work_mem ~ work_mem * 1 * max_connection / 4
>
> That is kind of hard to do because we would have to figure out if the
> old maintenance_work_mem was set from a default computation or by the
> user.

FYI, this auto-tuning is not for us, who understand the parameters and
how they interact, but for the 90% of our users who would benefit from
better defaults.  It is true that there might now be cases where you
would need to _reduce_ work_mem from its default, but I think the new
computed default will be better for most users.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +