Jeff Janes <jeff.janes@gmail.com> writes:
> On Sat, Feb 14, 2015 at 10:14 AM, <jd@ods.org> wrote:
>> So it appears that the logic is that autovacuum operates solely on one
>> database at a time. Even if there is only one table that needs vacuuming in
>> that first database, it will not spawn any workers to vacuum tables that do
>> need vacuuming in a second database until it has completed vacuuming the
>> necessary tables from the first database.
> This is a known issue, but so far there hasn't be a lot of urgency to fix
> it, probably because of a lack of complaints from the field until now, and
> because there are a few ways to address it and no one has forcefully
> advocated for one particular solution.
> Basically it considers the database being in need of wrap around vacuum as
> an emergency (even thought it probably isn't) and funnels all future
> workers to it. But when only one table is in need of that wrap around
> vacuum, all the other workers can't accomplish anything in that database,
> and other databases get starved.
Yeah. The thing that makes it difficult to behave in an ideal way is that
the autovacuum launcher has only database-level granularity of knowledge.
It knows that there's at least one table in that database that is at risk
of wraparound, but it doesn't know if there's more than one (and, btw,
it usually is a reasonable bet that there *is* more than one, because
probably most of the database last got an anti-wraparound vacuum at about
the same time). So it's hard to tell whether "send all the troops that
way" is good strategy or just a waste of resources.
The previous thread you mentioned failed to come up with any ideas that
materially improved things, and I suspect that it's not possible to do
much without giving the launcher more information than it gets now.
One simple mechanism that perhaps could do that is having autovacuum
workers report back on whether they found anything useful to do or not.
If we know we have worker(s) active in a given database, but another
worker exits reporting there was nothing for it to do, we could conclude
that that database is saturated for the moment, and stop dispatching new
workers to it till the situation changes. I'm not entirely sure about
details though; in particular you'd have to be very sure that you didn't
miss any "situation changing" conditions or a database could become
permanently ignored.
There might be other feedback mechanisms that would work better, but
I think we need to consider adding something.
regards, tom lane