Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception

On Wed, 27 Aug 2008, Andrew Sullivan wrote:

> On Wed, Aug 27, 2008 at 02:45:47PM -0700, david(at)lang(dot)hm wrote:
>
>> with memory overcommit enabled (the default), the kernel recognises that
>> most programs that fork don't write to all the memory they have
>> allocated,
>
> It doesn't "recognise" it; it "hopes" it. It happens to hope
> correctly in many cases, because you're quite right that many programs
> don't actually need all the memory they allocate. But there's nothing
> about the allocation that hints, "By the way, I'm not really planning
> to use this." Also. . .

Ok, I was meaning to say "recognises the fact that a common pattern is to
not use the memory, and so it..."

>> seperate copies for the seperate processes (and if at this time it runs of
>> of memory it invokes the OOM killer to free some space),
>
> . . .it kills processes that are using a lot of memory. Those are not
> necessarily the processes that are allocating memory they don't need.

the bahavior of the OOM killer has changed over time, so far nobody has
been able to come up with a 'better' strategy for it to follow.

> The upshot of this is that postgres tends to be a big target for the
> OOM killer, with seriously bad effects to your database. So for good
> Postgres operation, you want to run on a machine with the OOM killer
> disabled.

I disagree with you. I think goof Postgres operation is so highly
dependant on caching as much data as possible that disabling overcommit
(and throwing away a lot of memory that could be used for cache) is a
solution that's as bad or worse than the problem it's trying to solve.

I find that addign a modest amount of swap to the system and leaving
overcommit enabled works better for me, if the system starts swapping I
have a chance of noticing and taking action, but it will ride out small
overloads. but the biggest thing is that it's not that much more
acceptable for me to have other programs on the box failing due to memory
allocation errors, and those will be much more common with overcommit
disabled then the OOM killer would be with it enabled

David Lang