Re: Auto-tuning work_mem and maintenance_work_mem

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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services