Re: autovacuum_work_mem

On Wed, Dec 11, 2013 at 2:37 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 11 December 2013 17:57, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Extensive testing will be needed to prove
>> that the new algorithm doesn't perform worse than the current
>> algorithm in any important cases.
>
> Agreed, but the amount of testing seems equivalent in both cases,
> assuming we weren't going to skip it for this patch.
>
> Let me repeat the question, so we are clear...
> In what circumstances will the memory usage from multiple concurrent
> VACUUMs become a problem? In those circumstances, reducing
> autovacuum_work_mem will cause more passes through indexes, dirtying
> more pages and elongating the problem workload. I agree that multiple
> concurrent VACUUMs could be a problem but this
> doesn't solve that, it just makes things worse.

That's not the problem the patch is designed to solve. It's intended
for the case where you want to allow more or less memory to autovacuum
than you do for index builds. There's no principled reason that
anyone should want those things to be the same. It is not difficult
to imagine situations in which you would want one set to a very
different value than the other. In particular it seems quite likely
to me that the amount of memory appropriate for index builds might be
vastly more than is needed by autovacuum. For example, in a
data-warehousing environment where updates are rare but large index
builds by the system's sole user are frequent, someone might want to
default index builds to 64GB of RAM (especially after Noah's patch to
allow huge allocations for the tuple array while sorting) but only
need 256MB for autovacuum.

In general, I'm reluctant to believe that Peter proposed this patch
just for fun. I assume this is a real-world problem that Heroku
encounters in their environment. If not, well then that's different.

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