Re: autovacuum_work_mem

On Wed, Dec 11, 2013 at 10:41 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> It looks fairly easy to estimate the memory needed for an auto vacuum,
> since we know the scale factor and the tuple estimate. We can then use
> the memory estimate to alter the scheduling of work. And/or we can use
> actual memory usage and block auto vac workers if they need more
> memory than is currently available because of other workers.
>
> We would still benefit from a new parameter in the above sketch, but
> it would achieve something useful in practice.
>
> That's about 2-3 days work and I know Peter can hack it. So the
> situation is not perfection-sought-blocking-good, this is more like
> fairly poor solution being driven through when a better solution is
> available within the time and skills available.

I don't agree with that assessment.  Anything that involves changing
the scheduling of autovacuum is a major project that will legitimately
provoke much controversy.  Extensive testing will be needed to prove
that the new algorithm doesn't perform worse than the current
algorithm in any important cases.  I have my doubts about whether that
can be accomplished in an entire release cycle, let alone 2-3 days.
In contrast, the patch proposed does something that is easy to
understand, clearly safe, and an improvement over what we have now.

Quite apart from the amount of development time required, I think that
the idea that we would use the availability of memory to schedule work
is highly suspect.  You haven't given any details on what you think
that algorithm might look like, and I doubt that any simple solution
will do.  If running more autovacuum workers drives the machine into
swap, then we shouldn't, but we have no way of calculating what size
memory allocation will cause that to happen.  But NOT running
autovacuum workers isn't safe either, because it could cause table
bloat that them drives the machine into swap.  We have no way of
knowing whether that will happen either.  So I think your contention
that we have the necessary information available to make an
intelligent decision is incorrect.

Regardless, whether or not a more complex change is within Peter's
technical capabilities is utterly irrelevant to whether we should
adopt the proposed patch.  Your phrasing seems to imply that you would
not ask such a thing of a less-talented individual, and I think that
is utterly wrong.  Peter's technical acumen does not give us the right
to ask him to write a more complex patch as a condition of getting a
simpler one accepted.

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