Re: Auto-tuning work_mem and maintenance_work_mem

2013/10/9 Bruce Momjian <bruce(at)momjian(dot)us>

> 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.
>

then we should to use as base a how much dedicated RAM is for PG - not
shared buffers.

Pavel

>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>