STMM과Table Partition 소개 - dbguide.net 2: Enable self-tuning ... Analyzing the db2diag.log and...

$: STMM과Table Partition 소개 - dbguide.net 2: Enable self-tuning ... Analyzing the db2diag.log and STMM log for changes ... \\db2diag.log$
IBM Confidential

The Best Reliable Partner for High Availability

© Copyright IBM Corporation 2008

STMM과 Table Partition 소개

박윤정 전문위원

[email protected]

**‘2008 하반기효과적인시스템관리를위한기술세미나

IBM Confidential



**‘2008 하반기효과적인시스템관리를위한기술세미나

Self Tuning Memory Manager

IBM Confidential2


© IBM Corporation 2008

Agenda

I. What is Self Tuning Memory Manager ?I. What is Self Tuning Memory Manager ?

II. How to enable STMM ?II. How to enable STMM ?

IV. ScenariosIV. Scenarios

III. Monitor the STMM TuningIII. Monitor the STMM Tuning

V. STMM SummaryV. STMM Summary

IBM Confidential3



What is Self Tuning Memory Manager?DB2 V9의 new feature여러 메모리 구성 매개변수의 값을 자동으로 tuning 함으로써 메모리 구성이 간단

workload 변경을 감지하며, 필요한 메모리 구성 매개변수의 값 및 버퍼 풀의 크기를 조정하여 성능을 최적화

자동으로 메모리 tuning 빈도를 조정

workload를 분석하여 안정이 되면 메모리 튜닝을 위해 필요한 시스템 자원을 check하는 간격이 늘어남

데이터베이스 공유 메모리 (database_memory) 구성 매개변수 내에 정의된 메모리 한계 내에서 조정

동적으로 사용 가능한 자원을 분배

대상 데이터베이스 메모리

buffer pools, package cache, lock memory, sort memory, database shared memory

On-line 메모리 튜닝

DBA 불필요

IBM Confidential4



Autonomic Self Tuning Memory

데이터베이스 공유(Shared) 메모리 매개변수

buffer pools, pckcachesz, locklist, maxlocks, sheapthres_shr, sortheap, database_memory

IBM Confidential5



How does STMM Work?totally new system metrics를 사용

추가 메모리로부터 예상되는 이득을 계산하고, STMM 프로세스에 이를 보고. STMM은 이 수치를 메모리 성능 조정의 기초로 사용하여 요구가 가장 적은 것으로부터 메모리를 취해서 가장 이득이 있을 메모리 영역에 할당

정기적으로 데이터베이스 workload의 변화를 점검

database memory를 조정하도록 되어있다면 free OS 메모리를 사용하기 위해서 STMM은 상시 시스템을 모니터

필요한 만큼 자원의 메모리 사용을 동적으로 적용

sort (sheapthres_shr, sortheap), package cache (pckcachesz), lock list (locklist, maxlocks), buffer pools 에 대한 메모리 구성을 최적화하기 위해 정기적으로 점검

GET DATABASE CONFIGURATION 명령을 사용하거나 Snapshot을 사용하여 현재 메모리 구성 확인

STMM으로 수행된 변경사항은 stmmlog 디렉토리의 STMM 로그 파일에 기록되며 성능 조정 간격마다 각각의 메모리에 대한 지원 요구 요약이 있음

메모리 사용을 최적화하기 위해 신속하게 매개변수가 조정되며, 시스템을 1시간 만에 초기 구성으로부터 성능을 조정할수 있음.

IBM Confidential6



How does STMM Work?

Find another heap

to donate memory

to first heap

Does any heap need more

memoryCan memory be taken from OS

Determine

Tuning

Frequency

Go to sleep

Wake up

Take memory

from OS and

give to heap

시작

끝

Yes

Yes

No

No

IBM Confidential7



STMM modes of operation(1)Mode1 : When DATABASE_MEMORY= AUTOMATIC

데이터베이스 공유 메모리 (DATABASE_MEMORY) = AUTOMATIC는 AIX와 Windows에서만 support

데이터베이스의 전체 메모리 요구사항을 판별한 후 현재 데이터베이스 요구사항에 따라 데이터베이스 공유 메모리에 할당되는 메모리의 양을 늘리거나 줄임

현재 데이터베이스 요구사항이 많고 시스템에 여유 메모리가 충분하면 데이터베이스 공유 메모리가 더 많은 메모리를 소비함.

데이터베이스 메모리 요구사항이 줄어들거나 시스템의 여유 메모리 양이 너무 적어지면 일부 데이터베이스 공유 메모리가 해제됨

DBA는 얼마나 많은 메모리가 할당되었는지 모름

OS가 추가 메모리가 필요할 경우 STMM은 1개 이상의 database heap을 줄여 OS에 반환

반환된 메모리는 같은 시스템상의 application이나 다른 database에서 사용됨

Mode2 : When DATABASE_MEMORY= <NUMERIC VALUE>전체 데이터베이스는 지정된 양의 메모리를 사용하며 필요에 따라 이를 데이터베이스 메모리 사용자에게 분배

STMM은 OS memory와 메모리 교환을 하지 않음. 전체 database memory size는 static

memory heap의 현 setting값은 상대적으로 작고 database memory size를 결정해야 할 때 유용

IBM Confidential8



STMM modes of operation(2)Mode3 : When DATABASE_MEMORY= COMPUTED

총 메모리 양은 데이터베이스 시작 시 데이터베이스 메모리 힙 초기 값의 총계를 기본으로 계산

V9.1의 COMPUTED설정은 V8의 AUTOMATIC 설정과 동일

데이터베이스 메모리 힙 크기의 20%를 overflow buffer로 계산하며 update db cfg 나 alter bufferpool 명령에 의해overflow buffer 사용

db2pd –memset으로 database shared memory 에 남아있는 미사용 메모리의 양을 모니터링 할 수 있음

database shared memory는 늘거나 줄지 않음

automatic으로 설정된 힘 사이에서는 메모리 교환가능

IBM Confidential9



STMM Tip자체 조정이 발생하기 위해서는 최소한 두 개의 메모리 영역에서 자체 조정이 가능해야 함

자체 조정을 수행하면 메모리가 다른 메모리 영역으로 다시 분배되기 때문

예: 잠금 메모리 영역 및 데이터베이스 공유 메모리 영역

단, sortheap 구성 매개변수에 의해 제어되는 메모리는 제외

sortheap 하나만 AUTOMATIC으로 설정되어 있어도 sortheap을 자체 조정

locklist 구성 매개변수를 자체 조정할 수 있게 하려면 maxlocks도 자체 조정이 가능해야 하므로, locklist가AUTOMATIC으로 설정되어 있으면 maxlocks도 AUTOMATIC으로 설정되어 있어야 함

sheapthres_shr 구성 매개변수를 자체 조정할 수 있게 하려면 sortheap도 자체 조정이 가능해야 하므로sheapthres_shr이 AUTOMATIC으로 설정되어 있으면 sortheap도 AUTOMATIC으로 설정되어 있어야 함

sheapthres_shr 또는 sortheap의 자동 조정은 데이터베이스 관리 프로그램 구성 매개변수 sheapthres가 0으로 설정될 때만 허용됨

메모리 자체 조정은 HADR 1차 서버에서만 실행

HADR 시스템에서 메모리 자체 조정이 활성화되면 2차 서버에서는 절대 실행되지 않으며 구성이 제대로 설정된 경우에만 1차서버에서 실행됨

HADR 데이터베이스 역할을 전환하는 명령을 실행할 경우 새로운 1차 서버에서 실행되도록 메모리 자체 조정 조작이 전환

IBM Confidential10



Agenda






IBM Confidential11



How to enable STMM?Command 1: Enable SELF_TUNING_MEM

db2 "update database configuration for <DBNAME> using SELF_TUNING_MEM ON“

Command 2: Enable self-tuning of memory areasdb2 "update database configuration for <DBNAME> using PCKCACHESZ AUTOMATIC”

db2 "update database configuration for <DBNAME> using LOCKLIST AUTOMATIC“

db2 "update database configuration for <DBNAME> using MAXLOCKS AUTOMATIC“

db2 "update database configuration for <DBNAME> using SORTHEAP AUTOMATIC“

db2 "update database configuration for <DBNAME> using SHEAPTHRES_SHR AUTOMATIC“

db2 "update database configuration for <DBNAME> using DATABASE_MEMORY AUTOMATIC“

Command 3: Enable self-tuning of buffer poolsdb2 "create bufferpool bpool8k size AUTOMATIC pagesize 8 k“

db2 “alter bufferpool bpool4k size automatic” : 이미 있는 경우

IBM Confidential12



Enable STMM in ‘active’ or ‘in-active’ modesSTMM이 acitive 나 in-active mode인지 확인 방법

db2 “connect to <DBNAME>”

db2 “get db cfg for <DBNAME> show detail” | grep –i SELF_TUNING_MEM

OutputSelf Tuning Memory (SELF_TUNING_MEM) = OFF

Self Tuning Memory (SELF_TUNING_MEM) = ON (Active)

Self Tuning Memory (SELF_TUNING_MEM) = ON (Inactive)

설명

parameter값이 "ON (Active)“인 경우, memory tuner가 시스템의 메모리를 튜닝 중

parameter값이 "ON (Inactive)“인 경우, parameter가 ON으로 setting되었지만, self-tuning을 위해서는 적어도 2개memory parameter가 enable되어야 하기 때문에 self-tuning은 발생하지 않음

IBM Confidential13



STMM 설정 값 확인

Description Parameter Current Value Delayed Value-----------------------------------------------------------------------------------------------------------------------------------------------------Self tuning memory (SELF_TUNING_MEM) = ON (Active) ON Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(37200) AUTOMATIC(37200) Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(7456) AUTOMATIC(7456) Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(98) Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(5600) AUTOMATIC(5600) Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5000) AUTOMATIC(5000) Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(256) AUTOMATIC(256)

STMM 사용 가능한 memory parametersdb2 get db cfg for <DBNAME> show detail

STMM이 가능한 bufferpool 목록 확인

db2 “select BPNAME, NPAGES from syscat.bufferpools”• STMM 가능할 경우, NPAGE 값은 -2• STMM 불가능할 경우, NPAGE 값은 bufferpool의 현재 크기

• STMM이 가능한 bufferpool의 현재 크기 확인

db2 get snapshot for bufferpools on <DBNAME>• bufferpool의 현재 크기(bp_cur_buffsz 모니터 요소 값) 확인

IBM Confidential14



STMM and Sort Memory공유 정렬의 정렬 힙 임계값 (sheapthres_shr) 또는 정렬 목록 힙 (sortheap) 의 자동 조정은 데이터베이스 관리프로그램 구성 매개변수 정렬 힙 임계값(sheapthres) 가 0 으로 설정될 때만 허용

SHEAPTHRES_SHR : sort 시 사용할 수 있는 데이터베이스 공유 메모리의 총 크기의 한계.

SORTHEAP : private sort에 사용될 개인용 메모리 페이지의 최대수나, shared sort에 사용될 공유 메모리 페이지의 최대수를지정하는 DB 구성 파라미터

• private sort 의 경우, 이 매개변수는 agent private memory에 영향

• shared sort의 경우, 이 매개변수는 데이터베이스 공유 메모리에 영향

• 정렬마다 필요한 만큼 데이터베이스 관리 프로그램에 의해 할당

SHEAPTHRES : DBM 구성 파라미터. 개별 정렬에 사용될 수 있는 총 메모리 양에 대한 인스턴스 전반의 소프트 한계

• 인스턴스의 총 개별 정렬 메모리 사용량이 이 한계에 도달하면 들어오는 추가 개별 정렬 요청에 대해 할당되는 메모리가 현저히감소.

• DB2 버전 9.1에서는 sheapthres 데이터베이스 관리 프로그램 구성 매개변수를 0으로 설정한 경우, 모든 정렬이 공유 메모리를 사용.

• sheapthres 데이터베이스 관리 프로그램 구성 매개변수를 0보다 큰 값으로 설정한 경우 둘 이상의 에이전트에서 액세스할 수있는 concentrator를 실행하는 정렬 또는 SMP 환경에서의 정렬만 공유 메모리를 사용

SHEAPTHRES_SHR 이 AUTOMATIC 이면, STMM이 가능하며 SORTHEAP도 AUTOMATIC 으로 설정됨

SHEAPTHRES 가 0이 아니면 sort에 대한 STMM tuning은 발생치 않음

SMP parallelism 이나 Connection Concentrator를 사용할 때만 DB2는 shared sort memory를 사용

private 과 shared sort memory는 V8.2와 동일하게 동작됨

IBM Confidential15



STMM and Lock MemoryLocklist

V8에서는 동적으로 증가만 가능

V9에서는 Locklist size는 동적으로 증가 또는 감소 가능

Maxlocksconnection당 locklist 사용률을 AUTOMATIC으로 설정 가능

DB2는 전반적인 locklist 사용률이 낮을 때 lock escalation을 피하기 위해 maxlocks를 증가 시킬 수 있음

LOCKLIST가 AUTOMATIC으로 setting이 되어 있으면, Maxlocks도 AUTOMATIC으로 설정해야 함

IBM Confidential16



Agenda






IBM Confidential17



Analyzing the db2diag.log and STMM log for changes시스템 tuning동안 STMM은 수많은 decision을 내리게 되며, 이러한 결정은 configuration parameter와buffer pool 크기의 변경을 하게 됨

STMM에 의한 모든 변경 사항은 db2diag.log과 stmmlog(stmm.0 … 4) 파일에 기록

최근 변경만이 configuration file에 반영

변경 사항에 대한 기록은 db2diag.log 와 STMM log 파일은 분석해야 함

IBM Confidential18



Log Locationsdb2diag.log location

Windows Platform:

<DB2 Install Location>\<Instance>\db2diag.log

Unix Platform:

~sqllib/db2dump/db2diag.log

STMM Log Location

Windows Platform:

<DB2 Install Location>\<Instance>\stmmlog\stmm.0 …

Unix Platform:

~sqllib/db2dump/stmmlog/stmm.0 …4

IBM Confidential19



Monitor the STMM Tuning in db2diag.log fileDBA나 STMM에 의해서 tuning된 configuration parameter의 변경된 log는 db2diag.log 에 기록됨

Example:

2008-09-23-19.54.03.110454-300 I67764A438 LEVEL: Event

PID : 794884 TID : 1 PROC : db2stmm (SAMPLE) 0

INSTANCE: v91pyj01 NODE : 000 DB : SAMPLE

APPHDL : 0-973 APPID: *LOCAL.DB2.080924005103

AUTHID : V91PYJ01

FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20

CHANGE : STMM CFG DB SAMPLE: "Maxlocks" From: "10" <automatic> To: "98" <automatic>

STMM에 의해 변경은 STMM로부터의 변경이라는 것을 알려주기 위해, db2diag.log 파일에 “STMM CFG”라고시작됨

User 가 configuration을 변경한 경우

FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20

CHANGE : CFG DB SAMPLE: "Dbheap" From: "1200" To: "1500"

IBM Confidential20



db2diag Tooldb2diag tool 을 이용하여 db2diag.log 로부터 configuration 변경을 찾아낼 수 있음

configure parameters의 변경분을 filter하기 위해 다음 명령문 사용

db2diag -node 0 -g "changeevent:=CFG DB" db2diag.log2008-09-23-20.09.03.205258-300 I74221A438 LEVEL: EventPID : 794884 TID : 1 PROC : db2stmm (SAMPLE) 0INSTANCE: v91pyj01 NODE : 000 DB : SAMPLEAPPHDL : 0-973 APPID: *LOCAL.DB2.080924005103AUTHID : V91PYJ01FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20CHANGE : STMM CFG DB SAMPLE: "Sortheap" From: "88" <automatic> To: "72" <automatic>

buffer pool changes을 filtering하기 위해서는 다음 명령문 사용

db2diag -g "message:=Altering bufferpool" db2diag.log2008-09-23-20.09.03.205258-300 I74221A438 LEVEL: EventPID : 794884 TID : 1 PROC : db2stmm (SAMPLE) 0INSTANCE: v91pyj01 NODE : 000 DB : SAMPLEAPPHDL : 0-973 APPID: *LOCAL.DB2.080924005103AUTHID : V91PYJ01FUNCTION: DB2 UDB, buffer pool services, sqlbAlterBufferPoolAct, probe:90MESSAGE : Altering bufferpool "IBMDEFAULTBP" From: "250" To: “384"

IBM Confidential21



Monitor the STMM Tuning in stmmlogSTMM tuning에 대한 정보는 stmmlog file에 더 자세히 기록됨

변경하고 action 취하기 전에 STMM은 수집된 통계정보를 기록

STMM tuning decision 이해를 도움

STMM log는 최대 5개 file로 분할되어 최대 10 MB까지 사용

IBM Confidential22



Agenda






IBM Confidential23



Scenarios Where STMM Shines – Memory Varied Workloadsmemory 요구가 dramatically 다양한 workload

Periods with high transaction throughput

Periods with long running transactions or online utilities (online reorg or load)

Load utility는 utility heap 사용(STMM 불가), Load performance는 sort memory나 temp space 에 영향

STMM은 memory 요구사항을 끊임없이 re-evaluate하고 workload에 맞춰 memory heap을 조정

1시간에 60번까지 메모리를 update할 수 있음

현재 실행중인 workload에 기반하여 memory usage를 최적화

manually 비슷한 tuning을 수행하는 것은 어려움

IBM Confidential24



Scenarios Where STMM Shines – Unknown Memory Requirement새로운 데이터베이스나 application 인 경우, usage나 memory 요구사항이 아직 결정되지 않은 경우

DB2를 처음 사용하는 DBA인 경우 도움

STMM은 DB2 kernel level에서 작동하므로 workload 변경에 빨리 대응할 수 있으며, 1시간 정도면 default setting에서 최적화된 구성 값을 제공할 수 있음

no DBA interaction once turned on

숙련된 DBA가 tuning한 비슷한 결과를 얻을 수 있음

workload가 안정되어 보이면 tuning interval을 조정하고 최적 configuration일 경우 자동으로 tuning을 멈춤

IBM Confidential25



STMM Experimental Results – Tuning an OLTP workload370 clients running transaction processing workload

Running on a machine with :128 GB of RAM

2TB database

494 * 36 GB disks

Workload is very sensitive to buffer pool sizing

Each of the 13 buffer pools are started with 1000 pages1000 pages is the default size for a newly created buffer pool

Workload is started and STMM begins tuning

STMM should dramatically improve performance…

IBM Confidential26



STMM in action – Tuning an OLTP workload

Transactions per Minute

IBM Confidential27



STMM in action – BP size during tuning

BP Size in pages

IBM Confidential28



STMM in action – dropped my indexes10 agents executing queries with “order by” clauseAt first, queries use indexes to avoid sortingAfter several iterations some of the indexes are accidentally droppedSimulates DBA error in dropping vital indexesLack of indexes forces sorts to be performedDramatically increases the demand on the sort memoryWith only manual tuning, workload will likely get much slowerSTMM should be able to alleviate some of the burden…

IBM Confidential29



STMM in action – dropped my indexes

order of execution

TPCH Query 21 – After drop index – Average times for the 10 streams

Time in seconds

IBM Confidential30



STMM in action – Two database on the same boxTwo databases running the same workload on the same box

4 clients looping through the 21 queries used in TPC-H

15 GB databases

Running on a machine with 32 GB of RAM

Demand for memory for each database is equal

One database is started first and allowed to use up all the memory

Then, six hours later, the second database is startedAfter both database run together, second database is stopped

STMM should allow for proper sharing of memory…

IBM Confidential31



STMM in action – Two database on the same box

Time (in seconds)

Mem

ory (in 4K pages)

IBM Confidential32



Agenda






IBM Confidential33



STMM – When it is most usefulDBA가 DB2 memory model 에 익숙하지 않은 경우

파악되지 않은 메모리 요구 사항의 workload 인 경우

같은 machine에 여러 대의 database/instances 가 운영 중인 경우

시스템의 여유 메모리가 많은 경우

시스템이 다른 page size의 buffer pool 들을 가진 경우

Workload는 파악이 되었지만 메모리의 요구사항이 시시각각 변경이 되는 경우

IBM Confidential34



STMM – When it is less usefulDBA 가 memory tuning 에 경험이 풍부한 경우

memory 요구 사항을 잘 파악한 경우

static memory requirements 의 workload 인 경우

얼마 동안 STMM 이 수행된 후 configuration 이 고정됨

DB2가 사용 할 memory의 양이 고정되어 있는 경우

사용할 총 메모리 양을 DATABASE_MEMORY 에 할당

machine에 오직 하나의 database 만이 수행될 때

DPF인 경우, 각 node에 요구되는 memory 사항이 아주 다른 경우

IBM Confidential35



SummarySelf Tuning Memory Manager

How does STMM work ?

Operation Mode : Automatic, Numeric Value, Computed

How to enable STMM ? enable

확인 방법

Monitor the STMM Tuningdb2diag.log and STMM log

Scenarios

IBM Confidential



Table Partition**

‘2008 하반기효과적인시스템관리를위한기술세미나

IBM Confidential37



Agenda

I. What is Table Partition ?I. What is Table Partition ?

II. How to implement Table Partition ?II. How to implement Table Partition ?

IV. What does Roll-in and Roll-out mean ?IV. What does Roll-in and Roll-out mean ?

III. What is Range ?III. What is Range ?

V. DB2 Data Organization SchemesV. DB2 Data Organization Schemes

VI. Table Partition SummaryVI. Table Partition Summary

IBM Confidential38



Table PartitionTable Partition

테이블 테이터가 하나 이상의 테이블 컬럼 값에 따라 range 또는 데이터 파티션 이라는 복수의 스토리지 오브젝트에 나누어져 있는 데이터 조직 스킴

• Data Partition 또는 Range Partition

Why ?Database growth

각 Data partition은 다른 테이블 스페이스, 동일한 테이블 스페이스 또는 둘 모두의 조합에 가능

IBM Confidential39



Partition Keys

테이블 파티션 키 컬럼 값은 각 테이블 행이 속하는 데이터 파티션을 판별하는 데 사용

테이블 파티션의 장점을 최대한 이용하려면 효율적인 테이블 파티션 키 컬럼을 선택해야 함

가이드 라인

• 데이터 롤인 크기와 일치하는 범위 정의. 날짜 또는 시간 컬럼에 따라 데이터를 파티션하는 것이 가장 일반적

• 데이터 롤아웃과 일치하는 범위 단위 정의. 월 또는 분기를 사용하는 것이 가장 일반적

• 파티션 제거시 유리한 컬럼의 파티션

지원되지 않는 데이터 타입

User defined types

LONG VARCHAR

CLOB

DBCLOB

BLOB

LONG VARGRAPHIC

XML

REF

IBM Confidential40



Agenda







IBM Confidential41



제어 센터

Command Line Processor

Implementing Table Partitioning

IBM Confidential42



Create the table using the wizard

IBM Confidential43



Step 1: Name the table

IBM Confidential44



Step 2: Define the columns

IBM Confidential45



Step 3: Define Data Partitions

IBM Confidential46



Boundary Value Specification

IBM Confidential47



Step 4: Tablespace specification

IBM Confidential48



Optional StepsStep 5 – Primary Keys

Step 6 – Dimensions

Step 7 - Constraints

IBM Confidential49



Step 8: Review and Complete

IBM Confidential50



제어 센터


Implementing Table Partitioning

IBM Confidential51



Using the DB2 Command Line ProcessorA simple create table command :

CREATE TABLE TAB1

(COL1 CHAR, COL2 INT)

PARTITION BY RANGE (COL2)

(

STARTING FROM (100)

ENDING AT (200)

)

범위를 벗어난 값을 insert 하면 SQL0327N “이 행은 정의된 데이터 파티션 범위에서 벗어나므로 XXX 테이블에 삽입할 수 없습니다. “란 에러 메시지 return

IBM Confidential52



How do we verify?describe table tab1 show detail

IBM Confidential53



Verification using Catalog Informationselect DATAPARTITIONKEYSEQ, DATAPARTITIONEXPRESSION

from syscat.DATAPARTITIONEXPRESSION

where tabname=‘TAB1’

IBM Confidential54



Verifying Boundary ValuesSELECT LOWVALUE, HIGHVALUE

FROM SYSCAT. DATAPARTITIONS

WHERE TABNAME = ‘TAB1’

IBM Confidential55



Agenda







IBM Confidential56



파티션 테이블 디자인을 위해 RANGE option 이해하는 것은 필수

한번 RANGE가 선택되면 동적으로 변경되는 것은 불가

RANGE 정의는 RANGE partition expression 과 RANGE partition element의 조합

Range Specification

IBM Confidential57



Range Partition Expression

테이블 파티션을 판별하도록 정의된 범위에 적용되는 키 데이터를 지정

최대 16 column까지 허용 함

가능한 optionNULLS LAST : NULL 값을 정렬된 값 목록의 맨 마지막에 표시하도록 지정

NULLS FIRST : NULL 값을 정렬된 값 목록의 맨 앞에 표시하도록 지정

Examples :PARTITION BY RANGE (LENGTH)

PARTITION BY RANGE (AREA NULLS LAST, PERIMETER NULLS FIRST)

CREATE TABLE ACCESSNUMBERS (AREA INTEGER, EXCHANGE INTEGER)

PARTITION BY RANGE (AREA NULLS LAST, EXCHANGE NULLS FIRST)

(STARTING (1,1) ENDING (10,100),

STARTING (11,1) ENDING (MAXVALUE,MAXVALUE))

• 두 개의 데이터 파티션이 있는 ACCESSNUMBERS라는 파티션된 테이블을 작성함

• 행(10, NULL)은 첫 번째 파티션에 위치하고 행(NULL, 100)은 두 번째(마지막) 데이터 파티션에 위치함

IBM Confidential58



Range Partition Element

데이터 파티션 키 범위 및 해당 범위의 테이블 행이 저장될 테이블 스페이스를 지정

'-PARTITION--partition-name-‘각각의 데이터 파티션의 이름은 unique 해야 함

파티션 이름이 지정되지 않을 경우, default는 PARTx

Example:… PARTITION BY RANGE (QUARTERS) ( PARTITION 1Q08 …

IBM Confidential59



Range Partition Element| boundary-spec |

Range Partition의 boundary를 지정

IBM Confidential60



Starting & EndingStarting – 데이터 파티션 범위의 하위 끝을 지정

Ending - 데이터 파티션 범위의 상위 끝을 지정

ExampleCREATE TABLE orders (id INT, shipdate DATE)

PARTITION BY RANGE (shipdate)

(STARTING FROM '1/1/2006',

ENDING AT '4/1/2006‘)

IBM Confidential61



Minvalue, MaxvalueMINVALUE 는 해당하는 column-name의 데이터 유형에 대한 최하위 가능 값보다 더 낮은 값을 지정

MAXVALUE는 해당하는 column-name의 데이터 유형에 대한 가장 큰 가능 값보다 더 큰 값을 지정



(STARTING (MINVALUE)

ENDING (MAXVALUE))

IBM Confidential62



Inclusive, ExclusiveINCLUSIVE 는 지정된 범위 값이 데이터 파티션에 포함됨을 표시

EXCLUSIVE 는 지정된 constant 값이 데이터 파티션에서 제외됨을 표시.



(STARTING ‘1/1/2006’ INCLUSIVE

ENDING ‘4/1/2006’ EXCLUSIVE)

IBM Confidential63



Boundary-spec revisited

Every자동으로 생성된 구문 양식을 사용하는 경우 각 데이터 파티션 범위의 너비를 지정

데이터 파티션이 작성되고 STARTING FROM 값에서 시작되며 범위에 있는 값의 해당 번호를 포함

Example :

• create table orders (shipdate date)

partition by range (shipdate) (starting '1/1/2006' ending '12/31/2008' every 1 YEAR)

IBM Confidential64



Duration-label다음 리스트의 Duration Label은 사용 가능

IBM Confidential65



Range Specification Re-visitedExample :

Create table foo (col1 int)

partition by range (col1 nulls last) (

partition YR1 starting (minvalue)ending at (200) exclusive,

partition YR2 starting from (200) inclusiveending at (300) exclusive)

IBM Confidential66



Partitioning on Multiple ColumnsPARTITION BY 절에서 multiple column을 기술

Example : CREATE TABLE sales (year INT, month int, …)

PARTITION BY RANGE (year, month)

(STARTING (2000, 1),

STARTING (2000, 4),

STARTING (2000, 7),

STARTING (2000,10) ENDING (2000,12)) ;

IBM Confidential67



Storage Mapping: Mapping Ranges to Table Spaces (1)Short Syntax

CREATE TABLE 의 IN절

round-robin 방식으로 table space에 배치

1Q/2000 data는 tbsp1에 , 2Q/2000은 tbsp2에, 3Q/2000은 tbsp3에 4Q/2000은 tbsp1에 배치

CREATE TABLE sales (sale_date DATE, customer INT, …)

IN TBSP1, TBSP2, TBSP3

PARTITION BY RANGE (sale_date)

(STARTING ‘1/1/2000’ ENDING ’12/31/2004’

EVERY 3 MONTHS );

tbsp1

…

sales.1Q/00

sales.4Q/00

tbsp2

…

sales.2Q/00

sales.1Q/01

tbsp3

…

sales.3Q/00

sales.2Q/01

IBM Confidential68



Storage Mapping: Mapping Ranges to Table Spaces (2)Long Syntax

table space를 명시적으로 기술할 수 있음

example :

• 1/1/2007이전 sales data는 tbspd1

• 1Q sales는 tbspd2



• 4Q 2007 sales는 tbspd5

CREATE TABLE sales (sale_date DATE, customer INT, …)

PARTITION BY RANGE (sale_date)

( PART rest STARTING MINVALUE IN TBSPD1,

PARTITION q1 STARTING ‘1/1/2007’ IN TBSPD2,



PARTITION q4 STARTING ‘10/1/2007’ ENDING ’12/31/2007’ IN TBSPD5)

INDEX IN TBSPI1 ;

TBSPD1

sales.rest

TBSPD2

sales.q1

TBSPD3

sales.q2

TBSPD4

sales.q3

TBSPD5

sales.q4

IBM Confidential69



Storage Mapping: Indexes are Global in DB2 9.1인덱스는 global ( in DB2 9.1 )

인덱스 페이지의 RID는 2-byte 파티션 ID를 포함.

각 인덱스는 별도의 storage object에 존재

default 로 첫 데이터 파티션의 tablespace에 존재

다른 table space에 생성할 수 있음

• CREATE TABLE 문의 INDEX IN 절에서 다른table space에 위치 시킬 수 있음

• CREATE INDEX에서 IN 절 명시

권장

• LARGE Table space에 index를 배치

CREATE TABLE t1 (c1 INT, c2 INT, …)

IN tbsp1, tbsp2, tbsp3

INDEX IN tbsp4

PARTITION BY RANGE (c1)

(STARTING FROM (1) END (99) EVERY (33));

CREATE INDEX i1 on t1 (c1);

CREATE INDEX i2 on t1 (c2) IN tbsp5;

tbsp1

t1.p1

tbsp2

t1.p2

tbsp3

t1.p3

tbsp4

i1

tbsp5

i2

IBM Confidential70



Storage Mapping: Large Objects are LocalLarge objects (LOBs, etc.) are local

default로 대응하는 data object와 같은 table space에 저장

CREATE TABLE의 LONG IN 절을 사용하여 partition마다 기술하여 default 를 변경

LOB용으로 각 partition마다 별도의 storage object가 있어야 함

large table space 이어야 함

CREATE TABLE t1 (c1 INT, c2 INT, c3 BLOB)

IN tbsp1, tbsp2, tbsp3

INDEX IN tbsp4

LONG IN tbsp6, tbsp7, tbsp8

PARTITION BY RANGE (c1)

(STARTING FROM (1)

END (99)

EVERY (33));

CREATE INDEX i1 on t1 (c1);

CREATE INDEX i2 on t1 (c2) IN tbsp5;

tbsp1

t1.p1

tbsp2

t1.p2

tbsp3

t1.p3

tbsp4

i1

tbsp5

i2

tbsp1

t1.p1

tbsp6

t1.LONG1

tbsp7

t1.LONG2

tbsp9

t1.LONG3

IBM Confidential71



How Partition Eliminations are achieved?Example :

CREATE TABLE ORDERS (customer INT, shipdate DATE) PARTITION BY RANGE (shipdate)

(STARTING ‘1/1/2008’ ENDING ‘3/31/2008’,

STARTING ‘4/1/2008’ ENDING ‘6/30/2008’,

STARTING ‘7/1/2008’ ENDING ‘9/30/2008’,

STARTING ‘10/1/2008’ ENDING ’12/31/2008’);

IBM Confidential72



How Partition Eliminations are achieved?Select * from orders where shipdate between ‘4/20/2008’ and ‘8/3/2008’

IBM Confidential73



Partition Elimination파티션된 테이블의 인덱스는 단일 테이블 스페이스에 작성

인덱스 페이지의 RID는 2-byte 파티션 ID를 포함

2-byte 파티션 ID는 인덱스 스캔하는 동안 쿼리 predicate에 매치되지 않는 파티션을 access 제외하는데 사용

DB2 optimizer는 데이터 파티션을 알고 있음

오직 관련된 데이터 파티션만 scan

쿼리 응답 속도를 향상

IBM Confidential74



Agenda







IBM Confidential75



Roll-In

데이터를 파티션되지 않은 테이블에 로드한 후 해당 데이터 파티션을 나머지 테이블에 추가

Example : CREATE TABLE NEW_ORDER (customer INT, shipdate DATE)

LOAD FROM data_file of DEL REPLACE INTO NEW_ORDER

ALTER TABLE ORDERS ATTACH PARTITION NEW_ORDER

STARTING FROM ‘1/1/2009' ENDING AT '3/31/2009‘

FROM NEW_ORDER

• 데이터 이동이 필요 없기 때문에 매우 빠름

• 인덱스 maintenance는 나중에 수행

IBM Confidential76



Roll-InCOMMIT

• 새로운 데이터는 아직 안 보임

SET INTEGRITY FOR ORDERS

• on-line 일 경우 기존 데이터를 읽기 쓰기 모두 사용 가능

• default는 ALLOW NO ACCESS

• long running operation : 새로 접속한 데이터가 범위 안에 있는지 확인

• 인덱스 및 MQT와 같은 다른 종속 오브젝트의 유지보수에도 SET INTEGRITY문이 필요

COMMIT

• 새로운 데이터 보임

IBM Confidential77



Roll-Out테이블에서 사용하지 않는 데이터를 제거

Example : ALTER TABLE ORDERS DETACH PARTITION PART0 INTO OLD_ORDER;

• 데이터 이동이 필요 없기 때문에 매우 빠름

• 인덱스 maintenance는 나중에 실행됨

COMMIT

• detach된 data는 안보이고 detach된 partition은 index scan시 무시됨

• 소스 테이블에서의 인덱스 정리가 백그라운드 비동기 인덱스 정리 프로세스를 통해 자동으로 수행

SET INTEGRITY FOR mqt1, mqt2 : optional ( MQT maintenance를 위함)

EXPORT OLD_ORDER ; DROP TABLE OLD_ORDER;

IBM Confidential78



Rolling-in and Rolling-Out Data테이블로부터 데이터는 쉽게 roll-in 또는 roll-out 될 수 있음

OFFLINE에서 수행할 필요 없음

데이터 웨어 하우스 환경에서 효과적

데이터 roll-in에서는 ATTACH 사용

데이터 roll-out에서는 DETACH 사용

IBM Confidential79



Utility Support for Partitioned TablesREORG TABLE

모든 Partitions은 serially reorganized 됨. 선택된 data partition을 reorg 하는 option은 없음

No INPLACE reorgs, OFFLINE reorg with ALLOW READ ACCESS allowed

REORG INDEX개별 index reorg 지원 ( REORG INDEX index_name for TABLE table_name)

ALLOW READ ACCESS 인 경우 read는 가능 but ALLOW WRITE ACCESS은 지원하지 않음

RUNSTATSALL partitions 에 대한 catalog statistic을 수집 (partition별로는 안됨)

I/O와 CPU cost를 줄이기 위해서 TABLESAMPLE 사용 할 수 있음

ROLLFORWARD to Point In TimeTable이 상주하는 모든 table space에 동일한 시점으로 rollforward를 해야 함

IBM Confidential80



Agenda







IBM Confidential81



DB2 Data Organization SchemesDatabase Partitioning (DPF)

Table Partitioning

Multi Dimensional Clustering (MDC)

IBM Confidential82



How Range Partition works with other data organization schemesEach clause of the CREATE TABLE statement includes an algorithm to indicate how the data should be organized

DISTRIBUTE BY -- Spreads data evenly across database partitions.

PARTITION BY -- Groups rows with similar values of a single dimension in the same data partition.

ORGANIZE BY -- Groups rows with similar values on multiple dimensions in the same table extent

IBM Confidential83



Three DB2 data organization schemes

IBM Confidential84



Agenda







IBM Confidential85



Benefits of Table Partition(1)

대형 테이블을 작성할 수 있음

파티션된 테이블은 일반 테이블보다 광대한 데이터를 포함할 수 있으며 여러 스토리지 오브젝트에서 테이블 데이터를 나누면테이블 크기를 크게 늘릴 수 있음

보다 유연한 관리 성능을 발휘

시간이 오래 걸리는 유지보수 작업을 더 작은 단위의 여러 작업으로 나누어 개별 데이터 파티션에서 관리 태스크를 수행할 수있음

• 데이터 파티션이 별도의 테이블 스페이스에 있을 경우, 백업 조작은 데이터 파티션별로 데이터 파티션에 대해 작업할 수있음.

• 따라서 파티션된 테이블의 데이터 파티션을 한 번에 하나씩 백업

보다 세분화하여 인덱스 배치를 제어

다른 테이블 스페이스에 인덱스를 배치하고 이러한 인덱스를 개별적으로 관리할 수 있음

• 인덱스 삭제 및 온라인 인덱스 작성시 성능이 향상

IBM Confidential86



Benefits of Table Partition(2)

데이터를 빠르고 쉽게 롤인 또는 롤아웃함

테이블 파티션을 이용해 테이블 데이터를 롤인 및 롤아웃할 수 있음

파티션된 테이블 데이터를 롤인하면 새 행을 쉽게 파티션된 테이블에 추가 데이터 파티션으로서 통합

파티션된 테이블 데이터를 롤아웃하면 후속 제거 또는 아카이브시 파티션된 테이블로부터 데이터 범위를 쉽게 분리

이 기능은 결정 지원 쿼리를 실행하기 위해 데이터를 외부 및 내부로 자주 이동하는 데이터 웨어하우스 환경에서 특히 유용할수 있음

쿼리 성능이 향상

테이블 파티션을 사용하여 데이터를 분리하면 무관한 데이터를 스캔하지 않으므로 쿼리 처리 성능이 향상될 수 있음

IBM Confidential87



Summary(1)Table Partition

Implementation of Table Partition제어센터


RangeNULLS LAST / NULLS FIRST

PARTITION <partition-name>

STARTING/ENDING clause

MINVALUE/MAXVALUE

INCLUSIVE/EXCLUSIVE

EVERY/duration-label

IBM Confidential88



Summary(2)Storage Mapping

Partition Elimination

Data Roll-inATTACH

Data Roll-outDETACH

Table Partition with other data organization schemesDatabase Partitioning

Multi-Dimensional Clustering

IBM Confidential89



감사합니다.감사합니다.Q & A

STMM과Table Partition 소개 - dbguide.net 2: Enable self-tuning ... Analyzing the db2diag.log and...

Documents

Transcript of STMM과Table Partition 소개 - dbguide.net 2: Enable self-tuning ... Analyzing the db2diag.log and...