Hello Found this on Postgres 9.6, but I think it affects back to 9.4.
I've seen a case where reorderbuffer keeps very large amounts of memory in use, without spilling to disk, if the main transaction does little or no changes and many subtransactions execute changes just below the threshold to spill to disk. The particular case we've seen is the main transaction does one UPDATE, then a subtransaction does something between 300 and 4000 changes. Since all these are below max_changes_in_memory, nothing gets spilled to disk. (To make matters worse: even if there are some subxacts that do more than max_changes_in_memory, only that subxact is spilled, not the whole transaction.) This was causing a 16GB-machine to die, unable to process the long transaction; had to add additional 16 GB of physical RAM for the machine to be able to process the transaction. I think there's a one-line fix, attached: just add the number of changes in a subxact to nentries_mem when the transaction is assigned to the parent. Since a wal ASSIGNMENT records happens once every 32 subxacts, this accumulates just that number of subxact changes in memory before spilling, which is much more reasonable. (Hmm, I wonder why this happens every 32 subxacts, if the code seems to be using PGPROC_MAX_CACHED_SUBXIDS which is 64.) Hmm, while writing this I am wonder if this affects cases with many levels of subtransactions. Not sure how are nested subxacts handled by reorderbuffer.c, but reading code I think it is okay. Of course, there's Tomas logical_work_mem too, but that's too invasive to backpatch. -- Álvaro Herrera PostgreSQL Expert, https://www.2ndQuadrant.com/
diff --git a/src/backend/replication/logical/reorderbuffer.c b/src/backend/replication/logical/reorderbuffer.c index f6ea315422..d5fdd7c6a6 100644 --- a/src/backend/replication/logical/reorderbuffer.c +++ b/src/backend/replication/logical/reorderbuffer.c @@ -869,6 +869,8 @@ ReorderBufferAssignChild(ReorderBuffer *rb, TransactionId xid, /* Possibly transfer the subtxn's snapshot to its top-level txn. */ ReorderBufferTransferSnapToParent(txn, subtxn); + txn->nentries_mem += subtxn->nentries_mem; + /* Verify LSN-ordering invariant */ AssertTXNLsnOrder(rb); } @@ -2333,7 +2335,6 @@ ReorderBufferSerializeTXN(ReorderBuffer *rb, ReorderBufferTXN *txn) spilled++; } - Assert(spilled == txn->nentries_mem); Assert(dlist_is_empty(&txn->changes)); txn->nentries_mem = 0; txn->serialized = true;
