Re: autovacuum: change priority of the vacuumed tables

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