Hi all,
we are facing problem, which was there some time ago: [1] and [2]. Autovacuum is hanging from time to time on a random database on shared table pg_database.
We are using PostgreSQL 9.5.4 with patch provided by [3], but it's still happening. I'm afraid there isn't problem with a multiple access to shared table only, but with autovacuum/vacuum/? itself, which somehow hangs on 'vacuum freeze'. PIDs of a autovacuum workers are changing in log - so new processes are forked every time (once or twice per second) - the log looks same like in [2], so I'm not sending it again. It's possible to fix it with manual run of vacuum freeze on pg_database of all DBs with age(datfrozenxid) > autovacuum_freeze_max_age.
The problem (can) occurs when the pg_database table hits `autovacuum_freeze_max_age` and I'm able to reporoduce it (but not always) with this stupid reproducer:
* set autovacuum_freeze_min_age to it's minimum - 100000
* generating ~ 1000 DBs
* make some transactions to almost hit `autovacuum_freeze_max_age`
* `vacuum freeze` all tables in all databases except pg_database
* run a few transactions to hit `autovacuum_freeze_max_age`
About our backend:
One Postgresql server has ~1200 DBs. The problem occurs from time to time on different servers. We use default values for vacuum_freeze_min_age, vacuum_freeze_table_age and autovacuum_freeze_max_age. autovacuum_max_workers is set to 6 workers. We have about 30 transactions per second on a server in average (that hangs are on servers with higher TPS).
Qs:
Was there any patch to a newer version, which I missed and which can solve it? (We are planning upgrade to PostgreSQL 9.6, but it will take some time).
Is it possible to tune it with changing settings? (if I count it well, we hit default autovacuum_freeze_max_age with ~30 TPS every ~ 77 days. But the true is that I don't know if it's good or not :))
Or is there anything I missed? I don't think that my solution - monitor logs and run vacuum freeze manually when the problem occurs - is the best one.
Thanks a lot,
- jj.
[1]
https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com[2]
https://www.postgresql.org/message-id/572B63B1.3030603%40flexibee.eu[3]
https://www.postgresql.org/message-id/E1b0Dwr-0003Ms-Mr@gemulon.postgresql.org