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 critical tables.

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
OpenSCG                 http://OpenSCG.com

Reply via email to