Partitioned Table 고찰 part-1

안녕하세요 박성원입니다.

이번에 꽤 큰녀석을 선택해서 파해치고 있는데요 여전히 삽질 중입니다. ^^;;

한번에 정리하기에는 꽤나 많은 분량이라 두부분으로 나뉘었는데요

우선 part 1에서는 partitioned table의 정의와 탄생비화(?)를 알아보도록 하구요
part 2에서는 실재로 partitioned table의 예와 함께 구성을 알아보도록 하겠습니다.

■ Partioned table 정의 
  - 대형 테이블 및 다양한 접근 패턴을 가진 테이블의 확장성 및 관리 용이성을 위해 제공되는 기능

  Q1. 대형 테이블??
  대형 데이터베이스나 대형 테이블의 경우 단순히 큰 용량의 DB나 Table을 말하는것이 아니라 설계 초기에 구상한 테이블의 크기와 예상되는 테이블 조회 처리를 초과하여 정상적인 성능을 이루지 못할 때를 말합니다. 또한 테이블의 경우 단순한 접근을 이용한 테이블이 아닌 다양한 접근을 통해 여러가지 데이터를 제공하는 경우 성능의 유해 요소로 판단될 정도의 테이블일 경우 대형 테이블로 간주합니다.

Tip. Patitioned Table 적용이 가능한 테이블

a.     대형테이블

b.     다양한 접근으로 성능 저하가 발생하는 테이블

c.     Log성 데이터(읽기전용)를 포함하는 Table

 
Q2. 성능 이슈?

a.     테이블을 분할하여 사용하기 때문에 I/O의 효율을 향상

b.     여러 개의 CPU가 있는 시스템에서는 병렬작업을 통해 효율성 극대화

- 병렬 조인

2000에서도 병렬조인을 지원하지만 하위 집합을 즉석에서 생성한 후 사용 가능함.

2005에서는 최적화 프로그램에서 병렬 조인을 감지하면 분할 테이블을 먼저 조인한 후 결과와 조인하는 알고리즘을 제공.

■ History

A. SQL Server 6.5 이전
  1. 6.5이전 버전에서는 디자인의 일부 활용해야 함으로 여러 개의 테이블을 만든 다음 저장된
     프로시저 혹은 뷰 또는 클라이언트 응용프로그램을 통해 데이터의 엑서스를 관리함으로써
     patitioned 기능을 구현하여 사용하였음
  2. 이와 같은 복잡한 방법으로 구현할 경우 관리상의 어려움이 발생하였으며 실제로 성능 향상
     이슈가 거의 없었음

B. SQL Server 7.0 paritioned View 제공

  1. View를 이용하여 제약조건과 결합되면서 쿼리 최적화 프로그램이 쿼리 계획에서 관련이
     없는 테이블을 제거(분할제거)할 수 있도록 했으며 UNION이 지정된 뷰가 여러 테이블에
     엑서스 할 때 전체적인 부담을 크게 줄이도록 제공

  2. 조건절에서 12월의 데이터를 조회할 경우 SalesDecember를 엑서스 함으로 선능의 향상을 제공

  3. Select의 성능 향상만을 제공하였으며 수정문의 경우 분할 구조를 사용할 수 없음

C. SQL 2000 partition View 제공
   1. SQL Server 2000에서는 수정문을 사용할 경우에도 동일한 분할 구조를 사용할 수 있으며,
     뷰를 통해 수정정한 사항을 적합한 기본 테이블로 이동시킬 수 있도록 제공합니다.
     (분할 키 및 분할 키 만들기에 대한 추가 설정이 필요함)

  2. 분할된 데이터 집합의 관리, 디자인 또는 개발이 단순화 되지 않아 분할된 보기를 사용할때는
     뷰가 정의 되는 기본 테이블을 모두 만들어 개별 관리 해야하는 어려움이 있음

  3. 하위 테이블의 캡슐화로 인해 사용자는 대상 테이블의 존재를 몰라도 사용하는데 어려움이 없는
     반면에 관리 테이블의 증가와 각 테이블간의 무결성 제약조건을 개별적으로 관리해야하므로
     관리상의 어려움이 있음
  4. 하위 테이블의 인덱스를 만든후 UNION을 지정하므로 최적화 프로그램은 각 분할에 대해 계획의
     유효성을 검사하고 확인하는 과정을 거침(인덱스가 바뀌었을 수 있기때문) 이로인해 쿼리 최적화
     시간은 처리된 분할 수 가 증가할 경우 길어지는 경우가 발생함

D. SQL 2005 partition Table 제공
  1. 테이블분할 예(OLTP 데이터를 분석 테이블로 옮길경우)
     a. 기존
        여러 개의 테이블을 인덱스가 존재하는 테이블로 이동시킬 경우인덱스의 분리나 잠기는 현상이
        발생해서 현재 사용자와 차단 현상이 발생하며, 각행이 insert 될때 인덱스를 유지하기위한
        비용이 들기 때문에 프로세스 비용이 증가함 
    - 해결 1. 인덱스가 없는 heap 테이블에 대량의 데이터를 로드 할때는 데이터로드가 종료한 후
      index를 생성할 수 있으므로 프로세스의 성능의 이슈가 10배 이상 차이남.
    - 해결 2. 인덱싱 되지 않은 테이블을 로드할 경우 데이터의 순서가 무의미 하므로 병렬 insert를
      통해 성능을 향상 시킬 수 있음
    b. SQL 2000 partitioned Table 
      새로 채운 분할을 기존 분할 스키마의 추가 분할로 바꿔 넣고 이전 분할은 빼는 작업만 수행하며
      (시간이 매우 짧다는 이점이 있음) 병렬 대량 로드 및 병렬 인덱스 만들기를 통해 작업을 보다
      효과적으로 수행할 수 있음(분할이 테이블 범위 외부에서 조정되므로 분할을 추가할 때까지
      쿼리한 테이블에는 어떠한 영향을 미치지 않음 – 분할은 몇초안에 수행됨)
  2. DATA 삭제
    a. 기존 
       모든 데이터가 하나의 테이블에 있는 경우 1GB의 이전 데이터를 삭제할경우 행 단위로 
    테이블을 조정 해야 하며, 관련 인덱스의 조정이 필요함. 데이터 삭제시 많은 양의 로그 작업이
    발생하며, 삭제 작업 동안 로그를 자를 수 업으므로 큰 로그가 필요할 수 있음
    (삭제 작업은 단일 자동 커밋 트랜젝션이나 여러 번 수행하여 트랜잭션 크기를 제어할 수 있음)
   b. SQL 2000
      분할된 테이블에서 특정 분할을 제거(메타데이터 작업임)한 후 독립 실행형 테이블을
   삭제하거나 자르면 됨

■ SQL 2005 patitioned Table

  SQL Server 2005에서는 테이블 및 인덱스를 분할 스키마에 만들 수 있습니다. 분할 스키마는 개체를 하나 이상의 파일 그룹에 매핑합니다. 적절한 실제 위치로 이동되는 데이터를 결정하기 위해 분할 스키마는 분할 함수를 이용합니다. 분할 함수는 행 위치를 지정하는데 사용될 알고리즘을 정의하며 스키마는 분할을 해당 실제 위치(파일그룹)와 연결합니다. 정리하면 테이블은 여전히 논리적 개념이지만 디스크에서 테이블의 실제 배치는 이전 버전과 크게 다르지 않습니다. 이제는 테이블에 스키마가 포함 될 수 있습니다.

  A. 범위분할

      사용자 지정 가능한 특정 데이터 범위에 의해 정의되는 테이블 분할하며 광범위한 데이터에 대한
  의사 결정 지원을 위해 데이터를 액서스 하는 경우에 많이 활요 함

        1. 분할 할 경계는 개발자가 선택하며 데이터 사용 패턴이 변경되었을 경우 변경이 가능

        2. 데이터 보관, 의사 결정지원(지정된 달이나 분기와 같이 특정 데이터 범위만 필요한 경우)  및
           데이터 사용이 행 수명에 따라 달라지는 결합된 OLTP 및 DSS(의사결정 지원 시스템)에 사용
       장점 : 이전 데이터를 매우 빠르게 보관 및 대처 할 수 있음

       단점 : 분할에 대해 경계 조건을 정의해야 하므로 처음 정의하기에 복잡함. 또한 스키마를 
               만들어 하나 이상의 파일 그룹에 각 분할을 매핑 해야 함.

  B. 분할 키 정의

     테이블 및 인덱스 분할 작업의 첫 단계로써 분할에 키가 지정되는 데이터를 정의하는 것
     (분할 키는 테이블에 단일 열로 존재해야 하며 특정 조건을 만족 해야 함)

  C. 인덱스 분할
    1. 같은 함수를 사용하여 테이블 및 해당 인덱스를 분할 하면 성능이 최적화 되는 경우가 많이
       발생함. 인덱스와 테이블이 같은 분할 함수와 열을 같은 순서로 사용하는 경우 테이블과
       인덱스는 맟춰진 것이라고 판단 할 수 있음.

    2. 분할 되어있는 테이블에 인덱스를 생성하면 SQL Server는 인덱스가 명시적으로 다르게
       분할되어 있지 않는 한 자동으로 새 인덱스를 테이블의 분할 스키마에 맞춰준다.

  D. 분할의 특수 조건 : 분할(split), 병합 및 전환 (분할테이블 및 디스크분할 예)

    1. 1년 분의 읽기 전용 데이터를 사용가능 한 상태로 유지하는 대용량의 DB

    2. 2003년 9월부터 2004년 8월까지 데이터가 보관되어있음

    3. 2004년 9월은 다른 데이터 베이스에 존재 함

    4. 테이블의 읽기 전용 버전에는 13개의 분할이 있음

       (그 중 12개의 분할에는 2003년 9월부터 2004년 8월까지의 데이터가 포함되어 있으며,
       마지막 분할 하나는 비어 있음)

Step 1. Data Load 전의 분할 경계 상태

Step 2. 테이블 분할 제외
a. 제외과정 (같은 파일그룹 내에 있는 불할되지 않는 테이블로 빼냄)

b. 제외 결과

 

 

Step 3. 마지막분할 분할 및 분할 추가
a. 마지막 분할의 분할 (가장 최신상태의 비어 있는 맨 마지막 범위를 두개의 분할로 분할함)

b. 분할 추가

결과 :: 최종 치환 테이블

참고

1. 분할(split) 및 병합은 ALTER PARTITION FUNCTION을 통해서 진행되며 전환은 ALTER TABLE을
   통해 처리 됨

2. 한번에 하나의 분할만을 추가 또는 제거할 수 있으므로 둘 이상의 분할을 추가하거나 제거해야 할
   경우 테이블을 다시 만들어야 함


<후기>
흠..
이해하고 정리하는데 꽤나 오랜시간이 걸린듯한데 실제로 작성하니 몇줄안되네요 Orz..
역시 저는 아직 부족한거 같습니다 ㅋ;;

출처 : SQL Server 2005의 분할된 테이블 및 인덱스 - Kimberly L. l Tripp SQLskills.com 설립자