On 3/3/18 2:53 PM, Tomas Vondra wrote:
That largely depends on what knobs would be exposed. I'm against adding
some low-level knobs that perhaps 1% of the users will know how to tune,
and the rest will set it incorrectly. Some high-level options that would
specify the workload type might work, but I have no idea about details.
Not knowing about details is why we've been stuck here for years: it's
not terribly obvious how to create a scheduler that is going to work in
all situations. Current autovac is great for 80% of situations, but it
simply doesn't handle the remaining 20% by itself. Once you're pushing
your IO limits you *have* to start scheduling manual vacuums for any
At least if we exposed some low level ability to control autovac workers
then others could create tools to improve the situation. Currently
that's not possible because manual vacuum lacks features that autovac has.
One fairly simple option would be to simply replace the logic that
currently builds a worker's table list with running a query via SPI.
That would allow for prioritizing important tables. It could also reduce
the problem of workers getting "stuck" on a ton of large tables by
taking into consideration the total number of pages/tuples a list contains.
I don't see why SPI would be needed to do that, i.e. why couldn't we
implement such prioritization with the current approach. Another thing
Sure, it's just a SMOC. But most of the issue here is actually a query
problem. I suspect that the current code would actually shrink if
converted to SPI. In any case, I'm not wed to that idea.
is I really doubt prioritizing "important tables" is an good solution,
as it does not really guarantee anything.
If by "important" you mean small tables with high update rates,
prioritizing those actually would help as long as you have free workers.
By itself it doesn't gain all that much though.
A more fine-grained approach would be to have workers make a new
selection after every vacuum they complete. That would provide the
ultimate in control, since you'd be able to see exactly what all the
other workers are doing.
That was proposed earlier in this thread, and the issue is it may starve
all the other tables when the "important" tables need cleanup all the time.
There's plenty of other ways to shoot yourself in the foot in that
regard already. We can always have safeguards in place if we get too
close to wrap-around, just like we currently do.
Jim Nasby, Chief Data Architect, Austin TX