728x90
조인의 효율적인 처리를 위한
DB별 대용량 데이터의 파티셔닝과 병렬 처리
파티셔닝은 용량이 큰 테이블을 보다 작은 단위로 나눔으로써 성능이 저하되는 것을 방지하고 관리를 수월하게 하고자 하는 개념이다. 하지만 무조건적인 파티셔닝으로는 원하는 목적을 달성하기 어렵다. 파티셔닝 기법에 대한 개념적 차이를 이해하고, 적절한 파티션 전략을 구사하는 방법을 정리했다.
최근 10년간 데이터베이스 기술의 발전과 더불어 컴퓨터의 활용이 증가하면서 기업이 축적하고 있는 데이터 량도 상상할 수 없을 만큼 증가했다. 하드웨어와 소프트웨어기술의 발전은 사용자로부터 더 많은 정보요구를 수용하게 만들었고 이렇게 축적된 정보량은 기하급수적으로 증가하면서 대용량 데이터를 보다 안전하고 효율적으로 관리하기 위한 파티셔닝 방안이 도입됐다. 파티셔닝은 용량이 큰(지속적으로 증가하는) 테이블을 파티션이라는 보다 작은 단위로 나눔으로써 성능 저하를 방지하고 관리를 보다 쉽게 하고자 하는 개념이다. 그렇지만 대용량의 데이터를 무조건 파티셔닝만 한다고 해서 목적이 이뤄지는 것은 아니다. RDBMS의 데이터 저장구조, 파티셔닝 기법에 따라 파티셔닝의 개념적 차이가 존재하기 때문에 이를 이해하고 적절한 파티션 전략을 구사해야 관리 용이성과 성능을 확보할 수 있다.
파티션 키의 선정
앞서 언급한 것처럼 무조건 파티션을 나눈다고 파티션이 가지고 있는 이점을 모두 얻을 수 있는 것은 아니다. 잘못된 인덱스가 처리 속도에 악영향을 미치듯 파티션 키를 어떻게 구성하느냐에 따라 비효율을 초래할 수도 있다. 따라서 파티션 키의 선정은 다음과 같은 기준에 따라 전략적인 관점에서 고려돼야 한다.
첫째, 성능향상을 위해 어떤 부분을 고려해야 하는가?
데이터를 처리하는 방법은 크게 인덱스를 경유하는 랜덤 액세스 방법과 인덱스를 경유하지 않고 전체 데이터를 스캔 하는 두 가지가 있다. 데이터의 분포도가 좋아서 인덱스를 사용할 수 있는 상황이라면 문제될 것이 없지만 분포도가 나빠서 인덱스를 사용할 수 없다면 인덱스를 사용하지 않고도 필요한 부분만 액세스를 할 수 있도록 파티셔닝이 이뤄져야 한다. 즉, 액세스 유형에 맞게 파티셔닝이 이뤄질 수 있도록 파티션 키를 선정해야 한다.둘째, 용이한 데이터 관리를 위해 어떤 부분을 고려해야 하는가?
이력을 관리하는 데이터는 데이터 관리 전략과 업무규칙에 따라 그 수명이 다하면, 별도 저장장치에 기록되고 데이터베이스에서 삭제된다. 이력 데이터는 활용가치에 따라 생성주기와 소멸주기가 결정되고 주기에 따라 데이터베이스를 정리해야 한다. 만약 사용자가 사용하지 않는 데이터를 삭제해야 되는데 그 데이터가 여러 파티션에 분산돼 있다면, 그 데이터를 추출해 삭제하는데 많은 노력과 시간이 필요할 것이다. 하지만 파티션이 데이터의 생성이나 소멸주기와 일치한다면 파티션을 대상으로 작업이 이뤄지므로 관리가 용이하다. 그러면 파티션 키의 선정에 따라 RDBMS에서 조인을 선정하는 방식과 성능적인 부분에 대해 알아보자.파티션키가 동일한 경우의 조인
대부분의 상용 RDBMS는 조인에 참여하는 두 테이블의 파티션 형태에 따라 다양한 형태의 파티션 조인을 지원하지만, 의미적으로는 크게 파티션 키가 동일한 경우와 파티션 키가 다른 경우이기 때문에 이 두 가지 형태만 알아보자.
DB2 환경에서 파티션 키가 동일한 경우
DB2에서 파티션 키가 동일한 경우 두 테이블은 같은 데이터베이스 파티션 그룹에 공동 배치되고, 조인 또는 서브쿼리를 위해 둘 이상의 테이블에 액세스할 때, 조인할 데이터가 동일한 데이터베이스 파티션에 위치돼 있는지를 인식한다. 이 때 동일한 파티션에 존재하는 경우 데이터가 저장된 데이터베이스 파티션에서 조인하거나 서브쿼리를 수행한다. 이러한 조인방식을 Collocated Join이라 하며, 데이터가 상주하는 파티션 내에서 로컬 조인이 수행되고 파티션 조인이 완료된 후 데이터를 코디네이트 노드로 보낸다(<그림1> 참조).
오라클 환경에서 파티션 키가 동일한 경우
오라클에서 파티션 키가 동일한 경우에는 파티션끼리만 조인을 시도하는 Partition-wise join방식을 사용한다. 이 방식은 조인되는 2개의 테이블이 모두 조인 컬럼으로 동일하게 파티션 돼 있고 두 테이블 모두 Parallel 대상일 경우, 동일 범위 파티션끼리만 조인을 시도하는 Full partition-wise join을 수행한다.그런데 Full Partition-wise Join은 사용자가 아무리 많은 Parallel Degree를 지정하더라도 파티션 개수 이상의 parallel process가 실행되지 않는다. 파티션 개수만큼의 Parallel Process가 실행됐다면 파티션 1개당 parallel process 1개가 할당되고 각 파티션 별로 해당 파티션끼리만 조인을 한 후 결과를 반환한다.
이런 처리 방법은 파티션 키가 조인키이므로 조인 시 다른 파티션들의 데이터를 고려할 필요가 없어 가능하다.
이 조인 방법은 플랜의 “PARTITION RANGE ALL”이 각각의 “TABLE ACCESS FULL” 위에 나타나지 않고 “JOIN” 위에 나타나는 것으로 확인할 수 있으며, 이것은 파티션 별로 조인한 후에 결과를 합친다는 의미다. DB2와 오라클은 데이터의 저장구조가 다르고 파티션 구현 방식이 차이가 나기 때문에 파티션 조인을 수행하는 방식에 약간의 개념적인 차이가 존재한다. 그렇지만 파티션 키가 동일한 경우에는 조인 수행 시에 파티션 키가 동일한 파티션끼리 쌍을 이뤄 동일한 파티션 내에서 조인을 수행하고 그 결과를 코디네이트 노드로 전송하는 방식으로 조인을 수행하기 때문에 파티션 조인 중 가장 좋은 성능을 낼 수 있다.
파티션 키가 다른 경우의 조인
파티션 키가 다른 경우의 조인 방식은 기본적으로 한쪽의 파티션 키를 기준으로 파티션 한 후 다른 테이블의 파티션이 존재하는 파티션으로 전송, 조인을 수행한 후 그 결과를 다시 코디네이터 노드로 전송하는 방식으로 조인한다. 이때 다른 파티션 키를 가진 테이블의 크기나 상태에 따라 파티션을 하고 데이터를 전송하는 방식에 약간의 차이는 있지만 기본적인 처리 방식은 거의 비슷하므로 가장 일반적인 사례를 통해 처리 방식을 알아보도록 하겠다.
DB2 환경에서 파티션 키가 다른 경우
DB2에서 파티션 키가 다른 경우 두 테이블은 같은 데이터베이스 파티션 그룹에 존재하지 않는다. 따라서 조인 시에 Outer 테이블의 각 행은 Inner 테이블의 파티션 속성에 기초해 Inner 테이블의 한 파티션으로 보내지고, 그 파티션에서 조인이 수행된다(<그림 3> 참조).
오라클 환경에서 파티션 키가 다른 경우
조인 수행 시에 하나의 테이블만 조인 키에 의해 파티션 돼 있고 다른 하나는 파티션 돼 있지 않는 경우에 병렬 처리가 수행될 경우 오라클은 수행 속도 향상을 위해 partial parition-wise join 방식으로 조인을 수행한다. 이때 오라클은 파티션 조인을 수행하기 위해 파티션 되지 않은 다른 테이블의 파티션 키를 기준으로 동적으로 파티션하고, 각각의 파티션 쌍끼리 조인이 가능하도록 데이터를 구성한다.
이 조인 방법을 수행을 위해서는 PARALLEL Execution Server를 두 SET로 나누고, 첫 번째 SET 쿼리 서버에서는 파티션 되지 않은 테이블을 병렬로 읽어 파티션 키 별로 구분해 두 번째 SET 쿼리 서버에 분배하고, 두 번째 SET 쿼리 서버에서는 SET1에서 받은 로우와 자신에게 할당된 파티션과 조인을 수행하게 된다. 예를 들어 SALE 테이블의 파티션1과 매칭하는 CUSTOMERS의 모든 로우들은 SET2의 서버 1로 보내지게 되고, SET2의 서버들은 SET1 서버들로부터 받은 로우들과 자신에게 소속된 파티션과 조인을 수행한다.
이런 메커니즘 때문에 partial partition-wise join으로 수행되는 경우에는 DOP*2개의 프로세서가 수행된다. 하지만 파티션 테이블의 각 파티션에는 하나의 프로세서만 할당할 수 있어 조인 시에 일을 하게 되는 프로세서의 수는 파티션의 수로 한정된다. 조인 시 SQL의 실행계획에서 PARTITION RANGE ALL 오퍼레이션이 파티션 테이블의 위에 나타나고 파티션 되지 않은 다른 테이블의 DISTRIBUTION 컬럼에 “PART(KEY)”라는 항목이 나타나면 Partial partition-wise join으로 수행된다고 생각하면 된다.
파티션 정책 수립
파티셔닝은 논리적 데이터 모델에서 물리적 데이터 모델로 전환하면서 이뤄지는 작업으로 논리적 데이터 모델의 초기 단계에서 데이터 볼륨이 큰 엔터티들에 대해 수직적/수평적 분할을 고려하게 되는데 이 엔터티들이 향후 파티션으로 구현될 후보가 된다. 그렇다고 관리자가 임의로 파티셔닝 여부를 결정할 수는 없으며 아래와 같은 작업을 거쳐 업무 담당 및 현업부서의 동의를 도출해야 한다. 파티션 정책수립의 초기단계에서 관리자가 해야 할 일은 크게 두 가지로 나뉜다.
1. 시스템의 스토리지 구성을 고려한 데이터 파일구성의 제약사항(한계) 값 등을 조사해야 한다. 즉, 아래와 같은 시스템 한계점(bandwidth)들을 기준으로 관리자가 파티션을 적용할 절대 기준을 마련한다.
• DBMS가 지원하는 파티션 개수의 한계
• DBMS가 지원하는 테이블 크기의 한계
• DBMS 버전이 가진 파티션 사용의 제약사항
예 1> key update 과부하 등의 예를 2개 정도 든다.
예 2> split
• 현재 사용하고자 하는 버전의 데이터베이스에서 지원하는 파티션 관리 기능 중 대표적인 관리 명령어를 3개 정도 예를 든다.
2. 파티션이 필요한 테이블들을 조사하기 위해서 현업을 대상으로 교육, 조사할 가이드 및 리서치 자료를 만든다. 이 단계에서는 우선 파티셔닝이 필요한 테이블들의 특성을 크게 아래 세 개의 범주로 나누고 대상 테이블들의 특징을 <표 1>과 같은 양식으로 조사한다.
• 용량이 큰 이력 관리용 테이블
- 이력 데이터는 아니지만 절대적으로 용량이 커서 문제가 될 만한 테이블의 경우 설계단계에서 로우 사이즈가 확정되면 관리자는 각 테이블의 예상 데이터 건수와 보관주기를 바탕으로 도출한다. 이력 데이터의 경우 현업이 필요로 하는 온라인 보관주기와 유효보유기간이 지난 데이터의 처리방안을 결정해야 하며, 지난 이력 데이터들이 수정되는 경우가 발생하는지도 확인해야 한다.• 대용량 병렬 배치작업의 대상이 되는 테이블
- 테이블 상세설계가 끝나고 프로그램 상세설계단계에 도출이 어려울 수도 있는데 이 경우에는 통합 테스트 시에 I/O 컨텐션이 발생하는 큰 테이블 위주로 선정하면 된다.• 여러 노드로부터 동시적인 데이터 액세스를 일으켜 LOCK 컨텐션이 예상되는 테이블
- 이 경우는 각 노드에 분산되는 업무의 특성과도 밀접한 관련이 있으며 각 노드의 업무적인 특성을 반영시켜 LOCK을 최소화해 구성해야 한다. 필요에 따라 각 노드의 서버 프로그램 목록을 업무적 기준으로 분류, 각 프로그램이 액세스하는 테이블의 I/O형태를 파악한다.
<표 1>에 취합된 테이블 중에서 테이블 크기가 DBA가 나름대로 정한 절대 기준치를 초과하거나 디스크 I/O 컨텐션이 예상되는 테이블들을 파티션 작업 대상 테이블로 선정한다.
테이블별 파티션 정책
테이블별 파티션 정책을 수립하기 위해서는 파티션 종류의 결정, 파티션 키의 선정, 파티션 수의 결정과 같은 단계를 거쳐야 한다. 여기에서는 각 RDBMS에서 지원하고 있는 파티션의 종류의 특징에 대해 별도로 언급하지 않았지만, 어떤 파티션을 적용할 것인가에 대해 판단하려면 사용하고자 하는 RDBMS의 파티션 종류별 특징의 이해는 필수다. 파티션 키의 선정은 파티션 정책에서 가장 중요한 단계로 파티션의 성공이 여기에 달려 있다고 해도 과언이 아니다. 앞서 이 부분에 대해 별도로 언급했지만 그것은 단지 단일 테이블의 파티셔닝 기준에 불과하다.
효율적으로 파티션 키를 선정하기 위해서는 자신의 액세스 패스 외에 파티션 키가 조인에 미치는 영향도 즉, 조인 효율을 고려해야만 한다. 이것은 인덱스 전략 수립 시 하나의 액세스 패스만 보고 인덱스를 결정하지 않는 것과 같은 이치다. 앞서 파티션 정책에 관한 두 단계에 대해 이미 언급했기 때문에 여기에서는 파티션 수의 결정에 대해서만 생각해 보자. 만약 업무 담당자와 면담에서 다음과 같은 요건이 정의된 테이블이 있다면 2년 간의 데이터를 몇 개의 파티션으로 나눠 관리하는 것이 적절할까?
파티션의 수를 몇 개로 할 것인가를 결정할 다양한 외부 영향 요소들을 면밀히 분석해 봐야 한다. 결국 이것은 데이터베이스 가용성과 성능 사이의 고민이 될 것이며 또한 파티션 데이터의 관리, 백업, 재구성이 용이하도록 파티션의 수를 결정하는 것이 효과적이다. 파티션의 수를 결정하는 중요 고려 사항은 다음과 같다.
• DBMS가 지원하는 데이터파일의 최대 개수
• DBMS가 지원하는 한 데이터파일의 최대 사이즈
• I/O 집중의 단위
- 예를 들면 동일시점 데이터에 대해서 지점별, 사원별, 상품별 등의 단위로 동시 트랜잭션이 집중적으로 모이는 경우 적절한 I/O분산을 고려한 개수.• 관리단위
- 위 표와 같은 경우 변경이 일어나는 현 시점 데이터와 변경이 일어나지 않는 과거 데이터는 일단 분리하고 조회용으로만 사용되는 과거 데이터에 대해서는 일정 단위로 등분해 저장한다.결론
지금까지 조인에 참여하는 두 테이블의 파티션 키 상태에 따른 병렬처리 방식과 조인 효율에 대해 알아봤다. 파티셔닝은 논리적 단계에서 물리적 단계로 전환하는 시점에 이뤄지기 때문에 물리적 특성을 반영하는 것이 파티션 정책에서 매우 중요하다. 하지만 이러한 접근은 자칫 나무를 보고 숲을 보지 못하게 되는 오류를 범할 수 있다. 즉, 개별적인 테이블 단위의 파티션 정책 수립은 데이터베이스 전체적인 관점의 효율에는 크게 도움이 되지 않을 수도 있다. 때문에 개별적인 파티션 정책 수립 후에는 파티션의 목적에 따라 파티션 키를 통합하고 액세스 패스를 일치시키는 등과 같은 전략적인 차원의 조율이 이뤄져야 한다.
[출처] 테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리|작성자 주디
'데이터베이스 > SQL Server' 카테고리의 다른 글
| 최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2) (0) | 2008.05.07 |
|---|---|
| 송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1) (0) | 2008.05.07 |
| 송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2) (0) | 2008.05.07 |
| master db 복구 SQL 2005 (0) | 2008.05.07 |
| SQL Server의 기본 인스턴스를 단일 사용자 모드로 시작하는 방법 SQL 2005 (0) | 2008.05.07 |
