Hi, On 2020-03-19 10:23:48 -0500, Justin King wrote: > > From a single stats snapshot we can't actually understand the actual xid > > consumption - is it actually the xid usage that triggers the vacuums? > > We have looked at this and the xid consumption averages around 1250 > xid/sec -- this is when we see the "aggressive" autovac kick off in > the logs. What I don't understand is why these xid's are being > consumed at this rate on the databases with no activity (postgres, > template1). The xid counter is global across all databases. > > What makes you think it is a problem that you have all these vacuums? If > > you actually update that much, and you have indexes, you're going want a > > lot of vacuums? > I actually don't think there's a problem with the vacuums (I was > mostly pointing out that they are very regular and not problematic). > The main problem I am having is that something is causing the > autovacuums to completely stop and require manual intervention to > resume -- and it seems to be when the "postgres" or "template1" > database hits the autovacuum_freeze_max_age. Did you look at pg_stat_activity for those autovacuums to see whether they're blocked on something? > > > What is interesting is that this happens with the 'postgres' and > > > 'template1' databases as well and there is absolutely no activity in > > > those databases. > > > > That's normal. They should be pretty darn quick in v12? > > Yes, a manual VACUUM FREEZE of either database takes less than 1 > second -- which is why it's perplexing that the autovac starts and > never seems to complete and prevents other autovacs from running. One big difference between a manual VACUUM and autovacuum is that with the default settings VACUUM is not throttled, but autovacuum is. What are your vacuum_cost_delay, autovacuum_vacuum_cost_delay, vacuum_cost_limit, autovacuum_vacuum_cost_limit, vacuum_cost_page_hit, vacuum_cost_page_miss set to? Greetings, Andres Freund
