Re: limiting query time and/or RAM

On Thu, Sep 17, 2009 at 1:35 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Sep 17, 2009 at 1:31 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>> In response to Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
>>
>>> On Thu, Sep 17, 2009 at 12:56 PM, Alan McKay <alan(dot)mckay(at)gmail(dot)com> wrote:
>>> > Is there any way to limit a query to a certain amount of RAM and / or
>>> > certain runtime?
>>> >
>>> > i.e. automatically kill it if it exceeds either boundary?
>>> >
>>> > We've finally narrowed down our system crashes and have a smoking gun,
>>> > but no way to fix it in the immediate term.  This sort of limit would
>>> > really help us.
>>>
>>> Generally speaking work_mem limits ram used.  What are your
>>> non-default postgresql.conf settings?
>>
>> work_mem limits memory usage _per_sort_.
>>
>> A big query can easily have many sorts.  Each sort will be limited to
>> work_mem memory usage, but the total could be much higher.
>>
>> The only way I can think is to set a per-process limit in the OS and allow
>> the OS to kill a process when it gets out of hand.  Not ideal, though.
>
> True, but with a work_mem of 2M, I can't imagine having enough sorting
> going on to need 4G of ram.  (2000 sorts? That's a lot)  I'm betting
> the OP was looking at top and misunderstanding what the numbers mean,
> which is pretty common really.

Note in followup, the danger is when pgsql looks at a hashagg subplan,
and thinks "that'll fit in work_mem" and goes ahead but in reality it
needs 1,000 times or more work_mem for such a plan, and exhausts
memory. But to believe that's happening, I'll need to see what the OP
saw to convince him it was happening. It's not unheard of, but it's
not that common either.