On 04/30/2019 10:12 AM, Rui DeSousa wrote: > > >> On Apr 30, 2019, at 9:27 AM, John Lumby <johnlumby@xxxxxxxxxxx >> <mailto:johnlumby@xxxxxxxxxxx>> 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. >
