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
