Thread: [ADMIN] Autovacuum of pg_database
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
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
=?UTF-8?Q?Jakub_Jedelsk=C3=BD?= <jakub.jedelsky@gmail.com> writes:
> 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`
I tried to reverse-engineer a test case out of this description,
without much success. If you have a script that can reproduce
the problem (even if not 100% success rate), could you share it?
regards, tom lane
On Mon, May 15, 2017 at 01:29:28PM -0400, Tom Lane wrote: > =?UTF-8?Q?Jakub_Jedelsk=C3=BD?= <jakub.jedelsky@gmail.com> writes: > > 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` > > I tried to reverse-engineer a test case out of this description, > without much success. If you have a script that can reproduce > the problem (even if not 100% success rate), could you share it? > > regards, tom lane Thanks for reply. I will try to prepare something asap, but it can take some time, so please be patient. (I just want to make you sure, that I'm not ignoring that thread :)) Regards, jj.