Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 318C0650295 for ; Tue, 26 Aug 2008 17:31:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96937-08 for ; Tue, 26 Aug 2008 17:31:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc2-s16.bay0.hotmail.com (bay0-omc2-s16.bay0.hotmail.com [65.54.246.152]) by postgresql.org (Postfix) with ESMTP id 1AA4F65028F for ; Tue, 26 Aug 2008 17:31:22 -0300 (ADT) Received: from BAY124-W9 ([207.46.11.172]) by bay0-omc2-s16.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 26 Aug 2008 13:29:48 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_6b12bcf3-8c4c-4432-aef0-6da6482ce886_" X-Originating-IP: [86.81.58.36] From: henk de wit To: 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 Importance: Normal In-Reply-To: <1219770615.28149.96.camel@archimedes.mir3.com> References: <1219770615.28149.96.camel@archimedes.mir3.com> MIME-Version: 1.0 X-OriginalArrivalTime: 26 Aug 2008 20:29:48.0709 (UTC) FILETIME=[7C46B550:01C907BA] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/340 X-Sequence-Number: 31221 --_6b12bcf3-8c4c-4432-aef0-6da6482ce886_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > If the select returns a lot of data and you haven't enabled cursors (by > calling setFetchSize)=2C then the entire SQL response will be loaded in > memory at once=2C 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 excep= tion anywhere besides the "An I/O error occured while sending to the backen= d.". The select query eventually returns something between 10 and 50 rows. = Also=2C 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 tha= t simultaneously with this select/insert query for this single 22 GB table= =2C thousands if not tens of thousands other queries are hitting other tabl= es in the same database. There are about 70 other tables=2C with a combined= size of about 40 GB. None of those 70 others tables has a size anywhere ne= ar 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=2C 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 ca= se this can hardly be true. As mentioned=2C a great number of other queries= are hitting the database. Some of these are very small (exeuction times of= about 10 ms)=2C while others are quite large (hundreds of lines of SQL wit= h 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=3B exceeding your max > connections=2C something like that. We indeed ran into that=2C but I think more as collateral damage. When this= single select query for the 22 GB table is executing and those inserts sta= rt to fail=2C 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 thereaf= ter lowered the max of our connection pool and didn't see that particular m= essage anymore. So=2C it seems likely that "An I/O error occured while sending to the backe= nd." doet not mean "connection limit exceeded"=2C 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=2C then you will also want to look at the > PostgreSQL logs on the server=3B anything as serious as a backend abortin= g > should write an entry in the log. We studied the PG logs extensively but unfortunately could not really detec= t 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/= --_6b12bcf3-8c4c-4432-aef0-6da6482ce886_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >=3B If the select returns a lot of data and yo= u haven't enabled cursors (by
>=3B calling setFetchSize)=2C then the e= ntire SQL response will be loaded in
>=3B memory at once=2C so there c= ould be an out-of-memory condition on the
>=3B client.

I hear y= ou. This is absolutely not the case though. There is no other exception any= where besides the "An I/O error occured while sending to the backend.". The= select query eventually returns something between 10 and 50 rows. Also=2C = the select query runs from another machine than the one that issues the ins= erts to the data base. I failed to mention in the openings post that simult= aneously with this select/insert query for this single 22 GB table=2C thous= ands if not tens of thousands other queries are hitting other tables in the= same database. There are about 70 other tables=2C with a combined size of = about 40 GB. None of those 70 others tables has a size anywhere near that 2= 2GB 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= =2C it became clear that a lot of these "An I/O error..." exceptions are ca= used by malfunctioning switches or routers in the network between the appli= cation server(s) and the data base. In our case this can hardly be true. As= mentioned=2C a great number of other queries are hitting the database. Som= e of these are very small (exeuction times of about 10 ms)=2C while others = are quite large (hundreds of lines of SQL with over 12 joins and an executi= on time of several minutes). Not a single one of those results in this I/O = error.

>=3B If could also be something else entirely=3B exceeding = your max
>=3B connections=2C something like that.

We indeed ran= into that=2C but I think more as collateral damage. When this single selec= t query for the 22 GB table is executing and those inserts start to fail=2C= this also starts holding up things. As a results the 6 writes per second s= tart queuing up and requesting more and more connections from our connectio= n pool (DBCP as supplied by Apache Tomcat). We had the maximum of our conne= ction pool set to a too high value and after a while Postgres responded wit= h a message that the connection limit was exceeded. We thereafter lowered t= he max of our connection pool and didn't see that particular message anymor= e.

So=2C it seems likely that "An I/O error occured while sending to= the backend." doet not mean "connection limit exceeded"=2C since the latte= r message is explitely given when this is the case.

>=3B A really = good place to start would be to enable tracing on the JDBC
>=3B driver= .

That's a good idea indeed. I'll try to enable this and see what it= does.

>=3B If the issue is server-side=2C then you will also want= to look at the
>=3B PostgreSQL logs on the server=3B anything as seri= ous as a backend aborting
>=3B should write an entry in the log.
We studied the PG logs extensively but unfortunately could not really det= ect anything that could point to the problem there.


Expr= ess yourself instantly with MSN Messenger! MSN Messenger = --_6b12bcf3-8c4c-4432-aef0-6da6482ce886_--