Re: admin control over cancelling autovacuum when blocked by a lock

On 04/29/2019 07:15 PM, Simon Riggs wrote:
> On Mon, 29 Apr 2019 at 22:02, John Lumby <johnlumby(at)hotmail(dot)com
> <mailto:johnlumby(at)hotmail(dot)com>> wrote:
>
> If a regular backend worker becomes blocked waiting for a lock
> held by an autovacuum worker,
> then , depending on some decision made by deadlock detector,
> the autovacuum worker may be signalled to terminate
>
> [ ... ]
>
>
>
> Is there any configuration parameter which controls this?
>
> [ ... ]
>
>
> Why would you want this?
Because it greatly reduces rate of growth of certain indexes in some
workloads
(based on test in which I modified the code to change the cancel to a
plain WARNING)
>
> If the autovacuum is cancelled, it will re-execute again in the near
> future, once your interrupting SQL has released locks.
In the particular workload I'm investigating, which involves a great
deal of repetitive
insert and delete activity on one table with several indexes,
even with the most aggressive autovacuum settings I can find,
it ends up being cancelled nearly 99% of the time, and (question)
I *think* when an autovacuum worker is cancelled, all or most of the work
it did on its last table is undone -- true? I am basing that on
observing
growth of dead rows, not on the code, so not sure.
But changing the cancel to a WARNING certainly prevents
growth of dead rows without slowing the workload down unacceptably.

John Lumby
>
> --
> Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services