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

On 04/30/2019 10:12 AM, Rui DeSousa wrote:
>
>
>> On Apr 30, 2019, at 9:27 AM, John Lumby <johnlumby(at)hotmail(dot)com
>> <mailto:johnlumby(at)hotmail(dot)com>> wrote:
>>
>> it does do ANALYZE - in this context is that "DDL"?
>
> I would consider that DDL and the application should avoid doing it;
> otherwise, you end up with blocking issues where both readers and
> writers are blocked unnecessarily. The application issuing analyze on
> its own temp tables is fine and non blocking. If you must analyze
> tables then I would limit which processes have access to those tables
> to avoid blocking issues.

It turns out you are absolutely right - the ANALYZE being done on
every loop was the culprit.
Changing my workload script to make the ANALYZE done less often or when
signalled,
and switching to vanilla postgresql (11.2 ), I see autovacuum not being
cancelled except for
exactly the times when the workload runs an ANALYZE. There is a
near-one-to-one
correlation between workload ANALYZE and autovacuum being cancelled.

The only reason I had included the ANALYZE on every loop was so that I
could see the growth,
not an essential part of the workload. So I think this gives me what
I need to limit growth
and occasionally monitor it.

But there is a bit of an irony in that I can either
limit growth but not watch it continuously
or not limit it and watch it grow
This may be a bit wild, but, I wonder if there would be any value in a
new configuration parameter
to address the very specific scenario of worker doing ANALYZE blocked by
autovacuum of same table.
After all, in this scenario, surely the user would prefer that the
ANALYZE would wait until after
the autovacuum has finished, rather than cancel the autovacuum and
see un-vacuumed stats?

>
> I would look at your auto vacuum/analyze settings as oppose to the
> application issuing analyze commands as those are going to be a non
> blocking option.
>