On 04/29/2019 07:15 PM, Simon Riggs wrote: > On Mon, 29 Apr 2019 at 22:02, John Lumby <johnlumby@xxxxxxxxxxx > <mailto:johnlumby@xxxxxxxxxxx>> 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
