I have an application that uses Postgres 9.3 as the primary datastore.
Like any real-life application, it's not all roses—There are many ugly,
convoluted, and inefficient queries.
Some of these queries use quite a bit of memory. I've observed a
"high-water mark" behavior in memory usage: running a query increases the
worker memory by many MBs (beyond shared buffers), but the memory is not
released until the connection is closed. For example, here's the memory
usage on my test server when running a query once and leaving the
connection open.
$ free -h # Before the query
total used free shared buffers cached
Mem: 7.8G 5.2G 2.6G 212M 90M 4.9G
-/+ buffers/cache: 248M 7.6G
Swap: 0B 0B 0B
$ free -h # After the query
total used free shared buffers cached
Mem: 7.8G 5.3G 2.5G 212M 90M 4.9G
-/+ buffers/cache: 312M 7.5G
Swap: 0B 0B 0B
$ sudo -u postgres smem -trs uss
PID User Command Swap USS PSS
RSS
8263 postgres postgres: postgres souschef 0 200204 203977
209540
8133 postgres /usr/lib/postgresql/9.3/bin 0 50456 61090
74596
8266 postgres /usr/bin/python /usr/bin/sm 0 5840 6261
7460
8138 postgres postgres: autovacuum launch 0 776 1146
2968
8139 postgres postgres: stats collector p 0 300 470
1872
8135 postgres postgres: checkpointer proc 0 148 342
1880
8137 postgres postgres: wal writer proces 0 140 322
1812
8136 postgres postgres: writer process 0 132 6814
15140
-------------------------------------------------------------------------------
8 1 0 257996 280422
315268
This is proving to be very troublesome on my production server because I
use connection pooling (so connections remain open indefinitely) and the
connection memory seems to rise without end, to the point where 25 open
connections OOM'd a 4GB server.
So I have a couple questions: Is this high-water mark memory behavior
expected? If so, how can I identify the queries that are using lots of
memory and driving the high-water mark upwards?
I understand that this post is rather vague, I didn't want to talk your ear
off with specifics in case this was pretty basic, well-understood
behavior. If necessary, I can follow up with an email diving into the
specifics of what I'm observing.
— Theron
