Re: a heavy duty operation on an "unused" table kills my server

Greg Smith <greg(at)2ndquadrant(dot)com> writes:
> In this context, "priority inversion" is not a generic term related to
> running things with lower priorities. It means something very
> specific: that you're allowing low-priority jobs to acquire locks on
> resources needed by high-priority ones, and therefore blocking the
> high-priority ones from running effectively. Unfortunately, much like
> deadlock, it's impossible to avoid the problem in a generic way just by
> being careful. It's one of the harder issues that needs to be
> considered in order to make progress on implementing this feature one day.

It might be worth remarking on how the vacuum_cost_delay logic deals
with the issue. Basically, the additional sleeps are carefully inserted
only at places where we are not holding any low-level locks (such as
buffer content locks). We do not do anything about the table-level
lock that vacuum has got, but vacuum's table lock is weak enough that it
won't block most ordinary queries. So in typical circumstances it's not
a problem if vacuum runs for a very long time. But you can definitely
get burnt if you have a competing session trying to acquire an exclusive
lock on the table being vacuumed, or if you enable vacuum_cost_delay on
a VACUUM FULL.

Autovacuum takes a couple of extra precautions: it never does VACUUM
FULL at all, and it is set up so that a request for a conflicting
exclusive lock causes the autovacuum operation to get canceled.

The upshot is that you can enable autovacuum_cost_delay without much
fear of creating priority-inversion delays for competing tasks. But
it's not at all clear how we'd generalize this design to allow slowdown
of other operations without creating significant inversion hazards.

BTW, it was suggested upthread that the "cost balance" stuff represented
an additional problem that'd have to be surmounted to get to a general
solution. I don't think this is necessarily the case. The point of the
cost balance code is to ensure that multiple autovacuum workers don't
eat a disproportionate amount of resources. It's not clear that someone
would even want such a feature for user-level background queries, and
even if desirable it's certainly not a must-have thing.

regards, tom lane