Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 7:30 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Josh Berkus suggested here that work_mem and maintenance_work_mem could
> be auto-tuned like effective_cache_size:

Have you ever thought that the role of maintenance_work_mem was a bit
muddled? It recently occurred to me that it might be a good idea to
have a separate setting that is used to bound the amount of memory
used by autovacuum (and possibly VACUUM generally) in preference to
the more generic maintenance_work_mem setting.

In the docs, maintenance_work_mem has this caveat:

"Note that when autovacuum runs, up to autovacuum_max_workers times
this memory may be allocated, so be careful not to set the default
value too high."

If Heroku could increase maintenace_work_mem without having it affect
the amount of memory used by autovacuum workers, I'm fairly confident
that our setting would be higher. Sure, you can just increase it as
you need to, but you have to know about it in the first place, which
is asking too much of many people tasked with semi-routine maintenance
tasks like creating indexes.

--
Peter Geoghegan