DATOR


파티션관리를 통해 알아보는 BLOCK I/O 작업량 SQL튜닝 사례


 테이블의 용량이 수천만, 더 나아가서 수억~수십억의 데이터가 쌓이면 일반 힙구조 테이블과 인덱스만 가지고는 성능을 낼 수가 없다.

 인덱스는 기본적으로 Single Block I/O이며, Random Access로 Block에 접근하기 때문에 클러스터링 팩터가 좋지 않은 테이블에서 수천건만 되어도 인덱스를 통한 접근으로는 좋은 성능을 기대하기가 어렵다.

 이러한 기본적인 상식은 데이터베이스에 전문적인 상식이 없더라도 경험상으로 많은 개발자들이 이미 알고 있는 사항일 것 이다.

 때문에 실질적으로 접근해야 할 Block의 양은 많지 않지만, 그 Block에 효율적으로 접근할 방법이 없어 야간 배치 작업으로 개발자가 접근하기 편한 구조로 테이블을 생성해 대용량 데이터의 작업을 하는 일은 심심치 않게 발생한다.

 이러한 방법이 다각도 적인 분석으로 활용할 수 있는 데이터를 생성한다면 문제가 없지만, 중대형 규모의 시스템이 아니라면 이러한 개념으로 데이터를 변환한다기 보다 특정 업무단에서 사용하기 위한 무분별한 데이터변환 및 복제를 하고 있는 개발자가 많이 있다는 것이 현실이다.

 이 번 글에서는 특별한 시나리오를 통해 파티셔닝을 이용한 관리가 쿼리 속도 및 시스템 성능 향상을 가져올 수 있는 지를 알아보도록 한다.

 A업체는 전자 가전 업체로서 컴퓨터, TV 등 각각의 제품군 별로 브랜드를 가지고 있다.
 A업체의 회원 관리는 기본 정보는 통합 회원으로서 관리하지만 각각 브랜드별로 개별 가입이 가능하다.
 A업체의 회원 체계는 아래의 ERD와 같다.


 회원 정보는 통합 관리 하지만, 브랜드 가입 테이블에 가입 브랜드, 가입일자, 이메일 및 SMS 수신여부등을 관리하여 브랜드 가입 테이블에 데이터가 입력된 경우만 해당 브랜드에 권한을 가질 수 있는 형태라고 할 수 있다.

현재 데이터는 약 3년간의 데이터이며 5개의 브랜드 사이트에 약 5천만건의 데이터가 입력되어 있다.
 
 이 데이터를 통해 해당 부서의 현업이 주로 알고자 하는 정보는 브랜드별 월별 가입자 수, 가입자 중 EMAIL수신 동의자 수,  SMS수신 동의자 수 등의 정보를 알고자 한다.
 일간 평균 회원 가입 건수는 브랜드당 8000~10000건으로 브랜드별로 월 30만건이 입력되고 있다.

 5000만건 중 30만건을 조회하기 위해 인덱스를 이용해 테이블 랜덤 억세스를 통해 데이터를 조회하고자 한다면 당연히 성능을 보장 받을 수 없다.

 때문에 기존 어플리케이션은 아래와 같은 테이블을 생성해 통계 및 기타 회원 정보를 조회하고 있었다.



  이렇게 회원 정보 및 브랜드 가입 정보를 1개의 로우로 반정규하여 전체 데이터의 로우 갯수를 줄이고 사용 하고 있었다. 
 기존 시스템 담당자는  전체 로우는 줄어들었지만 그렇다고 하여 인덱스를 효율적으로 이용하여 데이터를 검색할 수 없다고 판단하였다. 때문에 해당 업무를 웹 어플리케이션으로 개발하기 어렵다 생각하여 해당 업무를 위해 개발자가 쿼리문을 작성하여 툴을 이용해 엑셀파일로 Export하여 현업 담당자에게 전달한다.
 600만건 중 30만건을 인덱스를 이용하여 테이블 엑세스 하는 것을 비효율적으로 알고 있는 개발자는 해당 테이블을 읽이 위해 쿼리문에 /+* FULL */ 힌트를 주어 600만건의 데이터를 풀스캔 하도록 유도하였다.

이 시스템은 아래와 같은 문제가 있었다.

1. 업무 프로세스의 복잡성
본문에 나와 있듯, 현업 담당자는 통계자료를 위해 응용 프로그램을 이용하는 것 이 아니라 쿼리문 조작이 가능한 개발자에게 업무요청을 하고 데이터를 받아보고 있다. 이는 현업 담당자와 개발자 모두 시간을 소요하는 작업이다.

2. 버퍼캐시 경합
 위의 테이블은 5000만건이던 브랜드별 회원가입 테이블을 600만건을 만들기 위해 브랜드가입 정보를 반정규화 하였기 때문에 하나의 로우사이즈가 크다.
 이러한 테이블을 풀스캔하여 버퍼캐시에 올린다면 트랜젝션이 몰리는 시간대에 버퍼캐시 경합이 발생하여 많은 페이지 변경이 일어나게 되어 전체 성능의 문제가 발생할 수 있다.

 위와 같은 일은 담당자가 데이터베이스의 데이터 검색에 대해 잘 못 된 이론을 가지고 있기 때문에 이러한 관리를 하고 있었던 것 이다.

 담당자가 잘 못 알고 있던 이론은 다음과 같다.

1. 데이터의 일부를 읽어 오는 방법은 인덱스를 이용한 랜덤 억세스 밖에 없다.
2. 인덱스의 손익 분기점은 조회건수가 전체 건수의 10~20%가량이고 대용량의 건수는 1%정도일 경우도 있으며 인덱스를 읽을 때 전체 3000건 이상이면 대부분 비효율적이다.
3. 손익 분기점을 벗어나면 테이블 풀스캔을 하는 것이 더 효율적이다.

 위의 내용은 많은 개발자들이 알고 있는 내용이다.
 담당자는 위와 같은 잘못된 이론을 토대로 8000만건을 풀스캔하는 것 보다는 600만건을 풀스캔하는 것이 더 효율적이라고 생각하여 두번째 그림과 같은 반정규 테이블을 생성한 것 이다.
 하지만 위의 내용은 아래와 같이 잘 못 된 내용이다.

1. 데이터의 일부를 읽어 오는 방법은 파티션 플루닝, 클러스터 인덱스 억세스, IOT인덱스등 시퀀셜 리드가 가능한 여러가지 기법이 있다.
2. 엑세스의 성능은 전체 데이터의 퍼센테이지의 문제이기 보다 블럭 I/O가 더 크다 클러스터링 팩터가 좋아 조회하고자 하는 데이터가 물리적으로 잘 모여있다면 인덱스를 이용하더라도 효율적인 성능을 낼 수 있다. 위에서 설명한 클러스터 인덱스, IOT등을 이용하면 기술적으로 데이터의 클러스터링 팩터를 모아둘 수 있다.
3. 풀스캔이 인덱스를 이용한 랜덤 엑세스 보다 빠른 것은 맞다. 하지만 대용량의 테이블을 풀 스캔 하기 보다 병렬처리, 파티션 플루닝 등을 통해 동시에 여러 프로세스로 작업하거나, 읽고자 하는 파티션만 풀스캔 하는 것 이 좋다.

 이번 예제는 조회 패턴이 날짜, 브랜드라는 조건으로 조회하는 경우가 많다.
 이번 예제는 파티션을 적용하였을시 대량의 데이터에서 일부분을 조회할 때의 효율을 알아보도록 한다.
  
 먼저 예제를 위해 데이터를 생성해 보도록 한다.

 CREATE TABLE BRAND_REG_INFO
PARTITION BY RANGE(REG_DATE)
SUBPARTITION BY LIST(BRAND_CD)
SUBPARTITION TEMPLATE (
  SUBPARTITION BRI_SUB_TV VALUES('TV'),
  SUBPARTITION BRI_SUB_AUD VALUES('AUD'),
  SUBPARTITION BRI_SUB_VDO VALUES('VDO'),
  SUBPARTITION BRI_SUB_PC VALUES('PC'),
  SUBPARTITION BRI_SUB_DVD VALUES('DVD')
)
(
 PARTITION BRI_200809 VALUES LESS THAN ('20081001')
 , PARTITION BRI_200810 VALUES LESS THAN ('20081101')
 , PARTITION BRI_200811 VALUES LESS THAN ('20081201')
  , PARTITION BRI_200812 VALUES LESS THAN ('20090101')
  , PARTITION BRI_200901 VALUES LESS THAN ('20090201')
  , PARTITION BRI_200902 VALUES LESS THAN ('20090301')
  , PARTITION BRI_200903 VALUES LESS THAN ('20090401')
  , PARTITION BRI_200904 VALUES LESS THAN ('20090501')
  , PARTITION BRI_200905 VALUES LESS THAN ('20090601')
  , PARTITION BRI_200906 VALUES LESS THAN ('20090701')
  , PARTITION BRI_200907 VALUES LESS THAN ('20090801')
  , PARTITION BRI_200908 VALUES LESS THAN ('20090901')
  , PARTITION BRI_200909 VALUES LESS THAN ('20091001')
  , PARTITION BRI_200910 VALUES LESS THAN ('20091101')
  , PARTITION BRI_200911 VALUES LESS THAN ('20091201')
  , PARTITION BRI_200912 VALUES LESS THAN ('20100101')
  , PARTITION BRI_201101 VALUES LESS THAN ('20110201')
  , PARTITION BRI_201102 VALUES LESS THAN ('20110301')
  , PARTITION BRI_201103 VALUES LESS THAN ('20110401')
  , PARTITION BRI_201104 VALUES LESS THAN ('20110501')
  , PARTITION BRI_201105 VALUES LESS THAN ('20110601')
  , PARTITION BRI_201106 VALUES LESS THAN ('20110701')
  , PARTITION BRI_201107 VALUES LESS THAN ('20110801')
  , PARTITION BRI_201108 VALUES LESS THAN ('20110901')
  , PARTITION BRI_201109 VALUES LESS THAN ('20111001')
  , PARTITION BRI_201110 VALUES LESS THAN ('20111101')
  , PARTITION BRI_201111 VALUES LESS THAN ('20111201')
  , PARTITION BRI_201112 VALUES LESS THAN ('20120101')
  , PARTITION BRI_201101 VALUES LESS THAN ('20120201')
  , PARTITION BRI_201102 VALUES LESS THAN ('20120301')
  , PARTITION BRI_201103 VALUES LESS THAN ('20120401')
  , PARTITION BRI_201104 VALUES LESS THAN ('20120501')
  , PARTITION BRI_201105 VALUES LESS THAN ('20120601')
  , PARTITION BRI_201106 VALUES LESS THAN ('20120701')
  , PARTITION BRI_201107 VALUES LESS THAN ('20120801')
  , PARTITION BRI_201108 VALUES LESS THAN ('20120901')
  , PARTITION BRI_201109 VALUES LESS THAN ('20121001')
  , PARTITION BRI_201110 VALUES LESS THAN ('20121101')
  , PARTITION BRI_201111 VALUES LESS THAN ('20121201')
)
AS
SELECT 'TV' AS BRAND_CD,
  'u' || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000001)), 7, '0') AS SID,
  TO_CHAR(SYSDATE - FLOOR(ROWNUM/ FLOOR(DBMS_RANDOM.VALUE(9000, 12000))), 'YYYYMMDD') AS REG_DATE,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') EMAIL_YN,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') SMS_YN
FROM DUAL
CONNECT BY LEVEL <= 10000000
UNION ALL
SELECT 'AUD' AS BRAND_CD,
  'u' || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000001)), 7, '0') AS SID,
  TO_CHAR(SYSDATE - FLOOR(ROWNUM/ FLOOR(DBMS_RANDOM.VALUE(9000, 12000))), 'YYYYMMDD') AS REG_DATE,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') EMAIL_YN,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') SMS_YN
FROM DUAL
CONNECT BY LEVEL <= 10000000
UNION ALL
SELECT 'VDO' AS BRAND_CD,
  'u' || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000001)), 7, '0') AS SID,
  TO_CHAR(SYSDATE - FLOOR(ROWNUM/ FLOOR(DBMS_RANDOM.VALUE(9000, 12000))), 'YYYYMMDD') AS REG_DATE,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') EMAIL_YN,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') SMS_YN
FROM DUAL
CONNECT BY LEVEL <= 10000000
UNION ALL
SELECT 'PC' AS BRAND_CD,
  'u' || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000001)), 7, '0') AS SID,
  TO_CHAR(SYSDATE - FLOOR(ROWNUM/ FLOOR(DBMS_RANDOM.VALUE(9000, 12000))), 'YYYYMMDD') AS REG_DATE,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') EMAIL_YN,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') SMS_YN
FROM DUAL
CONNECT BY LEVEL <= 10000000
UNION ALL
SELECT 'DVD' AS BRAND_CD,
  'u' || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000001)), 7, '0') AS SID,
  TO_CHAR(SYSDATE - FLOOR(ROWNUM/ FLOOR(DBMS_RANDOM.VALUE(9000, 12000))), 'YYYYMMDD') AS REG_DATE,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') EMAIL_YN,
  DECODE(FLOOR(DBMS_RANDOM.VALUE(1, 3)), 1, 'Y', 'N') SMS_YN
FROM DUAL
CONNECT BY LEVEL <= 10000000
;

* 파티션 구성은 전체 데이터가 가질 수 있는 범위로 해야합니다.
예제 시점에서는 11월 9일 시점으로 데이터를 생성하였을 때를 기준으로 파티션을 구성하였음.

아래의 쿼리문을 날려보도록 한다.

 SELECT BRAND_CD, REG_DATE, COUNT(*) AS CNT FROM BRAND_REG_INFO T
WHERE REG_DATE BETWEEN '20110901' AND '20110930'
AND BRAND_CD = 'TV'
GROUP BY ROLLUP(BRAND_CD, REG_DATE)

2011년 09월에 TV브랜드에 가입자에 대한 일별 가입자 정보를 얻고자 하는 것이다.
데이터의 검색 결과는 다음과 같다.

 
 일별 평균 데이터는 1만여건이고, 총 314427의 데이터를 읽었다.

 30만 건의 데이터를 읽는 데 걸린 시간은 어떻게 될 까?

 


 5000만건의 테이블에서 일부인 30만 건을 읽는 데 걸리는 시간은 불과 0.333초에 불과하였다.
 어떻게 이렇게 빠르게 데이터를 읽을 수 있었던 것인가?

 일단 아래의 실행계획을 확인해 보도록 한다.

TV 20110901 10483
TV 20110902 10468
TV 20110903 10534
TV 20110904 10439
TV 20110905 10601
TV 20110906 10213
TV 20110907 10401
TV 20110908 10611
TV 20110909 10414
TV 20110910 10562
TV 20110911 10614
TV 20110912 10359
TV 20110913 10309
TV 20110914 10425
TV 20110915 10540
TV 20110916 10410
TV 20110917 10512
TV 20110918 10737
TV 20110919 10540
TV 20110920 10716
TV 20110921 10433
TV 20110922 10413
TV 20110923 10485
TV 20110924 10568
TV 20110925 10498
TV 20110926 10390
TV 20110927 10353
TV 20110928 10453
TV 20110929 10482
TV 20110930 10464
TV   314427
 Rows     Row Source Operation
-------  ---------------------------------------------------
     32  SORT GROUP BY ROLLUP (cr=1310 pr=6 pw=0 time=281273 us)
 314427   PARTITION RANGE SINGLE PARTITION: 25 25 (cr=1310 pr=6 pw=0 time=34 us)
 314427    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=1310 pr=6 pw=0 time=31 us)
 314427     TABLE ACCESS FULL BRAND_REG_INFO PARTITION: 121 121 (cr=1310 pr=6 pw=0 time=29 us)

 PARTITION RANGE는 위에 테이블 생성시 메인 파티션으로 잡은 날짜 파티션이다. SINGLE PARTITION은 하나의 파티션만을 읽었다는 것으로, 2011년 09월의 데이터들만 읽었기 때문에 PARTITION BRI_201009 VALUES LESS THAN ('20111001') 으로 나뉜 BRI_2011 파티션만 읽게 된 것이다.
 PARTITION LIST는 브랜드별 서브파티션으로 역시 TV브랜드 파티션만 읽었기 때문에 SINGLE PARTION 실행 계획이 발생하였다.
 TABLE ACCESS FULL BRAND_REG_INFO PARTITION은 메인 파티션중 2011년 09월 데이터가 모인 BRI_201009 파티션 중 BRI_SUB_TV에 해당하는 데이터만 접근하여 풀스캔하였다는 것 이다.
 인덱스를 이용한 TABLE ACCESS BY ROWID의 경우 Single Block I/O이다. 반면 TABLE ACCESS FULL은 Mulit Block I/O 이다.
 즉, 인덱스를 이용한 테이블 스캔은 한번에 하나의 블럭을 읽어 들이는 반면 풀스캔은 여러 블럭을 읽어 들인다. 때문에 5000만건중 30만건의 데이터를 읽었음에도 불과하고 0.328초라는 시간 밖에 걸리지 않는 것이다.

 TRACE를 통해 좀 더 자세히 작업량에 대해 알아보도록 한다.

 


 30만건의 데이터를 읽는 데 물리적으로 디스크에 접근한 건은 1306블럭 밖에 되지 않는 다.
 블럭당 약 230로우의 데이터가 뭉쳐 있는 것이고, 해당 파티션에 대해서만 TABLE ACCESS FULL을 실행 하였기 때문에 Multiblock I/O 로 읽어 들였기 때문에 클러스터링 팩터가 최악일 경우 Singleblock I/O로 데이터를 읽는 것과는 비교도 되지 않을 정도로 속도가 빠르다.
 인덱스를 통한 랜덤엑세스의 경우 최악의 경우 1블럭에 접근하여 하나의 로우 밖에 가져오지 못 할 수 있다. 하지만 위의 예제의 경우 Multiblock I/O의 기본 설정인 16블럭을 한번에 접근 한다면 약 2700여 로우를 한번에 불러올 수 있는 것이기 때문에 이러한 속도 차이가 나게 되는 것 이다.

 예제의 경우 한번에 데이터를 입력하였기 때문에 구지 파티션적용을 하지 않더라도 클러스터링 팩터가 나쁘지는 않겠지만, 위와 같은 스키마라면 실제 실무에서 작업하더라도 비효율은 없다.
 파티션은 각각 파티션 별로 세그먼트를 할당하고, 파티션 플루닝이 일어 날 시 해당 파티션만 읽기 때문에 실무에서 데이터가 불규칙하게 들어오더라도 위와 같은 스키마라면 비효율적인 클러스터링 팩터로 관리 되지는 않는다.

Tag :

Leave Comments