developerWorks에 처음 로그인하면 developerWorks프로파일이 생성됩니다.귀하의 프로파일에서 동의하신 내용이 공개되지만 이 사항은 언제든지 변경 가능합니다. 귀하의 성명(숨김으로 체크되어 있어도 표시됩니다)과 디스플레이 이름은 게시한 컨텐츠나 사이트 엑세스시 표시됩니다.

모든 정보가 안전하게 전송되었습니다.

처음 developerWorks에 로그인할 때 프로파일이 작성되므로, 이를 위해 디스플레이 이름을 선택해야 합니다. 선택하신 디스플레이 이름은 developerWorks에 게시한 컨텐츠에 표시됩니다.

3글자 이상 31글자 이하의 길이로 사용 가능합니다. dW커뮤니티 내에서는 보안상 이메일주소를 제외한 다른 이름을 지정하셔야 합니다.

모든 정보가 안전하게 전송되었습니다.

My developerWorks：

나의 통지：

로그아웃

언어 선택：

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

Bin Qiao, 자문 소프트웨어 엔지니어, IBM

Bin Qiao는 솔루션 아키텍처를 전문으로 하는 소프트웨어 엔지니어이다. 현재는 IBM China 개발 연구소에서 근무하고 있다.

요약： 통계에 따르면 데이터베이스 튜닝은 튜닝된 적이 없는 시스템에 20퍼센트 성능 개선을 창출할 수 있다고 합니다. 하지만, 프로덕션 시스템을 튜닝하는 것은 올바르게 수행되지 않으면 위험할 수 있습니다. 이 기사는 IBM DB2® for Linux®, UNIX® 및 Windows® 환경에서 데이터베이스 성능 튜닝의 실제 사례 연구를 시연합니다. 독자는 자체적인 데이터베이스 성능 튜닝에 적용하기 위해 유용한 기술을 학습할 수 있습니다.

여기에 태그 달기!

기사 게재일： 2012 년 1 월 31 일
난이도： 중급
원문： 보기
PDF： A4 and Letter (57KB | 18 pages)Get Adobe® Reader®
페이지뷰： 922 회
의견：

소개

불완전하게 설계된 시스템은 가동하면 바로 성능 문제에 당면할 수 있다. 심지어 올바르게 튜닝된 시스템도 장기간 운영 또는 주요 기능 변경 이후에 성능 문제에 직면할 수 있다. 시스템을 튜닝하는 것은 시스템 관리자가 피할 수 없는 태스크이다. 대부분의 애플리케이션 시스템의 주요 부분이 되는 데이터베이스 성능 튜닝은 이 태스크의 중요한 부분이다. 통계에 따르면 데이터베이스 튜닝은 튜닝된 적이 없는 시스템에 20퍼센트 성능 개선을 창출할 수 있다고 한다. 하지만, 프로덕션 시스템을 튜닝하는 것은 올바르게 수행되지 않으면 위험할 수 있다. 이 기사는 IBM DB2 for Linux, UNIX 및 Windows 환경에서 데이터베이스 성능 튜닝의 실제 사례 연구를 시연한다.

이 사례 연구에서 튜닝된 시스템은 백엔드 데이터베이스로 DB2를 사용하는 JIRA Enterprise 패키지를 기반으로 하는 워크플로 애플리케이션이다. 애플리케이션은 두 가지 모드인 야간 일괄처리 모드와 주간 OLTP 모드로 운영한다. 야간 일괄처리 시간 동안 쉘 스크립트 시리즈는 외부 데이터(일반 텍스트 파일의 형태)를 데이터베이스로 전송하기 위해 실행한다. 그리고 주간 OLTP 시간 동안, 운영자는 JIRA에서 정의된 워크플로에 따라 이러한 비즈니스 데이터를 처리한다.

애플리케이션이 거의 1년 동안 가동된 후에 고객들은 문제 사건의 비율 면에서 줄어드는 것을 확인하지 못했다. 조사에서는 이러한 사건 중 일부가 데이터베이스 교착 상태 및 JIRA 파일 잠금 제한시간 초과 등 성능 문제로 야기되었다고 밝혔다. 계약에 따르면 연간 5퍼센트 워크로드 증가가 예정되었다. 시스템 성능이 개선될 수 없으면 성능 관련 사건은 향후에 더 많이 예상될 수 있다. 성능 튜닝은 필수가 되었다.

문제점 발견하기

시스템 성능 튜닝의 전제조건이 되는 태스크는 성능 문제가 어디에서 나오는지를 찾는 것이다. Nigel의 Linux용 성능 모니터(NMON)는 CPU 활용, 메모리 활용, 사용 중인 디스크 비율, 최상위 프로세스 및 기타 등등의 핵심적인 성능 데이터를 수집하기 위한 훌륭한 도구이다. NMON은 시스템 내에서 각 서버의 성능 정보를 수집하는 데 사용되었다.

수집된 NMON 데이터를 검토한 후에 다음 두 가지 성능 문제가 식별되었다.

야간 일괄처리 시간 도중에 데이터베이스 서버의 CPU 활용은 거의 1시간 동안 80퍼센트로 유지되었다.
데이터베이스 서버의 일부 디스크는 낮 시간에 주기적으로 100퍼센트 사용된다.

정상적인 데이터베이스 성능 튜닝에 다음 단계가 들어있다.

데이터베이스 서버 정보 수집
데이터베이스 사용 정보 수집
데이터베이스 정보 분석
튜닝 활동 설계
튜닝 결과 구현 및 평가

다음 섹션은 이들 각 단계에 대해 자세히 논의한다.

위로

데이터베이스 서버 정보 수집

이 단계 도중에 데이터베이스 서버와 데이터베이스 서버의 구성을 위해 하드웨어 및 소프트웨어 정보를 수집한다. 다음은 수집하는 데 필요한 일부 정보이다.

데이터베이스 서버 유형
CPU의 숫자와 유형
메모리의 양
디스크 드라이브의 숫자와 제조업체
스토리지 서브시스템의 유형 및 제조업체
스토리지 서브시스템의 구성
운영 체제 및 데이터베이스 정보
서버에서 실행 중인 각 인스턴스에 대한 db2look 도구로부터 출력 (db2look –d dbname –e –o outputfile)
각 테이블스페이스 및 컨테이너의 설명 (db2 list tablespaces 및 db2 list tablespace containers for tablespacename show details)

많은 정보가 더 많은 도움을 준다는 것을 기억한다. 여기에서 수집한 어떤 정보가 나중에 독자의 분석에 크게 도움이 될 수 있다. 예를 들어, 사례 연구의 경우, db2look 및 테이블스페이스 정보는 디스크 사용 중 문제가 나타난 원인이 무엇인지 설명했다. 즉, 모든 사용자 데이터 테이블은 동일한 디스크에 위치한 동일한 테이블스페이스에서 작성되었다.

위로

데이터베이스 사용 정보 수집

데이터베이스 사용 정보를 수집하는 두 가지 방법은 스냅샷을 찍는 것과 이벤트를 모니터하는 것이다. 두 가지 메소드 모두 버퍼 풀 활동, 잠금 상태, SQL문 정보 및 기타 등등의 실시간 데이터베이스 사용 정보를 수집한다. 하지만, 이들은 다른 모니터링 메커니즘을 보유하며, 이는 다른 상황에서 이들이 사용될 수 있음을 의미한다.

스냅샷은 이름이 암시하는 대로 특정 시점에 데이터베이스에 대한 즉각적인 정보를 캡처한다. 정기적인 간격으로 찍힌 스냅샷은 경향을 관찰하거나 잠재적 문제를 예측하는 데 사용될 수 있다. 이는 알려진 기간 또는 애드혹 데이터베이스 상태 검사 도중에 발생하는 문제를 해결하는 데 유용하다. 스냅샷을 사용하는 것은 이벤트 모니터를 사용하는 것보다 자원 소모가 적다.

한편, 이벤트 모니터는 이벤트 구동형이다. 사전정의된 기간 내에 이벤트 모니터가 지정된 이벤트가 발생하는 어느 시점에나 레코드를 작성한다. 스냅샷과 비교하면 이벤트 모니터는 데이터베이스 오브젝트 기반 통계를 더 많이 제공할 수 있다(예를 들어, 데이터베이스, 테이블, 테이블스페이스 및 기타 등등에 대한 통계). 모니터링은 모니터된 시간 동안 전체 데이터베이스 사용을 수집할 정도로 지속적이다. 이러한 지속성으로 인해 소모되는 자원은 대상 시스템이 가장 많이 사용 중일 때 엄청나게 클 수 있다. 프로덕션 시스템이 조사 중일 때 모니터링이 시스템을 손상시키지 않도록 노력해야 한다.

모니터링의 성능 영향을 낮추는 방법을 알아보기 전에 이벤트 모니터를 설정할 때 옵션이 무엇인지 살펴보자. 즉, 이는 테이블 이벤트 모니터, 파일 이벤트 모니터 및 파이프 이벤트 모니터이다. 이름이 암시하는 대로 이벤트 모니터는 이벤트 레코드가 작성되는 장소에 따라 서로 구별된다. 즉, 이는 SQL 테이블과 파일에 작성하거나 이름 지정된 파이프를 사용하여 작성하는 것이다. 파이프 이벤트 모니터가 실제로 자주 사용되지 않기 때문에(프로그램은 이름 지정된 파이프에서부터 데이터를 읽는 데 필수임) 이 기사는 테이블과 파일 이벤트 모니터에 주목한다.

표 1. 이벤트 모니터의 성능 영향을 낮추는 팁

카테고리	옵션	사용법
테이블 및 파일 팁	Eventtype	`CREATE EVENT MONITOR emon1 FOR STATEMENTS`
	Eventtype	STATEMENTS 모니터가 가장 큰 성능 위협이다. 성능이 관심사라면 STATEMENTS 모니터는 자체적인 일괄처리를 형성하기 위해 다른 모니터에서 분리되어야 한다.
	Buffersize	`CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BUFFERSIZE 8`
	Buffersize	테이블 삽입의 오버헤드 또는 파일 쓰기를 줄이기 위해 이벤트 레코드는 먼저 버퍼 공간에 작성된다. 버퍼 공간이 가득 차면, 이벤트 레코드는 이벤트 테이블이나 파일로 이동된다. 성능 목적상 고도로 활성화된 이벤트 모니터는 상대적으로 비활성화된 이벤트 모니터보다 더 큰 버퍼가 있어야 한다. BUFFERSIZE는 버퍼 크기의 용량(4K 페이지)을 표시한다. 공간이 데이터베이스 모니터 힙에서부터 할당되기 때문에 모든 이벤트 모니터의 결합된 용량은 최대 크기를 초과하지 말아야 한다(값을 찾기 위해 `db2 get dbm cfg \| grep MON_HEAP_SZ` 사용).
	Blocked/Nonblocked	`CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BLOCKED/NONBLOCKED`
	Blocked/Nonblocked	BLOCKED가 설정되면, 이벤트 레코드가 버퍼 공간에서 테이블/파일로 이동될 때(버퍼 공간이 가득 참) 이벤트를 생성하는 각 에이전트는 이동이 완료되도록 대기한다. 이러한 방법으로 이벤트 데이터의 손실 없음이 보장될 수 있다. 하지만 이 또한 데이터베이스 성능을 저하시킬 것이다. 그러므로, 성능이 관심사일 때, 이벤트 모니터는 NONBLOCKED로 설정되어야 한다. 데이터 손실이 있겠지만, 데이터베이스 성능으로 영향은 최소화된다.
테이블별 팁	Logic data groups monitor elements	`CREATE EVENT MONITOR emon1 FOR DEADLOCKS WITH DETAILS WRITE TO TABLE DLCONN (EXCLUDES(agent_id, lock_wait_start_time)), DLLOCK (INCLUDES(lock_mode, table_name))`
	Logic data groups monitor elements	각 이벤트 모니터는 여러 데이터베이스 테이블을 사용하여 수집된 데이터를 저장한다. 예를 들어, STATEMENTS 이벤트 모니터는 명령문 데이터를 수집하고 테이블에 이를 저장한다. 즉, 이는 CONNHEADER, STMT, SUBSECTION 및 CONTROL이다. 불필요한 이벤트 테이블과 필드가 수집되는 것을 배제하여 성능 영향은 최소화될 수 있다.
	Tablespace	`CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN (TABLE conns, IN mytablespace)`
	Tablespace	사용 중인 디스크가 성능 병목현상일 때, 디스크 쓰기가 더 균등하게 분배되기 위해 분리된 테이블스페이스와 분리된 디스크에서 이벤트 테이블을 찾는다.
	PCTDEACTIVATE	`CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN PCTDEACTIVATE 90`
	PCTDEACTIVATE	PCTDEACTIVATE 옵션은 이벤트 모니터의 스토리지 사용을 제어하는 데 사용된다. 이는 백분율 숫자로 정의된다. 예를 들어, PCTDEACTIVATE가 90으로 설정되면, 이벤트 테이블이 위치한 테이블스페이스가 90퍼센트 용량에 도달할 때, 이벤트 모니터가 자동으로 비활성화된다. 이러한 옵션은 데이터베이스 관리형 테이블스페이스(DMS)에만 사용될 수 있다.
파일별 팁	Maxfiles/Maxfilesize	`EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO FILE myfile MAXFILES 10 MAXFILESIZE 32`
파일별 팁	Maxfiles/Maxfilesize	PCTDEACTIVATE 옵션과 마찬가지로, MAXFILES와 MAXFILESIZE는 어느 이벤트 모니터로 얼마나 많은 스토리지가 권한 지정되는지를 제어하는 데 함께 사용될 수 있다. MAXFILESZIE는 하나의 이벤트 모니터 파일이 포함할 수 있는 4K 페이지를 최대치로 정의한다. 최대에 도달할 때, 새 파일은 수신 이벤트 데이터를 저장하기 위해 작성된다. 이는 파일의 수가 사전정의된 MAXFILES 값에 도달할 때까지 지속하며, 이는 이벤트 모니터가 자동으로 비활성화되는 시점이다.

다음 두 가지 접근방식을 적용하여 프로덕션 성능에 영향을 주는 위험을 한층 더 낮출 수 있다.

프로덕션 환경에서 이벤트 모니터링을 수행하기 전에 테스트 환경에서 테스트 실행을 수행하거나 프로덕션 환경에서 단기간 평가판 실행을 수행하여 실제 성능 영향을 평가한다.
각 성능 표시기의 임계값을 설정하고(예를 들어, CPU 활용: 90퍼센트) 모니터링 기간 동안 이러한 표시기를 긴밀하게 모니터한다. 임계값이 초과되면, 모니터링을 즉시 중지한다.

위로

데이터베이스 정보 분석하기

모든 정보가 수집되면 이 섹션에서 설명한 다양한 분석을 수행할 수 있다.

SQL문 분석

SQL문 분석의 주요 정보 자원은 명령문 이벤트 모니터이다. 이벤트가 이벤트 파일을 사용하여 모니터되면, db2evmon은 리스트 1과 같이 출력을 형식화하는 데 사용될 수 있다.

리스트 1. db2evmon 명령

 
db2evmon –path event_files_directory > output_filename

결과 항목은 리스트 2에 나타난다.

리스트 2. 샘플 명령문 이벤트 항목

            	
1) Statement Event ...
   Appl Handle: 53793
   Appl Id: *LOCAL.db2inst1.101126060601
   Appl Seq number: 00003
            	   
   Record is the result of a flush: FALSE
   -------------------------------------------
   Type     : Dynamic
   Operation: Describe
   Section  : 201
   Creator  : NULLID
   Package  : SQLC2G15
   Consistency Token  : AAAAALIY
   Package Version ID  : 
   Cursor   : SQLCUR201
   Cursor was blocking: TRUE
   Text     : select * from schema.table
   -------------------------------------------
   Start Time: 11/26/2010 15:06:35.641755
   Stop Time:  11/26/2010 15:06:35.665380
   Elapsed Execution Time:  0.023625 seconds
   Number of Agents created: 1
   User CPU: 0.003768 seconds
   System CPU: 0.000000 seconds
   Statistic fabrication time (milliseconds): 0
   Synchronous runstats time  (milliseconds): 0
   Fetch Count: 62
   Sorts: 0
   Total sort time: 0
   Sort overflows: 0
   Rows read: 62
   Rows written: 0
   Internal rows deleted: 0
   Internal rows updated: 0
   Internal rows inserted: 0
   Bufferpool data logical reads: 1
   Bufferpool data physical reads: 0
   Bufferpool temporary data logical reads: 0
   Bufferpool temporary data physical reads: 0
   Bufferpool index logical reads: 0
   Bufferpool index physical reads: 0
   Bufferpool temporary index logical reads: 0
   Bufferpool temporary index physical reads: 0
   Bufferpool xda logical page reads: 0
   Bufferpool xda physical page reads: 0
   Bufferpool temporary xda logical page reads: 0
   Bufferpool temporary xda physical page reads: 0
   SQLCA:
      sqlcode: 0
      sqlstate: 00000

Text 행은 SQL문이 실행되었음을 보여준다. Elapsed Execution Time은 이 SQL문을 실행하는 데 얼마나 오래 걸리는지 표시한다. 누적된 실행 시간은 동일한 명령문의 모든 경과 실행 시간을 합하여 각 SQL문에 계산될 수 있다. 그러면 가장 높게 누적된 실행 시간을 보유한 명령문은 SQL문 분석의 후보이다.

IBM은 SQL문을 분석하는 도구 시리즈를 제공한다. Visual Explain, db2exfmt 및 db2expln은 각 명령문의 액세스 계획을 검토하는 데 유용하다. db2advis 도구는 새 인덱스가 실행 성능을 최적화하기 위해 작성되어야 하는지 여부에 대해 권장사항을 제공한다.

교착 상태 분석

교착 상태 이벤트 모니터는 교착 상태가 어떻게 발생하고 각 발생의 히스토리에 대한 자세한 정보를 제공한다. 리스트 3은 샘플 교착 상태 이벤트 항목을 보여준다.

리스트 3. 샘플 교착 상태 이벤트 항목

            	
3382) Deadlocked Connection ...
  Deadlock ID:   1
  Participant no.: 2
  Participant no. holding the lock: 1
  Appl Id: 10.207.4.51.40897.100826202041
  Appl Seq number: 03988
  Tpmon Client Workstation: server01
  Appl Id of connection holding the lock: 10.207.4.51.39361.100826202035
  Seq. no. of connection holding the lock: 00001
  Lock wait start time: 08/27/2010 10:38:13.168058
  Lock Name       : 0x020012032900E9161100000052
  Lock Attributes : 0x00000000
  Release Flags   : 0x20000000
  Lock Count      : 1
  Hold Count      : 0
  Current Mode    : none
  Deadlock detection time: 08/27/2010 10:38:22.765817
  Table of lock waited on      : table
  Schema of lock waited on     : schema   
  Data partition id for table  : 0
  Tablespace of lock waited on : USERSPACE1
  Type of lock: Row
  Mode of lock: X   - Exclusive
  Mode application requested on lock: U   - Update
  Node lock occured on: 0
  Lock object name: 73398812713
  Application Handle: 957
  Deadlocked Statement:
    Type     : Dynamic
    Operation: Fetch
    Section  : 1
    Creator  : NULLID
    Package  : SYSSH200
    Cursor   : SQL_CURSH200C1
    Cursor was blocking: FALSE
    Text     : SELECT value1, value2 FROM schema.table WHERE value1 = ? for update with rs
  List of Locks:
	……
      Lock Name                   : 0x020012032900EC161100000052
      Lock Attributes             : 0x00000000
      Release Flags               : 0x00000080
      Lock Count                  : 1
      Hold Count                  : 0
      Lock Object Name            : 73399009321
      Object Type                 : Row
      Tablespace Name             : table
      Table Schema                : schema     
      Table Name                  : EXCLUSION
      Data partition id           : 0
      Mode                        : U   - Update
	……
13384) Deadlocked Connection ...
  Deadlock ID:   1
  Participant no.: 1
  Participant no. holding the lock: 2
  Appl Id: 10.207.4.51.39361.100826202035
  Appl Seq number: 09195
  Tpmon Client Workstation: server01
  Appl Id of connection holding the lock: 10.207.4.51.40897.100826202041
  Seq. no. of connection holding the lock: 00001
  Lock wait start time: 08/27/2010 10:38:13.166513
  Lock Name       : 0x020012032900EC161100000052
  Lock Attributes : 0x00000000
  Release Flags   : 0x40000000
  Lock Count      : 1
  Hold Count      : 0
  Current Mode    : none
  Deadlock detection time: 08/27/2010 10:38:22.787777
  Table of lock waited on      : table
  Schema of lock waited on     : schema     
  Data partition id for table  : 0
  Tablespace of lock waited on : USERSPACE1
  Type of lock: Row
  Mode of lock: U   - Update
  Mode application requested on lock: U   - Update
  Node lock occured on: 0
  Lock object name: 73399009321
  Application Handle: 951
  Deadlocked Statement:
    Type     : Dynamic
    Operation: Execute
    Section  : 1
    Creator  : NULLID
    Package  : SYSSH200
    Cursor   : SQL_CURSH200C1
    Cursor was blocking: FALSE
    Text     : UPDATE schema.table SET value2 = ?, value3 = ? WHERE value1 IN (?,?)
  List of Locks:
      Lock Name                   : 0x020012032900E9161100000052
      Lock Attributes             : 0x00000000
      Release Flags               : 0x40000000
      Lock Count                  : 1
      Hold Count                  : 0
      Lock Object Name            : 73398812713
      Object Type                 : Row
      Tablespace Name             : USERSPACE1
      Table Schema                : schema     
      Table Name                  : table
      	……

리스트 3은 어느 두 가지 잠금이 교착 상태, 각 잠금의 유형 및 해당 SQL문에 수반되는지를 보여준다. 관련 명령문을 수정하면 교착 상태의 발생이 줄어들 수 있다.

버퍼 풀 분석

독자는 버퍼 풀 이벤트 모니터가 제공하는 정보를 사용하여 버퍼 풀 분석을 수행할 수 있으며, 이는 리스트 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

읽기/쓰기 숫자는 테이블스페이스 또는 테이블이 어느 정도로 사용 중인지 표시한다. 가장 자주 액세스되는 테이블이 다른 테이블과 동일한 디스크에 있으면, 별도의 디스크로 위치를 바꾸어 디스크 읽기와 쓰기를 더 균등하게 분배하는 것이 좋다. 또 다른 솔루션은 여러 실제 디스크에 걸쳐 테이블 내에서 데이터를 분배하는 것이다.

위로

튜닝 활동 설계

수집한 모든 정보를 바탕으로 실제 튜닝 활동을 설계할 수 있다. 하지만, 각 튜닝 활동은 연관된 위험 및 비용이 있다. 신중한 위험 및 투자수익률 분석이 솔루션을 구현하기 위해 결정하기 전에 수행되어야 한다. 분석은 결과적으로 튜닝 활동을 즉시 구현 방식, 조건적 구현 방식 또는 구현 방식 없음으로 분류할 수 있다. 사례 연구의 경우, 표 2는 튜닝 의사결정을 내리는 데 도움을 주도록 작성되었다.

표 2. 튜닝 의사결정 테이블

튜닝 활동	성능 개선	위험	투자수익률	의사결정	조건
새 인덱스 추가	낮음	낮음	낮음	즉시	해당사항 없음
CPU 업그레이드	중간	낮음	중간	조건부	최대 CPU 활용이 90퍼센트에 도달함
데이터베이스 테이블 재배치	높음	높음	중간	조건부	높은 CPU 대기 I/O가 관찰됨

위로

튜닝 결과 구현 및 평가

튜닝 활동이 테스트된 후에 프로덕션 환경으로 이를 배치할 수 있다. 튜닝 결과를 평가하기 위해 튜닝으로부터 성능 개선이 얼마나 확보되었는지 평가하기 위해 NMOM을 다시 사용할 수 있다.

사례 연구의 경우, 튜닝 결과가 이해 당사자(stakeholder)들에게 시연된 후 새 인덱스 추가 옵션만 선택되었다. 다른 옵션은 이해 당사자들에게 합리적인 투자수익률을 제시하지 않았다. 이들은 다른 옵션이 너무 비용이 많이 들거나 너무 위험하다고 결정했다. 이해 당사자들은 절대적으로 필요할 때에만 취해진 이러한 조치를 취하려고 했다.

사례 연구는 모두가 아니라 개선을 위해 가치 있는 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는 초기 db2advis 결과를 튜닝 후에 달성된 이러한 결과들과 비교한다.

표 3. db2advis 결과

데이터베이스	SQL문	실행 시간(초)	개선(%)	절약된 시간(초)
DB1	SQL1	188	0.00%	0
DB1	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 도구의 결과에 따르면 가장 높은 네 가지 절약된 시간(Saved Time) SQL 튜닝 활동이 구현되었다. 두 번째 NMON 분석은 튜닝 결과를 평가하기 위해 수행되었다. 예상한 대로, 최대 CPU 활용에서 엄청난 감소는 없었다. 하지만, 최대 시간은 약 55분에서 50분 이하로 줄어들었다. 이해 당사자들은 결과에 매우 만족했다.

물론, 신중하게 해야 할 일은 CPU 활용 및 CPU 대기 I/O 데이터를 계속 모니터하는 것이다. 이러한 숫자들이 사전정의된 임계값에 도달하면 사례 연구에서 추가 조치를 취할 것이다.

위로

결론

이 기사는 DB2 for Linux, UNIX 및 Windows 데이터베이스에서 성능 문제를 조사하고 최소의 위험으로 최대의 결과를 야기할 가능성이 있는 개선 사항을 프로덕션 시스템에 적용하기 위한 방법론을 시연했다.

참고자료

교육

DB2 for Linux, UNIX 및 Windows용 developerWorks 참고자료 페이지를 방문하여 기사와 튜토리얼을 읽고 다른 참고자료와 연계하여 DB2 기술을 확장시키자.
developerWorks Information Management 영역에서는 Information Management에 대한 정보를 제공한다. 기술 자료, 사용법 기사, 교육, 다운로드, 제품 정보 등을 찾아볼 수 있다.
developerWorks 기술 행사 및 웹 캐스트를 통해 최신 정보를 얻을 수 있다.
Twitter의 developerWorks 페이지를 살펴보자.

제품 및 기술

DB2 for Linux, UNIX, and Windows의 무료 평가판을 다운로드하자.
현재는 DB2를 무료로 사용할 수 있다. 커뮤니티용 DB2 Express Edition의 무료 버전인 DB2 Express-C를 다운로드하자. 이 제품은 DB2 Express Edition과 동일한 핵심 데이터 기능과 애플리케이션 빌드 및 전개를 위한 안정적인 환경을 제공한다.
developerWorks에서 직접 다운로드할 수 있는 IBM 시험판 소프트웨어를 사용하여 후속 개발 프로젝트를 구현해 보자.

토론