Re: Auto-tuning work_mem and maintenance_work_mem

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +