Autovacuum of pg_database

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

Responses

Browse pgsql-admin by date

  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