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/