A reason for waiting around Richard Banville Fellow Progress Software June 7 2011 Playing Fair and Being Consistent Why do I need to wait Shared resource synchronization Implementation restrictions ID: 144165
Download Presentation The PPT/PDF document "What are you waiting for?" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
What are you waiting for?
A reason for waiting around.
Richard Banville
Fellow, Progress Software
June 7, 2011Slide2
Playing Fair and Being Consistent
Why do I need to wait?Shared resource synchronization
Implementation restrictionsWait on only one resourceConcurrent waitsTimer interrupt (timeout processing)Screen/process interruptNetwork“table1 in use by user1 on p/97. Wait or press CTRL-C to stop
.”
Focus is on resource waits
How & why we waitSlide3
What can a user wait on
Database Resource
Internal resourceShared data structureBuffer lockTXE lockCheckpointBI/AI (buffer, I/O…)Transaction endUnique key insertionRecord lockDatabase extends
Page writers
Index operation
S
earch, split
Undo/rollbackLogin (semaphore)Schema changes (some)Quiet pointMaintenance activityBackupTable/index move/activateReplication (pica)Screen input (select)Network message (poll)System callsI/O (low level)Etc.
Some you can avoid; Some are out of your control.Slide4
Wait Mechanisms
LatchesUsed for
“short term” waitsWaiting user NOT visible (activity & waits are visible)Test, test-and-set (atomically), “nap”, try againSemaphoresUsed for “long term” waitsWaiting user visible via promon (VSTs)Often can be controlled at application levelExample: record lock durationSometimes notExample: I/O duration
Try, queue, wait for explicit wakeupSlide5
SemaphoresSlide6
Semaphores
Wait on semaphore summary
“Long term” wait synchronization mechanismAlways server sideUnix semaphore info:ipcs -sa-semsets: min 1, max 32,000, default 3Kernel control
SEMMNS
- max semaphores available system wide
SEMMNI
- max semaphore setsSEMMSL - max semaphores per setSemaphore usageReady semaphore – db startupLogin semaphore – connect/disconnectResource wait semaphore – one per connectionSlide7
Semaphores
What could I be waiting on?
Ready/Login semaphoreSchema lock waitRecord lock waitBuffer lock wait (db, ai, bi)Ordered wait/wake-up (fifo queue)Add to queueDecrement semaphoreOS suspend state
When resource available, semaphore “
ping’ed
”
OS activates process again
High semaphore waitsApplication contentionBuffer contention
Ready
Login
5 spares
-
semsets
(3)
User1
User3
User5
…
User2
User4
User6
…
#1
#2
#3
26 Connection
sems
= -n 20 + -
Mn
4 + 1 (broker) + 1 (
proshut
)Slide8
Promon R&D: Other Display
Wait on semaphore summary
High waits can indicate a potential for improvementIncrease resourcesAlter applicationImprove queriesAvoid full table scansAlter lock behaviorPhysical changes (Use TII storage areas)Direct connections onlyZero and monitorContrast with a more happy time
12/07/10 Activity: Other
Total Per Min Per Sec Per
Tx
Wait on semaphore 1545 1862 31.00 0.21Slide9
Performance Indicators (summary)
Total waits – Lock waits + Resource waits
12/07/10 Activity: Performance Indicators
Total Per Min Per Sec Per
Tx
Total waits 1558 1870 31.16 0.23
Lock waits 337 404 6.74 0.05
Resource waits 1221 1465 24.42 0.18
Latch timeouts 1625 1950 32.50 0.24
Lock waits (application)
Record lock
Schema lock
Transaction end (unique key insertion)
Latch timeouts
#times –spin exhausted
Resource waits (internal)
Row get lock
Buffered lock in shared memory (db,
ai
,
bi)
Buffer I/O (db,
ai
, bi)
Micro-transaction end lock
Latches (queue latches)Slide10
Promon: Other Display vs
Performance Indicators
Semaphore waits != Total waitsRace condition – stats not (always) latchedClient server record waits – no semaphore wait
Semaphore latch waits – no lock/resource wait
“DB Intent” waits counted twice – excl buffer wait
12/07/10 Activity: Other
Total Per Min Per Sec Per
Tx
Wait on semaphore 1545 1862 31.00 0.21
12/07/10 Activity: Performance Indicators
Total Per Min Per Sec Per
Tx
Total waits 1558 1870 31.16 0.23
Lock waits 337 404 6.74 0.05
Resource waits 1221 1465 24.42 0.18
Wait a minute.
Something doesn’t add up!Slide11
Promon: Other Display vs
Performance Indicators
12/07/10 Activity: Other Total Per Min Per Sec Per Tx
Wait on semaphore 1545 1862 31.00 0.21
Non-blocking waits 14 8 0.16 0.02
Semaphore latch waits 1 0 0.00 0.00
12/07/10 Activity: Performance Indicators
Total Per Min Per Sec Per
TxTotal waits 1558 1870 31.16 0.23
Lock waits 337 404 6.74 0.05
Resource waits 1221 1465 24.42 0.18
That’s better.
*
*Slide12
Wait IdentificationSlide13
Locks vs
Locks Waits
Locks from “Performance Indicators” optionBroken out by userCan indicate a performance issueRecord locks/waitsTransaction locks/waitsSchema locks/waits
12/07/10 Other: Performance Indicators:
Lock Requests By User
Usr
User --- Record --- ---- Trans --- --- Schema --- Name Locks Waits Locks Waits Locks Waits 0 richb 0 0 0 0 0 0
5 richb 12 0 1 0 0 0
6 richb 222 10 10 0 0 0 Slide14
Recovery Waits
Busy
buffer waits# of waits for exclusive BI buffer accessWait for other user to finish BI buffer update/writeOnly seen with page writers (due to MTX latch)Empty buffer waits# of waits because all buffers were
full
I
ncrease –
bibufs
and run with biwLog force waits# of waits for “commit notes” to be written to diskObsolete
12/07/10 Activity: BI Log Total Per Min Per Sec Per Tx
Busy buffer waits 10 2 0.02 0.20
Empty buffer waits 1 0 0.00 0.00
Log force waits 0 0 0.00 0.00Slide15
BI Buffer Pool – Recording a change
-
bibufs 10
free
- a
free
- b
free -
c
free
- d
free
- e
32
31
30
29
Modified Queue
Free List
BI
Current Output Buffer
PROMON:
Total BI Writes
Records (notes)
written
Partial Writes
Busy
buffer
waits
increase
bibufs
Empty
buffer
waits
increase
bibufs biw, apw
New Notes (Actions)
Forward
Processing - Slide16
Label Confusion
12/07/10 Status: Blocked Clients
Usr Name Type Wait Trans id
7 richb SELF/ABL TRAN 2053 2104
8 richb SELF/ABL REC 32768 2224
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait
Info Trans id
7 richb SELF/ABL TRAN 2053 2104
8 richb SELF/ABL REC 32768 2224
User Control:
Usr
Name Type Wait Table
Dbkey
Trans
0
dba
BROK
-
-
0 0 0
1 dba SERV -- 0 0 0 6 bob SELF/ABL REC 2 385 2108 24 joe REMC/ABL TRAN 0 2053 2261
R&D
Blocked
Clients
Display
Users
-- Wait Info --
Current
trid
Not blocking tridSlide17
Blocked Clients
12/07/10 Status: Blocked Clients
Usr Name Type Wait Wait Info Trans id
7 richb SELF/ABL TRAN 2222 2104
8 richb SELF/ABL TRAN 2104 2222
Deadlock detection:
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait Info Trans id 7 richb SELF/ABL REC 32226 2104
8 richb SELF/ABL REC 1924 2222
Need to look at multiple screens to figure out.
Internal protocol prevents this from happening.Slide18
Blocked Clients: Record Lock
REC – Waiting on a record lock
“Record Lock” (Activity lock description)Wait info: Record’s rowidNo table informationRecord lock wait in Lock Table option has more detailIncluding table informationEasy to identify if “hung”
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait Info Trans id 7 richb SELF/ABL REC 1282 2104Slide19
Record Lock Waits
Flags: Lock request (Table only)
Flags: Lock stateU – Upgrade, L - Limbo, Q - Queued, P - Purge, H – No HoldTran StateDead, Begin, Active, Prep, Phase 1, Phase 2Record lock queuing mechanism
12/07/10 Status: Lock Table
Usr
Name Trans ID Type
Rec-id Table Flags Tran State 6 richb 121759 REC 8199 2 X Begin
7 richb 121761 REC 8199 2 S QH Dead
Rowid
S – Shared
X – Exclusive
SX – Shared/Exclusive(?)
IS – Intent shared
IX – intent exclusive
SIX – SHR w/intent EXCL
X
SQ
SQ
X
Q
S
S
X
Q
S
QSlide20
Locking Code Examples
find first customer.
pause message "about to assign".assign name = "xxx“.
X U Q
S
find last customer.
assign
cust
-num = 39999.
release customer.
pause.
S
X
X L
find first customer exclusive-lock.
delete customer.
pause message “deleted!”.
P
X
Limbo Table lock
IX L
Limbo table lock: Serialization Isolation Cooperation
(since record lock(s) released)
Limbo record lock
Purge lock until
released internally
Record lock (‘til purged)
NOTE: Record locks released
(on conflict)
Deadlock Alert
(no conflict)Slide21
Lock Waits Statistics
Nice overall wait summary
Helps identify application performance issues*Table locks only12/07/10 Activity: Lock Table
Total Per Min Per Sec Per
Tx
Waits:
Share 59 2 0.04 0.01
*Intent Share 0 0 0.00 0.00
Exclusive 218 9 0.15 0.04
*Intent Exclusive 0 0 0.00 0.00
*Share Intent Excl 0 0 0.00 0.00
Upgrade 0 0 0.00 0.00
Record Get Lock 0 0 0.00 0.00
Table Lock 0 0 0.00 0.00
Record Lock 277 11 0.19 0.05Slide22
Blocked Clients: Record Get
RGET – Record “get lock”
"Record Get Lock"Wait info: Record’s rowidNo-lock wait
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait Info Trans id 7 richb SELF/ABL RGET 1282 2104
A no-lock record lock wait?
User 6
User 7
Modify/delete record
( buffer state change)
No-lock read a record Frag1
Must wait for user6
shm
buffer update to get consistent record.
New optimistic locking – only needed for fragmented records.
Read record fragment #2Slide23
Blocked Clients: Buffer Locks
BKSH & BKEX – Database buffer locks
Wait info: Block identifier (dbkey) of blockArea information would be useful“DB Buf S Lock” - OR – “DB Buf X
Lock”
12 (BKS2), 13 (BKX2)
“DB
Buf S/X Lock LRU2”Alternate buffer pool lockCatch all (sequence(192), object block, etc)
Record updates:Record lock first, then buffer lock“Rarely” lock multiple buffers – deadlock avoidance
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait
Info Trans id
7 richb SELF/ABL BKSH 1280 2104
8 richb SELF/ABL BKEX 192 2234
What’s a
dbkey
?Slide24
Record Locator (indicator, block #, row #)
Looking for ROWID 42562, Table 1, Area
7 w/64 RPB setting
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 0 1 1 0 0 1 0 0 0 0 1 0
Block
number: 1
to
2^25
Row
#: 0
to 63
0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0
0 0 0 0 0 0 0 0
Block
number: 1
to
whatever
Row #: 0
to
255 (based
on RPB)
Rowid
Consists of 3 parts (32 bit example)
0
Rowid
: 42562
(Row # included)
Block identifier (
dbkey
)
: 42560
(Row # as zeros)
0
0
0 0 1 00 0 0
0 0 0
0
0 0 0 1 0
Block # (in area): 665
Row #: 2Slide25
Online Backup Wait
Online “point in time”
backup - can result in BKEX waitsSlide26
Online Backup Wait
Online “point in time”
backup - can result in BKEX waits
User 1
BI
Area 6
Area 7
Area 8
…
Online Backup
.
bak
Backup
queue
Modify request
Modify request
Back it up and mark it
BFP
TXE
MTX
BFP
BKEX
Wait…
Wait on (TXE) semaphore during bi backup
Wait on BKEX during data backupSlide27
Blocked Clients: Read/Write I/O
DBRD
& DBWR – Database read/write“DB Buf Read” – OR – “DB Buf Write”Wait info: Block identifierDBW2 (14)Same as DBWR but for alternate buffer pool
BIRD
, BIWR,
AIRD, AIWR – Bi/Ai read/write
“BI
Buf Read” –OR – “BI Buf Write”Wait info: bi/ai block # (not particularly useful to you)Can use to determine if waiting on different block (not hung)
12/07/10 Status: Blocked Clients
Usr Name Type Wait Wait
Info Trans id
7 richb SELF/ABL DBRD 1280 2104Slide28
Buffer Pool Access
Block in
memory
BKSH & BKEX
vs
DBRD & DBWR
Updating
different
records in same block
Record read/update
BK
Buffer
lock
User3
Flush/overwrite for paging
Flush for performance
DB
Buffer
lock
DBRD
– reading in – all must wait!
DBWR
– Writing out, cannot be changed
BKSH
– In memory, read attempt
BKEX
– In memory, change attempt
Pretty
much same mechanism for all buffer pools (-B, -B2, -
bibuf
, -
aibuf)
APW
User1
User2
DBSlide29
Blocked Clients: Special Buffer Lock
DBSI – Intent to update buffer lock
“DB Buf I Lock”Wait info: Block identifier (dbkey) of conflictUsed by index manager when deleting or inserting entries“Safe” locking protocol
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait Info Trans id 7 richb SELF/ABL DBSI 1280 2104Slide30
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Insert an Index Key Entry
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
root
internal
leaf
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
records
Block’s DBKEY
Type
Chain
Backup Ctr
Next DBKEY in Chain
Block Update Counter
Top
Reserved
Free
Space
o o o
. . . Compressed Index Entries . . .
Bot
Index No.
Num Entries
Bytes Used
. . . Compressed Index Entries . . .
Dummy Entry . . .
DBSI buffer lock
“crabbing” mechanism
Release previous node
Increases concurrency
- Shared
- Shared
- DBSI
DBSI
(on split)Slide31
Blocked Clients: Buffer Availability
NOBF (11) – Wait for available buffer in -B
Wait statistic accumulatedActual wait never seen in “Blocked Client”Wait info: NoneShows up as “DB Buf Avail” in resource waitsWait mechanismSleep for a second, then retry
5 attempts then hard failure
Error message: “Not
enough database buffers (-B
)”
12/07/10 Status: Blocked Clients Usr
Name Type Wait Wait Info Trans id Slide32
Blocked Clients: Transaction End
TRAN – Wait for transaction to commit
“Trans Commit"Wait info: transaction Id waiting onCan occur when adding a unique index key entry 12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait
Info Trans id 7 richb SELF/ABL TRAN 2053 2104
Wait info is actually a trid
User 6
User 7
1. Begin transaction
2. Delete unique key abc@my.net (or insert)
3. Begin transaction
4. Insert unique key abc@my.net
Must wait for user6 to commit or rollback.Slide33
Blocked Clients: Micro-transaction
TXE - Micro-transaction wait
A micro-transaction?Wait info:1: Share – buffer read/write2: Update – multi-buffer change3: Commit – commit note & txn table sync4: Exclusive – Quiet point and backupNo commit TXE if multi-buffer update already outstanding
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait Info Trans id
7 richb SELF/ABL TXE 1 2104Slide34
Blocked Clients: Schema Lock
SCH – Schema lock
“Schema Lock"Values9 - Share lock request10 - Exclusive lock request 12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait
Info Trans id 7 richb SELF/ABL SCH 10 2104
User 6
User 7
Login – cache schema
Share schema lock
Change Schema (w/o online)
Exclusive schema lock
“Database in use by richb on pts/274. Wait or press CTRL-C to stop.”Slide35
Blocked Clients: Miscellaneous
SRPL – OpenEdge Site Replication
"Repl TEND Ack“Waiting for TEND ACK from a Sync Repl AgentSchema lock request
Wait info: none
.
DBSQ – Service Queue
"DBSQ Send
Lock“Database service queue message wait. User waiting to queue IPC message to database service queueWait info: none.STCA – Statement CacheStatement caching waitWait info: none.
12/07/10 Status: Blocked Clients
Usr Name Type Wait
Wait
Info Trans id
7 richb SELF/ABL SRPL 0 2104Slide36
Other Major WaitsSlide37
Other Major Waits: Checkpoint
Checkpoint – sync in memory changes
TXE – lockPrevents transaction commitMTX – latchPrevents db changesBIB – latchPrevents BIW writesWait not definitive; Latch waits not shown in “Blocked Clients”
12/07/10 Other: Checkpoints
Ckpt
------ Database Writes ------
No. Time Len Freq Dirty CPT Q Scan APW Q Flushes 2 09:34:57 317 0 170 0 0 0 0
1 09:14:19 1238 1238 272 0 0 0 72
12/07/10 Status: Blocked Clients
Usr
Name Type Wait
Wait
Info Trans id
7 richb SELF/ABL TXE 3 2104Slide38
Other Major Waits: Quietpoint
Quietpoint
Many wait examples – shared TXE share wait shown above.lg file – “Quiet point has been enabled by the broker”TXE – exclusive lockPrevents data access MTX – latchPrevents db changes
BIB/AIB – latch
Prevents BIW/AIW writes
12/07/10 Status: Blocked Clients
Usr Name Type Wait
Wait Info Trans id 7 richb SELF/ABL TXE 1 2104Slide39
Summary
Many different waits conditionsIdentifying waits
Help id performance bottlenecksUnderstanding wait conditionsHelp remove performance bottlenecksSlide40
Questions
?Slide41Slide42
Sept. 19 – 22, 2011
Boston Westin Waterfront Hotel
and Boston Convention & Exhibition CenterSlide43
Table Lock Types
Lock Type
Allow
Block
Intent Share (IS)
Row S
Table X
Intent Exclusive (IX)
Row X
Table X
Shared (S)
Row S
Row X
Shared with Intent Exclusive (SIX)
Row S, X
other Row X
Exclusive (X)
Row S, X
any other accessSlide44
Table Lock Compatibility
IS
IX
S
SIX
X
None
OK
OK
OK
OK
OK
IS
OK
OK
OK
- Q -
- Q -
IX
OK
OK
- Q -
- Q -
- Q -
S
OK
- Q -
OK
- Q -
- Q -
SIX
OK
- Q -
- Q -
- Q -
- Q -
X
- Q -
- Q -
- Q -
- Q -
- Q -
Lock Request Type
Other Active LocksSlide45
Progress Implementation
Share
Share
Record Lock
Share
Intent
Share
Intent
Share
None
Table Lock
Select
Exclusive
Share
Intent
Exclusive
Serializable
Exclusive
Intent
Exclusive
Repeatable Read
Exclusive
Intent
Exclusive
Read
Committed
Not Allowed
Not Allowed
Read
Uncommitted
Record Lock
Table Lock
Isolation Level
Insert/Update
None
None