Re: Auto-tuning work_mem and maintenance_work_mem - Mailing list pgsql-hackers

On Thu, Oct 10, 2013 at 10:20:36PM -0700, Josh Berkus wrote:
> Robert,
> 
> >> The counter-proposal to "auto-tuning" is just to raise the default for
> >> work_mem to 4MB or 8MB.  Given that Bruce's current formula sets it at
> >> 6MB for a server with 8GB RAM, I don't really see the benefit of going
> >> to a whole lot of code and formulas in order to end up at a figure only
> >> incrementally different from a new static default.
> > 
> > Agreed.  But what do you think the value SHOULD be on such a system?
> 
> That's the problem: It Depends.
> 
> One thing in particular which is an issue with calculating against
> max_connections is that users who don't need 100 connections seldom
> *reduce* max_connections.  So that developer laptop which only needs 3
> connections is still going to have a max_connections of 100, just like
> the DW server where m_c should probably be 30.
> 
> > I guess the point I'm making here is that raising the default value is
> > not mutually exclusive with auto-tuning.  We could quadruple the
> > current defaults for work_mem and maintenance_work_mem and be better
> > off right now, today.  Then, we could improve things further in the
> > future if and when we agree on an approach to auto-tuning.  And people
> > who don't use the auto-tuning will still have a better default.
> 
> Seems fine to me.

I think we are nearing a conclusion on these issues, and I thank
everyone for the vigorous discussion.  When Josh showed disappointment
at the small increases in work_mem and maintenance_work_mem from
autotuning, I realized the complexity of autotuning just wasn't
warranted here.  Andrew's concern about the risks of having a work_mem
too high was also sobering.  Effective_cache_size has neither of these
issues, and hence was logical for auto-tuning.  I know Robert originally
suggested just improving the work_mem default --- I now agree with him,
and am sorry it took me so long to realize he was right.

One other problem with auto-tuning is that it really relies not only on
allocated_memory, but also on max_connections and
autovacuum_max_workers, which are going to be rather arbitrary and hard
for a user to set good enough to help auto-tuning.  Josh might be right
that auto-tuning of work_mem has to be more dynamic, perhaps based on
the number of _active_ backends or number of backends who have allocate
or are currently using work_mem.  Our new dynamic shared memory
allocation routines might help here in allocationg memory that can be
easily purged from the process address space.  I am now seeing a pattern
that per-backend allocations really need run-time tuning, rather than
being based on fixed GUC values.

In summary, I think we need to:

*  decide on new defaults for work_mem and maintenance_work_mem
*  add an initdb flag to allow users/packagers to set shared_bufffers?
*  add an autovacuum_work_mem setting?
*  change the default for temp_buffers?

I will try to think some more about work_mem dynamic/runtime tuning and
return to it later.  I know Kevin has also thought about it.

I am also interesting in working on a server-side function that will
make configuration suggestions or use ALTER SYSTEM to set values.  I
could do it in PL/pgSQL, but PL/Perl would allow me to run operating
system commands to probe for OS information.  The function could look at
statistics and pg_buffercache output, and would be run during a typical
workload.

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