I have a PostgreSQL 9.3.4 instance running in a CentOS 5.x VM (Linux 2.6.18-308.8.2.el5xen) with 6GB RAM. I've set
shared_buffers to 1GB and
work_mem to 1MB (a low value for testing). Here are my custom settings.
I'm trying to figure out why a certain (admittedly nasty, it's generated by a Java application) SQL query is taking so much memory beyond the 1GB RES I'd expect to see in
top. I understand that there's no direct way to cap how much memory a worker process will use, but you adjust
work_mem to make it spool large sort operations and hash tables off to disk to indirectly control it. Now, the
D_MC108 table in the query is "big" (12GB), but I thought Postgres would just deal with this by going through the buffer cache and spooling to disk as needed. Slow would be preferable to consuming all memory on the box. (By the way, the devs are off changing this query, but it remains quite a mystery at this point).
So when the app in question runs that query, I see in top that its RES ramps up pretty quickly to the 1GB mark (which is expected, I get that the worker process eventually maps the entirety of shared_buffers into its own address space). However it then continues to grow seemingly without bound, as you can see in top here:
Eventually it consumes all available memory and Linux's OOM killer wakes up and starts killing things.
My question is really simple: I thought the a postgres worker's RES usage could be explained as the sum of
shared_buffers (if it's mapped the entirety of the buffer cache) plus some multiple of
work_mem, which I understand could be a "a lot", based on the number of sorts and joins, and there are a bunch in this query. But to explain an additional 2.8GB of memory usage (3.8GB -
shared_buffers) as seen in
top above, that'd be 2,800 times my
work_mem of 1MB. Is that nasty query I linked to capable of using 2,800 times
work_mem? Or is my understanding incorrect? Here is the EXPLAIN (I can't run EXPLAIN ANALYZE for obvious reasons).
Thanks so much.
Edit: okay, sometimes it just helps to write it all down to figure it out. I think I figured it out at least. I noticed a bunch of
Materialize steps in that EXPLAIN plan. Just to test I tried
set enable_material=false; and found the process never went over 1GB RES. The plan was even crazier of course. So it was the Materialize steps, and whatever memory that uses is what was consuming all the memory. I can't find any way to limit that, please post something if you know. So for now, I'm at least relieved now knowing what mysterious force was at work here. The devs can fix up the query and we'll move on.