| 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. I found some hint in these slides (slide 58 - 64) https://www.slideshare.net/AlexeyBashtanov/postgresql-and-ram-usage 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 |
