I have a 9.6 PostgreSQL RDS instance that I am tending to, but did not initially configure, and have been struggling with two different symptoms:
the system (a
db.r3.2xlargew/ 61GB memory) would experience "cliffs" in freeable memory, dropping 20-30GB almost instantaneously, followed by -
an auto-recovery event after some amount of time (could be minutes, could be hours).
On investigation, I found
shared_buffers set abnormally high, actually exceeding physical memory on the machine. I have reset to a more sane level, but would like some more insight into the expected behavior.
The workload is modest - about a dozen or so concurrent users, manually triggering some large queries (e.g., aggregates on 300+ million row tables). My guess is that those queries trigger the cliffs. My question is about the after effects:
shared_buffersexplain the stability problems? E.g., PostgreSQL tries to allocate more buffer than the OS has left, the OS says "nope" and things go south from there.
shared_buffermemory ever released back to the OS? For example, I would frequently see a couple idle backends consuming 20-30GB, terminate them, but never see a rise in freeable memory.