Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Mon, Jan 29, 2018 at 3:33 PM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> From: Masahiko Sawada [mailto:sawada(dot)mshk(at)gmail(dot)com]
>> What I thought is that a worker reports these two values after scanned
>> pg_class and after freezed a table. The launcher decides to launch a new
>> worker if the number of tables requiring anti-wraparound vacuum is greater
>> than the number of workers running on the database. Similarly, the
>> autovacuum launcher doesn't launch a new worker if two values are equal,
>> which means all tables requiring an anti-wraparound vacuum is being vacuumed.
>> There are chances that new relation is added during a worker is running
>> on the last one table that requires anti-wraparound vacuum and launcher
>> launches a new worker on the database. I think it's no problem because the
>> new worker would update that two values and exits soon.
>
> I got it. Currently, the launcher assigns all workers to one database even if that database has only one table in danger of wraparound. With your suggestion, the launcher assigns as many workers as the tables to be frozen, and use remaining workers for the other databases.
>
>
>> > How about just modifying do_start_worker(), so that the launcher chooses
>> a database in the following order?
>> >
>> > 1. wraparound-risky database not being vacuumed by any worker 2.
>> > non-wraparound-risky database not being vacuumed by any worker 3.
>> > wraparound-risky database being vacuumed by any worker 4.
>> > non-wraparound-risky database being vacuumed by any worker
>> >
>>
>> IMO the limiting the number of worker on a database to 1 seems risky.
>> If a database has many tables that require an anti-wraparound vacuum, it
>> takes a long time to freeze the all of these tables. In current
>> implementation, as I mentioned as above, launcher can launch multiple worker
>> on the one database.
>
> I can understand your concern. On the other hand, it's unfair that one database could monopolize all workers, because other databases might also be facing wraparound risk.

On third thought, we can change the policy of launching workers so
that the launcher dispatches workers evenly to wraparound-risky
databases instead of choosing only one most wraparound-risky database.

Regards,

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