Re: pg locking problem - Mailing list pgsql-hackers

For various reasons, not wholly dependent on me, the test should show
good perf on Windows. Otherwise Sorry about that. Can't change the
platform for this one. I did scan the archives, without finding
anything similar - though maybe my search was not thorough enough.

I managed to isolate the bug further. 

1. Running with read-only transactions the bug does not occur. This
means that the bug is not directly related to the number of users (as
long as there's more than one).

2. Running with read-only transactions _and_ just _one_ type of a
read-write transaction the bug occurs. This means that the bug is not
caused by a deadlock - single transaction type always requests the
tables in the same order. (Am I right here? i'm sleepy so my thinking
is not up to scratch). Anyway, regardless which one of read-write tx
types is chosen, the problem occurs.

3. Overall this suggests that, in crude terms, the problem is
triggered when reading updated but uncommitted records. Possibly even
by one user reading updated uncommitted records of another (since this
happens with only two users)

4. The seizure problem manifests itself in high (100%) cpu
utilization. Also, about 80% of that cpu utilization is system state.
All pg processes (for all users) use about the same amount of cpu time
- that is the situation is not caused by one process/user getting out
of whack.


t-ishii@sra.co.jp (Tatsuo Ishii) wrote in message news:<20011114100112O.t-ishii@sra.co.jp>...
> > 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...
> 
> Have you ever tried the test on UNIX (or UNIX like systems)? I have
> never seen such a problem with PostgreSQL running on UNIX. Also I
> think PostgreSQL on Win is not ready for practical use...
> 
> > 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.
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)