Re: Out of memory on SELECT in 8.3.5

* Scott Marlowe (scott(dot)marlowe(at)gmail(dot)com) wrote:
> 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.

Reducing work_mem solved the problem by reducing the memory used, sure.
In the end, however, we actually want to make the other 14G or so in
the box useful for something, not just try to fit everything in under
that 10G committed limit. PG allocates the memory it needs, it doesn't
just allocate whatever work_mem is set to (in fact, I don't believe
work_mem is ever directly used in allocations).

I'm not against looking at lowering work_mem to something else, but you
don't want to suddenly get shifted over to disk-based sorts or similar
when your data set grows too big if there's a bunch of unused memory in
the box.

Thanks,

Stephen