Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 10, 2013 at 2:04 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Wed, Oct 9, 2013 at 4:40 PM, Bruce Momjian <bruce(at)momjian(dot)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!

While unrelated to the main topic of this thread, I think this is very
important as well. I often have to advice people to remember to cap
their maintenance_work_mem because of autovacuum, and to remember to
re-tune maintenance_wokr_mem when they change the number of autovacuum
workers.

I would, however, vote for an autovacuum_work_mem rather than a
vacuum_work_mem. Analog to the autovacuum_vacuum_cost_* parameters
that override the "foreground" parameters.

(Though you can give a custom one to pg_restore can't you - just issue
a SET command inthe session, it won't affect autovac or anybody else)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/