From: Scott Marlowe <scott.marlowe@gmail.com>
To: david@lang.hm
Cc: Andrew Sullivan <ajs@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception
Date: Wed, 27 Aug 2008 16:28:56 -0600
Message-ID: <dcc563d10808271528x3fb78aa3r87f42765591b05b0@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.1.10.0808271516300.907@asgard.lang.hm>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, david@lang.hm, ajs@commandprompt.com
Subject: Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception
In-Reply-To: <dcc563d10808271528x3fb78aa3r87f42765591b05b0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox