Quick Links

Re: User concurrency thresholding: where do I look?

From:	"Jignesh K(dot) Shah" <J(dot)K(dot)Shah(at)Sun(dot)COM>
To:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	Simon Riggs <simon(at)2ndquadrant(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject:	Re: User concurrency thresholding: where do I look?
Date:	2007-07-25 22:58:53
Message-ID:	46A7D5AD.9000706@sun.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

Here is how I got the numbers..
I had about 1600 users login into postgresql. Then started the run with
500 users and using DTrace I started tracking Postgresql Locking "as
viewed from one user/connection". Echo statements indicate how many
users were active at that point and how was throughput performing. All
IO is done on /tmp which means on a RAM disk.

bash-3.00# echo 500 users - baseline number
500 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
FirstLockMgrLock Exclusive 1
RelCacheInitLock Exclusive 2
SInvalLock Exclusive 2
WALInsertLock Exclusive 10
BufMappingLock Exclusive 12
CheckpointLock Shared 29
CheckpointStartLock Shared 29
OidGenLock Exclusive 29
XidGenLock Exclusive 29
FirstLockMgrLock Shared 33
CheckpointStartLock Exclusive 78
FreeSpaceLock Exclusive 114
OidGenLock Shared 126
XidGenLock Shared 152
ProcArrayLock Shared 482

Lock Id Combined Time (ns)
SInvalLock 29800
RelCacheInitLock 30300
BufMappingLock 168800
FirstLockMgrLock 414300
FreeSpaceLock 1281700
ProcArrayLock 7869900
WALInsertLock 11113200
CheckpointStartLock 13494700
OidGenLock 25719100
XidGenLock 26443300
CheckpointLock 194267800

bash-3.00# echo 600 users - Throughput rising
600 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
RelCacheInitLock Exclusive 1
SInvalLock Exclusive 1
BufMappingLock Exclusive 2
CLogControlLock Exclusive 2
WALInsertLock Exclusive 11
FirstLockMgrLock Shared 20
CheckpointLock Shared 24
CheckpointStartLock Shared 24
OidGenLock Exclusive 24
XidGenLock Exclusive 24
CheckpointStartLock Exclusive 72
FreeSpaceLock Exclusive 102
OidGenLock Shared 106
XidGenLock Shared 128
ProcArrayLock Shared 394

Lock Id Combined Time (ns)
SInvalLock 15600
RelCacheInitLock 15700
BufMappingLock 31000
CLogControlLock 41500
FirstLockMgrLock 289000
FreeSpaceLock 3045400
CheckpointStartLock 7371800
WALInsertLock 9383200
ProcArrayLock 10457900
OidGenLock 20005900
XidGenLock 20331500
CheckpointLock 187067900

bash-3.00# echo 700 users - Throughput rising
700 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
RelCacheInitLock Exclusive 1
SInvalLock Exclusive 1
BufMappingLock Exclusive 2
WALInsertLock Exclusive 17
CheckpointLock Shared 33
CheckpointStartLock Shared 33
OidGenLock Exclusive 33
XidGenLock Exclusive 33
FirstLockMgrLock Shared 81
CheckpointStartLock Exclusive 87
FreeSpaceLock Exclusive 124
OidGenLock Shared 125
XidGenLock Shared 150
ProcArrayLock Shared 500

Lock Id Combined Time (ns)
RelCacheInitLock 15100
SInvalLock 15400
BufMappingLock 47400
FirstLockMgrLock 3021000
FreeSpaceLock 3794300
WALInsertLock 7567300
XidGenLock 18427400
ProcArrayLock 20884000
CheckpointStartLock 24084900
OidGenLock 26399500
CheckpointLock 256549800

bash-3.00# echo 800 users - Throughput rising
800 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
BufMappingLock Exclusive 1
RelCacheInitLock Exclusive 1
SInvalLock Exclusive 1
WALWriteLock Exclusive 1
WALInsertLock Exclusive 11
CheckpointLock Shared 27
CheckpointStartLock Shared 27
OidGenLock Exclusive 27
XidGenLock Exclusive 27
FirstLockMgrLock Shared 32
CheckpointStartLock Exclusive 73
FreeSpaceLock Exclusive 110
OidGenLock Shared 118
XidGenLock Shared 140
ProcArrayLock Shared 442

Lock Id Combined Time (ns)
WALWriteLock 13900
SInvalLock 15000
RelCacheInitLock 15500
BufMappingLock 18600
FirstLockMgrLock 391100
WALInsertLock 3953700
FreeSpaceLock 4801300
CheckpointStartLock 13131800
ProcArrayLock 14480500
OidGenLock 17736500
XidGenLock 21723100
CheckpointLock 206423500

bash-3.00# echo 850 users - SLIGHT DROP in throughput
850 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
FirstLockMgrLock Exclusive 1
RelCacheInitLock Exclusive 1
SInvalLock Exclusive 1
WALWriteLock Exclusive 1
BufMappingLock Exclusive 3
WALInsertLock Exclusive 7
CheckpointLock Shared 39
CheckpointStartLock Shared 39
OidGenLock Exclusive 39
XidGenLock Exclusive 39
FirstLockMgrLock Shared 47
CheckpointStartLock Exclusive 113
FreeSpaceLock Exclusive 152
OidGenLock Shared 162
XidGenLock Shared 194
ProcArrayLock Shared 621

Lock Id Combined Time (ns)
WALWriteLock 14200
RelCacheInitLock 15100
SInvalLock 15600
BufMappingLock 64100
WALInsertLock 2073200
FirstLockMgrLock 3040300
FreeSpaceLock 7329500
OidGenLock 21619100
CheckpointStartLock 23261300
ProcArrayLock 23917500
XidGenLock 24873100
CheckpointLock 309221200

bash-3.00# echo 900 users - ANOTHER SLIGHT DROP IN THROUGPUT
900 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
WALWriteLock Exclusive 1
WALInsertLock Exclusive 7
CheckpointStartLock Shared 13
OidGenLock Exclusive 13
CheckpointLock Shared 14
XidGenLock Exclusive 14
FirstLockMgrLock Shared 15
FreeSpaceLock Exclusive 51
OidGenLock Shared 51
XidGenLock Shared 62
CheckpointStartLock Exclusive 170
ProcArrayLock Shared 202

Lock Id Combined Time (ns)
WALWriteLock 16800
FirstLockMgrLock 170300
FreeSpaceLock 601500
ProcArrayLock 3971300
WALInsertLock 7757200
OidGenLock 8261900
XidGenLock 18450900
CheckpointStartLock 39155100
CheckpointLock 143751500

bash-3.00# echo 950 users - BIG DROP IN THROUGHPUT
950 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
WALInsertLock Exclusive 3
FirstLockMgrLock Shared 4
CheckpointLock Shared 7
CheckpointStartLock Shared 7
OidGenLock Exclusive 7
XidGenLock Exclusive 7
FreeSpaceLock Exclusive 29
OidGenLock Shared 30
XidGenLock Shared 36
ProcArrayLock Shared 115
CheckpointStartLock Exclusive 134

Lock Id Combined Time (ns)
FirstLockMgrLock 64400
FreeSpaceLock 342300
WALInsertLock 1759600
OidGenLock 4276900
ProcArrayLock 6234300
XidGenLock 6865000
CheckpointStartLock 37590800
CheckpointLock 58994300

bash-3.00# echo 1000 users - STEADY AT PREVIOUS LOW VALUE
1000 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
BufMappingLock Exclusive 1
RelCacheInitLock Exclusive 1
SInvalLock Exclusive 1
WALInsertLock Exclusive 3
CheckpointLock Shared 9
CheckpointStartLock Shared 9
OidGenLock Exclusive 9
XidGenLock Exclusive 9
FirstLockMgrLock Shared 14
FreeSpaceLock Exclusive 33
OidGenLock Shared 37
XidGenLock Shared 44
CheckpointStartLock Exclusive 122
ProcArrayLock Shared 145

Lock Id Combined Time (ns)
RelCacheInitLock 14300
SInvalLock 15600
BufMappingLock 21400
FirstLockMgrLock 184000
FreeSpaceLock 366200
WALInsertLock 1769500
ProcArrayLock 5076500
XidGenLock 5898400
OidGenLock 9244800
CheckpointStartLock 31077500
CheckpointLock 91861900

bash-3.00# echo 1050 users - SMALL INCREASE
1050 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
BufMappingLock Exclusive 2
WALInsertLock Exclusive 9
CheckpointLock Shared 24
XidGenLock Exclusive 24
CheckpointStartLock Shared 25
OidGenLock Exclusive 25
FirstLockMgrLock Shared 30
FreeSpaceLock Exclusive 100
OidGenLock Shared 107
XidGenLock Shared 129
CheckpointStartLock Exclusive 153
ProcArrayLock Shared 400

Lock Id Combined Time (ns)
BufMappingLock 36600
FirstLockMgrLock 420600
FreeSpaceLock 2998400
WALInsertLock 3818300
ProcArrayLock 8986900
OidGenLock 18127200
XidGenLock 18569200
CheckpointStartLock 44795700
CheckpointLock 206488400

bash-3.00# echo 1100 users - SMALL DROP AGAIN
1100 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
BufMappingLock Exclusive 1
WALInsertLock Exclusive 6
CheckpointLock Shared 11
XidGenLock Exclusive 11
CheckpointStartLock Shared 12
OidGenLock Exclusive 12
FirstLockMgrLock Shared 24
FreeSpaceLock Exclusive 39
OidGenLock Shared 44
XidGenLock Shared 51
CheckpointStartLock Exclusive 88
ProcArrayLock Shared 171

Lock Id Combined Time (ns)
BufMappingLock 19500
FirstLockMgrLock 302700
FreeSpaceLock 511200
ProcArrayLock 5042300
WALInsertLock 5592800
CheckpointStartLock 25009900
OidGenLock 25231600
XidGenLock 108045300
CheckpointLock 379734000

bash-3.00# echo 1150 users - STEADY AT LOW VALUE
1150 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
WALWriteLock Exclusive 1
WALInsertLock Exclusive 2
CheckpointLock Shared 5
CheckpointStartLock Shared 6
OidGenLock Exclusive 6
XidGenLock Exclusive 6
FirstLockMgrLock Shared 8
FreeSpaceLock Exclusive 21
OidGenLock Shared 26
XidGenLock Shared 31
ProcArrayLock Shared 93
CheckpointStartLock Exclusive 122

Lock Id Combined Time (ns)
WALWriteLock 14900
WALInsertLock 116900
FirstLockMgrLock 120600
FreeSpaceLock 2177800
XidGenLock 4899200
ProcArrayLock 20721700
CheckpointStartLock 27805200
CheckpointLock 76369300
OidGenLock 470145800

bash-3.00# echo 1250 users - STEADY AT LOW VALUE
1250 users
bash-3.00# ./3_lwlock_acquires.d 19178

Lock Id Mode Count
CheckpointLock Shared 2
CheckpointStartLock Shared 2
OidGenLock Exclusive 2
WALInsertLock Exclusive 2
XidGenLock Exclusive 2
FreeSpaceLock Exclusive 9
OidGenLock Shared 10
XidGenLock Shared 12
ProcArrayLock Shared 36
CheckpointStartLock Exclusive 135

Lock Id Combined Time (ns)
WALInsertLock 39500
FreeSpaceLock 98600
ProcArrayLock 318800
XidGenLock 1379900
OidGenLock 3437700
CheckpointLock 9565200
CheckpointStartLock 56547900

bash-3.00#

In response to

Re: User concurrency thresholding: where do I look? at 2007-07-23 18:19:20 from Tom Lane

Responses

Re: User concurrency thresholding: where do I look? at 2007-07-25 23:24:25 from Tom Lane

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Tom Lane	2007-07-25 23:24:25	Re: User concurrency thresholding: where do I look?
Previous Message	Karl Denninger	2007-07-25 21:01:28	Re: Performance issue with 8.2.3 - "C" application