backend proccess memory accumulates

From: Sebastian Gabbert <pg(at)huehnerhose(dot)de>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: backend proccess memory accumulates
Date: 2018-11-22 15:15:28
Message-ID: 6E65FD77-6FA1-485E-AEF2-318B56964255@huehnerhose.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 <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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2018-11-22 16:26:31 Re: backend proccess memory accumulates
Previous Message Shreeyansh Dba 2018-11-22 13:03:18 Re: pgAdmin 4 v3.5 eternal loading