데이터베이스|효율적인 채번 방식을 적용한 성능향상

효율적인 채번 방식을 적용한 성능향상

    데이터베이스에 유일한 식별자인 Primary Key를 일련번호 형식으로 디자인하는 경우가 많다. 업무적으로 의미 있는 식별자와 일련번호 형식의 시스템적 식별자에는 장단점이 존재한다. 실제 테이블에서는 일련번호 형식을 많이 사용하고 이로 인한 이슈도 제법 많이 발생되고 있다. 이번에는 두 식별자에 대한 기능적인 면에서의 비교보다 일련번호 형식의 식별자일 때 어떻게 번호를 증가시키는 것이 효율적인지를 알아보고자 한다.

대체 식별자인 일련번호 체계를 사용하는 데이터 모델에서는 반드시 채번(PK값을 증가하는 번호 형식으로 생성하는 것)을 해야 하는데, 이때 채번을 목적으로 설계해 사용된 채번 테이블로 인해 성능저하가 나타나는 경우가 많다.
< 그림 1>은 트랜잭션 LOG라는 업무테이블에 해당 데이터베이스에서 발생하는 모든 트랜잭션 정보를 보관하고 추적하기 위해 설계한 테이블이다. 이 테이블에 PK는 로그 번호로 일련번호 형식이며 속성값을 채번 테이블을 통해 가져온다. 채번 테이블에는 채번 구분에 따라 항상 하나의 레코드만이 존재해, 맨 마지막에 생성된 번호만을 채번이라는 속성값에 가지고 있는 구조이다. 채번 구분은 채번 테이블을 이용하는 다른 업무 테이블이 여러 개가 있을 때 구분하기 위한 구분자이다. 
 <그림 1>에서 나타나듯, 먼저 채번의 속성값을 채번 속성값 +1로 수정한 이후, 업무 테이블에 데이터를 입력하고 COMMIT을 해야 트랜잭션이 종료되는 특징을 가지고 있다. 따라서 입력하는 작업이 길어지면 잠금현상(Lock)으로 인해 대기현상도 길어져 문제가 되는 경우가 많다. 
특히 동시에 여러 트랜잭션이 집중적으로 시스템을 이용할 때 잠금현상이 길어져 경합이 발생해 대기시간을 가중시키고 이에 따라서 심각한 성능문제를 야기하는 경우가 자주 있다. 특히 RAC(Real Application Clustering)과 같이 각 노드 간의 자원점유정보를 주고받는 환경에서는 그 문제가 더 심각해 질 수 있다.

■ 왜 설계된 채번 테이블이 성능 저하를?
그 이유는 채번 테이블을 이용할 때에는 필연적으로 채번의 대상이 되는 테이블을 수정해야 하고, Commit을 날리지 않은 상태에서 즉 잠금현상이 지속되고 있는 상태에서 일련번호를 가지고 있는 다른 테이블에 데이터를 입력해야 하기 때문이다. 
사람들이 시스템을 많이 사용하게 될수록 해당 테이블에 트랜잭션을 발생시킬 빈도가 높기 때문에, 늦게 처리되는 트랜잭션일수록 많은 시간을 대기해야 하는 현상이 발생된다. 각 트랜잭션이 독립적으로 해당 테이블에 들어가 트랜잭션을 처리하지 않고 항상 채번 테이블과 입력대상 테이블에 같이 발생되고 또 이 트랜잭션 기간 동안 무조건 기다려야 하므로 자원을 해제하기 전까지 기다릴 수밖에 없다. 

■ 채번의 방법
채번의 방법은 크게 세 가지로 구분해 설명할 수 있다. 
첫 번째는 앞서 언급한 것과 같이 채번 테이블을 이용해 일련번호를 증가시키는 방법이고, 두 번째는 해당 테이블의 일련번호에 최대값 +1을 바로 가져오면서 입력하는 방법이다. 세 번째는 DBMS에서 제공하는 일련번호 증가 오브젝트(예를 들어 오라클의 시퀀스 오브젝트)를 이용해 처리하는 방법이다. 
실제 프로젝트에서는 이 세 가지 방법을 어떻게 적용하는 것이 가장 효율적인지를 고민하는 경우가 많다. 따라서 이 세 가지에 대한 기능적인 부분과 장/단점을 비교해 프로젝트 수행 시 참고할 수 있도록 <표 1>로 정리해봤다. 
 이 외에도 추가적으로 세 가지 채번 방식을 프로젝트에 적용하는 경우가 있다.
첫 번째는 프로그램에서 공유되는 규칙을 가지고 있으면서 채번하는 방식이 있다. 이 경우 프로그램에서 공유해 처리하므로 트랜잭션이 실패할 경우에도 채번이 된 상태이며, 시퀀스 오브젝트와 비슷한 빠른 성능과 중복 에러가 없다는 점 그리고 순차적인 데이터 입력이 불가능하다는 장단점이 있다. 
두 번째는 채번 테이블을 업데이트 하고 바로 Commit을 찍은 상태에서 대상이 되는 테이블에 입력하는 방식이다. 이 경우 채번 테이블을 이용할 때 발생하는 잠금현상이 지속되는 것을 예방해 성능은 빠르게 할 수 있다. 
그 러나 트랜잭션이 완료되지 않고 채번 테이블만 수정완료(Commit)된 채 본 테이블에 입력할 때에는 실패할 수 있기 때문에 순차적인 데이터입력이 불가능해져 예측된 데이터가 안 들어 갈 수 있다. 이렇게 하는 것은 시퀀스 오브젝트를 사용하는 것과 동일한 효과를 사용자가 만든 테이블에서 한다고 생각해도 무방하다.
 세 번째는 미리 데이터 순번을 가져와서 비트맵 형식으로 매핑해 처리하는 방법도 있다. 그러나 이 경우 성능은 좋으나 역시 어떤 일련 번호값이 들어갈지 모르기 때문에 시퀀스 오브젝트와 유사한 장단점을 가지고 있다고 볼 수 있다.
채번 방식은 워낙 성능에 중요하게 영향을 미치고 그 선택하는 방식에 따라 사용자에게 미치는 영향이 크므로 다양한 방식이 지금까지 사용된다고 볼 수 있다.

■ 채번의 방법, 그 선택기준은
만약 트랜잭션이 아주 많지 않고 이론적으로 발생될 수 있는 데이터 중복에러(Duplication Error)에 대해 응용프로그램에서 보완 처리(예를 들어 입력을 2회 반복 실행)할 수 있다면 두 번째 방법을 권한다. 
사실 웬만한 트랜잭션 량에서는 중복에러가 거의 발생되지 않는다. 하지만 중복에러가 발생할 만큼 트랜잭션 량이 대량으로 발생되고 순차적 데이터 입력이 요구되지 않는다면 세 번째 방법인 시퀀스 오브젝트를 이용할 것을 권한다. 
시 퀀스 오브젝트는 읽기만 하면 증가하는 특징을 가지고 있다. 그래서 가끔씩 이빨이 빠진 모양처럼 중간이 비는 경우(1,2,3,5,6,7,8,9...)가 있는데 이러한 데이터가 테이블에 존재해도 된다는 결정을 한다면 시퀀스 오브젝트를 이용하는 것을 고려할 수 있다. 
채번 테이블을 이용하는 경우는 데이터처리 트랜잭션이 적을 때나 성능이 문제가 되지 않는 업무특성일 경우 적용될 수 있다. 또한 트랜잭션이 대량으로 발생해 중복에러가 예상이 되고, 데이터도 순차적으로 입력이 돼야 한다는 전제가 있을 경우에는 채번 테이블을 이용해 처리할 수밖에 없게 된다. 그러나 이 경우 성능이 저하될 수 있음을 미리 알고있어야 한다. 

■ 채번 방법 중 가장 좋은 것은
채번에 대해서 여러 가지 방법을 가지고 연구하고 프로젝트의 문제점 등을 진단해 본 필자의 경험으로 가장 좋은 채번 방법은 해당 테이블에 최대값을 이용해 처리하는 방법이다.
이 경우 모든 조건이 다 좋으나 트랜잭션이 많아지는 경우 중복에러가 발생하는 단점이 있다. 이 중복에러 현상을 피할 수 있는 방법은 대상이 되는 테이블의 PK 구성을 <그림 3>에서 보는 것과 같이 조정해서 설계하면 안정적으로 입력처리 할 수 있다. 
 왼쪽의 테이블과 같이 그냥 하나의 일련번호를 가지고 모든 데이터에 대해 한꺼번에 채번을 하는 경우 이것을 동시에 처리하는 트랜잭션이 양이 많아져 가끔씩 중복에러가 유발이 될 수 있다. 해당 테이블에 데이터를 처리하는 모든 사용자가 동시에 처리하는 경우에도 중복에러가 발생이 될 수 있는 것이다. 
그런데 오른쪽과 같이 테이블의 일련번호 앞에 구분자를 위치시키고 해당 테이블에 처리하는 사용자를 구분자에 따라 분리해 채번할 수 있게 하면 중복에러를 회피할 수 있게 된다. 
예를 들어 구분자에 지역구분 코드라든지??이터 입력 시 이 코드에 따라 채번할 수 있도록 하면 중복에러가 거의 발생하지 않을 것이다. 
빨 라진 컴퓨터에 의해 순간적으로 처리하기 때문에 사실 중복에러가 발생하기도 쉽지는 않다. 그러나 사용자가 많아지는 경우에는 중복에러가 발생할 가능성이 높아지기 때문에 테이블에 대해서만 오른쪽과 같이 한층 더 특별한 테이블의 PK 설계를 해 줄 필요가 있다.
또 한 가지의 방법은 만약 입력처리 시 중복에러가 발생하면 사용자가 재처리하지 않고 시스템 상에서 자동적으로 다시 채번이 돼 입력될 수 있게 하는 방법도 사용자 에러를 피할 수 있다. 
이 와 같은 방식이 비록 간단한 설계적 기법으로 보이나 그냥 단일 일련번호만 설계해 시스템을 사용하다가, 중복에러가 발생되고 Hot Block에 의한 성능저하로 업무적 영향도에 큰 장애로 촉발될 수 있으므로 상당히 유용한 방법이 된다. 
항상 그렇지만 테이블은 설계할 때는 매우 간단한 듯 보이지만 이미 구축된 시스템을 운영 중인 경우 그것을 수정하는 것은 엄청난 비용이 들 수밖에 없음을 알아야 한다. 사전에 설계할 때부터 이러한 내용을 충분히 고려해 데이터베이스에 반영될 수 있도록 항상 유의해야 한다. 

■ 테이블에 대한 채번을 결정하는 프로세스는
앞서 정리해 기술한 바와 같이 <표 2>와 같은 의사결정 프로세스를 가져가면 합리적으로 채번에 대한 의사결정을 했다고 본다.

 
프 로젝트를 할 때는 위 세 가지 사항을 가지고 어떤 방법을 사용할지 의사결정을 하면 된다. 비교적 쉬운 프로젝트라면 위와 같은 의사결정 포인트만을 가지고 결정하면 될 일이지만, 항상 그렇듯 문제는 두 가지 사항이 모두 필요할 때나 다음과 같은 요구사항이 있을 때 어떻게 의사결정을 하겠느냐는 것이다.

“동시 트랜잭션이 많은 시스템으로, 사용자가 이벤트를 날리는 즉시 응답돼야 하는 경우” 

이 때는 어떤 것을 사용하는 것이 가장 좋겠는가? 동시 사용량이 많으므로 일단 최대값을 이용해 입력하는 것은 중복에러가 많이 발생할 것으로 예상된다. 이때는 시퀀스 오브젝트를 사용해 중복에러도 피하면서 빠른 처리가 가능하도록 하는 게 좋다. 
이 시나리오에 한 가지 조건을 추가해, 다음과 같은 완벽한 요구사항이라면 어떻게 의사결정을 해야 할까?

“동시 트랜잭션이 많은 시스템으로, 사용자가 이벤트를 날리는 즉시 응답돼야 하고, PK 일련번호 체계가 빈틈없이 채워져야 하는 경우” 

성능도 우수해야 하고, 동시 사용량도 처리해 주면서, 테이블에 PK 일련번호가 비워있지 않고 차곡차곡 채워져야 한다? 이것을 가능하게 하는 솔루션이 있을까? 
PK 일련번호는 트랜잭션이 실패했을 경우 정상적으로 반환하면 되는데 시퀀스 오브젝트를 사용하는 것은 불가능하다. 또한 채번 테이블을 사용하는 경우에는 Lock을 이용해 일련번호를 체계적으로 부여할 수 있겠지만 성능저하는 피할 수 없게 된다. 아쉽게도 이것을 모두 만족하는 완벽한 해결점은 제시할 수 없을 것 같다. 
다만, 앞에서 설명한데로 PK를 설계할 때 구분자를 포함해 구분자 단위로 일련번호를 채번하는 것이 가장 최선의 대안이 될 것으로 보인다. 이때 구분자 단위로 동시에 발생할 수 있는 트랜잭션이 분산될 수 있도록 하는 것은 업무특성에 따라 잘 분산될 수 있도록 구분자 값을 반영해야 한다. 

필자소개

이춘식 csklee11@chol.com｜재 LG CNS DB관리팀장으로 국내외 데이터베이스를 총괄해 운영하는 리더를 맡고 있다. ‘데이터베이스 설계와 구축’이라는 전문서로 수많은 프로젝트 설계방법을 제시했으며, 진단을 통한 실질적인 문제점과 개선점을 제시했다. 데이터베이스 영역과 함께 기술사/감리사로서 강의, 집필, 평가위원, 자격 검정위원 등의 활동을 수행하고 있다. 저서로 데이터베이스 설계와 구축, 아는 만큼 보이는 데이터베이스 등 총 9권이 있다.

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

제공 : DB포탈사이트 DBguide.net

[출처] 이력유형 데이터모델링의 A to Z 효율적인 채번 방식을 적용한 성능향상 (DBGuide)|작성자 좋은생각