Re: Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:25 PM, Matt Magoffin <postgresql(dot)org(at)msqr(dot)us> wrote:
>> * Matt Magoffin (postgresql(dot)org(at)msqr(dot)us) wrote:
>>> [root(at)170226-db7 ~]# cat /proc/meminfo
>>> CommitLimit: 10312588 kB
>>> Committed_AS: 9760756 kB
>>
>> 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.
>
> Thanks for the advice. Should we have more than 2GB of swap available? I
> thought the goal for a Postgres system was to avoid swap use at all cost?
> Would it be better for us to add more swap, or adjust this
> overcommit_ratio as you discuss?

I'd do both. But only after I'd reduced work_mem. Given that
reducing work_mem removed the problem, it looks to me like pgsql is
requesting several large blocks of ram, then only using a small port
of them. But overcommit set to 2 means that the OS will not allow an
overcommit of memory to these allocations, the allocations fail, and
you get your error.