Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Fri, Jan 26, 2018 at 2:22 PM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> From: Masahiko Sawada [mailto:sawada(dot)mshk(at)gmail(dot)com]
>> On Thu, Jan 25, 2018 at 3:14 PM, Tsunakawa, Takayuki
>> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
>> > * Why does autovacuum launcher always choose only one database when that
>> database need vacuuming for XID wraparound? Shouldn't it also choose other
>> databases?
>>
>> Yeah, I'd also like to fix this issue. This can be problem even in other
>> case; there are two databases that require anti-wraparound vacuum, and one
>> of them has a very large table that could take a long time to vacuum. In
>> this case, if autovacuum chooses the database having big table first,
>> another database would not be selected until an autovacuum worker completes
>> vacuum on the large table. To deal with it, I think we can make autovacuum
>> workers tell that it is no longer necessary to launch a new autovacuum worker
>> on the database to autovacuum launcher before exit. For example, autovacuum
>> worker reports both the total number of relations and the number of relations
>> that require an anti-wraparound vacuum to the stats collector.
>
> Thanks for commenting. I believe you have deep knowledge and experience with vacuum because you did a great work for freeze map in 9.6, so I appreciate your help!

Thanks. The most of hackers on the community have deep knowledge, so
I'd like to hear other opinion.

> How would you use those two counts?

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.

>
> 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. Since the above idea would be complex a
bit, as an alternated idea it might be better to specify the number of
worker to launch per database by a new GUC parameter or something. If
the number of worker running on the database exceeds that limit, the
launcher doesn't select the database even if the database is about to
wraparound.

Regards,

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