ProcArrayLock (The Saga continues)

Based on feedback after the sessions I did few more tests which might be
useful to share

One point that was suggested to get each clients do more work and reduce
the number of clients.. The igen benchmarks was flexible and what I did
was remove all think time from it and repeated the test till the
scalability stops (This was done with CVS downloaded yesterday)

Note with this no think time concept, each clients can be about 75% CPU
busy from what I observed. running it I found the clients scaling up
saturates at about 60 now (compared to 500 from the original test). The
peak throughput was at about 50 users (using synchrnous_commit=off)

Here is the interesting DTrace Lock Ouput state (lock id, mode of lock
and time in ns spent waiting for lock in a 10-sec snapshot (Just taking
the last few top ones in ascending order):

With less than 20 users it is WALInsert at the top:
52 Exclusive 721950129
4 Exclusive 768537190
46 Exclusive 842063837
7 Exclusive 1031851713

With 35 Users:
52 Exclusive 2599074739
4 Exclusive 2647927574
46 Exclusive 2789581991
7 Exclusive 3220008691

At the peak at about 50 users that I saw earlier (PEAK Throughput):
46 Exclusive 3669210393
4 Exclusive 6024966938
52 Exclusive 6529168107
7 Exclusive 9408290367

With about 60 users where the throughput actually starts to drop
(throughput drops)
41 Exclusive 4570660567
52 Exclusive 10706741643
46 Exclusive 13152005125
4 Exclusive 13550187806
7 Exclusive 22146882562

With about 100 users ( below the peak value)
42 Exclusive 4238582775
46 Exclusive 6773515243
7 Exclusive 7467346038
52 Exclusive 9846216440
4 Shared 22528501166
4 Exclusive 223043774037

So it seems when both shared and exclusive time for ProcArrayLock wait
are the top 2 it is basically saturated in terms of throughput it can
handle.

Optimizing wait queues will help improve shared which might help
Exclusive a bit but eventually Exclusive for ProcArray will limit
scaling with as few as 60-70 users.

Lock hold times are below (though taken from different run)
with 30 users:

Lock Id Mode Combined Time (ns)
1616992 Exclusive 1199791629
4 Exclusive 1399371867
34 Exclusive 1426153620
1616978 Exclusive 1528327035
1616990 Exclusive 1546374298
1616988 Exclusive 1553461559
5 Exclusive 2477558484

With 50+ users
Lock Id Mode Combined Time (ns)
4 Exclusive 1438509198
1616992 Exclusive 1450973466
1616978 Exclusive 1505626978
1616990 Exclusive 1850432217
1616988 Exclusive 2033226225
34 Exclusive 2098542547
5 Exclusive 3280151374

With 100 users

Lock Id Mode Combined Time (ns)
1616992 Exclusive 1206516505
1616988 Exclusive 1486704087
1616990 Exclusive 1521900997
34 Exclusive 1532815803
1616978 Exclusive 1541986895
5 Exclusive 2179043424
5 2395098279

(Why 5 was printing with blank??)
Rerunning it with slight variation of the script

Lock Id Mode Combined Time (ns)
1616996 0 1167708953
36 0 1291958451
5 4299305160 1344486968
4 0 1347557908
1616978 0 1377931882
34 0 1724752938
5 0 2079012548

Looks like trend of 4's hold time looks similar to previous ones..
though the new kid is 5 with mode <> 0,1 .. not sure if that is causing
problems..What mode is "4299305160" for Lock 5 (SInvalLock) ? Anyway at
this point the wait time for 4 increases to a point where the database
is not scaling anymore

any thoughts?

-Jignesh