Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 4:40 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Well, if a person does not use vacuum_work_mem, then the cost to that
>> person is low. If they do, the benefits could be immense. At the
>> Heroku office, I've had people wonder why creating an index took what
>> seemed like way too long. I told them to increase
>> maintenance_work_mem, and then the index creation was almost
>> instantaneous. Now, you can attribute some of that to the I/O of temp
>> files on EC2's ephemeral storage, and you'd probably have a point, but
>> that certainly isn't the whole story there.
>
> I am unclear what you are suggesting here.  Are you saying you want a
> separate vacuum_work_mem and maintenance_work_mem so they can have
> different defaults?

Well, the Postgres defaults won't really change, because the default
vacuum_work_mem will be -1, which will have vacuum defer to
maintenance_work_mem. Under this scheme, vacuum only *prefers* to get
bound working memory size from vacuum_work_mem. If you don't like
vacuum_work_mem, you can just ignore it.

This allows someone like me (or an author of a tool like pgtune, even)
to set maintenance_work_mem appreciably higher, because I know that
over-allocation will only be a problem when a less well informed human
writes a utility command and waits for it to finish (that might not be
true in the broadest possible case, but it's pretty close to true).
That's a very important distinction to my mind. It's useful to have
very large amounts of memory for index creation; it is generally much
less useful to have such large allocations for vacuum, and if
autovacuum ever does use a lot more memory than is generally expected
(concurrent autovacuum worker activity is probably a factor here),
that could be totally surprising, mysterious or otherwise inopportune.
Obviously not everyone can afford to be an expert.

It's relatively rare for a human to do a manual VACUUM from psql, but
there might be some POLA issues around this if they set
maintenance_work_mem high for that. I think they're resolvable and
well worth it, though. Quite apart from the general scenario where
there is a relatively small number of well informed people that
anticipate under-sizing maintenance_work_mem during semi-routine index
creation will be a problem, there is no convenient way to give tools
like pg_restore a custom maintenance_work_mem value. And, even
well-informed people can be forgetful!

-- 
Peter Geoghegan