| |
아래 한개의 프로젝트를 따라가 보면서 오늘의 이야기를 해 보자.
의뢰 사항 :
- 모보험사의 청약내용 1200만건의 DB-TEXT자료(매년 약 300만씩 추가 증가)
- 해당 청약문서를 스캔한 무작위의 이미지 1200만 건이 CD에 나눠 담겨 있다.
- 스캔이미지를 모니터에서 눈으로 확인하면서 DB검색을 통해 1건씩링크를 걸어야 한다.
- 작업인원은 100여명
**** 주의사항 : 위 작업에서 시간은 곧 인견비 증가로 직결된다. (항상 이 돈이 문제다 ㅋㄷㅋㄷ)
위 조건에서 가장 문제가 되는 부분은 100여명의 클라이언트들이 1200만건의 데이터에 대해 다양한 검색을 하게 됨으로 SELECT, UPDATE시간을 얼마나 줄일수 있는지가 관건이다.
DB설계과정을 살펴보자.
1. 자료분석
- 주어진 DB-TEXT자료를 살펴본 결과 일정한 필드사이즈이다.
- 자료량이 많아서 년도별로 DB를 생성할 것인가? 통DB로 갈 것인가?를 고민하게 된다.
자료를 년도별로 분리해서 데이터베이스를 만들면 자료량이 줄어 검색시간은 줄일 수 있으나 관리상 MERGE, 여러 데이터베이스에서 검색을 해야하는 등의 오버헤드가 발생할 소지가 있다. -> 통 테이터베이스로 설계하자.
- LIKE검색은 최후의 수단이 되도록 해야한다.
2. 설계
- 일정한 필드사이즈라면 VARCHAR형이 아니라 CHAR형으로 설계를 해서 자료크기와 검색을 단순화 한다.
- 많은 사용자가 작업을 함으로 로그인 시스템과 작업자 관리, 작업자별 작업량을 분석할 수 있어야 한다.
- 주된 작업이 INSERT, UPDATE가 대부분임으로 INDEX를 고려한다.
- INSERT작업은 별도의 테이블을 이용하며 작업자들이 이용하지 않는 시간에 일괄 처리하도록 해서 작업중의 오버헤드를 없앤다.
- 실시간처리가 목표임으로 트랜잭션을 사용하지 않고 바로 COMMIT.
- 스케줄러를 이용해서 자동백업을 시행한다.
3. 프로그래밍
- 로그인, 이미지처리, CD관리, 작업자관리, 색인, 통계, 레포팅으로 구성된다.
- 가장 핵심 작업인 색인작업의 경우 작업자들이 CD에서 순차적으로 이미지 판독->검색->색인의 단계로 작업이 반복 이뤄짐으로 사용자 인터페이스를 최대한 단순화하는 것이 효율로 직결된다.
- 검색, 색인의 경우 마스크를 이용해서 사용자의 오류를 허용하지 않도록 한다.
4. 테스팅
- 텍스트를 DB에 인포트 시키고 프로그램을 통해 확인한다. 인포트된 DB파일이 10G에 달한다.
- 허~~걱(ㅠ.ㅠ;;) 검색을 해본 결과 28초에 해당한다. (불안하다 이런 속도라면 끝장인데...ㅡ.ㅡ;;)
5. 튜닝
- 28초라는 시간은 작업이 불가한 시간이다. 이 시스템에서 가장 큰 리스크 요인은 검색이므로 검색의 주 대상인 사람이름, 주민번호 등에 인덱스를 작성한다.===> 결과 : 리얼타임의 검색이 가능하다.
6. 실전테스팅
네트워크를 구성하고 허브를 통해 100여대의 컴을 연결한다.
작업자들의 작업이 시작되고 조마조마한 심정으로 작업을 지켜본다.
^^;; 휴~~~~ 100여명의 동시 작업이 실시간으로 검색,색인이 가능하다.
프로그램의 색인시 중복체크는 1:1200만건의 비교가 이뤄지므로 속도의 저해요인으로 밝혀짐으로 검증단계를 별도로 두어 중복체크는 삭제했다.
서버:P4 2.4Ghz dual, 2G RAM, sql-server 2000 클라이언트 : P3 400Mhz급의 pc들
오늘 말하려고 하는 것은 바로 인덱스의 중요성이다.
위에서 28초 소요되던 것이 실시간 처리가 가능하게 한 것은 인덱스의 힘이다.
전문가들도 인덱스에는 정답이 없다고 말한다. 이유인 즉은 인덱스는 잘 사용하면 약이 되지만 잘 못 사용하면 독이 된다.
아래는 독이된 케이스를 하나 살펴보자.
한번은 모 회사에서 개발한 시스템의 입력속도가 느려서 도져히 쓸수가 없으니 살펴봐 달라는 문의가 있었다.
오라클을 사용하고 있었으며, 작업을 위해 퍼스널 오라클을 이용해서 입력을 한 후에 일괄처리 방식으로 서버로 인포트 시키는 방식이 었다. 입력되는 사항이 필드 10개 수준의 색인 텍스트로 한개의 레코드를 전부 합쳐서 200글자 정도 이다. db에 입력되어 있는 레코드는 3만건 수준이 었다. 시스템이 p4의 512RAM을 사용하는 최신형 PC였는데 한 레코드 입력에 5분이 소요되어 담배피고 와도 될 정도라면 도저히 이해가 안가는 속도다.
구조를 살펴본 결과 테이블은 불필요하게 쪼개져서 조인되어 있고, 테이블 별로 4개의 필드를 엮어서 함께 키값으로 잡고 있었으며, 인덱스가 필드마다 거진 죄다 걸려 있었다. 당연 인덱싱과 제약조건을 모두 만족할때 까지 체킹을 해야만 한 건의 자료가 입력되는 구조이다. 자료가 쌓일 수록 속도는 10분까지 느려지고 있었다.
내가 손을 볼 수 있는 상황은 없어보였다. 프로그램을 개발한 측에서 프로그램과 DB를 함께 손을 봐야만 한다는 멘트를 전할 뿐이었다.
인덱스를 사용할 때 어떤 점을 주의할 것인가?
인덱스는 필드를 순서를 두어 재가공 해서 색인 저장함으로 DB공간을 추가로 차지하며, 인덱스를 해야 하는 경우와 그렇지 말아야 하는 경우가 있다. 인덱스가 성능을 발휘하지 못하는 경우도 있다.
(김규경님이 써 놓은 글을 참고 하셔서 읽어 보시길. http://www.officetutor.com/column/kkk-db/kkk_01_4.htm)
한가지 최선의 인덱스 생성 룰은 INSERT/DELETE/UPDATE별로 많/이/사/용/하/는 필드를 중심으로 가이드 라인을 참조해서 결정하시라는 것이다.
** 참조 : 위와 같이 1200만 (즉 대용량)의 자료에 만건의 레코드를 INSERT 한다면 어떤 결과가 생길까?
30~40분 가량을 멍하니 기달려야 한다. HDD가 혼자 죽어라고 돌아가는데 이는 INSERT시에 해당 제약조건들을 모두 검증하기 때문이다. (필자는 처음에 기달리다 지쳐서 서버를 꺼버린 적이있다.ㅋㅋㅋ)
그렇다면 어떻게 빨리 할수 없나. 답은 있다. 인덱스를 삭제해버리시라. 그리고 INSERT 한 후에 다시 인덱스를 구성하시라. 5분이내에 작업이 모두 끝난다.
** 참조 : 쿼리분석기에는 쿼리문이 어떤 테이블, 어떤 색인테이블을 이용해서 얼마나 시간이 걸려 결과를 얻는지 비주얼하게 확인하는 툴이 포함되어 있으니 자신의 색인을 점증 할때 사용해 보시길.
~~EASY IS POWERFUL SIMPLE IS BEAUTIFUL~~~
|
|
액세스에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
