Re: Out of memory on SELECT in 8.3.5

> * Matt Magoffin (postgresql(dot)org(at)msqr(dot)us) wrote:
>> Just running top, it does appear to chew through a fair amount of memory.
>> Here's a snapshot from top of the postgres processing running this query
>> from just before it ran out of memory:
>>
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 4486 postgres 18 0 4576m 3.6g 3.3g R 90 23.1 0:34.23 postgres:
>> postgres lms_nna [local] EXPLAIN
>>
>> These values did start out low, for example the RES memory started in the
>> 130MB range, then climbed to the 3.6GB you see here.

That is almost certainly meaningless; it just reflects the process
touching a larger and larger fraction of shared buffers over its
existence. The number to pay attention to is the non-shared memory size
(VIRT - SHR is probably the right number here).

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Uhh.. I saw that your system was 64-bit, but is your PG process
> compiled as 64bit? Maybe you're hitting an artificial 32-bit limit,
> which isn't exactly helped by your shared_buffers being set up so high
> to begin with? Run 'file' on your postgres binary, like so:

I think it must be compiled 64-bit, or he'd not be able to get
shared_buffers that high to start with. However, it's possible that the
postmaster's been started under a ulimit setting that constrains each
backend to just a few hundred meg of per-process memory.

regards, tom lane