Re: Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:32 PM, Matt Magoffin <postgresql(dot)org(at)msqr(dot)us> wrote:
>> I suspect this may be it... Apparently, while you're only using about
>> 2G, you've got 10G or so of outstanding commitments, and Linux is
>> refusing to allocate more.
>>
>> You probably want to up your overcommit_ratio, esp. in light of the fact
>> that you've only got 2G of swap on this box. I'd probably say up it to
>> 80, which would give you 14.8G of commitable memory, leaving some room
>> in-memory (1.2G) for cache/buffers and whatnot. Alternativly, you could
>> go for 90, which would allow commits up to 16.4G, so if everyone used
>> all their memory, you'd be into swap.
>
> Also, by adjusting this, would I possibly just be delaying the problem we
> currently have (i.e. over time, we start to run out of memory)? I just
> wonder why the system is reaching this limit at all... do you feel it is
> quite normal for a system with this memory configuration to be configured
> with the ratio set to 80? I'm not terribly familiar with these VM
> parameters, so I apologize if I sound vague.

I think that you're fixing a symptom, but ignoring the cause.
Twiddling VM parameters may help out, but this problem of too much
memory allocated is the real issue, so yeah, you're just putting off
the inevitable.

Test your individual queries with varying settings for work_mem. Find
the weighted-averaged knee of diminishing returns and set work_mem
closer to that. I'm willing to be it's way less than 128M. 2M to 16M
are the numbers I come up with the most.