From: Bill Moran <wmoran@collaborativefusion.com>
To: henk de wit <henk53602@hotmail.com>
Cc: pgsql-performance@postgresql.org <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 09:01:23 -0400
Message-ID: <20080827090123.c0e8e638.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <BAY124-W383ADB13D25B83F8B60E92F5660@phx.gbl>
References: <BAY124-W57D3654AF1159CAF8EAC19F5660@phx.gbl>
<dcc563d10808261123q57c8797bk77bc00eb949fc747@mail.gmail.com>
<BAY124-W383ADB13D25B83F8B60E92F5660@phx.gbl>
In response to henk de wit <henk53602@hotmail.com>:
> > What do your various logs (pgsql, application, etc...) have to say?
>
> There
> is hardly anything helpful in the pgsql log. The application log
> doesn't mention anything either. We log a great deal of information in
> our application, but there's nothing out of the ordinary there,
> although there's of course always a chance that somewhere we missed
> something.
There should be something in a log somewhere. Someone suggested the oom
killer might be getting you, if so there should be something in one of
the system logs.
If you can't find anything, then you need to beef up your logs. Try
increasing the amount of stuff that gets logged by PG by tweaking the
postgres.conf settings. Then run iostat, vmstat and top in an endless
loop dumping their output to files (recommend you run date(1) in between
each run, otherwise you can't correlate the output to the time of
occurrence ;)
While you've got all this extra logging going and you're waiting for the
problem to happen again, do an audit of your postgres.conf settings for
memory usage and see if they actually add up. How much RAM does the
system have? How much of it is free? How much of that are you eating
with shared_buffers? How much sort_mem did you tell PG it has? Have
you told PG that it has more memory than the machine actually has?
I've frequently recommended installing pg_buffercache and using mrtg
or something similar to graph various values from it and other easily
accessible statistics in PG and the operating system. The overhead of
collecting and graphing those values is minimal, and having the data
from those graphs can often be the little red arrow that points you to
the solution to problems like these. Not to mention the historical
data generally tells you months ahead of time when you're going to
need to scale up to bigger hardware.
On a side note, what version of PG are you using? If it was in a
previous email, I missed it.
Hope this helps.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
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: wmoran@collaborativefusion.com, henk53602@hotmail.com
Subject: Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception
In-Reply-To: <20080827090123.c0e8e638.wmoran@collaborativefusion.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