From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Craig Milhiser <craig(at)milhiser(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Maximum xid increasing |
Date: | 2023-11-08 20:59:48 |
Message-ID: | aceaaf7781a4fd8c7792f510efd6d7743a43fbdd.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2023-11-08 at 09:09 -0500, Craig Milhiser wrote:
> Our max xid are over 500m and climbing. Auto vacuuming and manual vacuuming are returning
>
> autovacuuming to prevent wraparound.
> WARNING: oldest xmin is far in the past
> HINT: Close open transactions soon to avoid wraparound problems.
> You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
>
> The autovacuum previously kicked in at 200m and reduced the maximum xid. Now there is no reduction.
>
> We have a writer (master) node, 3 readers (slaves) and a hot-standby. We have an ETL vendor hooked
> up to replicate. All of the replications are working. There is current data in each. We have checked
> our replication slots. Everything is in use and working.
>
> From transactions, we have terminated every writer node PID since before this climb has started.
> Almost every client has been restarted.
>
> We are handling 100+ insert/updates per second. Most transactions complete in ~2 seconds. Though some
> may take 1 minute. There are no long running idle in transaction activities.
>
> We have been scouring the internet reading many helpful pages and trying their advice but we cannot
> find a problem.
>
> We have restored a daily snapshot in another cluster. After vacuuming the max xid dropped to 50m.
> No client was connected. Implying that a restart should fix the issue.
>
> I am searching for any hints. We have not found a smoking gun to focus on. This is a 24x7 operation
> and would rather not have to restart the server.
>
> We are on v13.8, AWS RDS.
>
> Is there a way to find which row or table or lock or some open transaction is preventing the vacuuming
> from lowering xid? Any hints on where to look?
If that is not a bug introduced by Amazon, and you are certain that there are no long-running
transactions and stale replication slots, there are two more possibilities:
1. a prepared transaction (check pg_prepared_xacts)
2. a long-running query on a standby, and "hot_standby_feedback = on"
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2023-11-08 21:12:59 | Weirdness (bug?) with aggregates and subqueries |
Previous Message | Adrian Klaver | 2023-11-08 19:16:13 | Re: No longer available installer for greater version then PostgreSQL 10 |