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

Another approach we used successfully for a similar problem -- (we had lots
of free high memory but were running out of low memory; oom killer wiped out
MQ a couple times and postmaster a couple times) -- was to change the
settings for how aggressively the virtual memory system protected low memory
by changing /proc/sys/vm/lowmem_reserve_ratio (2.6.18?+ Kernel). I don't
remember all of the details, but we looked at
Documentation/filesystems/proc.txt for the 2.6.25 kernel (it wasn't
documented for earlier kernel releases) to figure out how it worked and set
it appropriate to our system memory configuration.

-Jerry

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Steve Atkins
Sent: Thursday, August 28, 2008 9:06 AM
To: PostgreSQL Performance
Subject: Re: [PERFORM] select on 22 GB table causes "An I/O error occured
while sending to the backend." exception

On Aug 28, 2008, at 6:26 AM, Matthew Wakeling wrote:

> On Wed, 27 Aug 2008, david(at)lang(dot)hm wrote:
>> if memory overcommit is disabled, the kernel checks to see if you
>> have an extra 1G of ram available, if you do it allows the process
>> to continue, if you don't it tries to free memory (by throwing away
>> cache, swapping to disk, etc), and if it can't free the memory will
>> return a memroy allocation error (which I believe will cause
>> firefox to exit).
>
> Remember that the memory overcommit check is checking against the
> amount of RAM + swap you have - not just the amount of RAM. When a
> fork occurs, hardly any extra actual RAM is used (due to copy on
> write), but the potential is there for the process to use it. If
> overcommit is switched off, then you just need to make sure there is
> *plenty* of swap to convince the kernel that it can actually fulfil
> all of the memory requests if all the processes behave badly and all
> shared pages become unshared. Then the consequences of processes
> actually using that memory are that the machine will swap, rather
> than the OOM killer having to act.
>
> Of course, it's generally bad to run a machine with more going on
> than will fit in RAM.
>
> Neither swapping nor OOM killing are particularly good - it's just a
> consequence of the amount of memory needed being unpredictable.
>
> Probably the best solution is to just tell the kernel somehow to
> never kill the postmaster.

Or configure adequate swap space?

Cheers,
Steve