Re: New IndexAM API controlling index vacuum strategies - Mailing list pgsql-hackers

On Thu, Dec 24, 2020 at 12:59 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Dec 22, 2020 at 2:54 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > I've started this separate thread from [1] for discussing the general
> > API design of index vacuum.
>
> This is a very difficult and very important problem. Clearly defining
> the problem is probably the hardest part. This prototype patch seems
> like a good start, though.
>
> Private discussion between Masahiko and myself led to a shared
> understanding of what the best *general* direction is for VACUUM now.
> It is necessary to deal with several problems all at once here, and to
> at least think about several more problems that will need to be solved
> later. If anybody reading the thread initially finds it hard to see
> the connection between the specific items that Masahiko has
> introduced, they should note that that's *expected*.
>
> > Summary:
> >
> > * Call ambulkdelete and amvacuumcleanup even when INDEX_CLEANUP is
> > false, and leave it to the index AM whether or not skip them.
>
> Makes sense. I like the way you unify INDEX_CLEANUP and the
> vacuum_cleanup_index_scale_factor stuff in a way that is now quite
> explicit and obvious in the code.
>
> > The second and third points are to introduce a general framework for
> > future extensibility. User-visible behavior is not changed by this
> > change.
>
> In some ways the ideas in your patch might be considered radical, or
> at least novel: they introduce the idea that bloat can be a
> qualitative thing. But at the same time the design is quite
> conservative: these are fairly isolated changes, at least code-wise. I
> am not 100% sure that this approach will be successful in
> vacuumlazy.c, in the end (I'm ~95% sure). But I am 100% sure that our
> collective understanding of the problems in this area will be
> significantly improved by this effort. A fundamental rethink does not
> necessarily require a fundamental redesign, and yet it might be just
> as effective.
>
> This is certainly what I see when testing my bottom-up index deletion
> patch, which adds an incremental index deletion mechanism that merely
> intervenes in a precise, isolated way. Despite my patch's simplicity,
> it manages to practically eliminate an entire important *class* of
> index bloat (at least once you make certain mild assumptions about the
> duration of snapshots). Sometimes it is possible to solve a hard
> problem by thinking about it only *slightly* differently.
>
> This is a tantalizing possibility for VACUUM, too. I'm willing to risk
> sounding grandiose if that's what it takes to get more hackers
> interested in these questions. With that in mind, here is a summary of
> the high level hypothesis behind this VACUUM patch:
>
> VACUUM can and should be reimagined as a top-down mechanism that
> complements various bottom-up mechanisms (including the stuff from my
> deletion patch, heap pruning, and possibly an enhanced version of heap
> pruning based on similar principles). This will be possible without
> changing any of the fundamental invariants of the current vacuumlazy.c
> design. VACUUM's problems are largely pathological behaviors of one
> kind or another, that can be fixed with specific well-targeted
> interventions. Workload characteristics can naturally determine how
> much of the cleanup is done by VACUUM itself -- large variations are
> possible within a single database, and even across indexes on the same
> table.

Agreed.

Ideally, the bottom-up mechanism works well and reclaim almost all
garbage. VACUUM should be a feature that complements these works if
the bottom-up mechanism cannot work well for some reason, and also is
used to make sure that all collected garbage has been vacuumed. For
heaps, we already have such a mechanism: opportunistically hot-pruning
and lazy vacuum. For indexes especially btree indexes, the bottom-up
index deletion and ambulkdelete() would have a similar relationship.

>
> > The new index AM API, amvacuumstrategy(), which is called before
> > bulkdelete() for each index and asks the index bulk-deletion strategy.
> > On this API, lazy vacuum asks, "Hey index X, I collected garbage heap
> > tuples during heap scanning, how urgent is vacuuming for you?", and
> > the index answers either "it's urgent" when it wants to do
> > bulk-deletion or "it's not urgent, I can skip it". The point of this
> > proposal is to isolate heap vacuum and index vacuum for each index so
> > that we can employ different strategies for each index. Lazy vacuum
> > can decide whether or not to do heap clean based on the answers from
> > the indexes.
>
> Right -- workload characteristics (plus appropriate optimizations at
> the local level) make it possible that amvacuumstrategy() will give
> *very* different answers from different indexes *on the same table*.
> The idea that all indexes on the table are more or less equally
> bloated at any given point in time is mostly wrong. Actually,
> *sometimes* it really is correct! But other times it is *dramatically
> wrong* -- it all depends on workload characteristics. What is likely
> to be true *on average* across all tables/indexes is *irrelevant* (the
> mean/average is simply not a useful concept, in fact).
>
> The basic lazy vacuum design needs to recognize this important
> difference, and other similar issues. That's the point of
> amvacuumstrategy().

Agreed.

In terms of bloat, the characteristics of index AM also bring such
differences (e.g., btree vs. brin). With the bottom-up index deletion
feature, even btree indexes on the same table will also different to
each other.

>
> > Currently, if INDEX_CLEANUP option is not set (i.g.
> > VACOPT_TERNARY_DEFAULT in the code), it's treated as true and will do
> > heap clean. But with this patch we use the default as a neutral state
> > ('smart' mode). This neutral state could be "on" and "off" depending
> > on several factors including the answers of amvacuumstrategy(), the
> > table status, and user's request. In this context, specifying
> > INDEX_CLEANUP would mean making the neutral state "on" or "off" by
> > user's request. The table status that could influence the decision
> > could concretely be, for instance:
> >
> > * Removing LP_DEAD accumulation due to skipping bulkdelete() for a long time.
> > * Making pages all-visible for index-only scan.
>
> So you have several different kinds of back pressure - 'smart' mode
> really is smart.
>
> > Also there are potential enhancements using this API:
>
> > * If retail index deletion feature[3] is introduced, we can make the
> > return value of bulkvacuumstrategy() a ternary value: "do_bulkdelete",
> > "do_indexscandelete", and "no".
>
> Makes sense.
>
> > * We probably can introduce a threshold of the number of dead tuples
> > to control whether or not to do index tuple bulk-deletion (like
> > bulkdelete() version of vacuum_cleanup_index_scale_factor). In the
> > case where the amount of dead tuples is slightly larger than
> > maitenance_work_mem the second time calling to bulkdelete will be
> > called with a small number of dead tuples, which is inefficient. This
> > problem is also solved by this proposal by allowing a subset of
> > indexes to skip bulkdelete() if the number of dead tuple doesn't
> > exceed the threshold.
>
> Good idea. I bet other people can come up with other ideas a little
> like this just by thinking about it. The "untangling" performed by
> your patch creates many possibilities
>
> > I’ve attached the PoC patch for the above idea. By default, since lazy
> > vacuum choose the vacuum bulkdelete strategy based on answers of
> > amvacuumstrategy() so it can be either true or false ( although it’s
> > always true in the currene patch). But for amvacuumcleanup() there is
> > no the neutral state, lazy vacuum treats the default as true.
>
> As you said, the next question must be: How do we teach lazy vacuum to
> not do what gets requested by amvacuumcleanup() when it cannot respect
> the wishes of one individual indexes, for example when the
> accumulation of LP_DEAD items in the heap becomes a big problem in
> itself? That really could be the thing that forces full heap
> vacuuming, even with several indexes.

You mean requested by amvacuumstreategy(), not by amvacuumcleanup()? I
think amvacuumstrategy() affects only ambulkdelete(). But when all
ambulkdelete() were skipped by the requests by index AMs we might want
to skip amvacuumcleanup() as well.

>
> I will need to experiment in order to improve my understanding of how
> to make this cooperate with bottom-up index deletion. But that's
> mostly just a question for my patch (and a relatively easy one).

Yeah, I think we might need something like statistics about garbage
per index so that individual index can make a different decision based
on their status. For example, a btree index might want to skip
ambulkdelete() if it has a few dead index tuples in its leaf pages. It
could be on stats collector or on btree's meta page.

Regards,

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