I have more information on this issue.
First of, the problem now happens after about 1-2 hours, as opposed to the 6-8
I mentioned earlier. Yey for shorter test cycles.
Furtermore, it does not happen on Linux machines, both single CPU and dual
CPU, nor on single CPU windows machines. We can only reproduce on a dual CPU
windows machine, and if we take one CPU out, it does not happen.
I executed the following after it hung:
db=# select l.pid, c.relname, l.mode, l.granted, l.page, l.tuple
from pg_locks l, pg_class c where c.oid = l.relation order by l.pid;
Which showed me that several transactions where waiting for a particular row
which was locked by another transaction. This transaction had no pending
locks (so no deadlock), but just does not complete and hence never
relinquishes the lock.
What gives? has anybody ever heard of problems like this on dual CPU windows
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 the
> 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: duration: 45087000.000 ms
> statement: EXECUTE <unnamed> [PREPARE: SELECT objID FROM objects WHERE
> objID = $1 FOR UPDATE]
>
> I think I can reliably reproduce this by loading the app, and waiting a
> couple of hours.
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------