기술자료
DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.
DB2 Basics:테이블 공간과 버퍼 풀 테이블 공간과 버퍼 풀의 중요성을 인식시키고, 이들을 올바르게 설계하고 튜닝하여 데이터베이스 성능을 높이는 방법을 설명합니다. 머리말 Section 1: 정의 테이블 공간 테이블 공간 관리 테이블 공간을 생성하고 보는 방법 컨테이너 테이블 공간 설정 CREATE TABLESPACE문의 예제 테이블 공간 애트리뷰트와 컨테이너를 보는 방법 버퍼 풀 CREATE BUFFERPOOL문 예제 버퍼 풀 애트리뷰트를 보는 방법 데이터베이스가 테이블 공간을 확보하는 방법 Section 2:성능 테이블 공간 구성 버퍼 풀 활용 물리적 스토리지 구성 데이터베이스 옮기기 결론 기사의 원문보기DB2 Basics: 테이블 공간과 버퍼 풀
데이터베이스 당 단 한 개의 카탈로그 테이블 공간이 있고 CREATE DATABASE 명령어가 실행될 때 만들어 진다. DB2에 의해 SYSCATSPACE로 이름이 붙여진 카탈로그 테이블 공간에는 시스템 카탈로그 테이블이 있다. 이 테이블 공간은 데이터베이스가 만들어질 때 항상 생성되어진다.Regular 테이블 공간
regular 테이블 공간에는 테이블 데이터와 인덱스가 포함된다. 만약 Large Objects ( LOBs ) 같은 Long 데이터가 Long 테이블 공간에 명시적으로 저장되지 않았다면 이와 같은 Long 데이터도 Regular 테이블 공간에 저장될 수 있다. 테이블과 인덱스는 개별적인 Regular 테이블 공간으로 분리될 수 있다. 테이블 공간이 데이터베이스 관리 공간(DMS)일 경우가 그렇다. DMS와 시스템 관리 공간(SMS)의 차이는 나중에 설명하도록 하겠다. 적어도 한 개의 Regular 테이블 공간은 각 데이터베이스에 존재해야 한다. 데이터베이스가 만들어질 때 디폴트로 USERSPACE1이라는 이름이 붙여진다.Long 테이블 공간
Long 테이블 공간은 길거나 LOB 테이블 칼럼을 저장하는데 사용되고 DMS 테이블 공간에 있어야 한다. 또한 구조화된 유형 칼럼이나 인덱스 데이터도 저장할 수 있다. Long 테이블 공간이 정의되지 않으면 LOB는 Regular 테이블 공간에 저장된다. Long 테이블 공간은 옵션이며 기본적으로 생기는 것이 아니다.시스템 임시 테이블 공간
시스템 임시 테이블 공간은 소팅, 테이블 재구성, 인덱스 생성, 테이블 결합 같은 SQL 연산 동안 필요한 내부의 임시 데이터를 저장하는데 사용된다. 데이터베이스에 최소 한 개는 있어야 한다. 데이터베이스와 함께 생성되며 디폴트 이름은 TEMPSPACE1이다.사용자 임시 테이블 공간
사용자 임시 테이블 공간에는 Declared Global Temporary Table이 저장된다. 데이터베이스가 만들어질 때에는 사용자 임시 테이블 공간은 존재하지 않으나 만약 Declared Global Temporary Table을 정의할 경우에는 적어도 한 개의 사용자 임시 테이블 공간이 존재해야만 한다. 사용자 임시 테이블 공간은 선택사항이며 디폴트로는 생성이 되지 않는다.
SMS 테이블 공간은 OS가 관리한다. 컨테이너들은 일반 OS 파일로서 정의되고 OS 호출을 통해 액세스 된다. 즉 이는 모든 일반 OS 함수들이 다음과 같은 것을 다룰 수 있다는 것을 의미한다. : I/O는 OS에 의해 버퍼링 되고 공간은 OS 규약에 따라 할당되고 테이블 공간은 필요할 경우 자동으로 확장된다. 하지만 SMS 테이블 공간에서 컨테이너들을 제거하는 것은 불가능하고 새로운 컨테이너들을 SMS 테이블 공간에 추가하는 것은 파티션 데이터베이스에서만 가능하다. 이전 섹션에서 설명한 이 세가지 기본 테이블 공간이 SMS이다.데이터베이스 관리 공간(DMS)
DMS 테이블 공간은 DB2가 관리한다. 컨테이너는 파일(테이블 공간이 만들어 질 때 주어진 크기로 할당될 것이다.) 또는 디바이스로 정의된다. DB2는 할당 메소드 만큼 많은 I/O를 관리할 것이고 OS가 이를 수락할 것이다. 컨테이너 확장은 ALTER TABLESPACE 명령어를 통해 가능하며 사용되지 않는 DMS 컨테이너 부분 역시 릴리스 될 수 있다. ( Version 8 부터 )다음은 컨테이너 크기를 늘리는 방법이다. (version 7과 version 8 모두 지원됨)
ALTER TABLESPACE TS1 RESIZE (FILE '/conts/cont0' 2000, DEVICE '/dev/rcont1' 2000, FILE 'cont2' 2000)
원래 컨테이너의 크기를 더 작은 크기로 조정하는 것은 Version 8에서만 지원된다.
CREATE DATABASE testdb CONNECT TO testdb LIST TABLESPACES
아래 Listing 1은 LIST TABLESPACES 명령어의 결과이다.Listing 1. LIST TABLESPACES 명령어의 결과
Tablespaces for Current DatabaseTablespace ID = 0 Name = SYSCATSPACE Type = System
위에 보이는 세 개의 테이블 공간은 CREATE DATABASE 명령어에 의해 자동으로 생성된다. 사용자는 CREATE DATABASE 명령어 수행시 테이블 공간에 대한 정의를 지정함으로써 테이블 공간을 디폴트 값으로 생성하지 않아도 된다. 하지만 카탈로그 테이블 공간과 최소 한 개의 일반 테이블 공간과 한 개의 시스템 임시 테이블 공간은 데이터베이스 생성시 만들어져야 한다. (카탈로그 테이블 공간을 제외한) 모든 유형의 테이블 공간들이 CREATE DATABASE 명령어나 CREATE TABLESPACE 명령어를 사용하여 생성될 수 있다.
managed space Contents = Any data State = 0x0000
Detailed explanation: NormalTablespace ID = 1 Name = TEMPSPACE1 Type = System
managed space Contents = System Temporary data State =
0x0000 Detailed explanation: NormalTablespace ID = 2 Name = USERSPACE1 Type = System
managed space Contents = Any data State = 0x0000
Detailed explanation: Normal
테이블 공간에 사용되는 페이지 크기를 정의한다. 지원되는 크기는 4K, 8K, 16K, 32K이다. 페이지 크기에 따라 테이블 공간에 저장되는 테이블의 한 행의 최대 길이와 컬럼 개수가 아래 표와 같이 제한된다.표 1. 페이지 크기
테이블 공간은 16384 페이지로 제한되기 때문에 이보다 더 큰 페이지를 선택하면 테이블 공간의 용량도 늘어난다.확장 크기 ( Extent Size )
페이지 크기
열 크기 한계
칼럼 카운트 한계
최대 용량
4 KB
4 005
500
64 GB
8 KB
8 101
1 012
128 GB
16 KB
16 293
1 012
256 GB
32 KB
32 677
1 012
512 GB
다음 컨테이너로 넘어가기 전에 컨테이너에 작성될 페이지의 수를 지정한다.데이터베이스 매니저는 컨테이너들을 반복적으로 순환하면서 데이터들을 저장한다. 이 매개 변수는 테이블 공간을 구성하는 컨테이너가 여러 개일 경우에만 유효하다.프리패치 크기 ( Prefetch Size )
데이터 프리패치(미리 가져오기)가 수행될 때 테이블 공간에서 읽혀질 페이지의 수를 지정한다. 프리패치는 쿼리에 의해 참조되기 전에 쿼리에 필요한 데이터를 읽어서 쿼리가 I/O가 수행되는 것을 기다리지 않도록 한다. 프리패치는 순차적 I/O가 적절하고 프리패치가 퍼포먼스를 향상시킬 수 있다고 판단될 때 데이터베이스 매니저가 선택하는 것이다.오버헤드와 전송 비율 ( Overhead and Transfer Rate )
이 값은 쿼리 최적화 동안 I/O의 비용을 결정하는데 사용된다. 두 값 모두 밀리초로 측정되고 모든 컨테이너의 평균치어야 한다. 오버헤드는 I/O 컨트롤러 액티비티, 디스크 탐색 시간, 회전 지연시간과 관련된 시간이다. 전송 비율은 한 페이지를 메모리로 읽어 들이는데 필요한 시간이다. 디폴트 값은 각각 24.1과 0.9이다. 이 값들은 하드웨어 스팩에 기반하여 계산될 수 있다.
CREATE TABLESPACE USERSPACE3
PAGESIZE 8K
MANAGED BY SYSTEM
USING ('d:\usp3_cont1', 'e:\usp3_cont2', 'f:\usp3_cont3')
EXTENTSIZE 64
PREFETCHSIZE 32
BUFFERPOOL BP3
OVERHEAD 24.1
TRANSFERRATE 0.9
LIST TABLESPACES SHOW DETAIL
Listing 2는 USERSPACE1 테이블 공간에 대한 정보를 보여준다. 디폴트로 데이터베이스 생성시 생성된 세 개의 테이블 공간들이 나열된다.Listing 2. LlST TABLESPACES SHOW DETAIL 명령어의 결과값
Tablespaces for Current DatabaseTablespace ID = 2 Name = USERSPACE1 Type = System
필요한 컨테이너 정보를 보려면 위의 결과값에서 Tablespace ID를 사용하여 아래 명령어를 수행한다.
managed space Contents = Any data State = 0x0000
Detailed explanation: Normal Total pages = 336 Useable
pages = 336 Used pages = 336 Free pages = Not
applicable High water mark (pages) = Not applicable
Page size (bytes) = 4096 Extent size (pages) = 32
Prefetch size (pages) = 16 Number of containers = 1
LIST TABLESPACE CONTAINERS FOR 2
Listing 3. LIST TABLESPACE CONTAINERS 명령어의 결과값
Tablespace Containers for Tablespace 2Container ID = 0 Name =
이 명령어를 사용하면 지정된 테이블 공간에 대한 모든 컨테이너들이 나타난다. 위에 나타난 경로는 컨테이너가 물리적으로 위치한 곳을 가리킨다.
C:\DB2\NODE0000\SQL00004\SQLT0002.0 Type = Path
Version 8에서는 블록 기반 프리패치를 위해 버퍼 풀(최대 98%) 부분을 남겨둘 수 있다. 블록 기반 I/O는 인접한 메모리 영역으로 블록을 읽어들임으로서 프리패치의 효율성을 높인다. 블록의 크기는 모든 버퍼 풀에 동일해야 하고 BLOCKSIZE 매개변수가 제어한다. 이 값은 페이지 단위의 블록 크기(2에서 256)이고 디폴트는 32이다.확장된 스토리지
DB2는 버퍼의 확장된 스토리지를 사용하지 않는다. 하지만 확장된 스토리지는 메모리 페이지를 캐싱하는데 사용될 수 있다. 따라서 메모리에서 페이지를 더욱 빨리 이동할 수 있다.
CREATE BUFFERPOOL BP3 SIZE 2000 PAGESIZE 8K
이 버퍼풀은 위 CREATE TABLESPACE 예제의 USERSPACE3에 지정된 것이고 테이블 공간이 생성되기 전에 만들어져야 된다. 이 버퍼 풀과 테이블 공간의 페이지 크기는 8K로 동일하다. 만약 버퍼 풀을 생성한 후에 테이블 공간을 생성하고 CREATE TABLESPACE 구문에서 BUFFER POOL BP3를 빼놓았다면 ALTER TABLESPACE 구문을 이용하여 기존 테이블 공간에 버퍼 풀을 추가할 수 있다.
ALTER TABLESPACE USERSPACE3 BUFFERPOOL BP3
SELECT * FROM SYSCAT.BUFFERPOOLS
어떤 버퍼 풀이 테이블 공간에 할당되었는지를 보려면 다음 쿼리를 실행한다.
BPNAME BUFFERPOOLID NGNAME NPAGES PAGESIZE ES
------------------ ------------ ------------------
----------- ----------- -- IBMDEFAULTBP 1 - 250 4096 N1 record(s) selected.
SELECT TBSPACE, BUFFERPOOLID FROM
위의 Query 결과값에서 BUFFERPOOLID 를 사용하면 어떤 버퍼 풀이 어떤 테이블 공간과 연결되어 있는지 알 수 있다.
SYSCAT.TABLESPACESTBSPACE BUFFERPOOLID ------------------ ------------
SYSCATSPACE 1 TEMPSPACE1 1 USERSPACE1 13 record(s) selected.
그림 1. 테이블 공간과 버퍼 풀
이 데이터베이스는 다섯 개의 테이블 공간을 갖고 있다. 카탈로그 테이블 공간, 두 개의 Regular 테이블 공간, Long 테이블 공간, 시스템 임시 테이블 공간. 그러나 사용자 임시 테이블 공간은 생성되지 않았다. 다섯 개의 테이블 공간에 속하는 여덟 개의 컨테이너가 있다.
이 시나리오에서 버퍼 풀들은 다음과 같이 할당된다.BP1 (4K) SYSCATSPACE와 USERSPACE2
BP2 (8K)- USERSPACE1
BP3 (32K)- LARGESPACE와 SYSTEMP1
2. 두 번째 단계에서는 다른 설정값을 가진 테이블 공간에 테이블을 생성하는 것이 성능을 향상시키는 것인지 고려해야 한다.
3. 임시적인 테이블 공간 디자인이 완성되면 버퍼 풀 활용에 대해 생각해야 한다. 이전 테이블 공간 디자인을 수정할 수도 있다.
4. 마지막으로, 테이블 공간에 컨테이너를 할당한다.이 프로세스는 반복적이고 스트레스 테스트와 벤치마킹을 통해 디자인을 검증 받아야 한다. 최적의 디자인은 강도 높은 노력을 통해 탄생하고 데이터베이스 퍼포먼스도 최상이어야 한다.● 단순한 디자인에서 출발한다.
● 테스팅에 기반하여 충분한 이유가 있을 때만 복잡성을 추가하도록 한다.가끔은 성능이 약간 저하된다 하더라도 데이터베이스에 대한 관리의 복잡성을 줄이고 데이터베이스 디자인을 단순화하는 것이 더 낫다. DB2는 매우 정교한 리소스 관리 로직을 가지고 있으므로 정교한 디자인 없이도 좋은 성능을 낸다.
db2set DB2_PARALLEL_IO=*
또 다른 레지스트리 변수인 DB2_STRIPED_CONTAINERS=ON 은 컨테이너 태그 크기를 한페이지에서 전체 EXTENT 로 변경하여 RAID 스트라이프로 테이블 공간 EXTENT 를 구성할 수 있다.다른 성능 평가와 마찬가지로 변경이 효과적인 결과를 가져올 것인지 알수 있는 확실한 방법은 벤치마크를 수행하는 것이다. 물리적 구성 변경의 경우 다소 복잡하다. 테이블 공간을 변경하는데 노력이 많이 든다. 가장 실질적인 방법은 디자인 단계에서 케이스의 수를 줄여서 나중에 벤치마크에도 적은 케이스만 쓰이도록 하는 것이다. 퍼포먼스가 중요할 경우에는 시간과 노력을 많이 들여도 좋다. 디자인 마다 퍼포먼스 차이가 상당하다. 버퍼 풀에 중요성을 두어야 하며, 이들은 가상 메모리에 할당되어서는 안되며 가장 효율적인 방식으로 사용되어야 한다.
SQL Reference:
●CREATE DATABASE command
●CREATE TABLESPACE statement
●ALTER TABLESPACE statement
●CREATE BUFFERPOOL statement
●ALTER BUFFERPOOL statementOther Table Space and Buffer Pool References::
●How DB2 Uses Memory
●Designing and Choosing Table Spaces
●Managing the Database Buffer Pool
Articles from the DB2 Developer Domain:
● DB2 Storage -
http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html
● Tuning Up for OLTP and Data Warehousing -
http://www.db2mag.com/db_area/archives/2002/q3/hayes.shtml
http://www.developer.ibm.com/.제공 : DB포탈사이트 DBguide.net