Re: Changing the autovacuum launcher scheduling; oldest table first algorithm

On Wed, Mar 14, 2018 at 11:29 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Tue, Mar 6, 2018 at 11:27 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>> Hello
>>
>> I haven't read your respective patches yet, but both these threads
>> brought to memory a patch I proposed a few years ago that I never
>> completed:
>>
>> https://www.postgresql.org/message-id/flat/20130124215715.GE4528%40alvh.no-ip.org
>
> Thank you for sharing the thread.
>
>>
>> In that thread I posted a patch to implement a prioritisation scheme for
>> autovacuum, based on an equation which was still under discussion when
>> I abandoned it. Chris Browne proposed a crazy equation to mix in both
>> XID age and fraction of dead tuples; probably that idea is worth
>> studying further. I tried to implement that in my patch but I probably
>> didn't do it correctly (because, as I recall, it failed to work as
>> expected). Nowadays I think we would also consider the multixact freeze
>> age, too.
>>
>> Maybe that's worth giving a quick look in case some of the ideas there
>> are useful for the patches now being proposed.
>
> Yeah, that's definitely useful for the patches. I'll look at this and
> will discuss that here.
>

I read that discussion. If I understand correctly, the patch proposed
in that discussion mainly focuses on table-selection algorithm for
each AV worker. But the patch I proposed also changes it but the main
part of this patch is to change the database-selection algorithm by AV
launcher (sorry, maybe the subject leads misleading). The problem I'd
like to deal with is, when there is at least one database needing
anti-wraparound vacuum it concentrates launching new AV workers on one
database.

If we change the table-selection algorithm to the "oldest table first"
algorithm in that case, we can deal with that problem by this patch.
However, I think it's worthwhile to consider another table-selection
algorithm such as the proposal on that old thread. So I think it would
be better to think these issue separately. That is, we can change the
database-selection algorithm in order to prevent the concentration
problem while not changing table-selection algorithm.

This brought me another idea; having AV workers report its autovacuum
results at its AV worker slot so that they can teach autovacuum status
to AV launcher. For example, each AV worker can report the following
information.

* Database oid
* The number of tables needing vacuum.
* The number of vacuumed tables.
* The number of skipped tables due to being processed by other AV workers.
* Timestamp of last update

If there is an up-to-date information meaning either that there is no
tables needing vacuum or that there is only table needing vacuum but
being vacuumed by other worker, AV launcher can launches new one to
other database.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center