From: henk de wit <henk53602@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: select on 22 GB table causes "An I/O error occured while sending to the backend." exception
Date: Tue, 26 Aug 2008 22:29:48 +0200
Message-ID: <BAY124-W9F9CDFB5AC3C6CF98F408F5660@phx.gbl> (raw)
In-Reply-To: <1219770615.28149.96.camel@archimedes.mir3.com>
References: <BAY124-W57D3654AF1159CAF8EAC19F5660@phx.gbl>
<1219770615.28149.96.camel@archimedes.mir3.com>
> If the select returns a lot of data and you haven't enabled cursors (by
> calling setFetchSize), then the entire SQL response will be loaded in
> memory at once, so there could be an out-of-memory condition on the
> client.
I hear you. This is absolutely not the case though. There is no other exception anywhere besides the "An I/O error occured while sending to the backend.". The select query eventually returns something between 10 and 50 rows. Also, the select query runs from another machine than the one that issues the inserts to the data base. I failed to mention in the openings post that simultaneously with this select/insert query for this single 22 GB table, thousands if not tens of thousands other queries are hitting other tables in the same database. There are about 70 other tables, with a combined size of about 40 GB. None of those 70 others tables has a size anywhere near that 22GB of the problematic table. There is never even a single problem of this kind with any of those other tables.
When doing research on the net, it became clear that a lot of these "An I/O error..." exceptions are caused by malfunctioning switches or routers in the network between the application server(s) and the data base. In our case this can hardly be true. As mentioned, a great number of other queries are hitting the database. Some of these are very small (exeuction times of about 10 ms), while others are quite large (hundreds of lines of SQL with over 12 joins and an execution time of several minutes). Not a single one of those results in this I/O error.
> If could also be something else entirely; exceeding your max
> connections, something like that.
We indeed ran into that, but I think more as collateral damage. When this single select query for the 22 GB table is executing and those inserts start to fail, this also starts holding up things. As a results the 6 writes per second start queuing up and requesting more and more connections from our connection pool (DBCP as supplied by Apache Tomcat). We had the maximum of our connection pool set to a too high value and after a while Postgres responded with a message that the connection limit was exceeded. We thereafter lowered the max of our connection pool and didn't see that particular message anymore.
So, it seems likely that "An I/O error occured while sending to the backend." doet not mean "connection limit exceeded", since the latter message is explitely given when this is the case.
> A really good place to start would be to enable tracing on the JDBC
> driver.
That's a good idea indeed. I'll try to enable this and see what it does.
> If the issue is server-side, then you will also want to look at the
> PostgreSQL logs on the server; anything as serious as a backend aborting
> should write an entry in the log.
We studied the PG logs extensively but unfortunately could not really detect anything that could point to the problem there.
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
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: 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: <BAY124-W9F9CDFB5AC3C6CF98F408F5660@phx.gbl>
* 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