Re: autovacuum scheduling starvation and frenzy

On Wed, Oct 1, 2014 at 11:44 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Robert Haas wrote:
>> This kind of seems like throwing darts at the wall. It could be
>> better if we are right to skip the database already being vacuumed for
>> wraparound, or worse if we're not.
>
> Well, it only skips the DB for half the naptime interval, so that other
> databases have a chance to be chosen before that. If you set up a
> nonsensical interval such as one day, this might be problematic.
>
> (I'm not sure I understand the darts analogy.)

I guess I meant: this seems pretty hit-or-miss. I don't see why we
should expect it to be better than what we have now. Sure, maybe
there's a table in some other database that needs to be vacuumed for
bloat more urgently than a table in the wraparound database needs to
be vacuumed to prevent XID wraparound. But the reverse could be true
also - in which case your patch could cause a cluster that would
merely have bloated to instead shut down.

The way to really know would be for the AV launcher to have knowledge
of how many tables there are in each database that are beyond the
wraparound theshold and not already been vacuumed. Then we could skip
wraparound databases where that number is 0, and give priority to
those where it isn't. I guess this is more or less what you said in
the portion of your email I'm not quoting here, but like you I'm not
quite sure how to implement that. Still, I'm reluctant to just go
change the behavior; I think it's optimistic to think that any
algorithm for making decisions without real knowledge will be better
than any other.

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