From: Jan de Visser <jdevisser@digitalfairway.com>
To: pgsql-performance@postgresql.org
Subject: Hanging queries and I/O exceptions
User-Agent: KMail/1.9.1
MIME-Version: 1.0
Content-Disposition: inline
Date: Mon, 6 Mar 2006 09:38:00 -0500
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200603060938.00751.jdevisser@digitalfairway.com>

Hello,

While doing performance tests on Windows Server 2003 we observed to followi=
ng=20
two problems.

Environment: J2EE application running in JBoss application server, against=
=20
pgsql 8.1 database. Load is caused by a smallish number of (very) complex=20
transactions, typically about 5-10 concurrently.

The first one, which bothers me the most, is that after about 6-8 hours the=
=20
application stops processing. No errors are reported, neither by the JDBC=20
driver nor by the server, but when I kill the application server, I see tha=
t=20
all my connections hang in a SQL statements (which never seem to return):

2006-03-03 08:17:12 4504 6632560 LOG:  duration: 45087000.000 ms  statement=
:=20
EXECUTE <unnamed>  [PREPARE:  SELECT objID FROM objects WHERE objID =3D $1 =
=46OR=20
UPDATE]

I think I can reliably reproduce this by loading the app, and waiting a cou=
ple=20
of hours.


The second problem is less predictable:

JDBC exception:

An I/O error occured while sending to the backend.
org.postgresql.util.PSQLException: An I/O error occured while sending to th=
e=20
backend.
        at=20
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214)
        at=20
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.=
java:430)
        at=20
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S=
tatement.java:346)
        at=20
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2State=
ment.java:250)


In my server log, I have:

2006-03-02 12:31:02 5692 6436342 LOG:  could not receive data from client: =
A=20
non-blocking socket operation could not be completed immediately.

At the time my box is fairly heavy loaded, but still responsive. Server and=
=20
JBoss appserver live on the same dual 2Ghz Opteron.

A quick Google told me that:

1. More people have seen this.
2. No solutions.
3. The server message appears to indicate an unhandled WSAEWOULDBLOCK winso=
ck=20
error on recv(), which MSDN said is to be expected and should be retried.

Is this a known bug?

jan=20


=2D-=20
=2D-------------------------------------------------------------
Jan de Visser =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 jdevisser@digitalfair=
way.com

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Baruk Khazad! Khazad ai-menu!
=2D-------------------------------------------------------------