Hi,
I am trying to get my head around the memory usage of my postgres setup. I started digging into this because of memory exhaustion on my server. Quick overview:
Postgres is running on a dedicated FreeBSD 11.2 server with 32GB of RAM, there is a second server in hot standby / wal replication. My “worker” processes, mainly php processes, connect through pgbouncer (session mode).
What I observed is that there are, at any time, around 10-15 postgres backend processes on my main db server consuming around 4GB of memory (RES in glances) each. These processes keep this footprint until pgbouncer closes the connection.
After a bunch of reading and trying to understand the fundamentals of postgres and its processes better, I realised that I still allow pgbouncer too many connections. But I keep running in this memory consumption problem:
So: I have backend processes consuming 4GB, which seems to be the cap, of RAM for their “whole” lifespan. I was able to reproduce this behaviour by connecting to my db, querying the process id (around 80MB baseline) running some expensive queries (each added between a few hundred MB and 1GB to RES) and waiting. The RES-memory consumption stayed until i closed the connection. Running smaller queries added to the memory consumption.
I ran “DISCARD ALL;”, since this is what pgbouncer does to “clean” after a session - no effect. I wrapped my queries inside a transaction and commited or rollbacked it - no effect.
As far as I understood connection pooling, with pgbouncer in my case, the pooler<->db connection is kept alive and got reused for a long time. If the memory consumption accumulates in this scenario my max_connections has to be very small (RAM / 4GB, where ever this cap comes from) - I think there is something wrong in my setup or my understanding.
It is mentioned there, that free(3) implementation on linux uses brk(), which… I’m kind of lost there - but my take away: Maybe I have to something to enable postgres to free / reclaim unused / no more used memory?
Thanks for any help and suggestions
Sebastian