VACUUM (DISABLE_PAGE_SKIPPING on)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
The docs are misleading for this feature, since they say:
"This option disables all page-skipping behavior, and is
intended to be used only when the contents of the visibility map are
suspect, which should happen only if there is a hardware or software
issue causing database corruption."

The docs do correctly say "Pages where all tuples are known to be
frozen can always be skipped". Checking the code, lazy_scan_heap()
comments say
"we can still skip pages that are all-frozen, since such pages do not
need freezing".

The code is quite clear: DISABLE_PAGE_SKIPPING makes the vacuum into
an aggressive vacuum. Line 487, heap_vacuum_rel().  Aggressive vacuums
can still skip a page that is frozen, and rely on the visibility map
for that information.

So the docs are wrong - we don't disable *all* page-skipping and it is
not appropriate to warn users away from this feature by saying "is
intended to be used only when the contents of the visibility map are
suspect".

Reworded docs patch attached.

--
Simon Riggs                http://www.EnterpriseDB.com/

aggressive_rewording.v1.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

David G Johnston
On Mon, Nov 16, 2020 at 1:52 PM Simon Riggs <[hidden email]> wrote:
The docs are misleading for this feature, since they say:
"This option disables all page-skipping behavior, and is
intended to be used only when the contents of the visibility map are
suspect, which should happen only if there is a hardware or software
issue causing database corruption."
[...] 

The code is quite clear: DISABLE_PAGE_SKIPPING makes the vacuum into
an aggressive vacuum. Line 487, heap_vacuum_rel().  Aggressive vacuums
can still skip a page that is frozen, and rely on the visibility map
for that information.

So the docs are wrong - we don't disable *all* page-skipping and it is
not appropriate to warn users away from this feature by saying "is
intended to be used only when the contents of the visibility map are
suspect".

This patch seems mis-placed, at least in HEAD.  If DISABLE_PAGE_SKIPPING isn't doing what the documentation says it should, but instead provides identical behavior to FREEZE, then the bug should be fixed in HEAD.  I'd argue for batch-patching it as well.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Masahiko Sawada
In reply to this post by Simon Riggs
On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <[hidden email]> wrote:

>
> The docs are misleading for this feature, since they say:
> "This option disables all page-skipping behavior, and is
> intended to be used only when the contents of the visibility map are
> suspect, which should happen only if there is a hardware or software
> issue causing database corruption."
>
> The docs do correctly say "Pages where all tuples are known to be
> frozen can always be skipped". Checking the code, lazy_scan_heap()
> comments say
> "we can still skip pages that are all-frozen, since such pages do not
> need freezing".
>
> The code is quite clear: DISABLE_PAGE_SKIPPING makes the vacuum into
> an aggressive vacuum. Line 487, heap_vacuum_rel().  Aggressive vacuums
> can still skip a page that is frozen, and rely on the visibility map
> for that information.
>
> So the docs are wrong - we don't disable *all* page-skipping and it is
> not appropriate to warn users away from this feature by saying "is
> intended to be used only when the contents of the visibility map are
> suspect".

I don't think the doc is wrong. If DISABLE_PAGE_SKIPPING is specified,
we not only set aggressive = true but also skip checking visibility
map. For instance, see line 905 and line 963, lazy_scan_heap().

Regards,

--
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
On Mon, 16 Nov 2020 at 22:53, Masahiko Sawada <[hidden email]> wrote:
> On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <[hidden email]> wrote:

> I don't think the doc is wrong. If DISABLE_PAGE_SKIPPING is specified,
> we not only set aggressive = true but also skip checking visibility
> map. For instance, see line 905 and line 963, lazy_scan_heap().

OK, so you're saying that the docs illustrate the true intention of
the patch, which I immediately accept since I know you were the
author. Forgive me for not discussing it with you first, I thought
this was a clear cut case.

But that then highlights another area where the docs are wrong...

> On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <[hidden email]> wrote:
> > The docs do correctly say "Pages where all tuples are known to be
> > frozen can always be skipped". Checking the code, lazy_scan_heap()
> > comments say
> > "we can still skip pages that are all-frozen, since such pages do not
> > need freezing".

The docs say this:
"Pages where all tuples are known to be frozen can always be skipped."
Why bother to say that if the feature then ignores that point and
scans them anyway?
May I submit a patch to remove that sentence?

Anyway, we're back to where I started: looking for a user-initiated
command option that allows a table to scanned aggressively so that
relfrozenxid can be moved forward as quickly as possible. This is what
I thought that you were trying to achieve with DISABLE_PAGE_SKIPPING
option, my bad.

Now David J, above, says this would be VACUUM FREEZE, but I don't
think that is right. Setting VACUUM FREEZE has these effects: 1) makes
a vacuum aggressive, but it also 2) moves the freeze limit so high
that it freezes mostly everything. (1) allows the vacuum to reset
relfrozenxid, but (2) actually slows down the scan by making it freeze
more blocks than it would do normally.

So we have 3 ways to reset relfrozenxid by a user action:
VACUUM (DISABLE_PAGE_SKIPPING ON) - scans all blocks, deliberately
ignoring the ones it could have skipped. This certainly slows it down.
VACUUM (FREEZE ON) - freezes everything in its path, slowing down the
scan by writing too many blocks.
VACUUM (FULL on) - rewrites table and rebuilds index, so very slow

What I think we need is a 4th option which aims to move relfrozenxid
forwards as quickly as possible
* initiates an aggressive scan, so it does not skip blocks because of
busy buffer pins
* skip pages that are all-frozen, as we are allowed to do
* uses normal freeze limits, so we avoid writing to blocks if possible

If we do all 3 of those things, the scan will complete as quickly as
possible and reset relfrozenxid quickly. It would make sense to use
that in conjunction with index_cleanup=off

As an additional optimization, if we do find a row that needs freezing
on a data block, we should simply freeze *all* row versions on the
page, not just the ones below the selected cutoff. This is justified
since writing the block is the biggest cost and it doesn't make much
sense to leave a few rows unfrozen on a block that we are dirtying.

Thoughts?

--
Simon Riggs                http://www.EnterpriseDB.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Álvaro Herrera
On 2020-Nov-17, Simon Riggs wrote:

> As an additional optimization, if we do find a row that needs freezing
> on a data block, we should simply freeze *all* row versions on the
> page, not just the ones below the selected cutoff. This is justified
> since writing the block is the biggest cost and it doesn't make much
> sense to leave a few rows unfrozen on a block that we are dirtying.

Yeah.  We've had ealier proposals to use high and low watermarks: if any
tuple is past the high watermark, then freeze all tuples that are past
the low watermark.  However this is ancient thinking (prior to
HEAP_XMIN_FROZEN) and we don't need the low watermark to be different
from zero, since the original xid is retained anyway.

So +1 for this idea.


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Masahiko Sawada
In reply to this post by Simon Riggs
On Tue, Nov 17, 2020 at 8:27 PM Simon Riggs <[hidden email]> wrote:

>
> On Mon, 16 Nov 2020 at 22:53, Masahiko Sawada <[hidden email]> wrote:
> > On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <[hidden email]> wrote:
>
> > I don't think the doc is wrong. If DISABLE_PAGE_SKIPPING is specified,
> > we not only set aggressive = true but also skip checking visibility
> > map. For instance, see line 905 and line 963, lazy_scan_heap().
>
> OK, so you're saying that the docs illustrate the true intention of
> the patch, which I immediately accept since I know you were the
> author. Forgive me for not discussing it with you first, I thought
> this was a clear cut case.
>
> But that then highlights another area where the docs are wrong...
>
> > On Tue, Nov 17, 2020 at 5:52 AM Simon Riggs <[hidden email]> wrote:
> > > The docs do correctly say "Pages where all tuples are known to be
> > > frozen can always be skipped". Checking the code, lazy_scan_heap()
> > > comments say
> > > "we can still skip pages that are all-frozen, since such pages do not
> > > need freezing".
>
> The docs say this:
> "Pages where all tuples are known to be frozen can always be skipped."
> Why bother to say that if the feature then ignores that point and
> scans them anyway?
> May I submit a patch to remove that sentence?

I think the docs describe the page skipping behaviour first and then
describe what DISABLE_PAGE_SKIPPING option does. When discussing this
feature, I thought this sentence would help users to understand that
because users might be confused with that option name without
description of page skipping. So I'm not sure it's unnecessary but if
this has confused you, it would need to be somehow improved.

>
> Anyway, we're back to where I started: looking for a user-initiated
> command option that allows a table to scanned aggressively so that
> relfrozenxid can be moved forward as quickly as possible. This is what
> I thought that you were trying to achieve with DISABLE_PAGE_SKIPPING
> option, my bad.
>
> Now David J, above, says this would be VACUUM FREEZE, but I don't
> think that is right. Setting VACUUM FREEZE has these effects: 1) makes
> a vacuum aggressive, but it also 2) moves the freeze limit so high
> that it freezes mostly everything. (1) allows the vacuum to reset
> relfrozenxid, but (2) actually slows down the scan by making it freeze
> more blocks than it would do normally.
>
> So we have 3 ways to reset relfrozenxid by a user action:
> VACUUM (DISABLE_PAGE_SKIPPING ON) - scans all blocks, deliberately
> ignoring the ones it could have skipped. This certainly slows it down.
> VACUUM (FREEZE ON) - freezes everything in its path, slowing down the
> scan by writing too many blocks.
> VACUUM (FULL on) - rewrites table and rebuilds index, so very slow
>
> What I think we need is a 4th option which aims to move relfrozenxid
> forwards as quickly as possible
> * initiates an aggressive scan, so it does not skip blocks because of
> busy buffer pins
> * skip pages that are all-frozen, as we are allowed to do
> * uses normal freeze limits, so we avoid writing to blocks if possible
>

This can be done with VACUUM today by vacuum_freeze_table_age and
vacuum_multixact_freeze_table_age to 0. Adding an option for this
behavior would be good for users to understand and would work well
with the optimization.

> If we do all 3 of those things, the scan will complete as quickly as
> possible and reset relfrozenxid quickly. It would make sense to use
> that in conjunction with index_cleanup=off

Agreed.

>
> As an additional optimization, if we do find a row that needs freezing
> on a data block, we should simply freeze *all* row versions on the
> page, not just the ones below the selected cutoff. This is justified
> since writing the block is the biggest cost and it doesn't make much
> sense to leave a few rows unfrozen on a block that we are dirtying.

+1

Regards,

--
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
On Wed, 18 Nov 2020 at 10:28, Masahiko Sawada <[hidden email]> wrote:

> > So we have 3 ways to reset relfrozenxid by a user action:
> > VACUUM (DISABLE_PAGE_SKIPPING ON) - scans all blocks, deliberately
> > ignoring the ones it could have skipped. This certainly slows it down.
> > VACUUM (FREEZE ON) - freezes everything in its path, slowing down the
> > scan by writing too many blocks.
> > VACUUM (FULL on) - rewrites table and rebuilds index, so very slow
> >
> > What I think we need is a 4th option which aims to move relfrozenxid
> > forwards as quickly as possible
> > * initiates an aggressive scan, so it does not skip blocks because of
> > busy buffer pins
> > * skip pages that are all-frozen, as we are allowed to do
> > * uses normal freeze limits, so we avoid writing to blocks if possible
> >
>
> This can be done with VACUUM today by vacuum_freeze_table_age and
> vacuum_multixact_freeze_table_age to 0. Adding an option for this
> behavior would be good for users to understand and would work well
> with the optimization.
>
> > If we do all 3 of those things, the scan will complete as quickly as
> > possible and reset relfrozenxid quickly. It would make sense to use
> > that in conjunction with index_cleanup=off
>
> Agreed.
Patches attached.
1. vacuum_anti_wraparound.v2.patch
2. vacuumdb_anti_wrap.v1.patch - depends upon (1)

> > As an additional optimization, if we do find a row that needs freezing
> > on a data block, we should simply freeze *all* row versions on the
> > page, not just the ones below the selected cutoff. This is justified
> > since writing the block is the biggest cost and it doesn't make much
> > sense to leave a few rows unfrozen on a block that we are dirtying.
>
> +1

I'll work on that.

--
Simon Riggs                http://www.EnterpriseDB.com/

vacuumdb_anti_wrap.v1.patch (6K) Download Attachment
vacuum_anti_wraparound.v2.patch (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Robert Haas
On Wed, Nov 18, 2020 at 12:54 PM Simon Riggs <[hidden email]> wrote:
> Patches attached.
> 1. vacuum_anti_wraparound.v2.patch
> 2. vacuumdb_anti_wrap.v1.patch - depends upon (1)

I don't like the use of ANTI_WRAPAROUND as a name for this new option.
Wouldn't it make more sense to call it AGGRESSIVE? Or maybe something
else, but I dislike anti-wraparound. It's neither the most aggressive
thing we can do to prevent wraparound (that's FREEZE), nor is it the
case that vacuums without this option (or indeed any options) can't
help prevent wraparound, because the aggressive strategy  may be
chosen anyway.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
On Wed, 18 Nov 2020 at 17:59, Robert Haas <[hidden email]> wrote:
>
> On Wed, Nov 18, 2020 at 12:54 PM Simon Riggs <[hidden email]> wrote:
> > Patches attached.
> > 1. vacuum_anti_wraparound.v2.patch
> > 2. vacuumdb_anti_wrap.v1.patch - depends upon (1)
>
> I don't like the use of ANTI_WRAPAROUND as a name for this new option.
> Wouldn't it make more sense to call it AGGRESSIVE? Or maybe something
> else, but I dislike anti-wraparound.

-1 for using the term AGGRESSIVE, which seems likely to offend people.
I'm sure a more descriptive term exists.

> It's neither the most aggressive
> thing we can do to prevent wraparound (that's FREEZE),

The new option is not the same thing as the FREEZE option, as discussed above.

> nor is it the
> case that vacuums without this option (or indeed any options) can't
> help prevent wraparound, because the aggressive strategy  may be
> chosen anyway.

Maybe.

The "aim [is] to move relfrozenxid forwards as quickly as possible" so
as to avoid wraparound, so having an unambiguous command that does
that is important for usability. It also allows us to rely on the
user's explicit intention to optimize vacuum towards that goal.

--
Simon Riggs                http://www.EnterpriseDB.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
In reply to this post by Álvaro Herrera
On Wed, 18 Nov 2020 at 02:04, Alvaro Herrera <[hidden email]> wrote:

>
> On 2020-Nov-17, Simon Riggs wrote:
>
> > As an additional optimization, if we do find a row that needs freezing
> > on a data block, we should simply freeze *all* row versions on the
> > page, not just the ones below the selected cutoff. This is justified
> > since writing the block is the biggest cost and it doesn't make much
> > sense to leave a few rows unfrozen on a block that we are dirtying.
>
> Yeah.  We've had earlier proposals to use high and low watermarks: if any
> tuple is past the high watermark, then freeze all tuples that are past
> the low watermark.  However this is ancient thinking (prior to
> HEAP_XMIN_FROZEN) and we don't need the low watermark to be different
> from zero, since the original xid is retained anyway.
>
> So +1 for this idea.
Patch to do this attached, for discussion.

--
Simon Riggs                http://www.EnterpriseDB.com/

one_freeze_then_max_freeze.v5.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Masahiko Sawada
In reply to this post by Simon Riggs
On Thu, Nov 19, 2020 at 8:02 PM Simon Riggs <[hidden email]> wrote:

>
> On Wed, 18 Nov 2020 at 17:59, Robert Haas <[hidden email]> wrote:
> >
> > On Wed, Nov 18, 2020 at 12:54 PM Simon Riggs <[hidden email]> wrote:
> > > Patches attached.
> > > 1. vacuum_anti_wraparound.v2.patch
> > > 2. vacuumdb_anti_wrap.v1.patch - depends upon (1)
> >
> > I don't like the use of ANTI_WRAPAROUND as a name for this new option.
> > Wouldn't it make more sense to call it AGGRESSIVE? Or maybe something
> > else, but I dislike anti-wraparound.
>
> -1 for using the term AGGRESSIVE, which seems likely to offend people.
> I'm sure a more descriptive term exists.

Since we use the term aggressive scan in the docs, I personally don't
feel unnatural about that. But since this option also disables index
cleanup when not enabled explicitly, I’m concerned a bit if user might
get confused. I came up with some names like FEEZE_FAST and
FREEZE_MINIMAL but I'm not sure these are better.

BTW if this option also disables index cleanup for faster freezing,
why don't we disable heap truncation as well?

Regards,

--
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Masahiko Sawada
In reply to this post by Simon Riggs
On Fri, Nov 20, 2020 at 6:02 AM Simon Riggs <[hidden email]> wrote:

>
> On Wed, 18 Nov 2020 at 02:04, Alvaro Herrera <[hidden email]> wrote:
> >
> > On 2020-Nov-17, Simon Riggs wrote:
> >
> > > As an additional optimization, if we do find a row that needs freezing
> > > on a data block, we should simply freeze *all* row versions on the
> > > page, not just the ones below the selected cutoff. This is justified
> > > since writing the block is the biggest cost and it doesn't make much
> > > sense to leave a few rows unfrozen on a block that we are dirtying.
> >
> > Yeah.  We've had earlier proposals to use high and low watermarks: if any
> > tuple is past the high watermark, then freeze all tuples that are past
> > the low watermark.  However this is ancient thinking (prior to
> > HEAP_XMIN_FROZEN) and we don't need the low watermark to be different
> > from zero, since the original xid is retained anyway.
> >
> > So +1 for this idea.
>
> Patch to do this attached, for discussion.

Thank you for the patch!

+                *
+                * Once we decide to dirty the data block we may as well freeze
+                * any tuples that are visible to all, since the additional
+                * cost of freezing multiple tuples is low.

I'm concerned that always freezing all tuples when we're going to make
the page dirty would affect the existing vacuum workload much. The
additional cost of freezing multiple tuples would be low but if we
freeze tuples we would also need to write WAL, which is not negligible
overhead I guess. In the worst case, if a table has dead tuples on all
pages we process them, but with this patch, in addition to that, we
will end up not only freezing all live tuples but also writing
XLOG_HEAP2_FREEZE_PAGE WAL for all pages. So I think it would be
better either to freeze all tuples if we find a tuple that needs to be
frozen or to make this behavior work only if the new VACUUM option is
specified.

Regards,

--
Masahiko Sawada
EnterpriseDB:  https://www.enterprisedb.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
In reply to this post by Masahiko Sawada
On Fri, 20 Nov 2020 at 01:40, Masahiko Sawada <[hidden email]> wrote:

>
> On Thu, Nov 19, 2020 at 8:02 PM Simon Riggs <[hidden email]> wrote:
> >
> > On Wed, 18 Nov 2020 at 17:59, Robert Haas <[hidden email]> wrote:
> > >
> > > On Wed, Nov 18, 2020 at 12:54 PM Simon Riggs <[hidden email]> wrote:
> > > > Patches attached.
> > > > 1. vacuum_anti_wraparound.v2.patch
> > > > 2. vacuumdb_anti_wrap.v1.patch - depends upon (1)
> > >
> > > I don't like the use of ANTI_WRAPAROUND as a name for this new option.
> > > Wouldn't it make more sense to call it AGGRESSIVE? Or maybe something
> > > else, but I dislike anti-wraparound.
> >
> > -1 for using the term AGGRESSIVE, which seems likely to offend people.
> > I'm sure a more descriptive term exists.
>
> Since we use the term aggressive scan in the docs, I personally don't
> feel unnatural about that. But since this option also disables index
> cleanup when not enabled explicitly, I’m concerned a bit if user might
> get confused. I came up with some names like FEEZE_FAST and
> FREEZE_MINIMAL but I'm not sure these are better.

FREEZE_FAST seems good.

> BTW if this option also disables index cleanup for faster freezing,
> why don't we disable heap truncation as well?

Good idea

--
Simon Riggs                http://www.2ndQuadrant.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
In reply to this post by Masahiko Sawada
On Fri, 20 Nov 2020 at 03:54, Masahiko Sawada <[hidden email]> wrote:

> > > So +1 for this idea.
> >
> > Patch to do this attached, for discussion.
>
> Thank you for the patch!
>
> +                *
> +                * Once we decide to dirty the data block we may as well freeze
> +                * any tuples that are visible to all, since the additional
> +                * cost of freezing multiple tuples is low.
>
> I'm concerned that always freezing all tuples when we're going to make
> the page dirty would affect the existing vacuum workload much. The
> additional cost of freezing multiple tuples would be low but if we
> freeze tuples we would also need to write WAL, which is not negligible
> overhead I guess. In the worst case, if a table has dead tuples on all
> pages we process them, but with this patch, in addition to that, we
> will end up not only freezing all live tuples but also writing
> XLOG_HEAP2_FREEZE_PAGE WAL for all pages. So I think it would be
> better either to freeze all tuples if we find a tuple that needs to be
> frozen or to make this behavior work only if the new VACUUM option is
> specified.
The additional cost of freezing is sizeof(xl_heap_freeze_tuple) = 11 bytes

I guess there is some overhead for writing the WAL record itself, the
only question is how much. If that is a concern then we definitely
don't want to do that only when using FAST_FREEZE, since that would
slow it down when we want to speed it up.

I've updated the patch to match your proposal, so we can compare. It
seems a shorter patch.

(This patch is an optimization that is totally independent to the
other proposals on this thread).

--
Simon Riggs                http://www.EnterpriseDB.com/

one_freeze_then_max_freeze.v6.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
In reply to this post by Simon Riggs
On Fri, 20 Nov 2020 at 10:15, Simon Riggs <[hidden email]> wrote:

>
> On Fri, 20 Nov 2020 at 01:40, Masahiko Sawada <[hidden email]> wrote:
> >
> > On Thu, Nov 19, 2020 at 8:02 PM Simon Riggs <[hidden email]> wrote:
> > >
> > > On Wed, 18 Nov 2020 at 17:59, Robert Haas <[hidden email]> wrote:
> > > >
> > > > On Wed, Nov 18, 2020 at 12:54 PM Simon Riggs <[hidden email]> wrote:
> > > > > Patches attached.
> > > > > 1. vacuum_anti_wraparound.v2.patch
> > > > > 2. vacuumdb_anti_wrap.v1.patch - depends upon (1)
> > > >
> > > > I don't like the use of ANTI_WRAPAROUND as a name for this new option.
> > > > Wouldn't it make more sense to call it AGGRESSIVE? Or maybe something
> > > > else, but I dislike anti-wraparound.
> > >
> > > -1 for using the term AGGRESSIVE, which seems likely to offend people.
> > > I'm sure a more descriptive term exists.
> >
> > Since we use the term aggressive scan in the docs, I personally don't
> > feel unnatural about that. But since this option also disables index
> > cleanup when not enabled explicitly, I’m concerned a bit if user might
> > get confused. I came up with some names like FEEZE_FAST and
> > FREEZE_MINIMAL but I'm not sure these are better.
>
> FREEZE_FAST seems good.
>
> > BTW if this option also disables index cleanup for faster freezing,
> > why don't we disable heap truncation as well?
>
> Good idea
Patch attached, using the name "FAST_FREEZE" instead.

--
Simon Riggs                http://www.EnterpriseDB.com/

vacuum_fast_freeze.v3.patch (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Álvaro Herrera
In reply to this post by Masahiko Sawada
On 2020-Nov-20, Masahiko Sawada wrote:

> I'm concerned that always freezing all tuples when we're going to make
> the page dirty would affect the existing vacuum workload much. The
> additional cost of freezing multiple tuples would be low but if we
> freeze tuples we would also need to write WAL, which is not negligible
> overhead I guess. In the worst case, if a table has dead tuples on all
> pages we process them, but with this patch, in addition to that, we
> will end up not only freezing all live tuples but also writing
> XLOG_HEAP2_FREEZE_PAGE WAL for all pages. So I think it would be
> better either to freeze all tuples if we find a tuple that needs to be
> frozen or to make this behavior work only if the new VACUUM option is
> specified.

There are two costs associated with this processing.  One is dirtying
the page (which means it needs to be written down when evicted), and the
other is to write WAL records for each change.  The cost for the latter
is going to be the same in both cases (with this change and without)
because the same WAL will have to be written -- the only difference is
*when* do you pay it.  The cost of the former is quite different; with
Simon's patch we dirty the page once, and without the patch we may dirty
it several times before it becomes "stable" and no more writes are done
to it.

(If you have tables whose pages change all the time, there would be no
difference with or without the patch.)

Dirtying the page less times means less full-page images to WAL, too,
which can be significant.


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Simon Riggs
On Fri, 20 Nov 2020 at 14:07, Alvaro Herrera <[hidden email]> wrote:

>
> On 2020-Nov-20, Masahiko Sawada wrote:
>
> > I'm concerned that always freezing all tuples when we're going to make
> > the page dirty would affect the existing vacuum workload much. The
> > additional cost of freezing multiple tuples would be low but if we
> > freeze tuples we would also need to write WAL, which is not negligible
> > overhead I guess. In the worst case, if a table has dead tuples on all
> > pages we process them, but with this patch, in addition to that, we
> > will end up not only freezing all live tuples but also writing
> > XLOG_HEAP2_FREEZE_PAGE WAL for all pages. So I think it would be
> > better either to freeze all tuples if we find a tuple that needs to be
> > frozen or to make this behavior work only if the new VACUUM option is
> > specified.
>
> There are two costs associated with this processing.  One is dirtying
> the page (which means it needs to be written down when evicted), and the
> other is to write WAL records for each change.  The cost for the latter
> is going to be the same in both cases (with this change and without)
> because the same WAL will have to be written -- the only difference is
> *when* do you pay it.  The cost of the former is quite different; with
> Simon's patch we dirty the page once, and without the patch we may dirty
> it several times before it becomes "stable" and no more writes are done
> to it.
>
> (If you have tables whose pages change all the time, there would be no
> difference with or without the patch.)
>
> Dirtying the page less times means less full-page images to WAL, too,
> which can be significant.

Summary of patch effects:

one_freeze_then_max_freeze.v5.patch
doesn't increase/decrease the number of pages that are dirtied by
freezing, but when a page will be dirtied **by any activity** it
maximises the number of rows frozen, so in some cases it may write a
WAL freeze record when it would not have done previously.

one_freeze_then_max_freeze.v6.patch
doesn't increase/decrease the number of pages that are dirtied by
freezing, but when a page will be dirtied **by freezing only** it
maximises the number of rows frozen, so the number of WAL records for
freezing is the same, but they may contain more tuples than before and
will increase the probability that the page is marked all_frozen.

So yes, as you say, the net effect will be to reduce the number of
write I/Os in subsequent vacuums required to move forward
relfrozenxid.

--
Simon Riggs                http://www.EnterpriseDB.com/


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Álvaro Herrera
In reply to this post by Simon Riggs
Note on heap_prepare_freeze_tuple()'s fifth parameter, it's not valid to
pass OldestXmin; you need a multixact limit there, not an Xid limit.  I
think the return value of GetOldestMultiXactId is a good choice.  AFAICS
this means that you'll need to add a new output argument to
vacuum_set_xid_limits (You *could* call GetOldestMultiXactId again, but
it seems a waste).

Maybe it's time for vacuum_set_xid_limits to have a caller's-stack-
allocated struct for input and output values, rather than so many
arguments and output pointers to fill.


Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Álvaro Herrera
In reply to this post by Simon Riggs
On 2020-Nov-20, Simon Riggs wrote:

> On Fri, 20 Nov 2020 at 01:40, Masahiko Sawada <[hidden email]> wrote:

> > Since we use the term aggressive scan in the docs, I personally don't
> > feel unnatural about that. But since this option also disables index
> > cleanup when not enabled explicitly, I’m concerned a bit if user might
> > get confused. I came up with some names like FEEZE_FAST and
> > FREEZE_MINIMAL but I'm not sure these are better.
>
> FREEZE_FAST seems good.

VACUUM (CHILL) ?



Reply | Threaded
Open this post in threaded view
|

Re: VACUUM (DISABLE_PAGE_SKIPPING on)

Robert Haas
In reply to this post by Álvaro Herrera
On Fri, Nov 20, 2020 at 9:08 AM Alvaro Herrera <[hidden email]> wrote:

> There are two costs associated with this processing.  One is dirtying
> the page (which means it needs to be written down when evicted), and the
> other is to write WAL records for each change.  The cost for the latter
> is going to be the same in both cases (with this change and without)
> because the same WAL will have to be written -- the only difference is
> *when* do you pay it.  The cost of the former is quite different; with
> Simon's patch we dirty the page once, and without the patch we may dirty
> it several times before it becomes "stable" and no more writes are done
> to it.
>
> (If you have tables whose pages change all the time, there would be no
> difference with or without the patch.)
>
> Dirtying the page less times means less full-page images to WAL, too,
> which can be significant.

Yeah, I think dirtying the page fewer times is a big win. However, a
page may have tuples that are not yet all-visible, and we can't freeze
those just because we are freezing others.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


12