Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 04:38:01PM +0200, Andres Freund wrote:
> On 2013-10-09 10:35:28 -0400, Bruce Momjian wrote:
> > On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote:
> > > On 2013-10-09 10:30:46 -0400, Bruce Momjian 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
> > > >
> > > > The attached patch implements this, closely matching the default values
> > > > for the default shared_buffers value:
> > >
> > > There imo is no correlation between correct values for shared_buffers
> > > and work_mem at all. They really are much more workload dependant than
> > > anything.
> >
> > Well, that is true, but the more shared_buffers you allocate, the more
> > work_mem you _probably_ want to use. This is only a change of the
> > default.
>
> Not at all. There's lots of OLTP workloads where huge shared buffers are
> beneficial but you definitely don't want a huge work_mem.
>
> > Effectively, if every session uses one full work_mem, you end up with
> > total work_mem usage equal to shared_buffers.
>
> But that's not how work_mem works. It's limiting memory, per node in the
> query. So a complex query can use it several dozen times.

True, but again, odds are all sessions are not going to use the full
work_mem allocation, so I figured assuming each session uses one full
work_mem was probably an over-estimate.

You are saying that auto-tuning work_mem for typical workloads is not a
win? I don't understand how that can be true. You can always change
the default for atypical workloads.

As a data point, users often set shared_buffers to 2GB, but the default
work_mem of 1MB would mean you would use perhaps 100MB for all sorting
--- that seems kind of small as a default.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +