Re: autovacuum prioritization - Mailing list pgsql-hackers (2024)

Home>mailing lists

> On Wed, Jan 26, 2022 at 10:55 AM Robert Haas <robertmhaas@gmail.com> wrote:> On Tue, Jan 25, 2022 at 3:32 PM Peter Geoghegan <pg@bowt.ie> wrote:> > For example, a> > page that has 5 dead heap-only tuples is vastly different to a similar> > page that has 5 LP_DEAD items instead -- and yet our current approach> > makes no distinction. Chances are very high that if the only dead> > tuples are heap-only tuples, then things are going just fine on that> > page -- opportunistic pruning is actually keeping up.>> Hmm, I think that's a really good insight. Perhaps we ought to forget> about counting updates and deletes and instead count dead line> pointers. Or maybe we can estimate the number of dead line pointers by> knowing how many updates and deletes there were, as long as we can> distinguish hot updates from non-HOT updates, which I think we can.All that we have to go on is a bunch of observations in any case,though -- the map is not the territory. And so it seems to me that thesensible thing to do is just embrace that we won't ever really exactlyknow what's going on in a given database, at any given time.Fortunately, we don't really have to know. We should be able to getaway with only having roughly the right idea, by focussing on the fewthings that we are sure of -- things like the difference betweenLP_DEAD items and dead heap-only tuples, which are invariant toworkload characteristics.I recently said (on the ANALYZE related thread) that we should bethinking probabilistically here [1]. Our understanding of the amountof bloat could very usefully be framed that way. Maybe the model weuse is a probability density function (maybe not formally, not sure).A PDF has an exact expectation, which for us might be the mostprobable number of dead tuples in total in a given table right now(let's just assume it's still dead tuples, ignoring the problems withthat metric for now).This is a useful basis for making better decisions by weighingcompeting considerations -- which might themselves be another PDF.Example: For a given table that is approaching the point where themodel says "time to VACUUM", we may very well spend hours, days, oreven weeks approaching the crossover point. The exact expectationisn't truly special here -- there is actually zero practical reason tohave special reverence for that precise point (with a good model,within certain reasonable bounds). If our model says that there isonly a noise-level difference between doing a VACUUM on a given tabletoday, tomorrow, or next week, why not take advantage? For example,why not do the VACUUM when the system appears to not be busy at all(typically in the dead of night), just because it'll definitely beboth cheaper in absolute terms (FPIs can be avoided by spreadingthings out over multiple checkpoints), and less disruptive?There are many opportunities like that, I believe. It's hard for me tosuppress the urge to blurt out 17 more ideas like that. What are thechances that you won't have at least a few real winners among all ofthe ideas that everybody will come up with, in the end?> > if successive ANALYZE operations notice> > a consistent pattern where pages that had a non-zero number of LP_DEAD> > items last time now have a significantly higher number, then it's a> > good idea to err in the direction of more aggressive vacuuming.> > *Growing* concentrations of LP_DEAD items signal chaos. I think that> > placing a particular emphasis on pages with non-zero LP_DEAD items as> > a qualitatively distinct category of page might well make sense --> > relatively few blocks with a growing number of LP_DEAD items seems> > like it should be enough to make autovacuum run aggressively.>> I think measuring the change over time here might be fraught with> peril.I'd say that that depends on how you define the problem we're tryingto solve. If you define the problem as coming up with a significantlyimproved statistical model that determines (say) how many dead tuplesthere are in the table right now, given a set of observations made byANALYZE in the past, then yes, it's fraught with peril. But why wouldyou define it that way? It seems far easier to improve things byputting model error and *actual* exposure to real known issues (e.g.line pointer bloat) front and center.It doesn't necessarily matter if we're *usually* wrong with a goodmodel. But with a bad model we may need to consistently get thecorrect answer. And so the model that is the most accuratequantitatively is probably *not* the best available model, all thingsconsidered. Most of the time we shouldn't VACUUM right this second,and so a model that consists of "return false" is very frequentlycorrect. But that doesn't mean it's a good model. You get the idea.> If vacuum makes a single pass over the indexes, it can retire> as many dead line pointers as we have, or as will fit in memory, and> the effort doesn't really depend too much on exactly how many dead> line pointers we're trying to find.Line pointer bloat is something that displays hysteresis; once ithappens (past some significant threshold) then there is no reversingthe damage. This makes the behavior very non-linear. In other words,it makes it incredibly hard to model mathematically [2] -- once youcross a certain hard to define threshold, it's total chaos, even in aclosed well-specified system (i.e. a highly constrained workload),because you have all these feedback loops.On top of all that, even with a perfect model we're still forced tomake a go/no-go decision for the entire table, moment to moment. Soeven a mythical perfect model runs into the problem that it issimultaneously much too early and much too late at the level of thetable. Which is even more reason to just focus on not going totallyoff the rails, in any particular direction. Note that this includesgoing off the rails by vacuuming in a way that's unsustainablyaggressive -- sometimes you have to cut your losses at that level aswell.There is usually some bigger picture to consider when things do gowrong -- there is usually some much worse fate that must be avoided.Like with VACUUM's failsafe. Sure, controlling index bloat isextremely important. But it's also much less important than keepingthe system online and responsive. That's another level up. (The levelup *after that* is "at least we didn't lose data", or maybe somethingabout limiting the amount of downtime, not going out of business,whatever.)> I feel like my threshold for the number of dead TIDs that ought to> trigger a vacuum grows as the table gets bigger, capped by how much> memory I've got.I thought of another PDF related idea when I read this, without eventrying: we could account for the discontinuity from multiple indexscans in a single VACUUM operation (instead of just one) by erring inthe direction of doing the VACUUM sooner rather than later, when themodel says that doing so will make very little difference in terms ofextra costs incurred (extra costs from vacuuming sooner rather thanlater, conservatively assuming that our concern about TIDs not fittingin memory is basically unfounded).> But I don't feel like the rate at which it's changing> necessarily matters. Like if I create a million dead line pointers> really quickly, wait a month, and then create another million dead> line pointers, I feel like I want the system to respond just as> aggressively as if the month-long delay were omitted.>> Maybe my feelings are wrong here. I'm just saying that, to me, it> doesn't feel like the rate of change is all that relevant.It's not that they're wrong, exactly -- I wouldn't say that. It's morelike this: you as a Postgres user actually care about a great manythings, not just one thing. Some of these things might be somewhat intension, from time to time. And so it seems wise to find a way to livewith any tension that may crop up -- by acknowledging the tension, weget the chance to honor the preferences of the user to the greatestextent possible.[1] https://postgr.es/m/CAH2-WzmvXXEKtEph7U360umZ5pN3d18RBfu=nyPg9neBLDUWdw@mail.gmail.com[2] https://en.wikipedia.org/wiki/Hysteretic_model--Peter Geoghegan