Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add wraparound failsafe to VACUUM.

  1. vacuum freeze - possible improvements

    Virender Singla <virender.cse@gmail.com> — 2021-04-12T08:19:04Z

    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

  2. Re: vacuum freeze - possible improvements

    Masahiko Sawada <sawada.mshk@gmail.com> — 2021-04-13T02:22:13Z

    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/

  3. Re: vacuum freeze - possible improvements

    Virender Singla <virender.cse@gmail.com> — 2021-04-13T04:51:03Z

    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).

"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?

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/
>

  4. Re: vacuum freeze - possible improvements

    David Rowley <dgrowleyml@gmail.com> — 2021-04-13T11:05:26Z

    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

  5. Re: vacuum freeze - possible improvements

    Masahiko Sawada <sawada.mshk@gmail.com> — 2021-04-13T12:32:15Z

    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/

  6. Re: vacuum freeze - possible improvements

    Virender Singla <virender.cse@gmail.com> — 2021-04-13T13:49:42Z

    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."

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/
>