DB2 performance Tips 2009/10/15 Hong, Mee Hee GTS. MTS., IBM Korea
Performance 의의미는? Performance ( noun ) The fulfillment of a claim, promise, or request Online Response time 초당수행된 Transaction Transaction 당지불비용 Batch Elapsed time CPU time Query 처리 Response time 분당처리되는 Query 수치 Throughput 을최대로 Concurrent user 수를최대로 Hardware / Software 자원사용을최대로 ( 부족한 ) System resource 에대한 application 영향을최소로 User 들이아직불만스러워하는가? Upgrade 비용을지불할필요는없는가?
Performance Management 란? The task of altering the system environment (hardware & software), applications (programs and database) and staff (training or re-allocation!) in order to meet previously defined performance objectives.
Performance Objective 결정절차 일반적인 objective 로정하지않도록 CPU time, elapsed time, memory usage 경감 Throughput, responsiveness, concurrent user 증가등 바람직한 performance objective 는 : 구체적이고 (Specific) 수량화되고 (Measurable) 달성가능하고 (Achievable) 상대적인 (Relevant) You can t manage what you can t control; you can t control what you can t measure
The Performance Management Cycle
DB2 시스템구성 Subsystem 간
DB2 시스템구성 Data Sharing 환경
Response Time 결정요소들 전반적 transaction 흐름 DB2 presentation network Application logic network Database processing
12 11 1 10 2 9 3 8 4 7 5 6 12 11 1 10 2 9 3 8 4 7 5 6 12 11 1 10 2 9 3 8 4 7 5 6 12 11 1 10 2 9 3 8 4 7 5 6 Response Time Scope Application Space DB2 11 10 12 1 2 transaction plan 9 8 7 6 5 3 4 program a package a statement 1 statement 2.. 12 11 10 9 8 7 6 1 2 3 4 5 statement 1 statement 2.. program b package b statement 1 statement 2.. 12 11 10 9 8 7 6 1 2 3 4 5 statement 1 statement 2..
DB2 Trace record Trace type 별
DB2 s Instrumentation Facility Interface (IFI)
DB2 System Tuning Dataset Open/Close Buffer Pool 과 Group Buffer Pool Lock/Latch Log EDM Pool System CPU time
Dataset Open/Close Rule-of-Thumb: NUMBER OF DATASET OPENS < 0.1 ~ 1 / sec #DSETS CONVERTED R/W -> R/O < 10-15 / minute Recommendations System checkpoint : CHKFREQ=2-5 (minutes) PCLOSEN/T 를조정하여 pseudo close 빈도를줄이도록 CLOSE(YES) 를 design default 로지정 (V8)
Buffer Pool Tuning
Buffer Pool Tuning PAGE-IN FOR READ / WRITE 전체 Buffer Pool 이 real storage 내에 Backup 되지못하면 P AGE-IN for READ 혹은 WRITE I/O 가발생함 Buffer Pool 크기가 Real storage 영역보다과도하게지정된경우 ALTER BPSIZE command 를이용한 BP expansion 의경우 Rule-of-Thumb ( steady-state ) : PAGE-IN for READ < 1-5% * (pages read) PAGE-IN for WRITE < 1-5% * (pages written)
Buffer Pool Tuning Long Term Page Fix : I/O 가빈번한 Buffer Pool 대상 Buffer i/o intensity = [pages read + pages written] / [number of buffers] ALTER BPOOL(name) PGFIX(YES NO) 로지정 상기예의경우 : BP32K, BP4, BP3, BP2 가우선고려대상 ( BP5 는 Data in memory 의개념 : Hit ratio = 100 %)
Group Buffer Pool Tuning GBP Read Tuning SyncRead (XI) nodata = 15K, SyncRead (XI) = 183K+15K SyncRead (XI) miss ratio = 15K/(183K+15K) = 7.6% ( 양호함 ) SyncReadXI miss ratio = (SyncReadXI-nodata / SyncReadXI) > 10% 이면 GBP 가부족함을의미하므로추가지정하여야합니다. Register Page List request = sequential, list, or dynamic prefetch request
Group Buffer Pool Tuning GBP Write Tuning Pages Sync Written to GBP - force write at Commit / P-lock negotiation Pages Async Written to GBP Deferred write / System checkpoint Pages Castout GBP-dependent 경우 LBP stats 내부의 Pages Written 에포함됨. Unlock Castout GBP-dependent 경우 LBP stats 내부의 #Async. Written 에포함됨.
Lock Tuning Lock avoidance : Unlock req/commit.=< 5 ( 양호 ) ISOLATION(CS) with CURRENTDATA(NO) 로지정 Compress / VL update 유의 MAX PG/ROW LOCKS HELD <100 으로유지 Commit 을자주Issue 하도록 LOCKSIZE PAGE 지정을우선 ( 필요시 LOCKSIZE ROW 고려 ) V8, 64bit IRLM 지원 PC=YES ECSA 감소
IRLM Latch Contention Rule-of-Thumb : Recommendation #IRLM latch contention < 1-5% * Total #IRLM Request 실예 : ( 양호함 ) #IRLM latch contention = SUSPENSIONS (IRLM LATCH) = 1.33 #IRLM Request = LOCK+UNLOCK+CHANGE = 31.94+7.39+1.03 = 40.36 #IRLM latch contention Rate = 1.33*100 / 40.36 = 3.3% 양호하다는결론임
Data Sharing Lock Tuning Rule-of-Thumb : Recommendation Global Contention Rate < 3-5% * #XES IRLM Request 실예 : ( 양호함 ) #Global Cont. = SUSPENDS - IRLM+XES+FALSE = 0.15+0.4+0.08 = 0.63 #XES IRLM Req. = SYNCH. XES (LOCK+CHANGE+UNLOCK) +SUSPENDS (IRLM+XES+FALSE) = 8.62+0.33+8.20+0.15+0.4+0.08 = 17.78 Global Contention Rate = 0.63*100 / 17.78 = 3.54%
Data Sharing Lock Tuning Rule-of-Thumb : Recommendation #P-lock Negotiation < 3-5% * #XES IRLM request 실예 : ( 양호함 ) #P-lock Negotiation = 0.01+0.06+0.01 = 0.08 #XES IRLM Req. = 17.78 ( 앞장의계산참고 ) #P-lock Negotiation Rate = 0.08*100 / 17.78 = 0.5%
Thread Reuse High volume simple transaction 에대한 Performance 개선시 Accounting Trace 를이용하여 Thread reuse 를 monitor 수행 Thread reuse 의수준점검 (%) : (#COMMITS- DEALLOCATION)*100/#COMMITS 상기예의경우 : (974827-212799)*100/ 974827= 78% thread reuse
Internal DB2 Latch Contention/Second Rule-of-Thumb : Recommendation Latch contention rate < 1K-10K / second Accounting Class 3 trace ( latch contention 과도발생시 CPU 소모 ) LC06 = Index split latch LC14 = Buffer pool LRU and hash chain latch LC19 = Log latch LC24 = Prefetch latch or EDM LRU chain latch
Log Statistics READ SATISFIED - FROM..." : Log apply (UNDO 혹은 REDO) 를위하여 Log Manager 가 Data Manager 에게보낸 log record 수 Read from output log buffer 가가장효율적이며 Read from archive log dataset 의효율이가장나쁨
Log Statistics Output Log Buffer size : #UNAVAIL OUTPUT LOG BUF > 0 이면확대지정 Output log buffer space 가커지면처리효율이좋아지나 page in activity 는유의하여점검하여야함 #OUTPUT LOG BUFFER PAGED IN > 1-5%* LOG RECORDS CREATED 이면확대지정 Approximate average log record size = (LOG CIs CREATED * 4KB)/(LOG RECORDS CREATED)
EDM Pool Tuning EDM Pool 크기확대지정기준 - % NON-STEALABLE PAGES IN US E (PTs, CTs) < 50% - FAILS DUE TO POOL FULL = 0 - CT/PT HIT RATIO > 90 ~ 95% 자주사용하는 Plan/Package 에대하여 Bind 시 Release(Commit) 으로지정하여 EDM 의크기를적절히유지하도록!
EDM Pool Tuning Global Dynamic Statement Cache hit ratio > 90-95% 유지하도록! = [Short Prepares] / [Short + Full Prepares] = 98.70% Local Dynamic Statement Cache hit ratio >70% 유지하도록! = [Prepares Avoided]/[Prepares Avoided + Implicit Prepares] = 62.51% Implicit Prepare 은 Short 혹은 Full Prepare 로!
System Address Space CPU Time Major MSTR SRB time Physical log write, thread deallocation, update commit (Page P-lock unlock 포함 ) Major DBM1 SRB time Deferred write, prefetch read, parallel child task, Castout, async GBP write, P-lock negotiation, Notify exit, GBP checkpoint, Delete Name (pageset close 혹은 pseudo-close 가 non GBP dependent 로변경 ) Major DBM1 TCB time Dataset open/close, DBM1 Full System contraction Major IRLM SRB time Local IRLM latch contention, IRLM / XES global contention, async XES request, P-lock negotiation
DB2 Application Tuning 대부분의 DB2 performance issue 들은 System 보다는 Application 과연관측면이강함 세가지주요측면 물리 database design ( 정규화, index 지정, partitioning 구분, RI 지정, stats, 등 ) SQL coding (joins 대비 subselect, stage 1 대비 stage 2 predicate, 등 ) Application design (singleton select 대비 cursor 사용, dynamic 대비 static SQL, host variable 사용, commit 빈도, 등 )
Accounting Trace 개요 Application performance 개선을위한 trace type System 내에서개별 application 들이사용하는 resource 들에대한정보기록 Program 의 CPU 와 elapsed time EDM pool 사용상황 Lock 과 latch Get page request 수치 Synchronous write 수치 SQL statements 수행형태와실행수치 COMMIT 과 ABORT 수치 Sequential prefetch 와여타 performance 특성들
Accounting Class 1 Data Out of DB2 thread allocate 1 st SQL 2 nd SQL thread deallocate In DB2 Activity Time Class 1 Elapsed and CPU Local application 의경우 : Application 에서소요된 CPU time 과 DB2 에서소요된 CPU time 모두를포함한시간을제공함. Activity Time : Class 1 Elapsed Time 과근사한값. Local application 의경우 : (Elapsed CPU) time 은 Application 의효율성여부를의미함. Distributed application 의경우 : 경우에따라다름.
Accounting Class 2 Data Out of DB2 In DB2 thread allocate Class 1 Elapsed and CPU Class 2 Elapsed and CPU Class 2 timer = 1 st SQL Time spent out of DB2 = Class 1 Elapsed - Class 2 Elapsed 2 nd SQL + Waiting in DB2 = Class 2 Elapsed - Class 2 CPU Class 7 : Package/DBRM 단위의 Report 을제공하므로 Class 2 와유사 thread deallocate
Accounting Class 3 Data Out of DB2 In DB2 thread allocate Class 1 Elapsed and CPU Class 2 Elapsed and CPU Class 3 Suspensions 1 st SQL 2 nd SQL Class 8 : Package/DBRM 단위의 Report 을제공하므로 Class 3 와유사 thread deallocate
Accounting Time 분포 Thread activity time Elapsed time spent out of DB2 Elapsed time spent in DB2 Processing time Waiting time Suspended time Not accounted time = Class 1 elapsed = Class 1 elapsed - Class 2 elapsed = Class 2 elapsed = Class 2 CPU = Class 2 elapsed - Class 2 CPU = Class 3 suspension time = Waiting time - Suspended time
Nested Activity: Triggers, SPs, UDFs Elapsed Time Class 1 Class 2 (in DB2) in Appl in DB2 in UDF 1st SQL.. (Creating Thread) SQL.. Trigger1 SQL.....UDF Trigger2 UDF SQL.. UDF Trigger Agent UDF Trigger Agent, nonnested (Terminating Thread)
Activity time 의분포에따라...? In DB2 Activity time 의분포를분석할필요가있슴 : where is the time really spent? Out of DB2 In DB2 Application logic 비효율성? 간혹발생하는경우. class 2 CPU << class 1 CPU Network 문제? Class 2 가 active 하여야!!! Out of DB2
In DB2 time 의분포에따라...? Processing time DB2 trace 를기동시켜야함 Access path 의비효율성? Explain 으로점검 Waiting time Processing time 가장큰 waiting 요인은? Class 3 와 8 분석 Waiting time
TCB 와 SRB Time 분석 Application DBAS SSAS IRLM T C B S R S Accounting Statistics Scans SQL Processing Open, Close Archiving Error Checking Lock Requests Synchronous I/O Extend BSDS Processing Buffer Updates Logical Logging Management Preformat *GBP reads *Global lock requests Asynchronous I/O Memory Management Physical Logging Deadlock Detection Real Time Stats Checkpoints Lock Resume *Castout Backouts *Global lock conflict Ignore it *P-lock negotiation Deallocation resolution Update Commit *Prefetch in CF *GBP writes at commit *SYSLGRNX updates *GBP checkpoints *Global unlocking at commit
Class 3 Suspension Type 들 ( Class 8 도? ) I/O Locking Synchronous EU Switch Archiving Scheduling Data Sharing V5 V6 V7&V8 Synchronous read/write & log write Synchronous read/write Log write Other agents' read Other agents' write Force-at-commit database writes (LOG NO LOBs only) IRLM lock/latch & DB2 internal latch Page latch Drain lock Claim release Synchronous Execution Unit switch total Open/Close Define/Extend/Delete SYSLGRNX recording Commit Other services Archive Log command Archive log read Stored procedures UDFs Global locks total Parent L-locks Child L-locks Other L-locks Pageset/Partition P-locks Page P-locks Other P-locks Sending Notify messages Synchronous-to-asynchronous coupling facility requests
DB2 PE Report Command (1)
DB2 PE Accounting Report (Short)
DB2 PE Accounting Report (Long-1)
DB2 PE Accounting Report (Long-2)
DB2 PE Accounting Report (Long-3)
DB2 PE Accounting Report (Long-4)
Synchronous I/O Suspension 의수치가클경우? Synchronous I/O Wait In DB2 Time Suspension 이자주발생하는경우? GetPage 과다발생. Explain 기능을이용하여 access path 조정 Buffer pool 의 contention BP statistics 내용점검 Disorganized data/indexes Catalog 점검후필요시 Reorg, Runstats 단위 Suspension 시간이크면? 상세분석을위하여 RMF Report 분석및 performance class 4 를 on! : DASD contention Control Unit cache miss CPU contention I/O priority 적정여부
Other Agent 의 Read 시 Suspension 발생? In DB2 Time Application address space FETCH FETCH FETCH FETCH DB2 address space wait getpage: miss prefetch begin first 32 pages getpage: hit getpage: hit What if... Other agent 의 Read 에대한 Wait? Suspension 발생수치가높으면? GetPage 과다발생? Explain 을이용한 access path 의 tuning Buffer pool 의 contention? BP statistics 점검 Data/index 분포가비정렬인경우 Catalog 점검 Suspension 의단위시간이크면? 상세분석을위하여 RMF report 및 performance class 4 분석 : DASD contention Control unit cache miss 부적절한 I/O priority 지정 Parallelism 고려사항
Other Agent 의 Write 시 Suspension 발생? In DB2 Time Application address space DB2 address space What if... Other agent 의 Write 에대한 Wait? UPDATE Buffer update UPDATE UPDATE wait Buffer update Deferred write start page being written Suspension 발생수치가높으면? Checkpoint 가빈번히발생하는경우 DB statistics 와 CHKFREQ 점검 빈번히 re-reference 되는 access pattern 에대한 Deferred write threshold 가낮은경우 BP statistics 점검 page written Suspension 의단위시간이크면? DASD contention CU cache misses 부적절한 I/O priority 지정
Lock/Latch Suspension 의발생수치가높으면? DB2 내부 Time Lock latch suspension 에의한지연 단위 suspension 별시간이크면 Lock suspension Commit 을 issue 하지않는 transaction Concurrent DDL 수행 RELEASE(DEALLOCATE) 지정 Detail analysis 를위한 performance trace class 6 와 7 분석 Lock suspension 의발생이빈번하면 Application 문제분석 Incompatible workload mix Page/Row level locking 비효율적인 preformatting V7 개선사항
Lock/Latch Suspension 의발생수치가높으면? DB2 내부 Time Lock latch suspension 에의한지연 IRLM latch suspension 의발생이빈번하면 모든 IRLM request 의 10% 를초과하여발생하면 IRLM Trace 를 on 시킴. IRLM dispatching priority 가낮은경우 잦은 IRLM Query request (e.g. DISP DATABASE LOCKS, 또는 MODIFY irlmproc,status ) Deadlock detection cycle 이작은경우 Internal latch suspension 의발생이빈번하면 LC06: 주로 GBP-dependent index page 의 split 에기인한 Index tree P-lock latch contention LC07: Dependency Manager Hash Table LC14: BP LRU chain 소량의빈번히사용되는 table 들을별개의 pool 로분리 LC19: Log Log I/O 의처리속도개선 LC24: EDM LRU 또는 BM latch LC25: EDM Pool hash chain LC32: Storage Manager Pool Header
Page Latch Suspension 발생? Application address space DB2 address space In DB2 Time FETCH getpage: hit... but page latched What if... Page latch Suspension 에대한 Waits wait page latch suspend page latch resume 대부분의경우 insert activity 과다발생시점! space map page - Partitioning - MEMBER CLUSTER data page - freespace 0 상세분석요구시 IFCID 226, 227 이용
이외의고려사항? CPU In DB2 Time Not accounted Suspensions Processor wait Dispatching priority 의지정이잘못된경우 Processor 에대한 overload RMF 점검 general tuning 수행 Paging overcommitted real storage RMF 점검 MAXKEEPD 을매우크게지정한경우 Buffer pools, EDM Pool, Sort, thread 개수 Storage 추가지정 OEM Monitor 점검 Parallel task 종료에대한 Wait time Sync-to-async CF request 에대한 conversion 수행 DSC miss 발생시 prepare 과정
Accounting Report 분석사례 (1) 현황및분석결과 문제 TRAN 인 AAA 는 PACKAGE 중가장높은 Elapsed time 을차지하고있는 AAAAAAAA Package 가 69% 의수행시간을점유하는데, DB2 내부 SYNCHRONOUS I/O TIME 이 55% 를차지합니다. AAAAAAAA (TIMES.) DSNA OCCURRENCES 13 SQL STMT - AVERAGE 190.85 SQL STMT - TOTAL 2481 ELAP-CL7 TIME-AVG 5.9460 CPU TIME 1.9253 SUSPENSION-CL8 3.9570 - LOCK/LATCH 0.0001 - SYNCHRONOUS I/O 3.2322 - OTHER READ I/O 0.7246 NOT ACCOUNTED 0.0637 AAAAAAAA ELAPSED TIME 분포 1% 12% CPU time 32% LOCK/LATCH SYNCHRONOUS I/O 55% 0% OTHER READ I/O NOT ACCOUNTED 권고사항 AAAAAAAA PACKAGE 가소모하는 Synchronous I/O 를발생시키는 SQL 의 ACCESS PATH 및 DASD contention, I/O Activity 점검, 분석이시급합니다.
Accounting Report 분석사례 (2) 현황및분석결과 문제 TRAN 인 BBBB 는 BBBBBBBB Package 가 90% 의수행시간을점유하는데, SQL Activity 가과도합니다. BBBBBBBB (TIMES.) DSNA OCCURRENCES 100 SQL STMT - AVERAGE 2181.40 SQL STMT - TOTAL 218140 ELAP-CL7 TIME-AVG 0.3328 CPU TIME 0.2741 SUSPENSION-CL8 0.0200 - SYNCHRONOUS I/O 0.0118 - OTHER READ I/O 0.0080 NOT ACCOUNTED 0.0386 0% 4% 2% BBBBBBBB ELAPSED TIME 분포 12% CPU time LOCK/LATCH SYNCHRONOUS I/O OTHER READ I/O 82% NOT ACCOUNTED 권고사항 BBBBBBBB PACKAGE 의 SQL Activity 를감소시키도록 APPLICATION 튜닝이필요합니다.
Accounting Report 분석사례 (3) 현황및분석결과 문제 TRAN 인 CCC 는 CCCCCCCC Package 가 80% 의수행시간을점유하는데, SQL Activity 에비해 GETPAGE 가과도합니다. BP3 의 GET PAGE REQUEST 가전체의 78% 를차지합니다. CCCCCCCC (TIMES.) DSNA OCCURRENCES 24 SQL STMT - AVERAGE 334.33 SQL STMT - TOTAL 8024 ELAP-CL7 TIME-AVG 0.9404 CPU TIME 0.7806 SUSPENSION-CL8 0.0020 -LOCK/LATCH 0.0000 -SYNCHRONOUS I/O 0.0016 - OTHER READ I/O 0.0002 NOT ACCOUNTED 0.1577 CCCCCCCC ELAPSED TIME 분포 17% CPU time LOCK/LATCH SYNCHRONOUS I/O OTHER READ I/O 83% NOT ACCOUNTED 권고사항 CCCCCCCC PACKAGE 의 SQL Activity 및 GETPAGE 를감소시키도록 APPLICATION 튜닝이필요합니다. BP3 의과도한 ACTIVTY 를발생시키는 SQL 을점검조정하셔야합니다.