From: | Jakub Jedelský <jakub(dot)jedelsky(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Autovacuum of pg_database |
Date: | 2017-05-15 08:55:51 |
Message-ID: | CAE9--j-08gTnFHab0t-fcuEUvsW=pw3yuPD46FCdzwV7rNfcGw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-05-15 17:29:28 | Re: Autovacuum of pg_database |
Previous Message | Adrian Klaver | 2017-05-13 22:26:56 | Re: [SQL] PostgreSQL 9.3 DB initialization issue |