Re: autovacuum maintenance_work_mem

On Tue, Nov 16, 2010 at 1:36 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 11/16/10 9:27 AM, Robert Haas wrote:
>> I'm a little skeptical about creating more memory tunables.  DBAs who
>> are used to previous versions of PG will find that their vacuum is now
>> really slow, because they adjusted maintenance_work_mem but not this
>
> Also, generally people who are using autovacuum don't do much manual
> vacuuming, and when they do, it's easy enough to do a SET before you
> issue the VACUUM statement.
>
> So, -1 for yet another GUC.
>
>> new parameter.  If we could divide up the vacuum memory intelligently
>> between the workers in some way, that would be a win.  But just
>> creating a different variable that controls the same thing in
>> different units doesn't seem to add much.
>
> Actually, that's not unreasonable.  The difficulty with allocating
> work_mem out of a pool involves concurrency, but use of maint_work_mem
> is very low-concurrency; it wouldn't be that challenging to have the
> autovac workers pull from a pool of preset size instead of each being
> allocated the full maint_work_mem.  And that would help with over/under
> allocation of memory.

I think the difficulty is figuring out what to get the existing
workers to give us some memory when a new one comes along. You want
the first worker to potentially use ALL the memory... until worker #2
arrives.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company