Re: Eagerly scan all-visible pages to amortize aggressive vacuum

Thanks for taking a look!

I've rebased and attached an updated v3 which also addresses review feedback.

On Sun, Dec 15, 2024 at 1:05 AM Robert Treat <rob(at)xzilla(dot)net> wrote:
>
> On Fri, Dec 13, 2024 at 5:53 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
> > On Thu, Nov 7, 2024 at 10:42 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > Hi,
> > > One thing worth mentioning around here seems that we currently can't
> > > partially-aggressively freeze tuples that are "too young" and how that
> > > interacts with everything else.
> >
> > I'm not sure I know what you mean. Are you talking about how we don't freeze
> > tuples that are visible to everyone but younger than the freeze limit?
> >
>
> FWIW that was my interpretation of his statement, though I had a
> clarifying question around this topic myself, which is, from a user
> perspective when would we expect to see these eager vacuums? ISTM we
> would be doing 'normal vacuums' prior to vacuum_freeze_min_age, and
> 'aggressive vacuums' after (autovacuum_freeze_max_age -
> vacuum_freeze_min_age), so the expectation is that 'eager vacuums'
> would fall into the ~50 million transaction window between those two
> points (assuming defaults, which admittedly I don't use). Does that
> sound right?

Basically, yes (but 100 mill instead of 50)

By default, vacuum_freeze_min_age default is 50 million and
vacuum_freeze_table_age is 150 million (autovacuum_freeze_max_age
[default 200 mill] determines whether or not we kick off an anti-wrap
vac while vacuum_freeze_table_age determines whether or not a given
vacuum is an aggressive vacuum, which means that all anti-wrap vacs
are aggressive). Anyway, that means the eager vacuums will be
triggered somewhere in that 100 million xid window.

This may be an admittedly small window. However, for users tuning
these parameters with the intent of freezing, I would hope they are
decreasing vacuum_freeze_min_age and increasing
autovacuum_freeze_max_age.

> > > > In the attached chart.png, you can see the vm_page_freezes climbing
> > > > steadily with the patch, whereas on master, there are sudden spikes
> > > > aligned with the aggressive vacuums. You can also see that the number
> > > > of pages that are all-visible but not all-frozen grows steadily on
> > > > master until the aggressive vacuum. This is vacuum's "backlog" of
> > > > freezing work.
> > >
> > > What's the reason for all-visible-but-not-all-frozen to increase to a higher
> > > value initially than where it later settles?
> >
> > My guess is that it has to do with shorter, more frequent vacuums at the
> > beginning of the benchmark when the relation is smaller (and we haven't
> > exceeded shared buffers or memory yet). They are setting pages all-visible, but
> > we haven't used up enough xids yet to qualify for an eager vacuum.
> >
> > The peak of AVnAF pages aligns with the start of the first eager vacuum. We
> > don't do any eager scanning until we are sure there is some data requiring
> > freeze (see this criteria):
> >
> > if (TransactionIdIsNormal(vacrel->cutoffs.relfrozenxid) &&
> > TransactionIdPrecedesOrEquals(vacrel->cutoffs.relfrozenxid,
> > vacrel->cutoffs.FreezeLimit))
> >
> > Once we have used up enough xids to qualify for the first eager vacuum, the
> > number of AVnAF pages starts to go down.
> >
> > It would follow from this theory that we would see a build-up like this after
> > each relfrozenxid advancement (so after the next aggressive vacuum).
> >
> > But I think we don't see this because the vacuums are longer by the time
> > aggressive vacuums have started, so we end up using up enough XIDs between
> > vacuums to qualify for eager vacuums on vacuums after the aggressive vacuum.
> >
> > That is just my theory though.
> >
>
> I like your theory but it's a little too counterintuitive for me :-)
>
> I would expect we'd see a change in the vacuum time & rate after the
> first aggressive scan, which incidentally your graph does show for
> master, but it looks a bit too smooth on your original patchset. I
> guess there could be a sweet spot where the rate of changes fit
> perfectly with regards to the line between lazy / eager vacuums, but
> hard to imagine you were that lucky.

I think it's harder to see differences on the chart for the patched
version in terms of how long the vacuums are taking. I went and took a
closer look at the logs and at the beginning of the benchmark vacuums
are taking ~10 seconds and from the end of one vacuum to the next,
about 1.5 million xids are used. By the time the aggressive vacuum
happens, vacuums are taking around 30-40 seconds (30 seconds is where
the vacuum duration stabilizes until the end of the benchmark) and ~3
million xids are being used between the end of one vacuum and the
next.

So, all vacuums are eager after the first eager vacuum (in this
workload). It still isn't a completely satisfactory explanation, but I
will admit that the version of the patch set on which I ran the
benchmarks and generated the chart from had a few bugs. Attached are
the same charts generated from a benchmark run I did on the patch set
you reviewed -- and it seems like this effect is basically gone.

> > From e36b4fac345be44954410c4f0e61467dc0f49a72 Mon Sep 17 00:00:00 2001
> > From: Melanie Plageman <melanieplageman(at)gmail(dot)com>
> > Date: Thu, 12 Dec 2024 16:44:37 -0500
> > Subject: [PATCH v2 10/10] Eagerly scan all-visible pages to amortize
> > aggressive vacuum
> >
> > @@ -27,11 +27,37 @@
> > * to the end, skipping pages as permitted by their visibility status, vacuum
> > * options, and the eagerness level of the vacuum.
> > *
> > + * There are three vacuum eagerness levels: normal vacuum, eager vacuum, and
> > + * aggressive vacuum.
> > + *
> > * When page skipping is enabled, non-aggressive vacuums may skip scanning
> > - * pages that are marked all-visible in the visibility map. We may choose not
> > + * pages that are marked all-visible in the visibility map. It may choose not
> > * to skip pages if the range of skippable pages is below
> > * SKIP_PAGES_THRESHOLD.
> > *
>
> I find the above confusing since page skipping is the regular activity
> but referred to in the negative, and because you use the term
> "non-aggressive vacuums" which in prior releases only mapped to
> "normal" vacuums, but now would map to both "normal" and "eager"
> vacuums, and it isn't clear that is desired (in my head anyway). Does
> the following still convey what you meant (and hopefully work better
> with the paragraphs that follow)?
>
> When page skipping is not disabled, a normal vacuum may skip scanning
> pages that are marked all-visible in the visibility map if the range
> of skippable pages is below SKIP_PAGES_THRESHOLD.

Yes, that is much better. Thank you! I've incorporated this into the
new version.

> > + * The eagerness level of a vacuum determines how many all-visible but
> > + * not all-frozen pages it eagerly scans.
> > + *
> > + * A normal vacuum (eagerness VAC_NORMAL) scans no all-visible pages (with the
> > + * exception of those scanned due to SKIP_PAGES_THRESHOLD).
> > + *
> > + * An eager vacuum (eagerness VAC_EAGER) scans a number of pages up to a limit
> > + * based on whether or not it is succeeding or failing. An eager vacuum is
> > + * downgraded to a normal vacuum when it hits its success quota. An aggressive
> > + * vacuum cannot be downgraded. No eagerness level is ever upgraded.
> > + *
>
> At the risk of being overly nit-picky... eager vacuums scan their
> subset of all-visible pages "up to a limit" based solely on the
> success ratio. In the case of (excessive) failures, there is no limit
> to the number of pages scanned, only a pause in the pages scanned
> until the next region.

Yes, this is a good point. I've changed it to specifically indicate
the limit is based on successes. However, I feel like I should either
not mention the success limit here or mention the regional limiting
based on failures -- otherwise it is confusing. Can you think of a
wording that would be good for this comment?

> > + * An aggressive vacuum (eagerness EAGER_FULL) must scan all all-visible but
> > + * not all-frozen pages.
> > + */
>
> I think the above should be VAC_AGGRESSIVE vs EAGER_FULL, no?

Yep, thanks! Fixed.

> > vacrel->skipwithvm = skipwithvm;
> >
> > + heap_vacuum_set_up_eagerness(vacrel, aggressive);
> > +
> > if (verbose)
> > - {
> > - if (vacrel->aggressive)
> > - ereport(INFO,
> > - (errmsg("aggressively vacuuming \"%s.%s.%s\"",
> > - vacrel->dbname, vacrel->relnamespace,
> > - vacrel->relname)));
> > - else
> > - ereport(INFO,
> > - (errmsg("vacuuming \"%s.%s.%s\"",
> > - vacrel->dbname, vacrel->relnamespace,
> > - vacrel->relname)));
> > - }
> > + ereport(INFO,
> > + (errmsg("%s of \"%s.%s.%s\"",
> > + vac_eagerness_description(vacrel->eagerness),
> > + vacrel->dbname, vacrel->relnamespace,
> > + vacrel->relname)));
> >
> > /*
> > * Allocate dead_items memory using dead_items_alloc. This handles
>
> One thing I am wondering about is that since we actually modify
> vacrel->eagerness during the "success downgrade" cycle, a single
> vacuum run could potentially produce messages with both eager vacuum
> and normal vacuum language. I don't think that'd be a problem in the
> above spot, but wondering if it might be elsewhere (maybe in
> pg_stat_activity?).

Great point. It's actually already an issue in the vacuum log output.
In the new patch, I save the eagerness level at the beginning and use
it in the logging output. Any future users of the eagerness level will
have to figure out if they care about the original eagerness level or
the current one.

- Melanie