Monthly Archives: April 2012

MySQL DB 데이터 이관 자동화 구현하기

Overview

DB를 운영하다 보면, 한 개의 MySQL 인스턴스에 여러 개의 데이터베이스를 모아서 보관하는 경우가 있습니다. 그러면 가끔 DB명이 충돌나는 경우도 발생하죠. 오늘은 Dump/Rename/Import 등 모든 프로세스를 자동화할 수 있는 방안을 제시해 봅니다.

요구사항

무조건 자동으로 동작해야 하고, 기억력이 나쁜 제가 나중에 사용하기 쉽게 재사용성도 좋아야한다는 것입니다. 그리고 사용 방법을 잊어도 쉽게 상기할 수 있는 방안도 있어야겠죠.ㅋ (제가 정한 요구사항입니다. ㅋ)

  1. 모든 프로세스는 자동화되어야 한다.
  2. 스크립트 수정 없이 재사용이 가능해야 한다.
  3. 사용 매뉴얼이 있어야 한다.

자동화 구현

프로세스 순서는 다음과 같고 2단계부터는 파이프( “|” )로 한번에 처리합니다.

  1. 인자 수 체크
  2. 로컬 데이터베이스 Drop & Create (Rename)
  3. 원격 데이터 Dump
  4. Dump 파일에서 DB명 Rename
  5. 로컬 데이터 베이스로 Import

SHELL 스크립트

작성한 SHELL 스크립트 원본이고, 이관 작업은 데이터베이스 레벨로 이루어집니다. 테이블 레벨로의 확장은 하단 스크립트를 약간만 변경하면 될 것 같네요. (따로 구현은 안했습니다. ㅎ)

참고로 예전 포스팅 “리눅스에 MySQL 설치하기(CentOS 5.6)“대로 서버를 설치를 했으면, .bash_profile 에 mysql root 패스워드가 명시되어 있을 것입니다. 아래 스크립트는 .bash_profile 이 필요합니다.

#!/bin/sh

if [ $# -ne 5 ]; then
echo "Usage: ${0} <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>"
echo "<Example>"
echo "REMOTE_HOST : 192.168.100.10"
echo "REMOTE_DB : snsdb"
echo "REMOTE_DB_ID : sns"
echo "REMOTE_DB_PW : sns12#"
echo "LOCAL_TARGET_DB : kth_snsdb"
echo "==> ${0} targetdb01 snsdb sns sns12# kth_snsdb"
exit 1
fi

## Exec profile for mysql user
. ~/.bash_profile

## Declare Valiables
REMOTE_HOST=${1}
REMOTE_DB=${2}
REMOTE_DB_ID=${3}
REMOTE_DB_PW=${4}
LOCAL_TARGET_DB=${5}

echo ">> Start Migration $db database"

echo ">> Drop and Create Database at Local Host"
mysql -uroot -p${ADMIN_PWD} -e"DROP DATABASE IF EXISTS ${LOCAL_TARGET_DB};CREATE DATABASE ${LOCAL_TARGET_DB};"

echo ">> Dump, Rename DB name, Insert Data"
mysqldump -u${REMOTE_DB_ID} -p${REMOTE_DB_PW} --host=${REMOTE_HOST} --single-transaction --no-create-db --databases ${REMOTE_DB} | sed -r 's/^USE `(.*)`;$/USE `'${LOCAL_TARGET_DB}'`/g' | mysql -uroot -p${ADMIN_PWD} ${LOCAL_TARGET_DB}

echo ">> Finish Migration $db database"

실제 사용은 다음과 같습니다.

$ ./mig_service_data 192.168.10.111 dbname dbuser dbpass dbname_renamed
>> Start Migration database
>> Drop and Create Database at Local Host
>> Dump, Rename DB name, Insert Data
>> Finish Migration database

모를 때는 아래와 같이 Script만 실행하면 매뉴얼이 나와요~

$ ./mig_service_data 
Usage: ./mig_service_data <REMOTE_HOST> <REMOTE_DB> <REMOTE_DB_ID> <REMOTE_DB_PW> <LOCAL_TARGET_DB>
<Example>
REMOTE_HOST : 192.168.100.10
REMOTE_DB : snsdb
REMOTE_DB_ID : sns
REMOTE_DB_PW : sns12#
LOCAL_TARGET_DB : kth_snsdb
==> ./mig_service_data targetdb01 snsdb sns sns12# kth_snsdb

Conclusion

위 프로세스는 Dump파일을 직접 열고, 데이터베이스 명을 원하는 이름으로 변경하여 진행해도 상관없습니다. 그러나 작은 운영이라도 최대한 자동화하여 운영 이슈를 최소화 하는 것을 저는 지향합니다. 설혹, 구현한 스크립트가 단 한번만 사용될 지언정^^;;

 

MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!

안녕하세요.  오늘 짧지만 재미있는 내용을 하나 공유할까 합니다.

커버링 인덱스(Covering Index)라는 내용인데, 대용량 데이터 처리 시 적절하게 커버링 인덱스를 활용하여 쿼리를 작성하면 성능을 상당 부분 높일 수 있습니다.

커버링 인덱스란?

커버링 인덱스란 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미합니다. B-Tree 스캔만으로 원하는 데이터를 가져올 수 있으며, 칼럼을 읽기 위해 굳이 데이터 블록을 보지 않아도 됩니다.

인덱스는 행 전체 크기보다 훨씬 작으며, 인덱스 값에 따라 정렬이 되기 때문에 Sequential Read 접근할 수 있기 때문에, 커버링 인덱스를 사용하면 결과적으로 쿼리 성능을 비약적으로 올릴 수 있습니다.

백문이 불여일견! 아래 테스트를 보시죠.

테이블 생성

먼저 다음과 같이 테이블을 생성합니다.

create table usertest (
 userno int(11) not null auto_increment,
 userid varchar(20) not null default '',
 nickname varchar(20) not null default '',
 .. 중략 ..
 chgdate varchar(15) not null default '',
 primary key (userno),
 key chgdate (chgdate)
) engine=innodb;

약 1,000만 건 데이터를 무작위로 넣고 몇가지 테스트를 해봅니다.

커버링 인덱스(SELECT)

select chgdate , userno
from usertest
limit 100000, 100
************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 9228802
        Extra: Using index
1 row in set (0.00 sec)

쿼리 실행 계획의 Extra 필드에 “Using Index” 결과를 볼 수 있는데, 이는 인덱스만으로 원하는 데이터 추출을 하였음을 알 수 있습니다.

이처럼 데이터 추출을 인덱스에서만 수행하는 것을 커버링 인덱스라고 합니다. 아시겠죠? ^^

그렇다면 일반 쿼리와 성능 테스트를 해볼까요?

커버링 인덱스(WHERE)

1) 일반 쿼리

select *
from usertest
where chgdate like '2010%'
limit 100000, 100

쿼리 수행 속도는 30.37초이며, 쿼리 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where

Extra 항목에서 “Using where” 내용은, Range 검색 이후 데이터는 직접 데이터 필드에 접근하여 추출한 것으로 보면 됩니다.

2) 커버링 인덱스 쿼리

select a.*
from (
      select userno
      from usertest
      where chgdate like '2012%'
      limit 100000, 100
) b join usertest a on b.userno = a.userno

쿼리 수행 시간은 0.16초이며 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where; Using index

Extra 에서 “Using Index”를 확인할 수 있습니다.

그렇다면 30초 넘게 수행되는 쿼리가 0.16초로 단축됐습니다. 왜 이렇게 큰 차이가 발생했을까요?

첫 번째 쿼리는 Where에서 부분 처리된 결과 셋을 Limit 구문에서 일정 범위를 추출하고, 추출된 값을 데이터 블록에 접근하여 원하는 필드를 가져오기 때문에 수행 속도가 느립니다.

두 번째 쿼리에서도 동일하게 Where에서 부분 처리된 결과 셋이 Limit 구문에서 일정 범위 추출되나, 정작 필요한 값은 테이블의 Primary Key인 userno 값입니다. InnoDB에서 모든 인덱스 Value에는 Primary Key를 값으로 가지기 때문에, 결과적으로 인덱스 접근만으로 원하는 데이터를 가져올 수 있게 됩니다. 최종적으로 조회할 데이터 추출을 위해서 데이터 블록에 접근하는 건 수는 서브 쿼리 안에 있는 결과 갯수, 즉 100건이기 때문에 첫 번째 쿼리 대비 월등하게 좋은 성능이 나온 것입니다.

커버링 인덱스(ORDER BY)

커버링 인덱스를 잘 사용하면 Full Scan 또한 방지할 수 있습니다. 대부분 RDBMS에는 테이블에 대한 통계 정보가 있고, 통계 정보를 활용해서 쿼리 실행을 최적화 합니다.

다음 재미있는 테스트 결과를 보여드리겠습니다. 전체 테이블에서 chgdate 역순으로 400000번째 데이터부터 10 건만 가져오는 쿼리입니다.

1) 일반 쿼리

select *
from usertest
order by chgdate
limit 400000, 100
************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9228802
        Extra: Using filesort
1 row in set (0.00 sec)

분명 인덱스가 있음에도, Full Scan 및 File Sorting이 발생합니다. 인덱스를 태웠을 때 인덱스 블록을 읽어들이면서 발생하는 비용보다 단순 Full Scan이 더 빠르다고 통계 정보로부터 판단했기 때문이죠. 인덱스도 데이터라는 것은 항상 기억하고 있어야 합니다^^

결과 시간은 책정 불가입니다. (안끝나요~!)

2) 커버링 인덱스 쿼리

위 결과와 다르게 커버링 인덱스는 조금 더 재미있는 결과를 보여줍니다.

select a.*
from (
      select userno
      from usertest
      order by chgdate
      limit 400000, 100
) b join usertest a on b.userno = a.userno
************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 400100
        Extra: Using index

File Sorting이 발생하지 않고 커버링 인덱스가 사용되었으며, 실행 시간 또한 0.24초로 빠르게 나왔습니다.^^

Conclusion

커버링 인덱스는 InnoDB와 같이 인덱스와 데이터 모두 메모리에 올라와 있는 경우에 유용하게 쓰일 수 있습니다. 물론 커버링 인덱스가 좋기는 하지만, 커버링 인덱스를 사용하기 위해 사용하지 않는 인덱스를 주구장창 만드는 것은 최대한 피해야 하겠죠^^

잊지마세요. 인덱스도 데이터라는 사실을..

 

 

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 방식을 써야만 했겠죠.

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