Category Archives: Oracle

오라클 블로그

서비스 로직 흐름을 변경하여 DB 튜닝을 해보자!

Overview

오래전 메가존(현 혜택존) 서비스를 담당하던 시기, 포인트 관련된 부분에서 심각한 성능 저하 및 유효성 취약 문제가 발생하였습니다. 장비 고도화를 통해 관련 문제를 해결하기에 앞서, 서비스 로직 및 테이블 재구성을 통한 최적화 작업을 통해 문제를 해결하였습니다.

이에 관해 간단하게 소개하도록 하겠습니다.

Problems

다음과 같이 크게 두 가지 문제가 있었습니다.

  1. 성능 이슈
    1. 로그 테이블은 거대한 한 개의 테이블로 구성
    2. 데이터 누적에 따라 성능이 급격하게 저하
  2. 유효성 이슈
    1. 자바 어플리케이션에서만 유효성 체크 – 비 정상적인 사용 존재
    2. 포인트가 현금처럼 사용될 수 있으므로 반드시 필요함

Solutions

1) 파티셔닝을 통한 성능 최적화

오라클 엔터프라이즈 버전에서는 테이블 파티셔닝을 제공하지만, 아쉽게도 오라클 스탠다드에서는 관련 기능이 없습니다. 즉, 어플리케이션 레벨에서 적당히 데이터 분산을 유도하는 방법 밖에는 없습니다. 데이터를 분산하는 방법으로는 여러가지가 존재하겠지만, 저는 월별로 테이블을 분산 저장하는 방식을 사용하였습니다.

매월 하단과 같은 스키마의 테이블을 MCHIP”YYYYMM” 형식으로 생성을 합니다.

물론 해당 월 다음 달로 생성을 해야겠죠?

 Name            Type
 --------------- ------------
 SEQ             NUMBER
 USER_NO         VARCHAR2(12)
 POINT           NUMBER
 ISSUE_DATE      DATE

그리고 인덱스 필요 시 데이터 테이블스페이스와는 “물리적으로 분리”된 전용의 테이블스페이스에 생성하여 DISK I/O 비효율도 최소화 유도하였습니다.

데이터 조회는 기본적으로 월별로만 가능하며, 최근 3개월 동안만 조회할 수 있도록 하였고, 필요 시 과거 테이블을 DROP하는 방식으로 변경하였습니다. 만약 최근 3개월 데이터가 필요할 시에는 “UNION ALL” 구문으로 데이터를 가져왔습니다.

다음은 쿼리를 월별로 생성하여 데이터를 저장하는 간단한 자바 프로그램입니다. 저는 SpringFramework 환경에서 구현했었는데, 그것보다는 알아보기 쉬운 단순 자바 프로그램으로 보여드리는 것이 좋을 것 같습니다. (설마 아래 있는 것을 그대로 쓰시는 것은 아니겠죠? ㅎㅎ)

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Test {
    public static void main(String[] args) {
        DateFormat df = new SimpleDateFormat("yyyyMM");
        Date date = new Date();

        // 현재 기준 년월 
        String yyyymm = df.format(date).toString();

        // 쿼리 생성
        String sql = "INSERT INTO MCHIP"+yyyymm+" \n" +
                "(SEQ, USER_NO, POINT, ISSUE_DATE) \n" +
                "VALUES \n" +
                "(SEQ_MCHIP.NEXTVAL, ?, ?, SYSDATE)";

        System.out.println(sql);
    }
}

위와 같이 쿼리를 생성하고, 원하는 테이블에 선별적으로 데이터를 넣습니다. 조회 시에도 위와 같은 방식으로 쿼리를 생성합니다. 만약 한달 이상의 데이터가 필요하다면 UNION ALL로 쿼리를 붙여서 데이터를 조회하면 되겠죠. ^^

2) 트리거를 사용한 유효성 체크

포인트 내역을 저장하는 테이블이 있었다면, 포인트 현황을 조회하기 위한 전용 테이블 또한 존재했습니다. 포인트 현황에 저장된 점수가 사용자가 현재 소지하고 있는 포인트이며, 이를 차감하여 서비스에서 사용하는 방식으로 사용하고 있었습니다.

개인 당 한 건의 데이터만 있기 때문에, 사용자 데이터를 조회하는 것에는 큰 무리가 없었지만, 유효성을 자바 어플리케이션에서만 체크했었기 때문에 여기저기 헛점이 많은 상태였습니다. 물론 내구성이 뛰어나게 개발되었다면 큰 문제는 없었겠지만, 협력사가 시간에 쫓겨서 급하게 개발한 산출물인지라.. 아무래도.. ^^;;

자바 소스 전체를 뒤져서 모든 유효성을 체크하는 것은 거의 무리에 가까웠고, 그래서 제가 채택한 방식은 트리거였습니다. 트리거를 사용하여 사용자 포인트 유효성 여부를 DB레벨에서 체크해서 어플리케이션과 분리하자는 의도였죠.

MCHIP201209 테이블에 포인트가 내역이 들어가는 동시에 유효성 체크 후 정상적이면 포인트 현황 테이블에 반영하는 트리거입니다.

CREATE OR REPLACE TRIGGER TRG01_MCHIP201209
BEFORE INSERT
ON MCHIP201209
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  POINT NUMBER;
BEGIN
  /*** 현재 사용자 포인트 조회 ***/
  SELECT POINT INTO POINT FROM MCHIP_INFO
  WHERE USER_NO = :NEW.USER_NO;
  … 중략…

  /*** 포인트 유효성 체크  ***/
  IF POINT - :NEW.POINT < 0 THEN
    RAISE_APPLICATION_ERROR(-20001,'Not enough point!!');
  END IF;

  /*** 포인트 현황 업데이트 ***/
  UPDATE MCHIP_INFO SET POINT = POINT + POINT_NEW
  WHERE USER_NO = :NEW.USER_NO;
END;
/

Conclusion

오래전에 성능을 최적화한 내용을 정리하였습니다.

당시 서버에 과부하로 인하여 서버 고도화 및 장비 도입을 위한 투자를 검토 중이었으나, 서비스 로직 재구성 이후 서버가 안정적이었기 때문에 기존 장비로 서비스를 진행하였습니다. 게다가 현재 기존 장비 사용률이 CPU 기준 80~90%였던 상황이 10~20%로 유지되는 쾌거를 거두었죠.

데이터 처리량을 최소로 유도하는 것이 성능 최적화의 첫걸음이라는 것을 느낀 경험이었습니다.

좋은 포스팅으로 다시 찾아뵐께요^^;

DB Link와 Export/Import를 활용한 데이터 이관 성능 리포트

안녕하세요. 한동안 MySQL에 빠져 있다가, 최근 Oracle 데이터 이관 작업 도중 재미난 사례 공유 합니다. DB Link를 통한 CTAS(Create Table As Select)와 Export/Import를 통한 데이터 이관 비교입니다.

서비스 요구 사항

서비스 DBMS 버전 : Oracle 9i
전체 데이터 파일 사이즈 : 120G (인덱스 포함)
타겟 테이블 데이터 사이즈 : 26G (인덱스 제외)
네트워크 속도 : 100Mbps (max: 12.5MB/s)
일 1회 현재 서비스 데이터 동기화 수행
모든 작업은 “자동화”하여 운영 이슈 최소화

위 환경을 고려하였을 때, 전체 데이터 파일 Copy는 동기화 시간 및 스토리지 낭비 요소가, Archive Log 활용하기에는 운영 이슈가 존재했습니다.

그래서 결정한 것이 필요한 테이블만 이관하자는 것이고, 가장 효율적인 방안을 모색하기 위해 DB Link와 Import/Export 두 가지 성능을 비교해보았습니다.

DB 환경 구축

Oracle DBMS 환경은 장비 효율을 높이기 위해서 Oracle VM 상에 가상 머신으로 구성을 하였습니다. Oracle VM에 대한 소개는 추후 천천히 소개를 드릴께요^^

서버 구성

서버 구성

Export/Import 결과

아래 그림과 같이 nfs Server/Client 구성을 하였는데, 타겟 테이블을 별도로 Export 후, 해당 파일을 nfs로 직접 끌어와서 데이터를 가져오는 방식입니다.

Export/Import를 위한 nfs 서버 구성

Export/Import를 위한 nfs 서버 구성

Export/Import 결과

Export 시간은 약 20분, Import시간은 약 60분 그리고 사전 작업 시간을 고려해봤을 때, 데이터만 이관할 시 90분이라는 시간이 소요되었습니다. 하루에 1시간 30분 정도 동기화 시간을 제공하면 되니 크게 나쁜 결과는 아니었습니다.

그러나 Import 수행되는 동안 alert.log를 확인을 해보니 Log Switch가 너무 빈도있게 발생(1분 단위)하였으며, 아래와 같이 Log가 데이터 파일로 Flush되지 않아서 대기하는 상황이 발생하였습니다.

Import 시 Alert 로그 현황

DB Link 결과

Export/Import 시간 체크 후 Redo Log 사용 없이 직접적으로 데이터를 Write할 수 있다면 더욱 빠른 데이터 이관이 가능할 것이라고 판단이 되어 이번에는 DB Link를 통한 CTAS(Create Table As Select) 방법으로 접근을 해보았습니다. 물론 CTAS에서 반드시 NOSLOGGING 옵션을 줘야겠죠 ^^

Oracle VM 서버에서 환경에 맞게 계정 별로 DB Link를 생성을 하고 하단과 같이 테이블 Drop 후 Create Table As Select  구문을 실행합니다.

SQL> DROP TABLE TABLE01;
SQL> CREATE TABLE TABLE01 NOLOGGING
   > AS
   > SELECT * FROM TABLE01@DB_LINK;

Oracle VM에서는 물리적으로 다른 장비 세 대로부터 동시에 데이터를 받으며, 물리적인 장비 안에는 각각 두 개의 서비스 계정이 포함됩니다.

결과는 다음과 같습니다.

DB Link Result

위와 마찬가지로 동시에 실행을 했기 때문에 가장 오래 걸린 서비스가 최종 소요된 시간이며, 00:00~ 00.19:13 에 종료되었으니 대략 20분 소요되었습니다. 앞선 결과에서 90분 소요되던 것을 20분으로 줄였으니, 상당히 괜찮은 결과이네요^^

물론 Alert Log에 Log Switch 관련 메시지는 나오지 않았습니다.

Conclusion

DB Link를 활용하여 데이터를 이관하는 것이 압도적으로 빠른 결과였습니다.

그러나 항상 DB Link가 좋은 것은 아닙니다. 다행히 대상 테이블에는 LOB 관련 필드는 없었지만, 만약 LOB 필드가 있었다면 어쩔 수 없이 Export/Import 방식을 써야만 했겠죠.

데이터 이관을 위한 여러가지 방안이 있겠지만, 한번 쯤은 두 가지 방법의 장단점을 따져보고 싶었는데 좋은 기회가 되어서 내용 공유 드립니다.