Re: Out of memory on SELECT in 8.3.5

On Mon, Feb 9, 2009 at 1:25 PM, Matt Magoffin <postgresql.org@msqr.us> wrote:
>> * Matt Magoffin (postgresql.org@msqr.us) wrote:
>>> [root@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.