I have been pondering how to deal with work_mem for a couple of months myself
and had very similar thoughts.
As far as I can tell the problem goes beyond work_mem though: 1. There are
several hash operations such as set-ops, hashed subplans, and hash aggregates
which today are not spilling at all. We have solved them partially so far and,
once complete, think the fixes can be pushed into community PG if there is
desire for it 2. We also worry about large objects which can bloat a backend 3.
Others random allocations I fear I just don’t know about. 4. OS are chronically
poor in trading memory between processes even after the memory is freed unless
it’s returned to the OS in big contiguous chunks.
Just as you have, we have also considered holistic provisioning of work_mem
across all consumers, but we find that to be too complex. Having an “emergency
fund” in shared memory is also an option, but I find it too limiting. Also this
approach what was done at DB2 when I was there and it proved cumbersome.
So I’m currently pressing forward with a much more fundamental approach:
Pushing Top Transaction Context and its children into shared memory. To avoid
fragmentation and serialization on latches I have defined the concept of “a
context cluster”. The root of the cluster is the sole true allocator of memory.
Child contexts allocate blocks as pallocs from the cluster root. Basically
memory management goes recursive and children live within the root. The root
(TopTransactionContext) allocates big blocks. e.g. 8MB at a time. Within a
transaction PG operates as usual with freelists and all turning over these same
8MB or allocating more if needed. But at the end of every transaction big
chunks of memory become available to share with other transactions again. A few
places where we reparent contexts need to detect that this can’t be done
between or in/out of clusters and do deep copies if needed, but there are few
of those. Come to think of it all the cases I encountered so far were SFDC
specific…
I’m also moving the e-state from the Portal Heap to the Top Transaction
Context. At the end of the day the assumption is that most transactions only
need one block from shared memory, and I can probably pin it to the backend,
further reducing contention. If there is an Out Of Memory situation - should be
very rare - there are multiple ways to deal with it. If there is no dead-lock
we can simply wait. If there is one rolling back the transaction that
encountered the OOM is the obvious - if not optimal solution. Finding the
biggest consumer and sending it a signal to back of would be another way to do
it.
My goal is to run a backend with 50-100MB with all local caches controlled for
size. Transaction Memory with e-states included should sized for 8MB/backend
plus a fixed “spill” of some GB.
Yes, this is invasive and I’m sure to debug this for a while given my limited
knowledge of the engine. I may yet fail spectacularly. On the other hand it’s
conceptually pretty straight forward.
Cheers Serge Rielau SFDC
