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.
