Re: cost based vacuum (parallel) - Mailing list pgsql-hackers

On Mon, Nov 4, 2019 at 11:42 PM Andres Freund <andres@anarazel.de> wrote:
>
>
> > The two approaches to solve this problem being discussed in that
> > thread [1] are as follows:
> > (a) Allow the parallel workers and master backend to have a shared
> > view of vacuum cost related parameters (mainly VacuumCostBalance) and
> > allow each worker to update it and then based on that decide whether
> > it needs to sleep.  Sawada-San has done the POC for this approach.
> > See v32-0004-PoC-shared-vacuum-cost-balance in email [2].  One
> > drawback of this approach could be that we allow the worker to sleep
> > even though the I/O has been performed by some other worker.
>
> I don't understand this drawback.
>

I think the problem could be that the system is not properly throttled
when it is supposed to be.  Let me try by a simple example, say we
have two workers w-1 and w-2.  The w-2 is primarily doing the I/O and
w-1 is doing very less I/O but unfortunately whenever w-1 checks it
finds that cost_limit has exceeded and it goes for sleep, but w-1
still continues.  Now in such a situation even though we have made one
of the workers slept for a required time but ideally the worker which
was doing I/O should have slept.  The aim is to make the system stop
doing I/O whenever the limit has exceeded, so that might not work in
the above situation.

>
> > (b) The other idea could be that we split the I/O among workers
> > something similar to what we do for auto vacuum workers (see
> > autovac_balance_cost).  The basic idea would be that before launching
> > workers, we need to compute the remaining I/O (heap operation would
> > have used something) after which we need to sleep and split it equally
> > across workers.  Here, we are primarily thinking of dividing
> > VacuumCostBalance and VacuumCostLimit parameters.  Once the workers
> > are finished, they need to let master backend know how much I/O they
> > have consumed and then master backend can add it to it's current I/O
> > consumed.  I think we also need to rebalance the cost of remaining
> > workers once some of the worker's exit.  Dilip has prepared a POC
> > patch for this, see 0002-POC-divide-vacuum-cost-limit in email [3].
>
> (b) doesn't strike me as advantageous. It seems quite possible that you
> end up with one worker that has a lot more IO than others, leading to
> unnecessary sleeps, even though the actually available IO budget has not
> been used up.
>

Yeah, this is possible, but to an extent, this is possible in the
current design as well where we balance the cost among autovacuum
workers.  Now, it is quite possible that the current design itself is
not good and we don't want to do the same thing at another place, but
at least we will be consistent and can explain the overall behavior.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com