Internal DB2 Latch Contention你真的懂么？

Typical high latch contention classes
– LC06 = Index split latch
– LC14 = Buffer pool LRU and hash chain latch
– LC19 = Log latch
– LC24 = Prefetch latch or EDM LRU chain latch

Latch Class details in IFCID 51/52 (Share) and 56/57 (Exclusive) performance trace

Disabling Acct Class 3 trace can help reduce CPU time due to high latch contention


LC06 for index tree P-lock by index split
– Index split is particularly painful in data sharing
? Results in two forced physical log writes
– Index split time can be significantly reduced by using faster active log
device
– Options to reduce index split
? Index freespace tuning for random Insert
? Minimum index key size especially if unique index
? V8 : NOT PADDED index for large varchar columns
? V9 : Large index page size, Asymmetric leaf-page split
? Number of index splits in LEAFNEAR/FAR in SYSINDEXPART and
RTS REORGLEAFNEAR/FAR
– Function Code (FC) X’46’ in IFCID 57 performance trace
– FC X’FE’ index tree latch in non

LC14 Buffer Pool latch
– If many tablespaces and indexes, assign to separate buffer
pools with an even Getpage frequency
– If objects bigger than buffer pool, try enlarging buffer pool if
possible
– If high LC14 contention, use buffer pool with at least 3000
buffers
– Use FIFO rather than LRU buffer steal (PGSTEAL) algorithm if
there is no read I/O, i.e. object(s) entirely in buffer pool
? LRU = Least Recently Used buffer steal algorithm (default)
? FIFO = First In First Out buffer steal algorithm
– Eliminates a need to maintain LRU chain which in turn
> Reduces CPU time for LRU chain maintenance
> Reduces CPU time for LC14 contention processing
– V10 : PGSTEAL NONE for in memory data / index

LC19 Log latch
– Minimise #log records created via
? LOAD RESUME/REPLACE with LOG NO instead of massive
INSERT/UPDATE/DELETE
? Segmented or UTS tablespace if mass delete occurs
– Increase size of output log buffer if non-zero unavailable count
? When unavailable, first agent waits for log write
? All subsequent agents wait for LC19
– Reduce size of output log buffer if non-zero output log buffer paging
– Reduced LC19 Log latch contention in DB2 9
? Log latch not held while spinning for unique LRSN
– V10 improvements :
? Log latch time held minimized
? Conditional attempts to get log latch before unconditional request
LC24 latch
– Used for multiple functions :
? EDM LRU latch – FC X’18’ in IFCID 57 performance trace
– Use EDMBFIT zparm of NO (V9)
– Thread reuse with RELEASE DEALLOCATE instead of RELEASE
COMMIT for frequently executed packages
? Prefetch scheduling – FC X’38’ in IFCID 57 performance trace
– Higher contention possible with many concurrent prefetches related to
Sort, Workfile, Parallel query processing
– Disable dynamic prefetch for in memory data/index BP by setting
VPSEQT=0 (V9)
– V10 : PGSTEAL NONE for in memory data/index BP
– Use more partitions
– V10 :
? Moving CT,PT from EDM pool to thread pools reduced LC24
significantly
? Latch no longer used for Buffer Manager page latch/unlatch

LC12 - Latch to coordinate Global Transactions
– Increased number of hash entries and improved hashing
algorithm

LC27 – Stored Procedure Queue Latch
– Increased number of latch entries

LC32 – Storage Pool Latch
– Moved shared storage to private thread storage pool
– Improved storage pool space management algorithm
V10 : High volume, many concurrent transactions
with high latch contentions expected to benefit
significantly with V10 latch contention reduction
improvements