DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=dPWTMwOq7Fh2xPn7kz/HOeRxCRGsNiYWGxhDKyiWZmFLurm9w58ODQNTL1ifS/qJ61
	i9QT7oDLiSmKjLoubIasatPZGBVd/eqzXqMJHaBf1X4d6HZP2FY5E55W+bpT4psIefYJ
	8yP5HgpoP3zaW1s8YTpD4KXRhm2bEB5U9YFWo=
Message-ID: <dcc563d10808271528x3fb78aa3r87f42765591b05b0@mail.gmail.com>
Date: Wed, 27 Aug 2008 16:28:56 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: david@lang.hm
Subject: Re: select on 22 GB table causes "An I/O error occured while sending
	to the backend." exception
Cc: "Andrew Sullivan" <ajs@commandprompt.com>,
 pgsql-performance@postgresql.org
In-Reply-To: <alpine.DEB.1.10.0808271516300.907@asgard.lang.hm>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <BAY124-W57D3654AF1159CAF8EAC19F5660@phx.gbl>
	<82bpze3ou8.fsf@mid.bfk.de>
	<alpine.DEB.1.10.0808271436210.907@asgard.lang.hm>
	<20080827221338.GP18946@commandprompt.com>
	<alpine.DEB.1.10.0808271516300.907@asgard.lang.hm>

On Wed, Aug 27, 2008 at 4:22 PM,  <david@lang.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.