SHBLITZ.NET

지금까지 우리는 데이터베이스 성능관리에 대하여 기존과는 다른 ‘활용’적인 면에 초점을 맞춰 개략적으로 알아보았다.

이번 시간은 단순한 편협한 지식만으로 해결되는 문제가 아닌 데이터베이스 성능에 관해서 필자가 현장에서 컨설팅했던 실전 사례들을 중심으로 데이터베이스 기반의 프로그래밍 사례들을 소개한다.

실제 현장에서 사용되는 프로그램은 크게 온라인 유형과 배치성의 유형으로 나눠지게 된다. 이것은 모든 프로그램의 유형을 대표한다고 하기 보다는 실제 end user(사용자)들이 사용하게 되는 유형으로 개발자들이 프로그래밍을 하는 유형이라고 볼 수 있다.

이렇게 유형을 나누는 이유는 이 유형에 따라 프로그래밍을 하는 방법과 전략이 달라야 하기 때문이다. 실제로 현장에서 많은 성능 문제를 일으키는 이유는 이 두 가지의 단순한(?) 유형의 구분 없이 프로그래밍을 하는 이유가 굉장히 많다.


<그림 1>에서 보듯이 온라인의 프로그래밍은 업무 분석이 선행되어진 상태에서 프로그래밍 기술적인 부분에 들어갈 때 여러 가지 팩터(factor)들을 고려해서 전략을 짜야 한다. 고려해야 할 팩터들은 다음과 같다.

이와 같은 팩터들에 대해 답을 얻었다면 다음과 같은 순서도에 의해 화면 구성과 프로그래밍 전략을 짜야 한다. 이것은 데이터베이스를 기반으로 하는 프로그래밍을 하기 위함이며, 이런 절차를 제대로 밟은 프로그램은 성능 상 전혀 문제를 일으키지 않고 향후 데이터가 많이 쌓이더라도 시스템의 재개발이 필요없게 된다. 따라서 사용자들의 만족도 또한 충족시킬 수 있게 된다.


화면이 복잡해질수록 프로그래밍하기가 어려워지고 SQL이 어렵게 되므로 당연히 성능이 저하되기 마련이지만 <그림 2>와 같은 순서에 입각하여 정리하다 보면 복잡한 프로그램이라도 프로그래밍을 단순하게 짤 수 있는 능력이 길러지게 된다. 온라인 프로그램은 대게 데이터베이스의 조인방식 중 Nested Loops 조인으로 풀리게 짜게 되는데 이것이 전략을 구성하는데 핵심요소가 된다. Nested Loops 조인이란 조인방식 중 가장 전통적이고 기본적인 방식으로 온라인 프로그래밍을 할 때 반드시 확인해야 할 방식이다.

이번 연재에서는 지면 관계상 데이터베이스의 조인 방식에 대하여 자세히 다루지는 않고 관련 자료를 찾아보거나 기회가 되면 자세히 기술하는 시간을 가져보도록 하겠다.

Nested Loops 조인 방식의 가장 핵심은 드라이빙 팩터를 전략적으로 부여해야 하는 것이 핵심이다. 즉, 인덱스와 Random Access로 풀리게 되는 방식인 만큼 드라이빙 팩터의 건수가 적을수록 성능 상에 문제를 일으키지 않는다. 따라서 화면을 구성할 때 단일 조건 또는 조건의 조합이 드라이빙 팩터의 건수를 적게 하기 위한 전략을 구사하면 되는 것이다. 만약 그렇게 불가능하다면 부분범위처리나 배치에 의한 방식으로 전략을 변경해야 성능 상에 문제가 되지 않는다. 실제 여러 가지 사례들을 통해서 데이터베이스 기반의 프로그래밍 방법에 대해 구체적으로 알아본다.

예 1. 효율적인 인덱스를 이용하지 못하고 있는 프로그램


<화면 1>은 어느 ‘봉사자’라는 회원을 조회하는 화면으로 조회 조건에 따라 어떻게 SQL을 구사하는 지를 알아보고자 한다. 간단하게 요약해 보면 다음과 같다.

‘봉사자’ 기본 정보를 조회하는 화면인데 그 조건으로 주민등록번호나 봉사자의 이름이 들어올 수 있다. 해당 테이블의 인덱스 상황에 따라 한 명의 봉사자를 검색하기 위하여 Full Scan을 할 수 있는 부분이다. 따라서 이 부분을 ‘이름’이나 ‘주민번호’가 조건이 들어올 경우로 나누어 SQL을 구성하여 인덱스를 이용할 수 있도록 Access Path를 고정함으로써 효과적인 인덱스 Access를 할 수 있도록 유도한다.



2) 봉사자의 주민번호가 들어올 경우


기존에는 이 검색조건만을 위해 bm_name, bm_jumin의 단일 인덱스가 만들어져 있었으나 이 쿼리는 내부적으로 cent_sigun이라는 상당히 변별력 있는 조건이 항상 같이 들어오게 되므로 단일 인덱스를 drop하고 현재는 cent_code+bm_jumin, cent_code+bm_name 등의 결합 인덱스를 만들었고 이 인덱스를 쓰기 위해서 이와 같이 SQL을 분리하고 힌트를 이용하여 인덱스를 쓸 수 있도록 유도했다.

물론 주민번호 13자리가 전부 조건에 들어오고 bm_jumin이라는 인덱스를 이용하게 되면 성능에는 문제가 없겠지만 기존에는 주민번호와 이름의 자리 수 제약이 전혀 없었고 이름으로만 조회하는 경우 같은 성을 가진 회원(김XX )이 수없이 많기 때문에 화면에 뿌리는 데 어려움이 많았지만 cent_sigun이라는 변별력이 좋은 조건을 함께 결합인덱스를 이용하여 필수 조건화했기 때문에 전혀 문제가 되지 않았다.

예 2. 다양한 검색조건과 많은 양의 결과 수를 뿌릴 경우


이 프로그램은 어느 전자회사의 상품판매 고객을 관리하는 프로그램으로 소위 콜센터에서 주로 이용하는 프로그램이다. 즉 고객의 전화가 올 경우 고객을 응대하기 위해 고객 정보를 조회하는 프로그램이다. 고객 정보를 여러 가지로 입력할 수 있게 하여 다양하게 조회를 할 수 있도록 프로그래밍된 온라인 화면이다. 이 화면을 분석한 결과는 다음과 같다.

문제점 및 원인
이 프로그램은 고객을 조회하는 화면으로 콜센터 업무의 Intro 업무이다. 조회조건이 다양한데 ‘주민등록번호’와 ‘카드번호’가 입력될 경우는 결과 Set이 많지 않을 것이지만 대부분 업무에서 조회할 것으로 예상하는 ‘고객명+휴대폰’이나 ‘고객명+자택전화번호’ 같은 경우는 조건 값에 따라서 많은 양의 결과 Set이 나올 것으로 예상이 된다.

3-티어 구조에서는 조건 값에 맞는 쿼리가 끝나지 않으면 결과로 나타나지 않고 또한 결과 Set이 많으면 그 결과 Set을 네트워크로 끌어오거나 화면에 뿌릴 때 상당한 시간이 소요되므로 업무에 대단한 지장을 초래할 가능성이 있다.

결과 Set이 많을 경우에 첫 페이지의 경우는 빨리 뜰 수 있으나 ‘NEXT’를 누르면 누를수록 속도 저하가 일어나게 되는 것이다. 기존에 되어 있던 프로그램 방식은 흔히 웹 프로그램에서 많이 볼 수 있는 것인데 조건에 따라 SQL을 조합해서 만드는 (if… else if에 의해 SQL을 조합) 조합형 SQL을 구사하고 있다. 이는 데이터베이스가 유일하게 이해할 수 있는 SQL과 일반 로직 부분을 혼용해서 이용하고 있는 것이다.

이렇게 되면 데이터베이스는 조건에 따라 다양한 SQL을 받아들이게 되고 전체적으로 Access Path가 최적화되지 못하여 조건에 따라 성능 차이가 심해지게 된다. 또한 결과 건수에 따른 부분범위 처리 방식의 프로그래밍 또한 불가능하게 되어 데이터베이스 중심의 프로그래밍이 아닌 절차형의 프로그램이 되는 것이다.

개선방안
‘주민등록번호’나 ‘카드번호’가 입력될 경우는 현재 프로그램 방식으로 이용해도 별 무리가 없어 보이고 ‘고객명+주민등록번호’의 경우는 ‘주민등록번호’ 조건으로 대체 이용 가능하므로 크게 문제가 되지 않을 것으로 보이나 ‘고객명+휴대폰’ 또는 ‘고객명+자택전화번호’ 같은 경우는 다음과 같이 부분범위처리 방식으로 프로그램을 변경해야 한다. ‘고객명’이 들어올 때 동명이인의 경우 휴대폰 번호와 Rowid 값을 이용해서 구분하여 페이징을 한다.

‘부분범위처리’ 방식의 프로그램이란 개념은 간단하나 구현에 있어 고도의 기술을 요하는 것으로 웹 페이징 처리 시 필요한 부분만 데이터베이스에서 읽어 올 수 있게 구현하는 방식을 말한다(지면 관계상 세부 내용은 메일이나 다른 지면을 통해 소개하도록 하겠다). 기존 프로그램을 ‘부분범위처리’ 방식으로 필자가 구현한 내용을 소개한다.



이 방식은 다양한 조건을 인덱스를 이용하여 Access Path를 고정시키고 ‘NEXT’를 누를 경우 해당하는 키 값을 넘겨주어 화면의 개수만큼만 DB를 Access하도록 하는 방식이다. 본 내용을 현재 다 이해하지 못하여도 상관이 없다. 이런 방식이 있다고 하는 것만 알게 된다면 독자 여러분은 그 목적을 다했다고 생각한다.

예 3. 굉장히 다양한 검색조건의 화면의 경우


이 프로그램은 처음에 소개한 예제의 상세 검색 화면에 해당된다. 즉, ‘회원’의 가입당시의 여러 조건들을 다양하게 검색할 수 있도록 구성한 화면인 것이다.

이런 화면을 구성할 때 개발자들은 데이터베이스 위주의 프로그램이 불가능하다고 생각하고 있고 로직 위주의 프로그램을 구사하면서 개발자 편의의 프로그램을 짜고 있는 것이다. 하지만 이런 복잡한 프로그램일수록 데이터베이스 위주의 즉, SQL 위주의 프로그램을 구사해야 프로그램이 단순해지고 성능상의 위력을 발휘할 수 있으며 데이터의 정확도와 차후 변경사항의 유지보수 등이 좋아지게 된다.

일단 이렇게 복잡하게 구성해야 하는 프로그램은 다음과 같은 단순한 절차를 생각하면 된다.

이 부분도 ‘부분범위처리’ 방식으로 구현한 것으로 로직으로 밑줄로 되어 있는 부분이 드라이빙 팩터이고 나머지 조건들은 전부 필터링 팩터에 해당된다. 구사했던 부분을 SQL로 구현하는 것은 쉽지 않지만 ‘생각의 전환’을 하게 되면 그리 어려운 부분도 아니다. 필자는 같이 프로젝트를 했던 개발자들이 프로젝트 종료시점에 놀랄만한 SQL 구사 능력 향상을 가져온 것을 여러 번 경험했다.

예 4. 인덱스 전략의 부재로 성능저하가 있는 경우


이 프로그램은 어느 유통회사에서 ‘상품’을 조회하는 간단한 화면으로 회사의 특성상 관리하는 상품의 종류와 수가 많고 대부분의 업무 자체가 이 상품을 관리하는 것이다 보니 이 간단한 프로그램을 제일 많이 사용하면서도 성능 저하가 가장 많이 되고 있는 프로그램이었다. 예를 들어 앞의 화면에서 ‘메이커’와 ‘품목’조건에 둘 중 하나만 들어와도 실행이 가능하다고 해보면, ‘메이커’의 조건에 ‘삼성’이라는 값을 주고 조회를 할 수 있다. 만약 이런 현상이 발생한다면 이 시스템의 문제는 심각해진다. 그런데 이것이 실제 사례이다.


문제점 및 원인
상기 프로그램은 상품조회 화면에서 쓰이는 프로그램으로 상품의 ‘메이커’ 또는 ‘품목’을 조회조건으로 하고 있다. 조건으로 들어온 ‘삼성’이라는 메이커에 해당되는 데이터량은 엄청나다. 즉, ‘삼성의 모든 상품을 조회’하는 결과가 되는 것이다. 따라서 데이터베이스를 엑세스하여 화면에 뿌리는 데는 상당한 시간이 걸리고 있는 것이다.

개선방안
‘메이커’나 ‘품목’ 두 조건을 모두 Madatory 조건으로 변경하고 ‘메이커’ 부분은 데이터베이스에서 ‘=’로 엑세스할 수 있도록 하며 ‘메이커’+ ‘품목’으로 결합 인덱스를 구성하여 결과 Row 수가 최소화되도록 유도하여 빠른 응답시간을 유도한다.


지금까지 필자가 실제로 경험했던 간단한 몇 개의 예제들을 통해 RDBMS에 기반을 두어 성능개선을 한 사례를 살펴 보았다. 데이터베이스 기반의 프로그래밍은 결코 어렵지 않다. 다만 대부분의 개발자들이 접근을 잘못하고 있을 뿐이다. 그리고 앞의 사례들을 현재 100% 설명하기란 지면관계상 불가능한 점을 안타깝게 생각한다. 하지만 이번 연재를 통해 데이터베이스 컨설팅 영역에 대해 공감을 했다면 필자의 목적을 달성한 것이라 생각한다. 자세한 기술적인 문제들은 다음 기회가 되면 자세히 설명하겠다.

이로써 본 연재가 여러 자동화된 툴을 단순히 이용하는 것이 아닌 종합적이고 활용적인 데이터베이스 성능관리 영역의 소개가 되었으면 하는 바람이다. 또한 진정한 데이터베이스 사용 전문가들이 많이 나타나길 기대하는 마음으로 연재를 마친다.

출처 : 마이크로소프트웨어 2005년 4월호

근래에 많은 기업들의 데이터베이스가 대용량화 되면서 이를 효과적으로 관리할 수 있는 방안을 찾는 것이 관리자들의 주요 업무가 됐다. 이를 위한 매우 효과적인 방안 가운데 하나가 파티셔닝이다.

일반적으로 단순한 명령어 위주로만 알려져 있지만 실제 현장에서 접하는 파티셔닝의 효용은 그 이상이다. 익숙한 개념이지만 그동안 제대로 알지 못했던 파티셔닝의 의미와 대표적인 활용 사례를 살펴보자.

필자는 많은 현장 사이트에서 대용량의 가치 있는 데이터들이 놀라운 능력을 보유하고 있는 데이터베이스 안에서 사용자의 무지로 인해 방치돼 있거나 잘못 사용되고 있어 역효과를 일으키는 모습을 많이 보아 왔다. 예를 들어 총 테이블 건수 1억 건이 넘는 상황에서 우리가 어떤 형태로든 건드려야 할 부분이 약 10% 정도라고 할 때 그 테이블 전체를 읽지 않고 1000만 건만 읽을 수 있게 해야 하는 것이 당연하지만 실제로는 그렇지 못한 경우를 많이 보아 왔다.

어떻게 처리해야겠다는 생각도 없이 무조건 명령어(command)부터 날리는 것이다. 그렇다면 필요한 테이블 만을 다루려면 어떻게 해야 할까. 이를 위해 필요한 개념이 바로 테이블 파티셔닝(Table Partitioning)이다.

파티셔닝은 지난 강좌에서 살펴본 사항들과 함께 어떤 자동화된 툴로 절대 해결할 수 없는 부분으로 실제로 어떤 상황에서 파티셔닝이 필요하다고 정형화된 법칙은 없다. 중소 용량의 데이터베이스에서도 상황에 따라 꼭 사용해야 하는 경우가 있고, 초대용량의 경우 파티셔닝을 쓰지 않으면 시스템 자체가 관리되지 않을 수도 있다(필자 역시 컨설팅을 하면서 이 파티셔닝을 이용해 많은 시스템을 효율적으로 운영할 수 있다는 것을 직간접적으로 체험한 바 있다).

그러나 대부분의 파티셔닝 관련 자료들은 형식적으로 파티셔닝의 종류를 나열하고 스크립트 정도를 언급하는 수준이다. 이런 식의 접근은 한계가 명확하다.

오히려 파티셔닝을 올바르게 이용하기 위해서는 먼저 데이터베이스 액세스 방식의 정확한 차이와 장단점 그리고 파티션을 이용한 풀 스캔(full scan)에 대해 정확하게 이해할 필요가 있다. 파티셔닝은 일종의 기능일 뿐이어서 스캔에 대한 정확한 이해없이는 이를 사용할 이유도, 어떻게 사용해야 할지도 전혀 알 수가 없다. 각 스캔 방식의 장단점을 알고 어떤 상황에서 어떤 스캔 방법이 유리한 지를 명확하게 이해해야 그에 대한 보완책으로서 파티셔닝의 개념이 보이기 시작한다.

파티셔닝 세계 입문
대용량 테이블이나 인덱스를 파티셔닝한다는 것은 하나의 Object를 여러 개의 세그먼트로 나눈다는 의미이다. 즉 하나의 테이블이나 인덱스가 동일한 논리적 속성을 가진 여러 개의 단위(partition)로 나누어져 각각이 PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, STORAGE PARAMETER 등 별도의 물리적 속성을 갖는 것이다.

특히 관리해야 할 데이터가 늘어나면 성능과 스토리지 관점에서 문제가 생길 수 있는데, 이를 해결할 수 있는 효율적인 방법 가운데 하나가 곧 파티셔닝이다. 파티셔닝은 보통 다음과 같은 장점을 갖고 있다.

오라클 DBMS에서 제공하는 파티셔닝 방식에는 레인지(range) 파티셔닝, 해시(hash) 파티셔닝, 리스트(list) 파티셔닝, 컴포지트(composite) 파티셔닝(레인지-해시, 레인지-리스트) 등이 있다.

특정 컬럼 값을 기준으로 분할하는 레인지 파티셔닝
레인지 파티셔닝은 어떤 특정 컬럼의 정렬 값을 기준으로 분할하는 것이다. 주로 순차적인(historical) 데이터를 관리하는 테이블에 많이 사용된다. 예를 들면 ‘가입계약’이라는 테이블이 있고 여기에 몇 년 동안의 데이터가 쌓여 있다면, 보통 5년치 데이터만 관리하고 이 가운데 자주 액세스하는 하는 것은 최근 1～2년 정도가 일반적이다.

따라서 이를 년별, 월별로 파티셔닝하고 애플리케이션의 SQL을 조정해 전체 데이터가 아닌 최근 정보를 가지고 있는 파티션만 액세스하도록 하면 전체 데이터베이스의 성능을 향상시킬 수 있다. 일부 사례의 경우 가입계약_1999, 가입계약_2000처럼 월별 또는 년별로 테이블을 따로 만들어 사용하기도 했지만 실제로 쓰는 데 불편한 점이 많고 액세스하는 SQL이 복잡해지는 단점이 있다. 다음은 레인지 파티션을 만드는 DDL(Data Definition Language) 스크립트다.


PARTITION BY RANGE (COLUMN_LIST)는 특정 컬럼을 기준으로 파티셔닝을 할 것인지를 결정하는 것이고, VALUES LESS THAN (VALUE_LIST)는 해당 파티션이 어느 범위에 포함될 것인지 상한을 정하는 것이다. PARTITION BY RANGE에 나타나는 COLUMN_LIST를 파티셔닝 컬럼이라고 하며 이 값이 파티셔닝 키를 형성한다.

파티셔닝 컬럼은 결합 인덱스처럼 최대 16개까지 지정할 수 있다. VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 상한 값으로, 여기 지정된 값보다 작은 값만을 저장하겠다는 의미이다. 이런 스크립트에서 지정한 물리적 속성들은 각 파티션들이 생성될 때 개별적으로 물리적 속성을 지정하지 않으면 각 파티션들은 이러한 속성 값을 적용 받게 된다.

오직 성능 향상, 해시 파티셔닝
해시 파티셔닝은 특정 컬럼 값에 해시 함수를 적용해 분할하는 방식으로, 데이터의 관리 목적보다는 성능 향상에 초점을 맞춘 개념이다. 레인지 파티셔닝은 각 범위에 따라 데이터 양이 일정치 않아 분포도가 일정치 않은 단점이 있는데, 해시 파티셔닝을 이런 단점을 보완해 일정한 분포를 가진 파티션으로 나누고, 균등한 분포도를 가질 수 있도록 조율해 병렬 프로세싱으로 성능을 높인다. 실제로 분포도를 정의하기 어려운 테이블을 파티셔닝을 할 때 많이 이용하고 2의 배수 개수로 파티셔닝하는 것이 일반적이다.

해시 파티셔닝으로 구분된 파티션들은 동일한 논리, 물리적 속성을 가지다(단 테이블스페이스(tablespace)는 유일하게 파티션별로 지정할 수 있다). 또한 레인지 파티션과 달리 각 파티션에 지정된 값들을 DBMS가 결정하므로 각 파티션에 어떤 값들이 들어 있는지를 알 수 없다. 그러나 대용량의 분포도가 일정치 않은 테이블을 마이그레이션할 때는 프로그램 병렬 방식과 함께 유용하게 사용할 수 있다. 다음은 해시 파티션을 만드는 DDL 스크립트이다.


함께 쓰일 때 더욱 강력한 리스트 파티셔닝
리스트 파티셔닝은 특정 컬럼의 특정 값을 기준으로 파티셔닝을 하는 방식이다. 주로 이질적인(distinct) 값이 많지 않고 분포도가 비슷하며 다양한 SQL의 액세스 패스에서 해당 컬럼의 조건이 많이 들어오는 경우 유용하게 사용된다. 예를 들어 ‘서비스 계약’이라는 테이블이 있고 서비스를 최초 가입한 대리점을 ‘가입 대리점’, 변경사항을 처리한 대리점을 ‘처리 대리점’이라고 한다면 모든 서비스의 가입, 해지, 전환 등의 처리 데이터에는 이 두 대리점이 존재한다. 테이블 구조를 보면 다음과 같다.


즉 I_DLR_IND(대리점 구분)라는 컬럼이 존재하고 ‘A’일 때는 ‘가입 대리점’, ‘S’일 때는 ‘처리 대리점“이라고 할 때 대부분의 조회 패턴에는 가입 대리점 또는 처리 대리점에 해당하는 값이 들어오기 마련이다. 이럴 때 I_DLR_IND로 리스트 파티셔닝을 한다면 어떨까. 즉 집합의 서브 타입을 분류할 때 리스트 파티션은 매우 유용하다. 지금 예로 든 것은 단편적인 것에 불과하지만 리스트 파티셔닝의 위력은 강력하다. 특히 컴포지트 파티션에서 레인지 파티션과 함께 사용하면 전체 데이터베이스의 성능을 크게 향상시킬수 있다. 다음은 리스트 파티션을 만드는 DDL 스크립트이다.


PARTITION BY LIST에 나타나는 COLUMN_LIST는 파티셔닝 컬럼으로 파티션 키에 해당하고(단 단일 컬럼만 지정할 수 있다), VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 값이다. 여기에 나타낸 값에 해당하는 행들을 저장하겠다는 의미가 된다.

레인지의 장점을 그대로, 레인지-해시 컴포지트 파티셔닝
레인지-해시 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각각의 파티션 내에서 해시 방식으로 서브 파트셔닝을 하는 방식이다. 서브 파티션이 독립된 세그먼트가 되는 것이 특징으로, 다음과 같은 장점이 있다.

레인지 파티션에서 해당 테이블이 단지 논리적인 구조이고 실제 데이터는 파티셔닝된 세그먼트에 저장됐던 것처럼 컴포지트 파티션에서도 해당 테이블과 파티셔닝된 테이블은 단지 파티셔닝을 위한 논리적인 구조일 뿐이다. 데이터는 가장 하위에 위치한 서브 파티션 영역에 저장된다. 다음은 레인지-해시 컴포지트 파티션을 생성하는 DDL 스크립트이다. PARTITION BY RANGE (I_YYYYMMDD)에 의해 레인지로 파티션을 한 후 SUBPARTITION BY HASH에 의해 서브 파티셔닝을 수행했음을 알 수 있다.


레인지-리스트 컴포지트 파티셔닝
레인지-리스트 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각 파티션 안에서 리스트 방식을 이용해 서브 파티셔닝하는 방식이다(이때 서브 파티션은 독립된 세그먼트가 된다). 레인지-리스트 컴포지트 파티션은 레인지-해시 컴포지트 파티션과 비슷하지만 서브 파티션이 리스트 파티션이라는 점이 다르다. 실제 업무에서는 레인지-해시보다 유용한 면이 많다. 다음은 레인지-리스트 컴포지트 파티션을 생성하는 DDL 스크립트이다.


파티션된 인덱스의 참뜻
‘파티션된 인덱스(partitioned index)’라고 하면 대부분의 개발자들은 로컬 인덱스를 떠올린다. 또한 파티션된 테이블에서만 쓰이는 것으로 생각한다. 그러나 이것은 명백한 오산이다. 파티션된 인덱스는 파티션된 테이블과 별개의 것으로, 단지 많은 상호 연관을 갖고 있을 뿐이다. 파티션된 인덱스는 문자 그대로 인덱스를 파티셔닝한 것으로, 해당 테이블이 파티션된 테이블이든 파티션되지 않은(non-partitioned) 테이블이든 상관없이 만들 수 있다.

예를 들면 ‘EMP’ 테이블의 크기가 상당히 크고 파티션되지 않은 일반 테이블일 경우 다음과 같은 과정을 통해 파티션된 인덱스를 만들 수 있다. 이를 ‘Global Prefixed Partitioned Index’라고 부르는데, 파티션 인덱스와 마찬가지로 대용량 데이터 환경에서 성능을 높이고 관리를 편리하게 하기 위해서다.


파티션된 인덱스가 유용한 이유는, 앞서 파티션의 개념에서 설명한 것처럼 하나의 인덱스를 여러 개의 독립적인 물리 속성을 가진 세그먼트로 나누어 생성, 관리할 수 있기 때문이다. 오라클 DBMS에서 제공하는 인덱스는 글로벌/로컬 인덱스와 Prefixed/Non-Prefixed 인덱스로 분류된다.

파티션된 인덱스와 일반 인덱스 사이의 차이점은 파티션 테이블과 일반 테이블의 그것과 동일하다. 인덱스는 인덱스 컬럼과 Rowid 순으로 값이 정렬되는데, 이런 특성은 파티션 인덱스에서도 동일하다. 많은 개발자들이 파티션된 인덱스는 전체 테이블 값이 정렬되지 않는다고 생각하지 하지만 이것은 사실과 다르다. 글로벌 파티션된 인덱스의 경우 테이블에 대해 값 정렬이 보장돼 있으며, 인덱스도 파티션별로 독립적으로 관리할 수 있다. 두 가지 방식의 차이는 <그림 1>과 같다.


파티션되지 않은 인덱스는 하나의 루트(root) 노드에서 리프(leaf) 노드까지 전체적인 밸런스를 유지하는 구조이고, 파티션 인덱스는 파티션 별로 독립적인 루트 노드와 리프 노드를 갖고 있음을 알 수 있다. 따라서 파티션되지 않으면 대용량 테이블에서는 글로벌 인덱스의 깊이(depth)가 매우 깊어질 수 있는 단점이 있다.

반면 파티션된 인덱스는 각 파티션별 깊이가 일반 인덱스의 깊이보다 얕고 인덱스도 파티션 별로 할 수 있어 병렬 프로세싱을 이용한 인덱스 관리에 매우 효과적이다.

그렇다면 글로벌 인덱스와 로컬 인덱스는 어떤 차이가 있는 것일까? 많은 개발자들이 파티션됐는지 여부로 판단하지만 이것은 잘못된 생각이다. 앞서 설명한 것처럼 글로벌 인덱스도 파티셔닝할 수 있으며, 이를 파티션별로 관리할 수도 있다. 글로벌 인덱스와 로컬 인덱스의 가장 큰 차이는 ‘정렬’이다. 즉 글로벌 인덱스는 테이블 전체에 대해 인덱스된 컬럼과 Rowid 순으로 정렬되고, 로컬 인덱스는 해당 파티션 내에서만 인덱스된 컬럼과 Rowid 순으로 정렬된다.

또한 로컬 인덱스는 ‘Local’이라는 말에서 알 수 있듯이 지역적인 인덱스로, 해당 테이블(base table)의 파티션 키로 파티셔닝된 인덱스다. 일반적으로 로컬 인덱스의 구성 컬럼에 반드시 파티션 키가 포함돼야 가능한 것으로 알려져 있지만 로컬 인덱스에는 파티션 키가 포함되어 있지 않아도 사용할 수 있다. 다음 예제를 보자. PACKAGE_DLR_IDX1 인덱스의 구성 컬럼에 테이블 파티션 키인 I_DLR_IND가 포함되지 않아도 검색조건에 I_DLR_IND = ‘C’라는 검색 조건이 있기 때문에 해당 파티션의 로컬 인덱스를 이용하는 것을 알 수 있다.


글로벌 인덱스는 전역적인 인덱스로, 기본적으로는 파티션되지 않은 인덱스이다. 대부분의 개발자들은 글로벌 인덱스를 파티셔닝해 사용할 생각을 하지 못하는데, 대용량 테이블에서 인덱스 관리의 효율성을 높이고 인덱스 검색 성능을 높이기 위해서는 이를 파티셔닝하는 것이 좋다. 글로벌 인덱스는 기본 테이블의 파티션 키와 무관하게 파티셔닝하는 것으로 설사 기본 테이블의 파티션 키로 글로벌 인덱스를 파티셔닝했다고 해도 로컬 인덱스처럼 동일파티셔닝(equipartitioning)된 개념이 아니므로 테이블 DDL시 전체 인덱스를 다시 생성해야 한다.

그렇다면 글로벌 파티션 인덱스의 인덱스 컬럼 값은 어떻게 전체 테이블에 대해 정렬을 보장하는 것일까. 예를 들어 5000만 건의 파티션되지 않은 EMP 테이블을 부서번호에 따라 파티셔닝했다고 가정하면 다음과 같다.



<그림 2>는 Global Prefixed Partitioned 인덱스의 구조다. Prefixed와 Non-Prefixed는 인덱스 파티셔닝 키가 인덱스의 선두 컬럼으로 오는가 그렇지 않은가의 차이가 있다. <그림 2>에서도 ‘Prefixed’란 인덱스의 파티션 키(DEPTNO)가 인덱스 선두 컬럼(DEPTNO)이 되는 것을 알 수 있다. 글로벌 인덱스의 경우 모든 인덱스 컬럼 값이 정렬돼 있다. 각 인덱스 파티션의 루트 블럭(root block)에 들어가는 값들이 인덱스 파티션에 따라 정렬되기 때문에 자연적으로 리프 블럭(leaf block)에 들어가는 모든 값들도 정렬되는 것이다. 반면 Global Non-Prefixed 인덱스를 파티셔닝하면 레인지 파티셔닝 방식으로만 가능하다. 이것은 정렬 때문인데, 레인지 파티션은 정렬 기능을 이용해 파티셔닝 키 자체를 생성하는데 반해 다른 파티셔닝 방식은 정렬과 상관없이 수행하기 때문이다.

로컬 인덱스는 Prefixed 인덱스와 Non-Prefixed 인덱스를 모두 지원한다. 로컬 인덱스는 기본적으로 현재 테이블의 파티션 키가 인덱스의 파티션 키가 되기 때문에 인덱스 컬럼에 현재 테이블의 파티션 키가 포함되지 않아도 인덱스를 생성할 수 있다. 또한 인덱스 컬럼 값의 정렬이 전체 테이블에 대해 보장된 것도 아니기 때문에 인덱스 파티션 키가 인덱스의 선두 컬럼이 될 필요가 없다. 또한 Non-Partitioned 인덱스이든 파티션 인덱스든 상관없이 인덱스를 이용하고자 할 때는 무조건 인덱스 파티션 키를 조회해야 하는 글로벌 인덱스와 달리 로컬 인덱스는 조회 검색조건에 파티션 키가 들어올 수도 있고 들어오지 않을 수도 있다.

대용량 DB 테이블과 인덱스 전략
파티션 인덱스 전략은 파티션 테이블과 밀접하게 연관되어 수립해야 하지만 여기서는 파티션 인덱스를 위주로 이야기를 풀어본다. 먼저 인덱스 크기에 대한 논의는 기본적으로 테이블보다는 훨씬 작게 생성, 관리하는 것이 원칙이다. 따라서 중소 용량의 데이터베이스 환경에서는 파티션 인덱스의 유용성을 따질 필요가 없다. 단 중소 용량의 데이터 환경일 경우에서도 테이블이 파티셔닝돼 있다면 파티션 인덱스를 고려해야 한다. 또한 기본적으로 파티션되지 않은 인덱스(일반 인덱스) 전략을 기본으로 해 테이블이 파티셔닝 된 경우와 인덱스를 파티셔닝했을 때의 장점을 비교해 보아야 한다.

먼저 테이블 파티션 키가 항상 ‘=’로 들어오는 경우 또는 파티션 범위가 크지 않은 경우에는 로컬 인덱스가 최상이다. 인덱스 컬럼의 순서와 구성은 액세스 패스에 따라 생성하면 되지만 최대한 가볍게 생성하는 것이 좋다. 기본 테이블의 파티션 키는 반드시 포함될 필요가 없으나, 테이블이 레인지 파티션이고 한 파티션 범위 안에서 파티션 키의 분포도가 좋을 경우 이를 포함하는 것을 고려해 볼만하다. 이렇게 하면 각 파티션당 인덱스가 파티션되지 않았을 때보다 가벼워지고 데이터 마이그레이션을 할 때도 테이블 파티션과 인덱스 파티션이 동일하므로 exchange, add, drop, split 등 파티션별 관리도 용이하다.

또한 빠른 응답 시간을 요구하는 환경에서 대용량 파티션 테이블의 조회 조건에 파티션 키가 들어오지 않을 가능성이 있다면 파티션 글로벌 인덱스를 고려해 볼만하다. 이렇게 하면 파티션되지 않은 글로벌 인덱스와 달리 레인지 파티션 별로 인덱스가 가벼워지는 장점이 있고, 레인지 파티션 별로 인덱스 split와 rebuild 명령을 독립적으로 수행할 수 있다. 컬럼 분포도에 따른 파티셔닝이나 민감한(critical)한 상수 레인지에 대해서는 파티션을 독립적으로 생성해 인덱스 크기를 줄임으로써 인덱스 검색 시간을 줄일 수 있는 이점도 있다.

exchange는 파티션된 테이블의 특정 파티션과 파티션되지 않은 일반 테이블 간의 구조를 서로 바꾸는 것으로, 대용량의 파티션된 테이블을 관리하는 데 상당한 효과가 있다. <그림 2>와 같이 데이터가 없는 새로운 데이터 테이블과 데이터가 들어 있는 파티션 2를 exchange하면 파티션 2에 해당하는 디렉토리 정보가 새로운 데이터로 바뀌고 새 테이블 데이터에는 데이터가 들어간다. 이것은 실제 데이터가 이동하는 것이 아니라 데이터를 저장하는 테이블 정보만을 업데이트하는 것이다. 한 가지 주의할 점은 exchange하고자 하는 파티션과 테이블의 구조가 같아야 하고 속성들의 특성도 같아야 한다는 사실이다.
exchange의 기본적인 문법은 다음과 같다.



한편 파티션된 대용량 테이블에 split 함수를 실행하면 많은 시간이 걸린다. 이럴 때 exchange 기능을 이용하면 빠르고 안전하게 작업할 수 있다. <그림 4>에서 보는 것처럼 split를 해야 하는 파티션을 exchange에 의해 빈 공간으로 만든 다음 split을 하고 다시 데이터를 채우기 위해 split하는 것이다. 이렇게 하면 대용량의 데이터라도 매우 빠른 시간내에 split 작업을 수행할 수 있다.


한편 대부분의 DBA들과 개발자들은 동일한 테이블을 생성할 때 create table ～ as select 구문을 이용한다. 대용량의 데이터일 경우 parallel 옵션을 줘 생성하기도 한다. 만약 1억 건의 테이블을 그대로 생성한다고 할 때 어떤 방법이 효과적일까. 이렇게 파티션된 대용량 테이블을 생성할 때는 exchange, program parallel 방법을 사용하는 것이 바람직하다.


<그림 5>는 이 과정을 도식화한 것이다. 먼저 생성할 TB_PART_1 테이블의 빈껍데기를 만든다. 대용량의 파티션된 테이블의 파티션 각각을 create table ～ as select 구문의 parallel 옵션을 이용해 각 테이블로 생성한다. 이후 미리 생성해 놓은 TB_PART_1 테이블의 파티션과 만들어 놓은 테이블들을 exchange하는 것이다. 이때 파티션별로 200105.sql, 200106.sql, 200107.sql…… 형식으로 만들어 놓고 이 프로그램들을 동시에 실행하면(program parallel) 극적인 효과를 볼 수 있다.

이번엔 데이터 마이그레이션에 대해 살펴 보자. 원격으로 데이터를 옮겨야 할 때 보통 database link를 이용한다. 네트워크를 통해 데이터를 옮기면 직렬(serial)로 데이터가 이동되므로 속도가 현저하게 떨어지기 때문이다. 따라서 소스 테이블을 파티셔닝하고 해당 파티션을 액세스하는 프로그램을 각각 띄워 병렬 프로세싱을 하게 되면 매우 빠른 속도로 데이터를 옮길 수 있다.

소스 테이블을 파티셔닝할 수 있는 상황이라면 테이블의 분포를 보고 레인지나 리스트 방식으로 파티셔닝할 수 있고, 일정한 분포가 존재하지 않는 테이블이라면 해시 파티셔닝으로 분포도를 고르게 나눈 다음 해당 파티션을 읽는 뷰를 액세스해 데이터를 옮기는 것이 좋다.

예를 들어 다음은 중대형 정도 크기인 약 2700만 건의 회원 테이블을 옮기는 DDL 스크립트다. 앞서 언급한 대로 이를 바로 database link를 이용해 처리하면 네트워크의 속도가 떨어져 엄청난 시간이 소요된다. 그러나 이것을 일반 테이블을 여러 개로 파티션을 나누어서 파티션과 병렬 처리하면 성능이 크게 향상된다. 작업 순서는 다음과 같다.


이제 다음과 같이 소스 테이블 뷰 생성한 후


다음과 같이 프로그램 패러럴(program parallel) 작업을 동시에 실행한다.


이것은 단적인 예에 지나지 않는다. 활용할 수 있는 사례는 얼마든지 있을 것이다. 한편 인덱스는 전체 데이터에 대해 해당 컬럼의 값으로 정렬하기 때문에 대용량 테이블의 경우 create, rebuild 명령을 실행할 때 많은 시간이 필요하다. 이때 파티션된 인덱스를 만들면 인덱스의 생성과 관리를 더 활용적으로 할 수 있다. 다음은 파티션된 인덱스를 Unusable로 생성한 사례다(로컬/글로벌 파티션된 인덱스).

먼저 파티션 인덱스를 ‘unusable’ 옵션을 이용해 생성한다. 실제 데이터를 정렬해 만드는 것이 아니라 일종의 껍데기를 만드는 과정이다. 이제 앞서 살펴본 병렬 처리를 이용해 여러 파티션을 동시에 rebuild를 하면 대용량 데이터라도 빠른 시간에 인덱스를 생성할 수 있다.


이제 파티션별로 index1.sql, index2.sql 등을 독립적으로 병렬 실행한다.


지금까지 테이블 파티셔닝에 대해 다뤄봤다. 자동화된 성능관리 툴로 커버할 수 없는 영역을 살펴보고 있으나 가장 중요한 것은 데이터베이스 액세스 개념에 대해 정확하게 이해하는 것이다. 많은 사람들이 파티셔닝을 알고 있지만 정확하게 사용하고 있지 못하는 현실이 아타까울 때가 많다. 그러나 이 점은 역설적으로 파티셔닝의 매력이기도 하다. 노력하는 데이터베이스 관리자 만이 도전해 볼 수 있는 영역이 바로 ‘파티셔닝’ 분야이기 때문이다.

출처 : 마이크로소프트웨어 2005년 3월호

데이터베이스 성능관리는 단순한 지식으로 해결할 수 있는 문제가 아니다. 자동화된 툴에서 보여지는 많은 지표의 도움으로 종합적인 판단해야 한다.

이번 글의 출발점은 바로 이 자동화 툴이다. 여기서는 기존의 접근 방법과는 조금 다르게 데이터베이스 활용과 성능관리에 대한 이야기를 시작해 보자.

오늘날에는 거의 모든 기업 활동이 정보 시스템을 통해 이루어지고 있다. 간단한 계산 작업에서부터 복잡한 업무 프로세스까지 정보 시스템을 이용하지 않고는 처리할 수 없는 환경이라고 해도 과언이 아니다. 이같은 상황에서 한 기업의 업무 프로세스를 정보 시스템화한다는 것은 단순히 업무를 전산화한다는 의미뿐만 아니라 회사의 다양한 업무를 표준화, 자동화, 고품질화 해 효율성을 높이고 나아가 회사의 경쟁력을 높이는 원동력이 되는 것이다.

그러나 현재 대부분의 전산 시스템의 상황은 어떠한가? 정보 시스템을 통해 고부가가치를 창출하려는 노력보다는 처음부터 계획성 없이 구축해 놓은 전산 시스템을 유지보수하거나 신뢰할 수 없는 데이터에 대한 보정작업(data cleansing) 등 고급 인력들이 단순 노무자(?)로 전락하고 있는 것은 아닌지 유감스럽다.

기업의 복잡하고 다양한 업무를 전산화하려면 무엇보다 이 수많은 유무형의 작업들을 데이터화하고 이를 관리하는 데이터베이스가 필수적이다. 현재의 모든 전산 시스템이 데이터베이스를 근간으로 만들어지고 있는 것도 이 때문이다. 하지만 실제로 업무 전산화를 담당하는 개발자들은 수많은 개발 언어를 익히는데 치중하는 나머지 업무를 데이터화하는 능력과 데이터베이스를 활용하는 능력을 기르는데 소홀할 수밖에 없다. 데이터베이스를 관리하는 DBA(DataBase Administrator)들은 업무 중 많은 시간을 데이터베이스 운영의 문제점과 이를 해결(trouble shooting)하는 데 허비하고 있다.

필자는 컨설턴트로 활동하면서 이런 데이터베이스 운영상의 문제점들을 수없이 많이 보아왔다. 본래 이러한 문제들의 근본원인을 이해하는 것도 중요하지만, 관리자 입장에서 보면 먼저 이런 문제점들을 발견하고 해결하는 것이 더 중요하다. 실제로 최근 많이 출시돼 있는 데이터베이스 모니터링 자동화 툴도 이런 취지에서 선보인 것들이다.

그러나 이런 툴이 있음에도 불구하고 데이터베이스 운영에 많은 문제점들이 끊임없이 나타나는 것은 데이터베이스란 하나의 물리적인 소프트웨어에 지나지 않지만 실제로는 업무의 논리적인 데이터적 활용이라는 관점에서 사용되기 때문이다.

앞으로 3회에 걸쳐 데이터베이스 운영의 문제점 중 가장 민감하다고 할 수 있는 ‘성능(performance)’ 관리에 대해, 자동화된 툴을 이용한 관리에서 더 논리적이고 데이터베이스 활용 측면의 이야기를 살펴 본다. 데이터베이스는 현재 시중에서 가장 널리 사용되는 오라클 데이터베이스를 예제로 하겠다.

성능관리의 성패를 가르는 요인들
요즘 대부분의 기업에서 이용하고 있는 데이터베이스는 RDBMS(Relational Database Management System)라 부르는 RDBMS이다. RDBMS를 근간으로 구축하는 시스템에서는 실제 대부분의 시스템에서 성능상 문제를 나타내는 원인의 많은 부분이 RDBMS 관련 문제 요소들이다.

4~5년 전만 해도 시스템 구성이나 인프라(infra), 리소스(resource) 부족으로 인한 문제가 많았으나, 하드웨어 기술이 빠르게 발전하고 상대적으로 비용 부담이 줄어들면서 쉽게 하드웨어를 증설할 수 있게 됐다. 실제로 대부분의 기업들이 하드웨어와 메모리 증설이라는 처방을 통해 어느 정도 시스템 부하와 자원부족 문제를 해결해 온 것도 사실이다.

그러나 이러한 시스템 문제들의 내부를 살펴보면 사실상 CPU, 메모리, 디스크, 네트워크 등 하드웨어 리소스 부족으로 인한 문제 상황은 거의 찾아볼 수 없다. 가끔씩 2티어(tier), 3티어 환경 또는 웹 환경에서 네트워크 구성 문제나 네트워크 속도 문제로 인한 문제가 발생하기도 하지만, 필자의 경험에 비추어 보면 거의 절대적으로 시스템 성능의 영향을 미치는 부분은 바로 RDBMS에 맞도록 시스템 설계(데이터모델링/DB Design)가 이루어졌는지 여부와 RDBMS에 적합한 개발기법으로 개발이 이루어졌는 지가 문제의 핵심이었다.

즉 운영업무의 특성과 발생 데이터량 그리고 트랜잭션 부하를 고려해 RDBMS의 특성과 장단점을 데이터베이스 설계에 반영하고, RDBMS 옵티마이저(optimizer) 개념을 이해하고 응용 프로그램을 개발해야 하며, 성능에 가장 직접적인 영향을 미치는 I/O(Input/Output) 성능을 개선하겠다는 설계자와 개발자들의 노력이 매우 중요하다 할 수 있다. 일반적으로 데이터 베이스 성능에 영향을 미치는 부분은 다음과 같이 구분할 수 있다.

물리적인 데이터베이스 설계 측면
RDBMS에 대해 정확하게 이해하지 못한 상황에서 DB 오브젝트 디자인을 해 대부분의 데이터베이스 파라미터를 기본(default)으로 설정해 관리하거나 조정을 하려고 해도 할 수 없는 경우, 혹은 스토리지 설계가 잘못된 경우가 대부분이다.

이런 요소들은 문제 위험요소에 치명적이지는 않지만 RDBMS 성능을 높이기 위해서는 RDBMS의 원리와 DB 스토리지 알고리즘을 이해해 DB 오브젝트 디자인에 반영해야 한다. 특히 PCTFREE, PCTUSED, FREELISTS, INITRANS, INITIAL, NEXT EXTENTS 설계 등 데이터 발생과 액세스(access) 등에 영향을 미칠 수 있는 물리적인 요소들에 대해 적절한 설계가 이루어져야 한다.

또한 대용량의 데이터 환경과 데이터 변환, 마이그레이션(migration) 등의 환경에서는 파티션(partition) 전략이 중요하다. 파티션의 장단점과 그 원리를 정확히 파악하고 현재 데이터 환경에 적용시킬 수 있는 능력이야 말로 관리자가 갖춰야 할 부분 중의 하나다. 많은 자동화 툴들이 이런 기능을 단순히 처리(operation)해 주는 편리한 기능을 많이 제공한다. 보통 단순 작업을 진정한 실력으로 착각하는 경우가 많은데, 적용 능력과 활용 기술 등은 자동화 툴로 대체할 수 있는 것이 아니다.

개발 응용 프로그램 SQL의 품질측면
RDBMS에 맞는 개발 기법은 유일하게 RDBMS가 이해할 수 있는 언어인 SQL을 RDBMS 옵티마이저가 최적화할 수 있게 만드는 것이다. 즉 실력있는 프로그래머는 옵티마이저이며 이것이 최적의 수행속도를 낼 수 있도록 개발자와 설계자가 적절하게 전략적인 최적화 팩터(factor)를 구축해야 한다. 즉 개발 응용 SQL 역시 옵티마이저가 알기 쉽게 집합적인 고급 SQL을 구사하여야 한다.

대부분의 성능상의 문제는 이런 집합적이고 전략적인 SQL을 구사하지 못해 발생한다. SQL은 사용하기는 편리하지만 잘못 사용하면 1초면 가능할 일을 1시간이 넘도록 수행하는 경우도 있다. 즉 SQL은 요구와 결과 밖에 없는 비절차형 언어이기 때문에 누구나 쉽게 배우고 사용할 수 있지만, 최종 사용자 컴퓨팅(End User Computing) 환경이 아닌 패키지 시스템이나 개발시스템에서는 가장 효과적인 고급 SQL을 사용할 수 있는 능력이 요구된다. 이런 능력은 단순하게 툴을 이용하거나 편협한 지식으로 해결될 수 있는 것이 아니다. 원리를 이해하려는 많은 노력으로만 가능하다.

최적화/클러스터링 전략 측면
필자의 경험에 따르면, 대부분의 데이터베이스 인덱스 전략은 한 마디로 주먹구구식으로 만들어 놓고 쓰는 것이 대부분이다. RDBMS를 정확히 이해하지 못하고 사용하는 습관이 만들어 낸 매우 위험한 현상이다. 실제로 개별 SQL의 속도를 빠르게 하기 위해 즉흥적으로 인덱스를 추가하면 오히려 큰 오류를 범하게 된다.

Trace 결과에 근거해 각종 SQL에 어떠한 조건들이 사용될 것인지 그리고 어떤 테이블 간에 연결 조인이 이루어지는지 등을 파악해 모두를 만족할 최소의 역할별 인덱스 또는 클러스터 전략을 수립해야 한다. 상황에 따라 개별 개발자들이 수행속도 향상을 위해 케이스(Case) 별로 인덱스를 추가하기 시작하면 무수히 많은 인덱스가 늘어나 시스템 부하만 가중시키고 정작 의도하는 엑세스 효율은 얻지 못하는 경우가 대부분이다.

반면 아무리 데이터량이 많아도 최적의 데이터베이스 디자인과 효율적이고 전략적인 인덱스 전략 등 최적화 전략이 갖춰져 있으면 전혀 걱정할 필요가 없다. 사실상 대부분은 반복 업무는 온라인 업무이며 이를 처리할 때 수십 수백만 건을 모두 읽어서 처리해야 한다면 이는 업무 정의가 잘못되거나 적절한 데이터베이스 디자인이 되어 있지 않은 것이다.

현재 시스템에 성능상의 문제가 있을 경우 적절하지 못한 데이터 모델링에 기인하는 경우가 많은데 실제로 이런 문제를 데이터 모델링의 문제라고 생각하기 보다는, 단지 개발자들의 능력이 부족하기 때문이라고 속단한다.

그러나 핵심은 원하는 데이터를 가장 효율적으로 최소의 비용으로 처리할 수 있도록 RDBMS에 최적화된 전략을 갖추고 있는가 하는 점이다. 사용자들이 쉽고 빠르게 시스템에 접근해 원하는 바를 얻고, 효율을 높이기 위해서는 전체적인 SQL 액세스 패스를 분석해 최적화 전략을 수립하는 것이 중요한 것이다.

시스템 자원 사용 측면
시스템 자원사용에 관한 부분도 성능상에 문제를 야기할 수 있는 중요한 측면이다. 과거에는 CPU, 메모리, 디스크 등 시스템 자원이 부족해 생기는 부분이 많았으나 현재는 가용한(available) 자원을 제대로 활용하고 있지 못하는데 대부분의 원인이 있다.

하드웨어 자원이 부족하기 때문이 아니라 비효율적인 애플리케이션으로부터 비롯된다는 사실은, 앞서 설명한 설계문제, 최적화 전략 부재, 비효율적인 애플리케이션 등이 복합적으로 작용해 현재 시스템의 자원 사용에 대한 부적절한 스케일링(scaling)이 뒤따를 수 있는 문제가 발생한 것이다. 데이터 중복이 심하고 비정규화로 인해 RDBMS의 기본 개념인 ‘관계’의 데이터 정합(integrity)가 깨지면, 당연히 데이터 액세스 작업이 비효율적으로 되고 불필요한 인덱스들이 양산될 수밖에 없다.

RDBMS 원리에 충실한 DB 디자인과 옵티마이저 원리에 충실한 응용 프로그램 개발, 앞을 내다보는 최적화 전략 등이 성능관리의 성패를 가름하는 것이다.

DB 관리 툴의 활용법과 한계
DBA들은 데이터베이스 운영의 책임을 지고 이를 안정적으로 운영할 의무가 있지만, 실제로 데이터베이스에 문제가 발견되면 그 원인을 찾기 힘든 경우가 많다. 이 때문에 자동화 툴을 이용해 성능상의 문제점을 파악하고 해결하려 한다. 데이터베이스는 매우 복잡한 소프트웨어이고 운영상 많은 지식과 경험 등이 필요해, 관리하는 사람들마다 서로 다를 수밖에 없다.

따라서 아무리 자동화된 툴을 이용한다고 해도 그 지식과 경험의 차이에 따라 툴을 이용하는 수준은 천차만별이다. <화면 1>은 거의 모든 DBA들이 매일같이 확인하는 데이터베이스 세션(Session)에 관한 모니터링 관리화면이다.


이런 종류의 관리화면은 거의 모든 데이터베이스 툴이 제공하는 것으로 데이터베이스 관리자가 데이터베이스에 액세스되고 있는 많은 세션 정보를 편리하게 볼 수 있도록 지원한다. 여기서는 모든 세션에 대한 정보는 물론 그 세션들이 현재 데이터베이스에 어떤 요구를 하고 있는지도 확인할 수 있다.

이런 요구들 가운데에는 데이터베이스에 많은 부하를 주거나 속도 저하를 일으키는 SQL들도 발견할 수 있다. 이를 편리하게 찾아 주는 것이 자동화된 툴의 장점이기도 하다. 실제로 문제가 되는 세션의 SQL을 찾고 싶다면(실제로 이런 요구가 빈번히 발생하게 된다) 대부분의 DBA나 개발자들은 개인적인 경험과 지식을 동원해 문제가 되는 SQL들을 찾아낸다.

Physical I/O, Logical I/O, Lock Wait 등을 보고 툴에서 보내주는 수치가 상대적으로 크거나 또 나름대로의 판단기준을 가지고 속도 저하의 가능성이 있는 SQL이라 판단할 수 있는 것이다. 예를 들어 <리스트 1>과 같은 SQL이 문제 요소로 부각됐다고 가정해 보자.


이때 가장 중요한 것은 어떤 부분이 문제가 되는지를 정확히 판단해 As-Is와 To-Be에 대한 요구 가능성이 충족될 수 있도록 최적화된 튜닝을 하는 것이다. 이를 위해서는 여러 가지 수많은 팩터들 중에서 가장 기본적으로 실행 계획과 트레이스(trace) 정보를 보고 이해할 수 있어야 한다.


<그림 2>와 같은 실행계획을 보고 현재 어떤 문제가 있고 어떻게 튜닝해야 할지 판단하는 것은 쉬운 일이 아니다. 단순히 SQL의 문법지식이나 인덱스 상태만으로 해결되는 것도 아니다. 데이터 상황과 시스템 상황, 인덱스 상황, 다른 SQL들의 액세스 패턴 등을 종합적으로 판단할 수 있어야 한다. 이런 종합적인 판단에 있어 툴은 많은 도움을 줄 수 있지만 ‘최종적인 판단’까지는 대신해 줄 수 없다. 이 부분은 사람만이 가능하다. <리스트 3>과 같은 또다른 사례를 살펴 보자.


<리스트 3>은 특정 게시판 테이블에서 날짜를 이용해 간단한 내용을 조회하는 SQL로, 요즘 대부분의 회사에서 많이 쓰이는 그룹웨어 시스템의 모든 화면에서 조회할 수 있는 SQL이다. BBS 테이블에 많은 데이터가 있지도 않고 특성상 단위 SQL이 수행되는 시간 또한 오래 걸리지 않으며, 언뜻 보기에도 별 문제가 없어 보인다. 어쩌면 실제로 별 문제가 없을 수도 있다.

그러나 <리스트 3>을 자세히 살펴 보면 owner_id 즉 로그인한 ID를 입력 값으로 해 BBS_GET_STATUS 함수를 통해 어떤 상태(status)를 리턴(return) 받는 부분이 있다. 만약 이 시스템을 이용하는 기업의 사용자가 매우 많고 빈번히 조회가 된다면 바인딩되는 owner_id 값의 종류가 기하급수적으로 늘어날 것이다.

이 때 동적으로(dynamic) 바뀌는 값을 데이터베이스에서 바인딩하지 않을 경우 동일한 SQL을 수천만번 파싱(parsing)해야 하는 경우가 생기게 된다. 따라서 단위 SQL은 빠르게 수행이 될 수 있으나 전체적인 데이터베이스의 성능은 시간이 갈수록 저하돼, 마치 시한폭탄과도 같아지게 된다.

만약 자동화된 툴에서 이런 SQL들을 전문적으로 필터링하는 기능이 있다면 이런 정형화된 경우에 한해서는 툴의 자동화 기능을 이용하는 것도 좋은 방법 중 하나가 될 것이다. 하지만 실제 업계에서는 데이터베이스 전문적인 툴이라 하더라도 데이터베이스 자체가 아닌 사용 관점에서 문제점을 발견하고 찾아내는 자동화된 툴은 본 적이 없다.

DBA는 물론 데이터베이스 전문가라고 하는 사람들도 자동화된 툴을 가지고 데이터베이스를 모니터링하는 수준에 불과한 경우도 많았으며, 데이터베이스 문제의 근본 원인을 제대로 파악하는 것이 쉽지 않은 작업임을 여러 차례 확인한 바 있다. 즉 모니터링 툴이 보여주는 물리적인 지표를 이용하면 데이터베이스 자체의 문제를 발견할 수는 있지만, 실질적인 근본 원인과 그 해결책을 찾는 것은 또 다른 이야기가 되는 것이다.


<화면 2>는 많은 자동화 툴에서 보여주고 있는 데이터베이스 모니터 화면이다. 이런 화면에서 보여주고자 하는 것을 제대로 이해하는 것도 쉬운 일은 아니지만, 제대로 이해했다고 해도 그 원인을 해결하는 것은 더욱 어렵다.

예를 들어 SGA Memory Usage에서 Shared Pool의 사용량이 상대적으로 많다면 그 이유가 무엇인지 파악해서 해결할 수 있어야 하지만 대부분의 DBA들은 그 원인을 치유하지 못한 채로 Shared Pool Size만 늘려 해결하려고 한다. 그러나 실제 그 이유를 살펴보면 메모리 크기가 부족한 것인지 아니면 다이나믹 쿼리(dynamic query)에 의한 파싱이 불필요하게 많아서인지 등 여러 가지가 있을 수 있으며, 설사 원인을 파악했다고 해도 이를 어떻게 해결할 수 있는지 알지 못한다.

필자가 경험적으로 느낀 바에 따르면, 대부분의 원인은 데이터베이스 지향적 관점으로 프로그램으로 개발하지 않은, 즉 애플리케이션의 문제가 80~90%를 차지한다. 이 경우 메모리 크기를 늘리면 당장의 해결책은 되겠지만 시간이 흐를수록 더 큰 문제가 야기될 수밖에 없다. 따라서 근본 원인을 찾고 해결하는데 시간을 투자하는 것이 안정적으로 데이터베이스를 운영하는데 필수적이다.

한편 인덱스를 통한 쿼리가 많고 적음에 따라 시스템의 부하를 줄일 수 있는 팩터가 되기도 한다. <그림 5>는 Indexed Queries%를 보여주는데 이 화면으로 데이터베이스에 요구하는 SQL들이 인덱스를 많이 이용하는지 아니면 전체 스캔(Full Scan)을 많이 하는지를 한눈에 알 수 있다.

여기서 독자들에 대한 질문 한 가지! 이때 인덱스를 많이 타면 좋은 것일까? 다양한 답변이 나올 수 있을 것이다. 인덱스에 대해 깊이 파고 들었던 독자는 다양한 상황을 예로 들며 장황하게 설명할 수도 있겠고, 단순하게 ‘그렇다’라고 대답하는 독자도 있을 것이다. 하지만 대부분은 인덱스를 타면 빨라지니 이를 많이 타면 시스템 부하가 내려갈 것이라고 생각할 것이다. 데이터베이스 전문가라고 자부하는 사람들조차 이렇게 생각하고 있으니 일반적인 개발자나 DBA들은 그렇게 생각하는 것이 어쩌면 당연할 수도 있다.

그러나 이것이 항상 옳은 것은 아니다. 인덱스에 대한 기본 구조와 개념, 인덱스를 통한 스캔 방식과 전체 스캔 방식의 차이, 각 스캔 방식의 데이터 상황에 따른 반응 시간(Response Time), 실시간 용인지 데이터 웨어하우스 용인지 등 데이터베이스의 사용목적 등을 심도있게 연구해야 다양한 환경에 따라 목적에 맞는 사용방식을 찾을 수 있다.

이런 관점에서 보면 기본으로 제공되는 자동화된 툴들은 현재 시스템의 전체 윤곽을 잡는데 큰 도움이 되는 것은 분명하다. 그러나 이 툴을 이용하는 사람들이 단순히 툴을 다루는(operation) 오퍼레이터(operator)에 머무른다면 수많은 문제를 올바르게 해결할 수 없다.

앞서 설명했듯 문제점의 원인을 파악하다 보면 대부분은 효율적이지 못한 SQL 문장, 최적화 전략의 부재 등에서 시작된다. 어떤 경우 프로그램 하나가 전체적인 시스템의 성능을 좌지우지 하는 경우도 있다. 필자가 컨설팅했던 한 사이트는 운영시스템이 항상 90% 이상의 CPU 사용률을 기록하고 있었고 시스템의 부하가 너무 심해 CPU와 메모리를 최대로 탑재해서 더 이상 리소스 증설을 할 수 없는 상태였다.

리소스를 최대로 증설한 상태에서도 사용률이 90% 이상을 기록하니 운영 중에 많은 부작용이 나타났고 상품을 판매하는 회사에서 고객을 유치하는 프로모션을 해야 함에도 불구하고 시스템이 이를 지원하지 못해 걱정만 하고 있었다.

분석 결과 이 시스템은 근본원인을 치유하기 보다는 즉흥적인 조치로 인덱스 수가 무수히 늘어나 시간이 갈수록 더욱 더 심각한 상황에 이른 경우였다. 운영시간에 조작되는 프로그램은 많았고 특히 대부분의 사용자들이 빈번하게 사용하면서 시스템의 부하를 크게 높이는 프로그램 한두 개를 찾아 이에 대한 긴급 처방을 하자 CPU 사용률이 30% 정도로 떨어졌다. 튜닝 컨설팅이 끝날 무렵에는 시스템의 과다 탑재된 CPU의 활용 용도를 놓고 행복한 고민에 빠지게 됐다.

회사 측면에서 보면 프로그램 한두 개가 막대한 손해를 끼친 경우로, 옵티마이저의 기본원리를 알고 프로그램을 개발했더라면 그런 현상을 미연에 방지했을 것이다. 이런 것은 특정 툴 하나를 능숙하게 다루고 자동화된 어떤 작업을 잘해서 해결되는 것이 아님을 독자들은 이해하리라 생각한다.

참고로 데이터베이스의 성능관리에 있어서 애플리케이션의 중요성을 강조하는 이유는 앞서 설명했듯, 데이터베이스가 스탠드 얼론(stand alone)으로 운영되는 것이 아니라 사용자의 요구(request)에 의해 운영되는 것이고, 그 사용 자체가 비즈니스이기 때문에 활용적인 면에서 접근할 수밖에 없기 때문이다.


<화면 3>과 <화면 4>는 RDBMS의 특성에 맞는 애플리케이션 개발 방법이 중요하며, 자동화된 툴의 한계가 명확하다는 것을 보여주는 대표적인 사례다. 이것은 모 유통회사에서 문제가 되었던 애플리케이션을 튜닝한 것으로, 대부분의 웹 애플리케이션 개발자가 접하게 되는 프로그램 화면과 프로그램 개발 소스다. 다양한 검색조건에 따라 SQL을 추가(append)해 조건에 맞는 답이 나올 수 있는 SQL을 만들어 데이터베이스에 요구하는 방식으로 짜여져 있다. 그러나 이러한 방식에는 다음과 같은 문제점이 있다.

이런 프로그램들을 과도하게 생성해 사용하면 클러스터링 팩터의 부재로 인해 성능이 저하되고, 파싱 오버헤드에 의해 시스템 리소스 사용이 증가돼 전체적으로 시스템 성능이 떨어지고 심하면 장애가 발생할 수도 있다. 이런 상황들을 모르는 시스템 담당자 또는 DBA들은 문제가 발생하면 원인을 찾기 힘들게 되고 시스템 증설이나 인덱스 추가 등 즉흥적인 대처에 급급해 시스템에 오히려 독이 되는 것이다. <화면 3>과 <화면 4>에서 보이는 SQL의 일부 트레이스 정보를 추출하면 <리스트 4>와 같다.


<리스트 4>는 <화면 3>의 화면에서 ‘상품명’의 조건으로만 조회했을 때의 SQL에 대한 트레이스 정보로, 전체적인 트레이스 정보를 각각 분석해 튜닝하는 방법보다 화면 단위로 전체적인 클러스터링 팩터를 선정해 튜닝하는 것이 효과적이다. 여기서는 화면 단위로 봤을 때 Mandatory 조건을 어떤 것으로 할 것이며 그에 따른 Optional 조건은 어떤 것으로 하고, 또 그에 따른 데이터 상황과 인덱스 전략 등을 수립해 종합적으로 판단해 <리스트 5>와 같이 SQL을 one-set으로 만드는 방법을 사용했다.

이와 같이 튜닝을 하면 모든 조건검색을 안전하고 빠르게 수행할 수 있으며, 동시에 시스템 부하를 줄일 수 있다. RDBMS의 특성과 기본 개념, SQL 등의 올바른 사용법 등을 참조해 데이터베이스 지향적인 프로그램을 개발한 단적인 사례로, 앞서 설명한 여러 자동화된 툴에서 채울 수 없는 ‘2%’이기도 하다.


지금까지 다양한 예제들을 통해 RDBMS의 특성을 이용한 성능 문제 해결 방안에 대해 살펴 봤다. 많은 관리자들이 자동화된 툴의 한계와 적절한 이용법을 익혀 데이터베이스의 문제점을 빨리 파악하고 이를 분석, 해결할 수 있는 데이터베이스 전문가로 성장하기를 기대한다. 다음 글부터는 실제 현장에서 자주 부딪히는 사례를 중심으로 각각의 해결 방법을 상세하게 알아 본다.

출처 : 마이크로소프트웨어 2005년 2월호