Обсуждение: backend proccess memory accumulates
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
Sebastian Gabbert <pg@huehnerhose.de> writes:
> Postgres is running on a dedicated FreeBSD 11.2 server with 32GB of RAM, there is a second server in hot standby /
walreplication. 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
consumingaround 4GB of memory (RES in glances) each. These processes keep this footprint until pgbouncer closes the
connection.
That is, most likely, almost entirely shared space (shared buffers and so
on). Sorting out how much is shared and how much is actually per-process
memory can be tricky, because most Unix process monitoring tools aren't
very good about showing the difference. I don't use FreeBSD enough to be
sure how to tell the difference with its tools --- but you can't get
anywhere with diagnosing PG's true memory consumption until you've got a
handle on that.
It would be interesting to know what you've got shared_buffers set to
on this server, though.
regards, tom lane
Thanks for the reply, but I think i sorted this out, that was why i spoke of “RES”. As far as I understand memory stats shownby top or glances there are two columns, one for “virtual memory” (top->Size, glances->VIRT) and one for residentialmemory (RES in both cases). And my understanding is that virtual shows the memory including shared memory chunks,RES shows the actually used memory by this process alone. I absolutely could be wrong in my interpretation - since I read at many places that theses distinctions are difficult. Bottom line is: My server swapes and pagedeamon is one of the busiest processes :) So RAM is used in vast amounts. > On 22. Nov 2018, at 17:26, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Sebastian Gabbert <pg@huehnerhose.de> writes: >> Postgres is running on a dedicated FreeBSD 11.2 server with 32GB of RAM, there is a second server in hot standby / walreplication. 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 consumingaround 4GB of memory (RES in glances) each. These processes keep this footprint until pgbouncer closes the connection. > > That is, most likely, almost entirely shared space (shared buffers and so > on). Sorting out how much is shared and how much is actually per-process > memory can be tricky, because most Unix process monitoring tools aren't > very good about showing the difference. I don't use FreeBSD enough to be > sure how to tell the difference with its tools --- but you can't get > anywhere with diagnosing PG's true memory consumption until you've got a > handle on that. > > It would be interesting to know what you've got shared_buffers set to > on this server, though. > > regards, tom lane >
On Nov 22, 2018, at 12:31 PM, Sebastian Gabbert <pg@huehnerhose.de> wrote:Thanks for the reply, but I think i sorted this out, that was why i spoke of “RES”. As far as I understand memory stats shown by top or glances there are two columns, one for “virtual memory” (top->Size, glances->VIRT) and one for residential memory (RES in both cases). And my understanding is that virtual shows the memory including shared memory chunks, RES shows the actually used memory by this process alone.
I absolutely could be wrong in my interpretation - since I read at many places that theses distinctions are difficult.
I don’t think that is correct. I look at them mean the following.
VIRT - is the total address space allocated.
RES - is the actual memory mapped to real memory (including shared memory). PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
70596 postgres 1 89 0 49971M 23581M CPU41 41 5:51 56.88% postgres
65566 postgres 1 88 0 50005M 24384M CPU38 38 8:04 56.79% postgres
19650 postgres 1 89 0 50154M 48762M CPU67 67 281:16 55.86% postgres
As you can see from the above; process 19650 has more of the shared memory mapped than the other two processes.
Note: You can use "procstat -v” to look at the memory map of each process.
Bottom line is: My server swapes and pagedeamon is one of the busiest processes :) So RAM is used in vast amounts.
Not good; sounds like your memory is not correctly allocated and/or you have some very large/bad queries.
Which file system are you using? If you’re using ZFS then it needs to be bound. ZFS will try to use as much memory as possible which doesn’t work well for Postgres.
You want to adjust ZFS's arc_max and Postgres shared buffers to be less than the total memory while leaving enough memory for the OS and processes to allocate their own memory. I would recommend starting with arc_max + shared buffers being around 60-70% of memory.
Configure ZFS’s arch_max in /boot/loader.conf and reboot (adjust appropriately):
vfs.zfs.arc_max=“256G"