최근 IT 비용 절감으로 인하여, 오픈 소스 소프트웨어 적용 비율이 높아지고 있는데, DB 쪽도 예외가 아닙니다.

비용이 그다지 문제가 되지 않는다면, Oracle Exadata를 구매해서 사용해보면 국내 웬만한 서비스들은 다 커버가능하지 않을까

하는 희망찬 꿈에 빠져보기도 하지만, 서비스 운영 시 비용은 반드시 고려해야할 난관입니다.
IT 비용을 절감하기 위해서, 물론 장비 도입 시기부터 결정하는 방법도 있겠지만,

기존 장비를 오픈소스 소프트웨어(Oracle to MySQL)로 데이터 이관해야하는 경우도 있습니다.

오늘 제가 말씀드리고 싶은 사항은 기존 Oracle로 구현되어 있던 솔루션을 MySQL로 포팅한 사례이며, MySQL로 서비스 후

나날이 성능 저하 현상이 발생한 원인과 개선 방안에 관한 내용입니다.

아무래도 솔루션인지라, 쉽게 Application을 변경하기 어려운 상황이고, 기존 구조를 거의 그대로 유지해야 하므로,

기존 SQL튜닝과는 다른 방향으로 문제를 해결하였습니다.

성능 저하 요소 분석

통계 분석 시간이 시간이 지날수록 크게 소요되는 현상과 데이터 저장을 위한 디스크 사용 효율이 시간이 지남에 따라

나날이 떨어지는 현상이 발생하였습니다.

디스크는 사용량은 300G를 데이터 영역으로 사용 중이었고, 일부 테이블에서는 인덱스 파일 사이즈가 데이터 파일 사이즈보다

큰 현상도 존재하였습니다.

다음은 개선 전 테이블이 사용하는 디스크 실제 크기입니다.

InnoDB특성을 고려하여 통계 DB의 성능 저하 원인을 다음과 같이 분석 하였습니다.

InnoDB Cluster Index (Primary Key) 로 인한 디스크 I/O
InnoDB Secondary Index 구조 특성에 따른 인덱스 사이즈 거대화
디스크 공간이 재사용되지 않아서 발생하는 저장 공간 낭비
250G 한 개 파일로 inndb file이 생성되어 있어서 관리가 어려움
이 외 Replication 관련 버그 Fix되기 버전 사용으로 인한 안정성 이슈

DB 성능 최적화 적용

DBMS 성능 최적화 활동InnoDB 특성을 고려하여 다음과 같이 최적화 활동을 진행하였습니다.

통계 쿼리 실행 최적화를 위해 Monthly Partitioning을 Daily Partitioning으로 변경
Primary Key 순으로 데이터가 순차 저장되지 않는 구조이기 때문에 DISK I/O가 크게 발생하였고, 기존 테이블 구조를 변경하기는 불가했습니다. Partitioning 관리를 월별에서 일별로 변경함으로써 기존 발생하던 DISK I/O이슈를 최소화하였습니다.자동 파티셔닝 관리를 위해 별도 Shell Script 작성
Primary Key 및 Secondary Key 재구성
일별 Partitioning 적용된 로그성 테이블에는 Primary Key를 제거 및 날짜 관련 인덱스 제거하여, 디스크 공간 사용 최소화
(통계 처리 시 1일 단위로 처리되므로, 인덱스가 굳이 불필요함)
로그 성 테이블 외 대용량 테이블은 Hash Partitioning을 활용하여 물리적으로 분리
로그 성 테이블을 제외한 테이블(일자 별 관리가 어려운 테이블)은 Primary Key 값을 기준으로 Hash Partitioning을 적용하여 물리적으로 분리하여 InnoDB Primary Key 유지를 위한 DISK I/O 발생 이슈를 최소화하였습니다.
한 개 파일로 관리되고 있는 innodb file을 innodb_file_per_table 옵션을 적용
autoextend 옵션으로 innodb file유지 시 저장 공간 반환이 되지 않기 때문에 재사용되지 않습니다. autoextend 옵션 없이 innodb file 유지 시 추후 innodb tablespace 공간 확장 시 DB를 shutdown후 작업을 해야하는 이슈가 발생하기 때문에 innodb_file_per_table 옵션을 활용하여 테이블 별로 저장 공간을 할당하였습니다.
Archive Storage 엔진 사용하여 과거 데이터 백업 관리
45일 이전 데이터는 다른 Database에 Archive Storage Engine 테이블에 백업 후 해당 일자 파티션 삭제하여 디스크 비효율을 제거하였습니다. Archive Storage Engine을 사용하게 되면 InnoDB 대비 1/5 정도 용량을 차지고, 인덱스를 제거하여 백업 데이터 유지한 결과 기존 대비 1/10 정도 Disk 공간으로 과거 데이터를 보관할 수 있습니다.
MySQL 5.1.57 으로 버전 Upgrade하여 Replication Fail Error 방지

다음은 파티셔닝 관리를 위해 나름 작성한 shell 스크립트입니다. shell 스크립트에 아직 익숙하지가 않은지라, 마음에 확 와닿게 짜지는 못했네요..^^;;

#!/bin/sh
MYSQL_HOME="/usr/local/server/mysql"
EXECUTE_QUERY="$MYSQL_HOME/bin/mysql -u아이디 -p패스워드 -e "

## 서버 목록 배열 "table명:파티셔닝필드" 로 정의

SERVERS=(

"tb01:field01"
"tb02:field02"
"tb03:field03"
"tb04:field04"
"tb05:field05"
"tb06:field06"

)

new_sdays=$1
new_edays=`expr $new_sdays + 1`

del_sdays=$2
del_edays=`expr $del_sdays - 1`

new_sdate=`date -d "+$new_sdays days" +%Y%m%d`
new_edate=`date -d "+$new_edays days" +%Y%m%d`
del_sdate=`date -d "-$del_sdays days" +%Y%m%d`
del_edate=`date -d "-$del_edays days" +%Y%m%d`

for INFO in ${SERVERS[@]}
do

tbl=`echo $INFO | cut -d: -f1`
col=`echo $INFO | cut -d: -f2`

## 신규 파티션 생성
query=" ALTER TABLE mstat.$tbl
REORGANIZE PARTITION P_MAX into (
PARTITION P_$new_sdate VALUES LESS THAN (TO_DAYS('$new_edate')),
PARTITION P_MAX VALUES LESS THAN MAXVALUE
)"
$EXECUTE_QUERY "$query"

## 데이터 다른 DB로 복사
query=" INSERT INTO 백업디비.$tbl
select * from mstat.$tbl
where $col >= '$del_sdate'
and $col < '$del_edate' "
$EXECUTE_QUERY "$query"

## 파티션 삭제
query=" ALTER TABLE 통계디비.$tbl DROP PARTITION P_$del_sdate"
$EXECUTE_QUERY "$query"
done