Thread: Memory-leak-like effect on insane query (postgres 8.1.6)

So I'm having a problem with an "insane" query that's the result of
joining several views.  This query seems to consume 10's of megabytes of
memory a second, until either the query completes or the Linux OOM
killer takes it out.  Completing a small subset of the data (5%) takes
almost 35% of the memory of a 4G box.

I would post an explain, but it's over 250 lines long (I told you the
query was insane- abuse of views, woo hoo!), and I haven't been able to
reproduce the problem with a simpler query.  This is on Postgres 8.1.6.
Fortunately this was on a development box, so no harm done, but it still
worries me.

The plan does, however, contain many dozens of sorts and dozens more of
hashes, so of course my first thought was my generous workmem (32768) or
temp_buffers (48000) settings.  Reducing these to 128 and 100
respectively did not change the memory utilization profile.

So, I have a couple of questions:
1) What can I look at to see where the memory is going? Or can someone
just tell me?
2) Is there any way I can prevent someone from crashing production in
the same way (production is 8.1.10)?

Thanks.

Brian

Sorry to follow up to my own post, but one fact I forgot to add: this is
not, in fact, a real memory leak.  Once the query completes (or is
cancelled), memory utilization of the postgres process drops back to
effectively 0, i.e. all the used memory is freed.  Which is why I called
it a memory-leak-like effect.

Brian

Brian Hurt <bhurt@janestcapital.com> writes:
> So, I have a couple of questions:
> 1) What can I look at to see where the memory is going? Or can someone
> just tell me?
> 2) Is there any way I can prevent someone from crashing production in
> the same way (production is 8.1.10)?

Running the postmaster under a per-process-memory ulimit setting small
enough to avoid the wrath of the OOM killer would help both of these.
(You'd get an out-of-memory error instead of kill -9, and then looking
at the memory dump in the postmaster log would help with #1.)

            regards, tom lane