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

사례 연구: 고성능을 위한 DB2 튜닝(1) * 출처: http://www.kdug.kr * 저자: Bin Qiao 님 튜닝 | 2011-08-16 09:32:51

여러 통계는 데이터베이스를 튜닝한 시스템이 그렇지 않은 시스템보다 20% 더 높은 성능을 낼 수 있음을 보여줍니다. 그러나 프로덕션 시스템을 잘못 튜닝하면 위험할 수 있습니다. 이 문서에서는 Linux®, UNIX® 및 Windows®용 IBM DB2® 환경에서 수행할 수 있는 데이터베이스 성능 튜닝 사례에 대해 다룹니다. 데이터베이스 성능 튜닝에 직접 적용할 수 있는 유용한 기술을 얻을 수 있을 것입니다. 원문 게재일 : 20011년 7월 28일 원문 출처 : http://ibm.co/mXpvV3 목차 l 개요 l 문제 발견 l 데이터베이스 서버 정보 수집 l 데이터베이스 사용 정보 수집 l 데이터베이스 정보 분석 → SQL 문 분석 → 교착 상태 분석 → 버퍼풀 분석 → 메모리 분석 → 테이블스페이스 및 테이블 분석 l 튜닝 활동 설계 l 튜닝 결과 구현 및 평가 l 결론 개요 잘못 설계된 시스템은 가동 직후부터 성능 문제가 발생할 수 있습니다. 튜닝이 잘된 시스템조차도 장기간 작동시키거나 주요 기능을 변경하게 되면 성능 문제에 부딪힐 수 있습니다. 시스템 튜닝은 시스템 관리자에게 피할 수 없는 업무입니다. 특히 대다수 애플리케이션 시스템의 주요한 부분인 데이터베이스 성능 튜닝은 매우 중요한 작업입니다. 여러 통계들은 데이터베이스를 튜닝한 시스템이 그렇지 않은 시스템보다 20% 더 높은 성능을 낼 수 있음을 보여줍니다. 그러나 프로덕션 시스템을 잘못 튜닝하면 위험할 수 있습니다. 이 문서에서는 Linux, UNIX 및 Windows용 IBM DB2 환경에서 수행할 수 있는 데이터베이스 성능 튜닝 사례에 대해 다룹니다. 이 사례 연구에서 튜닝한 시스템은 백엔드 데이터베이스로 DB2를 사용하는 JIRA Enterprise 패키지 기반의 워크플로우 애플리케이션입니다. 이 애플리케이션은 2가지 모드, 즉 야간 배치 모드와 주간 OLTP 모드로 작동합니다. 야간 배치 시간 동안은 일반 텍스트 파일 형식의 외부 데이터를 데이터베이스로 전송하는 일련의 쉘 스크립트가 실행됩니다. 주간 OLTP 시간 동안에는 JIRA에 정의된 이 비즈니스 데이터 워크플로우를 운영자가 처리합니다. 고객은 이 애플리케이션을 1년 정도 가동시킨 후 문제 발생 건수가 낮아지지 않음을 발견했습니다. 조사 결과, 데이터베이스 교착 및 JIRA 파일 잠금 시간초과 같은 성능 문제가 일부 문제의 원인으로 밝혀졌습니다. 워크로드는 매년 5%씩 증가하는 것으로 계약되어 있었습니다. 시스템 성능을 높이지 못했다면 점점 더 많은 성능 문제점이 발생 했을 것입니다. 이제 성능 튜닝은 필수였습니다. 문제 발견 시스템 성능 튜닝을 수행하기 위해서는 먼저 성능 문제의 기원이 어디인지 알아내야 합니다. Linux에서 사용되는 Nigel's performance MONitor (NMON) 은 CPU 활용률, 메모리 활용률, 디스크 사용률, 자원 사용량이 많은 주요 프로세스 등 핵심적인 성능 데이터를 수집하기에 유용한 툴입니다. NMON은 시스템의 각 서버에 대한 성능 정보를 수집하는 데 사용됩니다. 수집된 NMON 데이터를 검토한 결과 2가지 성능 문제가 식별되었습니다. 야간 배치 시간에 데이터베이스 서버의 CPU 활용률이 약 1시간 동안 80%에 머무름 데이터베이스 서버의 일부 디스크가 정기적으로 하루 종일 100% 사용됨 데이터베이스 성능 튜닝은 보통 다음 단계로 구성됩니다. 데이터베이스 서버 정보 수집 데이터베이스 사용 정보 수집 데이터베이스 정보 분석 튜닝 활동 설계 튜닝 결과 구현 및 평가 다음 섹션에서 각 단계를 자세히 살펴봅니다. 데이터베이스 서버 정보 수집 이 단계에서는 데이터베이스 서버의 하드웨어 및 소프트웨어 정보와 데이터베이스 구성을 수집합니다. 다음과 같은 정보를 수집해야 합니다. 데이터베이스 서버 유형 CPU 유형 및 개수 메모리 크기 디스크 드라이브 개수 및 제조업체 스토리지 서브시스템 유형 및 제조업체 스토리지 서브시스템 구성 운영 체제 및 데이터베이스 정보 서버에서 실행 중인 각 인스턴스에 대한 db2look 툴 출력(db2look –d dbname –e –o outputfile) 각 테이블스페이스 및 해당 컨테이너에 대한 설명(db2 list tablespaces and db2 list tablespace containers for tablespacename show details) 정보가 많으면 많을수록 좋습니다. 이 단계에서 수집한 모든 정보는 이후 분석 단계에 큰 도움이 될 수 있습니다. 예를 들어, db2look 및 테이블스페이스 정보는 디스크 사용 문제의 원인으로 모든 사용자 데이터 테이블이 동일한 디스크의 동일한 테이블스페이스에 생성되어 있었는지를 파악할 수 있습니다. 데이터베이스 사용 정보 수집 데이터베이스 사용 정보를 수집하는 두가지 방법으로 스냅샷을 생성과 이벤트 모니터링이 있습니다. 두 방법 모두 버퍼풀 활동, 잠금 상태, SQL 문 정보 같은 실시간 데이터베이스 사용 정보를 수집합니다. 그러나 이 두 방법은 모니터링 메커니즘이 서로 다르기 때문에 해당 상황에 따라 적합한 방법을 선택해서 사용해야 합니다. 스냅샷은 말 그대로 특정 시점에 데이터베이스의 인스턴트 정보를 캡처하는 방법입니다. 주기적으로 캡처된 스냅샷을 사용하여 동향을 관찰하거나 잠재적인 문제점을 예측할 수 있습니다. 스냅샷은 특정 기간에 발생한 문제점을 해결하거나 임시 데이터베이스 상태 검사에 유용합니다. 스냅샷은 이벤트 모니터보다 자원 소모량이 적습니다. 반면에 이벤트 모니터는 이벤트를 기반으로 합니다. 이벤트 모니터는 지정된 기간에 특정 이벤트가 발생할 때마다 레코드를 작성합니다. 스냅샷에 비해, 이벤트 모니터는 데이터베이스 오브젝트 기반의 통계(예를 들어 데이터베이스, 테이블, 테이블스페이스에 대한 통계)를 더 많이 제공할 수 있습니다. 모니터링은 모니터 기간 동안 전체 데이터베이스의 사용 정보를 수집을 지속적으로 수행합니다. 이 지속성 때문에, 대상 시스템의 사용률이 높은 경우 모니터링에 엄청난 양의 자원이 소모될 수 있습니다. 프로덕션 시스템을 조사할 때는 모니터링 때문에 시스템이 손상되는 일이 없도록 방지해야 합니다. 모니터링으로 인한 성능 저하를 줄이는 방법을 살펴보기 전에 먼저 여러 이벤트 모니터 설정 옵션, 즉 테이블 이벤트 모니터, 파일 이벤트 모니터 및 파이프 이벤트 모니터에 대해 알아보겠습니다. 각각의 이름에서 알 수 있듯이, 이러한 이벤트 모니터는 이벤트가 작성되는 위치(SQL 테이블, 파일 또는 명명된 파이프)에 따라 서로 식별됩니다. 파이프 이벤트 모니터는 명명된 파이프에서 데이터를 읽어올 프로그램이 필요한 관계로 실제로 자주 사용되지 않습니다. 따라서 이 문서에서는 테이블 모니터와 파일 이벤트 모니터를 중점적으로 다룹니다. 표 1. 이벤트 모니터로 인한 성능 저하를 줄이는 방법 구분 옵션 사용법 테이블 및 파일 팁 Eventtype CREATE EVENT MONITOR emon1 FOR STATEMENTS STATEMENTS 모니터는 성능에 가장 큰 위협입니다. 성능이 염려되는 경우 STATEMENTS 모니터를 다른 모니터에서 분리하여 자체 배치 형식으로 구성해야 합니다. Buffersize CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BUFFERSIZE 8 테이블 삽입 또는 파일 쓰기에 대한 오버헤드를 줄이기 위해, 이벤트 레코드는 먼저 버퍼 공간에 기록됩니다. 버퍼 공간이 가득 차면 이벤트 테이블 또는 파일로 이벤트 레코드가 이동됩니다. 성능상의 이유로, 활동적인 이벤트 모니터는 상대적으로 활동이 적은 이벤트 모니터보다 더 큰 버퍼를 필요로 합니다. BUFFERSIZE는 버퍼 공간의 용량(4K 페이지 단위)을 나타냅니다. 버퍼 공간은 데이터베이스 모니터 힙에서 할당되므로 모든 이벤트 모니터의 총 용량이 최대 크기를 넘어서는 안됩니다. 해당 값을 확인하려면 use db2 get dbm cfg | grep MON_HEAP_SZ를 사용하십시오. Blocked/Nonblocked CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE BLOCKED/NONBLOCKED BLOCKED를 설정하면, 버퍼 공간이 가득 차서 버퍼 공간에 있던 이벤트 레코드가 테이블/파일로 이동될 경우 이벤트를 생성하는 각 에이전트는 모든 레코드가 이동 완료될 때까지 대기합니다. 이 방법을 사용하면 이벤트 데이터의 유실을 완전히 방지할 수는 있습니다. 하지만 데이터베이스 성능이 저하될 것입니다. 따라서 성능이 염려될 경우 이벤트 모니터를 NONBLOCKED로 설정해야 합니다. 데이터는 유실될 수 있지만 데이터베이스 성능 저하를 최소화할 수 있습니다. 테이블별 팁 논리 데이터 그룹 모니터 요소 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)) 각 이벤트 모니터는 수집된 데이터를 여러 개의 데이터베이스 테이블에 저장합니다. 예를 들어, STATEMENTS 이벤트 모니터는 명령문 데이터를 수집하여 CONNHEADER, STMT, SUBSECTION 및 CONTROL 테이블에 저장합니다. 수집된 데이터에서 불필요한 이벤트 테이블 및 필드를 제거하면 성능 저하를 최소화할 수 있습니다. 테이블스페이스 CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN (TABLE conns, IN mytablespace) 디스크로 인해 성능 병목이 발생하면 디스크 쓰기가 고르게 분배될 수 있도록 분리된 테이블스페이스 및 디스크에 이벤트 테이블을 배치하십시오. PCTDEACTIVATE CREATE EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO TABLE CONN PCTDEACTIVATE 90 PCTDEACTIVATE 옵션을 사용하면 이벤트 모니터의 스토리지 사용량을 제어할 수 있습니다. 값은 백분율로 정의합니다. 예를 들어, PCTDEACTIVATE를 90으로 설정할 경우 이벤트 테이블이 위치한 테이블스페이스 용량이 90%에 도달하면 이벤트 모니터가 자동으로 비활성화됩니다. 이 옵션은 데이터베이스 관리 테이블스페이스(DMS)에만 사용할 수 있습니다. 파일별 팁 Maxfiles/Maxfilesize EVENT MONITOR emon1 FOR CONNECTIONS WRITE TO FILE myfile MAXFILES 10 MAXFILESIZE 32 MAXFILES 및 MAXFILESIZE는 PCTDEACTIVATE와 유사한 옵션으로, 이벤트 모니터에 사용할 스토리지 공간을 제어하는 데 사용됩니다. MAXFILESZIE는 이벤트 모니터 파일 1개에 4K 페이지가 최대 몇 개까지 포함될 수 있는지 정의합니다. 이 최대 수에 도달하면 수신되는 이벤트 데이터를 저장하기 위해 새 파일이 작성됩니다. 이 동작은 전체 파일 수가 사용자가 사전에 정의한 MAXFILES 값에 도달할 때까지 계속되며 이때 이벤트 모니터도 자동으로 비활성화됩니다. 아래 두 가지 방법을 적용하면 프로덕션 성능 저하의 위험을 한층 더 줄일 수 있습니다. 프로덕션 환경에서 이벤트 모니터링을 수행하기 전에, 먼저 테스트 환경에서 테스트 운영을 수행하거나 프로덕션 환경에서 단기적인 시험 운영을 통해 실제 성능 저하를 평가합니다. 각 성능 측정 지표에 대한 임계값을 설정(예를 들어 CPU 활용률 – 90%)하고 모니터링 기간 동안 해당 지표를 면밀히 모니터합니다. 임계값이 초과되면 즉시 모니터링을 중단합니다. 데이터베이스 정보 분석 위에서 수집한 모든 정보를 아래의 다양한 분석 방법으로 분석할 수 있습니다. SQL 문 분석 SQL 문 분석에 주로 사용되는 정보 자원은 명령문 이벤트 모니터입니다. 이벤트 파일을 사용해서 이벤트를 모니터할 경우, 목록 1에서와 같이 db2evmon명령으로 출력 형식을 지정할 수 있습니다. 목록 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은 교착 상태에 빠진 2개의 잠금, 각 잠금의 유형 및 해당 SQL 문을 보여줍니다. 관련된 명령문을 수정하여 교착 상태의 발생 횟수를 줄일 수 있습니다. * 다음 회에서 데이터베이스 정보 분석 방법 중 버퍼풀 분석, 메모리 분석, 테이블스페이스 및 테이블 분석에 대해 알아보고 튜닝 활동 설계 및 튜닝 결과 구현 및 평가 에 대한 설명이 이어집니다. 리소스 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 평가판 소프트웨어로 다음 개발 프로젝트를 구축하십시오.