Thread: vacuum freeze - possible improvements
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.
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.
For Ex - A Binary Tree structure where it gets all the rows corresponding to a table including transaction ids. So whenever we say free all tuples having transaction id greater than x and less than y. Yes that makes extra overhead on data load and lots of other things to consider.
Thanks,
Virender
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 (lookingat visibility map). That means even if we want to freeze less transactions only (For ex - by increasing parametervacuum_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 freezeup. > > Benefit of such an improvement is that if we are reaching transaction id close to 2B (and downtime), that time we can quicklyrecover the database with vacuuming freeze only a few millions rows with quick lookup rather than going all the pagesfrom 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/
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/
On Tue, 13 Apr 2021 at 19:48, Virender Singla <virender.cse@gmail.com> wrote: > 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? You might want to have a look at [1], which was just pushed for PG14. In particular: > When the failsafe triggers, VACUUM takes extraordinary measures to > finish as quickly as possible so that relfrozenxid and/or relminmxid can > be advanced. VACUUM will stop applying any cost-based delay that may be > in effect. VACUUM will also bypass any further index vacuuming and heap > vacuuming -- it only completes whatever remaining pruning and freezing > is required. Bypassing index/heap vacuuming is enabled by commit > 8523492d, which made it possible to dynamically trigger the mechanism > already used within VACUUM when it is run with INDEX_CLEANUP off. David [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e55e7d1755cefbb44982fbacc7da461fa8684e6
On Tue, Apr 13, 2021 at 1:51 PM Virender Singla <virender.cse@gmail.com> wrote: > > Thanks Masahiko for the response. > > "What is > 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 ofoutage.The Purpose of increasing "vacuum_freeze_min_age" to high value is that anti wraparound vacuum will have to do lesswork because we are asking less transactions/tuples to freeze (Of Course subsequent vacuum has to do the remaining work). I think I understood your proposal. For example, if we insert 500GB tuples during the first 1 billion transactions and then insert more 500GB tuples into another 500GB blocks during the next 1 billion transactions, vacuum freeze scans 1TB whereas we scans only 500GB that are modified by the first insertions if we’re able to freeze directly tuples that are older than the cut-off. Is that right? > > "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." > > 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? I think it's a good idea to skip all work except for freezing tuples in emergency cases. Thanks to vacuum_failsafe_age we can avoid index vacuuming, index cleanup, and heap vacuuming. > > Thanks for sharing PG14 improvements, those are nice to have. But still the anti wraparound vacuum will have to scan allthe pages (from visibility map) even if we are freezing fewer transactions because currently there is no way to know whatblock/tuple contains which transaction id. Yes, that feature is to speed up vacuum by dynamically disabling both cost-based delay and some cleanup work whereas your idea is to do that by speeding up heap scan. > If there is a way then it would be easier to directly freeze those tuples quickly and advance the relfrozenxid for thetable. Maybe we can track the oldest xid per page in a map like visiblity map or integrate it with visibility map. We need to freeze only pages that are all-visible and whose oldest xid is older than the cut-off xid. I think we need to track both xid and multi xid. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
exactly my point, want to scan only 500GB data instead of 1TB. That can be handy for vacuum freeze at a dangerous stage (reaching towards 2B).
"Maybe we can track the oldest xid per page in a map like visiblity map
or integrate it with visibility map. We need to freeze only pages that
are all-visible and whose oldest xid is older than the cut-off xid. I
think we need to track both xid and multi xid."
or integrate it with visibility map. We need to freeze only pages that
are all-visible and whose oldest xid is older than the cut-off xid. I
think we need to track both xid and multi xid."
Yes I thought of that (keep track of olderst xid per page instead of per tuple), only thing here is every time there is some modification on the page, that oldest xid needs to be recalculated for respective page. Still that makes sense with kind of BRIN type structure to keep the xid per page.
With Binary Tree Index structure, new transaction/tuple will fit right side (as that would be news transaction until 2B) and then other side leaf blocks can be removed with every vacuum freeze.
On Tue, Apr 13, 2021 at 6:02 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Tue, Apr 13, 2021 at 1:51 PM Virender Singla <virender.cse@gmail.com> wrote:
>
> Thanks Masahiko for the response.
>
> "What is
> 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).
I think I understood your proposal. For example, if we insert 500GB
tuples during the first 1 billion transactions and then insert more
500GB tuples into another 500GB blocks during the next 1 billion
transactions, vacuum freeze scans 1TB whereas we scans only 500GB that
are modified by the first insertions if we’re able to freeze directly
tuples that are older than the cut-off. Is that right?
>
> "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."
>
> 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?
I think it's a good idea to skip all work except for freezing tuples
in emergency cases. Thanks to vacuum_failsafe_age we can avoid index
vacuuming, index cleanup, and heap vacuuming.
>
> 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.
Yes, that feature is to speed up vacuum by dynamically disabling both
cost-based delay and some cleanup work whereas your idea is to do that
by speeding up heap scan.
> If there is a way then it would be easier to directly freeze those tuples quickly and advance the relfrozenxid for the table.
Maybe we can track the oldest xid per page in a map like visiblity map
or integrate it with visibility map. We need to freeze only pages that
are all-visible and whose oldest xid is older than the cut-off xid. I
think we need to track both xid and multi xid.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/