사례 연구: 고성능을 위한 DB2 튜닝(2)

사례 연구: 고성능을 위한 DB2 튜닝(2) * 출처: http://www.kdug.kr * 저자: Bin Qiao 님 튜닝 | 2011-08-23 10:15:46

여러 통계는 데이터베이스를 튜닝한 시스템이 그렇지 않은 시스템보다 20% 더 높은 성능을 낼 수 있음을 보여줍니다. 그러나 프로덕션 시스템을 잘못 튜닝하면 위험할 수 있습니다. 이 문서에서는 Linux®, UNIX® 및 Windows®용 IBM DB2® 환경에서 수행할 수 있는 데이터베이스 성능 튜닝 사례에 대해 다룹니다. 데이터베이스 성능 튜닝에 직접 적용할 수 있는 유용한 기술을 얻을 수 있을 것입니다. 원문 게재일 : 20011년 7월 28일 원문 출처 : http://ibm.co/mXpvV3 목차 l 개요 l 문제 발견 l 데이터베이스 서버 정보 수집 l 데이터베이스 사용 정보 수집 l 데이터베이스 정보 분석 → SQL 문 분석 → 교착 상태 분석 → 버퍼풀 분석 → 메모리 분석 → 테이블스페이스 및 테이블 분석 l 튜닝 활동 설계 l 튜닝 결과 구현 및 평가 l 결론 * 참고 사항 : “사례 연구: 고성능을 위한 DB2 튜닝(1)” 에 이어서 연재합니다. 데이터베이스 정보 분석 버퍼풀 분석 버퍼풀 이벤트 모니터에서 제공되는 정보를 사용하여 목록 4와 같이 버퍼풀 분석을 수행할 수 있습니다. 목록 4. 버퍼풀 이벤트 샘플 항목 3) Bufferpool Event ... Bufferpool Name: IBMDEFAULTBP Database Name: database Database Path: /shared/dbg/db2inst3/db2inst3/NODE0000/SQL00001/ Buffer Pool Statistics: Buffer pool data logical page reads: 14871152 Buffer pool data physical page reads: 1699818 Buffer pool data page writes: 53823 Buffer pool index logical page reads: 8606405 Buffer pool index physical page reads: 290822 Buffer pool index page writes: 272282 Buffer pool xda logical page reads: 0 Buffer pool xda physical page reads: 0 Buffer pool xda page writes: 0 Buffer pool read time (milliseconds): 1536574 Buffer pool write time (milliseconds): 353641 Files closed: 0 Buffer pool asynch data page reads: 1694131 Buffer pool asynch data page read reqs: 59110 Buffer pool asynch data page writes: 53371 Buffer pool asynch index page reads: 227455 Buffer pool asynch index page read reqs: 8527 Buffer pool asynch index page writes: 270292 Buffer pool asynch xda page reads: 0 Buffer pool asynch xda page read reqs: 0 Buffer pool asynch xda writes: 0 Buffer pool asynch read time: 1327887 Buffer pool asynch write time: 347809 No victim buffers available: 1509238 Unread prefetch pages: 2995 Direct I/O Statistics: Sectors read directly: 13610 Sectors written directly: 1695616 Direct read requests: 1382 Direct write requests: 3763 Direct read time: 3758 Direct write time: 22236 Vectored IOs: 67407 Pages from vectored IOs: 1921234 Block IOs: 0 Pages from block IOs: 0 목록 5에 나와 있는 수식을 사용하면 버퍼풀의 효율를 대략적으로 계산할 수 있습니다. 목록 5. 버퍼풀 효율 계산 수식 1 – (Bufferpool data logical page reads + Bufferpool index logical page reads) divided by (Bufferpool data physical page reads + Bufferpool index physical page reads) 계산 결과가 90% 미만이면 버퍼풀 크기를 늘리는 것이 바람직합니다. 메모리 분석 목록 6과 같이 데이터베이스 이벤트 모니터의 정보를 메모리 분석을 위해 사용할 수 있습니다. 목록 6. 메모리 이벤트 샘플 항목 3) Database Event Record is the result of a flush: FALSE Lock Statistics: Lock Waits: 0 Total time waited on locks (milliseconds): 0 Deadlocks: 0 Lock escalations: 0 X lock escalations: 0 Lock timeouts: 0 Sort Statistics: Sorts: 844 Total sort time (milliseconds): 160043 Sort overflows: 80 Sort share heap high water mark: 9851 Post Shared Memory Threshold Sorts: 20 Hash Statistics: Hash Joins: 25 Hash Loops: 0 Hash Join Small Overflows: 0 Hash Join Overflows: 0 Post Shared Memory Threshold Hash Joins: 0 …… Node Number: 0 Memory Pool Type: Backup/Restore/Util Heap Current size (bytes): 65536 High water mark (bytes): 196608 Configured size (bytes): 319815680 출력에 잠금 에스컬레이션이나 X 잠금 에스컬레이션이 너무 많으면 LOCKLIST 메모리가 너무 적게 할당된 것일 수 있습니다. 정렬 오버플로우 비율(정렬 오버플로우 수를 정렬 수로 나눈 값)이 높거나 해시 조인 오버플로우 비율((작은 해시 조인 오버플로우 수 + 해시 조인 오버플로우 수) / 해시 조인 수)이 높으면 SORTHEAP에 할당된 메모리가 부족한 것을 의미합니다. 메모리 용량 표시가 구성된 크기에 근접한 경우, 메모리가 너무 적게 할당된 것을 의미합니다. 테이블스페이스 및 테이블 분석 목록 7과 같이 테이블스페이스 및 테이블 이벤트 모니터 정보를 사용하면 가장 자주 액세스하는 테이블스페이스나 테이블을 식별할 수 있습니다. 목록 7. 테이블스페이스/테이블 이벤트 샘플 항목 5) Tablespace Event ... Tablespace Name: USERSPACE1 Record is the result of a flush: FALSE File System Caching: Yes Buffer Pool Statistics: Buffer pool data logical page reads: 14846454 Buffer pool data physical page reads: 1699227 Buffer pool data page writes: 31111 Buffer pool index logical page reads: 8593610 Buffer pool index physical page reads: 290381 Buffer pool index page writes: 272125 Buffer pool xda logical page reads: 0 Buffer pool xda physical page reads: 0 Buffer pool xda page writes: 0 Buffer pool read time (milliseconds): 1529939 Buffer pool write time (milliseconds): 350770 Files closed: 0 Buffer pool asynch data page reads: 1693042 Buffer pool asynch data page read reqs: 58409 Buffer pool asynch data page writes: 30761 Buffer pool asynch index page reads: 227412 Buffer pool asynch index page read reqs: 8489 Buffer pool asynch index page writes: 270137 Buffer pool asynch xda page reads: 0 Buffer pool asynch xda page read reqs: 0 Buffer pool asynch xda writes: 0 Buffer pool asynch read time: 1325077 Buffer pool asynch write time: 345169 No victim buffers available: 1435565 Unread prefetch pages: 2982 Direct I/O Statistics: Sectors read directly: 3488 Sectors written directly: 1695176 Direct read requests: 436 Direct write requests: 3752 Direct read time: 476 Direct write time: 22217 4) Table Event ... Table schema: SCHEMA Table name: TEMP (00001,00002) Data partition id: 0 Record is the result of a flush: FALSE Table type: Temporary Data object pages: 1 Index object pages: 0 Lob object pages: 0 Long object pages: 0 Rows read: 3 Rows written: 1 Overflow Accesses: 0 Page reorgs: 0 Tablespace id: 1 읽기/쓰기 수는 테이블스페이스나 테이블의 사용 빈도를 나타냅니다. 가장 자주 액세스하는 테이블이 하나의 디스크에 집중적으로 배치된 경우 각 테이블을 다른 디스크로 다시 할당하여 디스크 읽기 및 쓰기를 고르게 분배하는 것이 좋습니다. 또한 테이블에 있는 데이터를 여러 개의 실제 디스크로 분배하는 방법도 있습니다. 튜닝 활동 설계 수집한 모든 정보를 바탕으로 실제 튜닝 활동을 설계할 수 있습니다. 그러나 각 튜닝 활동에는 위험과 비용이 따르기 때문에 실제 솔루션 구현을 결정하기 전에 면밀한 위험 및 ROI 분석이 선행되어야 합니다. 이 분석 과정에서는 튜닝 활동을 주로 3가지 범주(즉시 구현, 조건부 구현, 구현 안 함)로 분류합니다. 이 사례 연구에서는 튜닝 구현을 결정하기 위해 표 2를 작성했습니다. 표 2. 튜닝 의사결정 표 튜닝 활동 성능 향상 효과 위험성 ROI 의사결정 조건 새 인덱스 추가 낮음 낮음 낮음 중간 해당 없음 CPU 업그레이드 중간 낮음 중간 조건부 최대 CPU 활용률이 90%인 경우 데이터베이스 테이블 재할당 높음 높음 중간 조건부 CPU의 I/O 대기 시간이 긴 경우 튜닝 결과 구현 및 평가 이제 튜닝 활동을 테스트한 뒤 프로덕션 환경에 배치할 수 있습니다. 튜닝 결과를 평가하려면, 앞서 소개한 NMON을 사용하여 튜닝으로 얻어진 성능 향상 효과를 평가하면 됩니다. 이 사례 연구를 위해 이해 당사자에게 튜닝 결과를 제시하자 유일하게 Add new indexes 옵션만 채택되었습니다. 이해 당사자들은 다른 2가지 옵션은 ROI가 비합리적이고 너무 비싸거나 위험한 것으로 여겼습니다. 또한 이러한 조치는 반드시 필요할 때만 취하려고 했습니다. 이 사례 연구에서는 모든 SQL 문을 튜닝하기 보다는 가장 가치가 큰 SQL 문을 튜닝하는 데 중점을 두었습니다. 그 대상은 누적 실행 시간이 60초를 넘은 SQL 문으로 지정했습니다. 대상 SQL 문에 대해 db2advis를 실행한 결과는 목록 8과 같습니다. 목록 8. db2advis 샘플 출력 Your SQL Statement execution started at timestamp 2011-04-06-11.02.28.049293 Recommending indexes... total disk space needed for initial set [ 0.134] MB total disk space constrained to [ 67.464] MB Trying variations of the solution set. Optimization finished. 3 indexes in current solution [ 16.9089] timerons (without recommendations) [ 7.5935] timerons (with current solution) [55.09%] improvement -- -- LIST OF RECOMMENDED INDEXES -- =========================== -- index[1], 0.134MB CREATE INDEX "SCHEMA "."IDX107060204130000" ON "SCHEMA"."TABLE1" ("FIELD1" ASC, "FIELD2" ASC, "FIELD3" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS; COMMIT WORK ; -- -- RECOMMENDED EXISTING INDEXES -- ============================ -- RUNSTATS ON TABLE "SCHEMA"."TABLE1" FOR SAMPLED DETAILED INDEX INDEX1 ; -- COMMIT WORK ; -- -- UNUSED EXISTING INDEXES -- ============================ -- DROP INDEX INDEX2; -- =========================== 13 solutions were evaluated by the advisor DB2 Workload Performance Advisor tool is finished. 표 3은 튜닝 전후에 해당 SQL 문에 대해 db2advis를 실행한 결과를 비교한 것입니다. 표 3. db2advis 결과 데이터베이스 SQL 문 실행 시간(초) 개선율(%) 단축된 시간(초) DB1 SQL1 188 0.00% 0 SQL2 60 0.00% 0 DB2 SQL1 421 0.00% 0 SQL2 236 55.09% 130 SQL3 153 3.45% 5 SQL4 63 49.94% 31 SQL5 62 0.00% 0 DB3 SQL1 1222 13.45% 164 SQL2 365 0.00% 0 SQL3 355 1.42% 5 SQL4 354 1.42% 5 SQL5 94 49.96% 47 SQL6 92 19.95% 18 SQL7 83 0.00% 0 SQL8 67 0.00% 0 db2advis 툴 결과에 따라, 시간 단축 폭이 가장 큰 4개의 SQL 문 튜닝 활동을 구현했습니다. 그런 다음, 튜닝 결과를 평가하기 위해 2차 NMON 분석을 수행했습니다. 예상대로 최대 CPU 활용률은 크게 낮아지지 않았지만 폭주 시간은 55분에서 50분 이하로 단축되었고 이해 당사자들은 이 결과에 상당히 만족했습니다. 물론, 가장 신중한 방법은 CPU 활용률과 CPU 대기 I/O 데이터를 지속적으로 모니터링하는 것입니다. 이 수치가 사전 정의된 임계값에 도달할 경우 사례 연구에서 추가 조치를 취할 것입니다. 결론 이 문서에서는 Linux, UNIX 및 Windows용 DB2 데이터베이스의 성능 문제를 조사하는 방법과 프로덕션 시스템에서 발생할 수 있는 위험을 최소화하면서 최상의 결과를 얻을 수 있는 성능 개선 방법에 대해 설명했습니다. 리소스 학습 Linux, UNIX 및 Windows용 DB2 developerWorks 리소스 페이지는 DB2 스킬을 넓힐 수 있는 다양한 문서와 자습서를 제공하며 다른 리소스로 바로 연결되는 링크를 제공합니다. developerWorks Information Management zone에서 정보 관리에 대해 학습할 수 있습니다. 기술 문서, 방법 문서, 교육, 다운로드, 제품 정보 등을 찾아 보십시오. 최신 소식은 developerWorks 기술 이벤트 및 웹캐스트를 참조하십시오. Twitter: developerWorks on Twitter 제품 및 기술 얻기 Linux, UNIX 및 Windows용 DB2 무료 평가판을 다운로드할 수 있습니다. 이제 DB2를 무료로 사용할 수 있습니다. 커뮤니티용 DB2 Express Edition의 무료 버전인 DB2 Express-C를 다운로드하십시오. 이 버전은 DB2 Express Edition과 동일한 핵심 데이터 기능을 제공하며 애플리케이션을 빌드 및 배치할 수 있는 탄탄한 기반을 제공합니다. developerWorks에서 직접 다운로드 가능한 IBM 평가판 소프트웨어로 다음 개발 프로젝트를 구축하십시오.