From: Jan de Visser <jdevisser@digitalfairway.com>
To: pgsql-performance@postgresql.org
Subject: Re: Hanging queries on dual CPU windows
Date: Thu, 9 Mar 2006 15:07:04 -0500
User-Agent: KMail/1.9.1
References: <200603060938.00751.jdevisser@digitalfairway.com>
In-Reply-To: <200603060938.00751.jdevisser@digitalfairway.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200603091507.04663.jdevisser@digitalfairway.com>

I have more information on this issue.

=46irst of, the problem now happens after about 1-2 hours, as opposed to th=
e 6-8=20
I mentioned earlier. Yey for shorter test cycles.

=46urtermore, it does not happen on Linux machines, both single CPU and dua=
l=20
CPU, nor on single CPU windows machines. We can only reproduce on a dual CP=
U=20
windows machine, and if we take one CPU out, it does not happen.

I executed the following after it hung:

db=3D# select l.pid, c.relname, l.mode, l.granted, l.page, l.tuple=20
from pg_locks l, pg_class c where c.oid =3D l.relation order by l.pid;

Which showed me that several transactions where waiting for a particular ro=
w=20
which was locked by another transaction. This transaction had no pending=20
locks (so no deadlock), but just does not complete and hence never=20
relinquishes the lock.

What gives? has anybody ever heard of problems like this on dual CPU window=
s=20
machines?

jan


On Monday 06 March 2006 09:38, Jan de Visser wrote:
> Hello,
>
> While doing performance tests on Windows Server 2003 we observed to
> following two problems.
>
> Environment: J2EE application running in JBoss application server, against
> pgsql 8.1 database. Load is caused by a smallish number of (very) complex
> transactions, typically about 5-10 concurrently.
>
> The first one, which bothers me the most, is that after about 6-8 hours t=
he
> application stops processing. No errors are reported, neither by the JDBC
> driver nor by the server, but when I kill the application server, I see
> that all my connections hang in a SQL statements (which never seem to
> return):
>
> 2006-03-03 08:17:12 4504 6632560 LOG: =A0duration: 45087000.000 ms
> =A0statement: EXECUTE <unnamed> =A0[PREPARE: =A0SELECT objID FROM objects=
 WHERE
> objID =3D $1 FOR UPDATE]
>
> I think I can reliably reproduce this by loading the app, and waiting a
> couple of hours.

=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-------------------------------------------------------------