Re: vacuum freeze - possible improvements - Mailing list pgsql-hackers
| From | Virender Singla |
|---|---|
| Subject | Re: vacuum freeze - possible improvements |
| Date | |
| Msg-id | CAM6Zo8xKwYNOe-jaa=2A-ov4WCQM3hoMz8AHccu7VVo5q3n7QQ@mail.gmail.com Whole thread Raw |
| In response to | Re: vacuum freeze - possible improvements (Masahiko Sawada <sawada.mshk@gmail.com>) |
| Responses |
Re: vacuum freeze - possible improvements
Re: vacuum freeze - possible improvements |
| List | pgsql-hackers |
Thanks Masahiko for the response.
"What is
the use case where users want to freeze fewer transactions, meaning
invoking anti-wraparound frequently?"
the use case where users want to freeze fewer transactions, meaning
invoking anti-wraparound frequently?"
My overall focus here is anti wraparound vacuum on huge tables in emergency situations (where we reached very close to 2B transactions or already in outage window). In this situation we want to recover ASAP instead of having many hours of outage.The Purpose of increasing "vacuum_freeze_min_age" to high value is that anti wraparound vacuum will have to do less work because we are asking less transactions/tuples to freeze (Of Course subsequent vacuum has to do the remaining work).
"So the vacuum freeze will still have to
process tuples that are inserted/modified during consuming 1 billion
transactions. It seems to me that it’s not fewer transactions."
process tuples that are inserted/modified during consuming 1 billion
transactions. It seems to me that it’s not fewer transactions."
Yes another thing here is anti wraparound vacuum also cleans dead tuples but i am not sure what we can do to avoid that.
There can be vacuum to only freeze the tulpes?
Thanks for sharing PG14 improvements, those are nice to have. But still the anti wraparound vacuum will have to scan all the pages (from visibility map) even if we are freezing fewer transactions because currently there is no way to know what block/tuple contains which transaction id. If there is a way then it would be easier to directly freeze those tuples quickly and advance the relfrozenxid for the table.
On Tue, Apr 13, 2021 at 7:52 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Mon, Apr 12, 2021 at 5:38 PM Virender Singla <virender.cse@gmail.com> wrote:
>
> Hi Postgres Community,
>
> Regarding anti wraparound vacuums (to freeze tuples), I see it has to scan all the pages which are not frozen-all (looking at visibility map). That means even if we want to freeze less transactions only (For ex - by increasing parameter vacuum_freeze_min_age to 1B), still it will scan all the pages in the visibility map and a time taking process.
If vacuum_freeze_min_age is 1 billion, autovacuum_freeze_max_age is 2
billion (vacuum_freeze_min_age is limited to the half of
autovacuum_freeze_max_age). So vacuum freeze will still have to
process tuples that are inserted/modified during consuming 1 billion
transactions. It seems to me that it’s not fewer transactions. What is
the use case where users want to freeze fewer transactions, meaning
invoking anti-wraparound frequently?
>
> Can there be any improvement on this process so VACUUM knows the tuple/pages of those transactions which need to freeze up.
>
> Benefit of such an improvement is that if we are reaching transaction id close to 2B (and downtime), that time we can quickly recover the database with vacuuming freeze only a few millions rows with quick lookup rather than going all the pages from visibility map.
Apart from this idea, in terms of speeding up vacuum,
vacuum_failsafe_age parameter, introduced to PG14[1], would also be
helpful. When the failsafe is triggered, cost-based delay is no longer
be applied, and index vacuuming is bypassed in order to finish vacuum
work and advance relfrozenxid as quickly as possible.
Regards
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e55e7d1755cefbb44982fbacc7da461fa8684e6
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
pgsql-hackers by date: