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

On Wed, Aug 27, 2008 at 4:22 PM, <david(at)lang(dot)hm> wrote:
> 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

I don't generally find this to be the case because I usually allocate
about 20-25% of memory to shared_buffers, use another 10-20% for
work_mem across all backends, and let the OS cache with the other
50-60% or so of memory. In this situation allocations rarely, if
ever, fail.

Note I also turn swappiness to 0 or 1 or something small too.
Otherwise linux winds up swapping out seldom used shared_buffers to
make more kernel cache, which is counter productive in the extreme.