When running a test with multiple (>=2) users pg 'seizes up' after a
few transactions. Cpu goes 100% and disk goes to 0%. This lasts
'forever' (overnight)On the same test all other tested databases don't
have this problem.
The error occurs with higher tx rate, when transactions bump into each
other more frequently. Some 'deadlock detected' messages appear around
the hang up time, but _not_ always.
Occasionally - but rarely - the seizure looks differently. CPU goes to
0%, disk goes to 0% and, after about one minute, the processing is
resumed.
The current suspicion is that it is due to difference in lock and
deadlock handling between pg and most other dbs. In general I found
that I can't set transaction isolation level to READ_UNCOMMITTED (as
for other databases), the lowest level is READ_COMMITEED.
Any ideas how to reduce this problem? I really want to prove pg perf
with 20-100 users and have a problem running 2...
It wouldn't be fair (to other db's) to rewrite the test with some
pg-only LOCK command etc. I suspect I'm missing something simple, like
one of .conf parameters.
P.S. Using WinNT/Win2K system, pg 7.1.3 (current cygwin), jdbc driver
is jdbc7.1-1.3, cygipc is 1.10-1, java is 1.3.1_01a (current jdk).
Default pg installation, except for bumped up memory and 8 wal files.