backend proccess memory accumulates

Поиск
Список
Период
Сортировка
От Sebastian Gabbert
Тема backend proccess memory accumulates
Дата
Msg-id 6E65FD77-6FA1-485E-AEF2-318B56964255@huehnerhose.de
обсуждение исходный текст
Ответы Re: backend proccess memory accumulates  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Shreeyansh Dba
Дата:
Сообщение: Re: pgAdmin 4 v3.5 eternal loading
Следующее
От: Tom Lane
Дата:
Сообщение: Re: backend proccess memory accumulates