Re: autovacuum: change priority of the vacuumed tables

On 03/03/2018 10:21 PM, Jim Nasby wrote:
> 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.
>

I have my doubts about both points - usefulness of low-level controls
and viability of tools built on them.

Firstly, my hunch is that if we knew what low-level controls to expose,
it would pretty much how to implement the tool internally. Exposing
something just because you home someone will find a use for that seems
like a dead-end to me. So, which controls would you expose?

Second, all the statistics used to decide which tables need vacuuming
are already exposed, and we have things like bgworkers etc. So you could
go and write a custom autovacuum today - copy the autovacuum code, tweak
the scheduling, done. Yet no such tool emerged yet. Why is that?

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

I disagree this a "query problem" - it certainly is not the case that
simply prioritizing the tables differently will make a difference. Or
more precisely, it certainly does not solve the autovacuum issues I'm
thinking about. I have no idea which issues are you trying to solve,
because you haven't really described those.

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

Which is why I mentioned we could have separate pools of autovacuum
workers - one for regular tables, one for "important" ones.

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

I haven't mentioned wraparound at all.

My point is that if you entirely ignore some tables because "important"
ones need constant cleanup (saturating the total autovacuum capacity),
then you'll end up with extreme bloat in those other tables. And then
you will need to do more work to clean them up, which will likely cause
delays in cleaning up the important ones.


FWIW I find this discussion rather too hand-wavy, because I have no idea
what controls would you like to expose, etc. If you have an idea, please
write a patch or at least a proposal explaining the details.

regards

--
Tomas Vondra                  https://urldefense.com/v3/__http://www.2ndQuadrant.com__;!!Ck4v2Rc!h1TqYTvzyQn1mDOR119nZYsN26gRVhRZ3UIT7sQT60SgRR7833KpQ3CtJ6l0JpMIEt3JBbd09ZZ9-N3U5MHRn7r7RU4NEiRE$
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services