lastOverflowedXid does not handle transaction ID wraparound
Stan Hu <stanhu@gmail.com>
From: Stan Hu <stanhu@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2021-10-13T04:53:22Z
Lists: pgsql-hackers
In a blog post (https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/), I described how PostgreSQL can enter into a suboverflow condition on the replica under a number of conditions: 1. A long transaction starts. 2. A single SAVEPOINT is issued. 3. Many rows are updated on the primary, and the same rows are read from the replica. This can cause a significant performance degradation with a replica due to SubtransSLRU wait events since the replica needs to perform a parent lookup on an ever-growing range of XIDs. Full details on how to replicate this: https://gitlab.com/-/snippets/2187338. The main two lines of code that cause the replica to enter in the suboverflowed state are here (https://github.com/postgres/postgres/blob/317632f3073fc06047a42075eb5e28a9577a4f96/src/backend/storage/ipc/procarray.c#L2431-L2432): if (TransactionIdPrecedesOrEquals(xmin, procArray->lastOverflowedXid)) suboverflowed = true; I noticed that lastOverflowedXid doesn't get cleared even after all subtransactions have been completed. On a replica, it only seems to be updated via a XLOG_XACT_ASSIGNMENT, but no such message will be sent if subtransactions halt. If the XID wraps around again and a long transaction starts before lastOverflowedXid, the replica might unnecessarily enter in the suboverflow condition again. I've validated this by issuing a SAVEPOINT, running the read/write test, logging lastOverflowedXid to stderr, and then using pg_bench to advance XID with SELECT txid_current(). After many hours, I validated that lastOverflowedXid remained the same, and I could induce a high degree of SubtransSLRU wait events without issuing a new SAVEPOINT. I'm wondering a few things: 1. Should lastOverflowedXid be reset to 0 at some point? I'm not sure if there's a good way at the moment for the replica to know that all subtransactions have completed. 2. Alternatively, should the epoch number be used to compare xmin and lastOverflowedXid? To mitigate this issue, we've considered: 1. Restarting the replicas. This isn't great, and if another SAVEPOINT comes along, we'd have to do this again. It would be nice to be able to monitor the exact value of lastOverflowedXid. 2. Raise the NUM_SUBTRANS_BUFFERS as a workaround until the scalable SLRU patches are available (https://commitfest.postgresql.org/34/2627/). 3. Issue SAVEPOINTs periodically to "run away" from this wraparound issue.
Commits
-
Reset lastOverflowedXid on standby when needed
- 05e6e78c1840 15.0 landed
- b0f6bd48f35d 14.1 landed
- 7381b79ade47 9.6.24 landed
- 774d00573966 10.19 landed
- 691c0df73a74 11.14 landed
- 8f779a1a3eb6 12.9 landed
- e1fee28a0441 13.5 landed
-
Fix variable shadowing in procarray.c.
- dc899146dbf0 15.0 cited
-
Move InRecovery and standbyState global vars to xlogutils.c.
- 317632f3073f 15.0 cited