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:

enter image description 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.