Monthly Archives: May 2012

Tumblr에서는 MySQL로 어떻게 대용량 데이터를 관리하였을까?

Overview

트위터의 새로운 분산 관리 라이브러리 Gizzard를 소개합니다.“를 알아보던 당시 부사수 “임창선”님과 진행했던 또 다른 해외 사례 “Tumblr”를 정리해보았습니다.

Tumblr는 국내에서는 사용자가 많지는 않지만, Twitter 정도의 트래픽을 자랑하는 Micro Blog 서비스입니다. 하루 평규 5억 이상의 PV가 나오고, 초당 4만 건 이상 Request가 나오며, 하루 평균 3TB 이상의 데이터를 쌓는다고 하니 엄청난 서비스인 것은 틀림 없습니다.

이정도 데이터를 관리하기 위해서 수 천대 이상의 서비스를 운영한다고 하는데, 데이터 관리를 MySQL을 활용하여 제공한다고 합니다. 그렇다면 MySQL로 어떻게 대용량 데이터를 멋지게 다뤘을까요?

Tumblr?

데이터 저장에 사용하고 있는 MySQL머신 수는 약 175대 이상(지금은 더욱 많아졌겠죠^^)이며, Master 데이터 용량만 약 11TB 이상(단일 데이터 건수 250억 건 이상)이라고 합니다. 어마어마한 데이터 용량이죠.

1년 동안 Tumblr 상황을 비교한 표입니다. 엄청난 데이터를 약 20명 정도의 인력으로 관리하고 있다니, 감탄을 금치 않을 수 없네요.^^
(정확히 언제 1년 전/후 인지는 파악은 안됩니다. ㅋ)

Tumblr 현황 비교

Shard Automation

Tumblr에서는 Shard를 자동화 구성을 위해 다음같은 것들을 구현하였습니다.

  1. 모든 Shard에 관한 위상 정보 수집
  2. 서버 설정을 조작하기 위한 Unix 명령 및 DB 쿼리 실행
  3. 여러 서버로 대용량 파일을 복사
  4. 임의의 데이터셋에 대한 Import/Export
  5. 단일 Shard를 대중 Shard로 분리할 수 있는 기능

Shard 목표는 다음과 같습니다.

  1. 지나치게 큰 Shard 조각을 새로운 N개의 Shard로 재배치한다.
  2. Lock이 없어야하며 어플리케이션 로직도 필요 없어야 한다.
  3. 5시간 안에 800GB 데이터 파일을 2개로 나누어야 한다.
  4. 전체적으로 서비스에 전혀 지장이 없어야 한다.

Shard 자동화는 다음 원칙하에 이루어집니다.

  1. 모든 테이블은 InnoDB  Storage Engine을 사용한다.
  2. 모든 테이블은 Shard Key로 시작하는 Index를 가진다.
  3. Shard Schema는 Range-based로 한다.
  4. Shard 과정 중 스키마 변경은 없다
  5. 디스크 사용률이 2/3 미만이거나, 별도 디스크가 있어야 한다.
  6. Shard Pool 마다 2 대의 대기 Slave 서버가 존재해야 한다.
  7. Master와 Slave 사이의 균일한  MySQL 설정 한다.
    (log-slave-updates, unique server-id, generic logbin and relay-log names, replication user/grants)
  8. Master Slave 사이에 데이터 동기화에 지연이 있으면 안된다.
  9. 잘못된 Shard에 일시적인 중복된 행은 서비스에 문제가 안된다.

Shard Process

Shard는 다음과 같은 프로세스 대로 자동화 구현합니다.

  1. 기존 Shard Pool에서 새로운 Slave N개 생성
  2. 신규 Slave에 기존 Master 데이터 분할 저장
  3. READ를 신규 분할 Master(신규 Slave)로 이동
  4. Write를 신규 분할 Master(신규 Slave)로 이동
  5. 기존 Master와 신규 Master 서버 간 Replication 끊기
  6. 신규 Master 데이터 보정

1) 기존 Shard Pool에서 새로운 Slave N개 생성

커다란 데이터 파일을 N개의 Slave 서버로 빠르게 재비치 하는 것을 목표로 합니다.

Tumblr Slave Data Clone

Tumblr Slave Data Clone

pigz(Parallel gzip)을 사용 빠르게 압축하고, 그와 동시에 유닉스 명령어인 nc로 신규 Slave에 파일을 바로 전송합니다.

Slave4(신규 장비)에 nc로 포트 개방 및 압축 해제하도록 구성합니다.

[mysql@Slave4~]$ nc -l 10000 | pigz -d | tar xvf -

Slave3(신규 장비)에 tee와 fifo를 활용하여 받음과 동시에 데이터를 Slave4로 보내도록 구성합니다.

[mysql@Slave3~]$ mkfifo myfifo
[mysql@Slave3~]$ nc Slave4 1234 <myfifo &
[mysql@Slave3~]$ nc -l 10000 | tee myfifo | pigz -d | tar xvf -

Slave2(기존 장비)에서 압축 후 nc 로 바로 신규 데이터를 전송하도록 합니다.

[mysql@Slave2~]$ tar cv ./* | pigz | nc Slave3 10000

Slave2 -> Slave3 -> Slave4 로 동시에 순차적으로 데이터가 전송됩니다. 결과적으로 세 대 Slave 장비는 CPU, Memory, Disk, Network 등을 효율적으로 사용하게 되죠.^^ 순차적으로 복사하거나 단일 소스에서 병렬로 복사하는 것보다 훨씬 성능이 좋습니다.

위 그림처럼 텀블러가 Slave 장비를 서비스에 투입하지 않고 Standby 상태로 구성하는 이유는 pigz의 사용시 발생되는 리소스 부하로 인한 서비스의 영향도 때문으로 추측됩니다. 실제 테스트를 해보니 서버 리소스 영향이 있었습니다.

17:16:54  CPU  %user  %nice  %system  %iowait  %idle
17:16:55  all  53.80   0.00     3.55     3.05  39.60
17:16:56  all  67.00   0.00     4.25     2.38  26.38
17:16:57  all  40.11   0.00     2.68     5.24  51.97
17:16:58  all  70.75   0.00     4.36     2.12  22.78
17:16:59  all  63.27   0.00     3.81     3.69  29.23
17:17:00  all  64.57   0.00     4.30     2.06  29.07
17:17:01  all  51.00   0.00     3.62     4.50  40.88
17:17:02  all  57.96   0.00     4.37     2.87  34.79

2) 신규 Slave에 기존 Master 데이터 분할 저장

신규 Slave에 기존 데이터를 N개로 나눠서 저장합니다.

신규 Slave 데이터 분할 저장

그런데 데이터를 분할하는 방식이 참으로 재미 있습니다.

먼저 “Select .. Into Outfile”로 데이터를 추출하고, 테이블을 Drop 및 Create 한 후 추출한 데이터를 “Load Data Infile”로 다시 넣는 것입니다.

과연 무엇이 더 빠른 것인지 판단이 정확하게 서지는 않지만, Tumblr에서는 기존 데이터를 날리고 Bulk Insert 하는 것이 훨씬 빠르다고 판단한 것 같습니다.

Load Data가 정상적으로 마무리되면, 신규 Slave 밑에 각각 두 개 Slave를 붙입니다. 아래 그림과 같은 형상이 되겠죠.^^

Slave 최종 형상

아래는 데이터를 Export/Import 시 주의할 사항이라고 합니다.

  1. import 속도를 위해 바이너리 로그를 비활성화 한다.
  2. 쿼리를 Kill하는 스크립트는 사용하지 않는다.
    (SELECT … INTO OUTFILE 과 LOAD DATA INFILE는 KILL하지 않도록 함)
  3. 어느 정도 import/export가 가능하지를 벤치마크하여 파악한다.
  4. 속도가 다른 디스크 여러 개를 동시 사용하는 경우 디스크 I/O 스케줄러 선택을 주의한다.

한 가지 의문이 가는 사항은 정상적인 Replication 상태라면 분명 신규 Slave에서 Replication Fail이 발생했을 것 같다는 것입니다. 아무래도 Slave 서버에서 모든 Error를 무시하는 설정을 해놨다는 생각이 드네요.

또한 Binlog Log 는 아무래도 Row 포멧이 위와 같은 경우에서는 조금 더 유리하지 않을까요? 그냥 추측 두 가지를 해봅니다.^^

3) READ를 신규 분할 Master(신규 Slave)로 이동

어플리케이션에서 READ를 기존  Master에서 신규 Slave(앞으로 Master로 구성될 서버)로 이동을 하여 서비스를 진행합니다.

Move Read to New Slave

만약 어플리케이션 서버에서 업데이트가 동시에 완료되지 않은 상태에서 갑자기 Read/Write 포인트가 이동한다면, 데이터 일관성 문제가 발생할 것입니다.

동시에 Write까지 이동되었다고 가정해봅시다. A가 새로운 DB 형상을 받고 200 게시물을 작성하였으나, B는 여전히 예전 형상을 바라보고 기존 Master 서버에서 데이터를 읽어오면 게시물을 찾을 수 없겠죠.

그렇기 때문에 기존 Master와 데이터 복제를 유지하면서 Read 포인트만 먼저 이동하는 것입니다.

4) Write를 신규 분할 Master(신규 Slave)로 이동

모든 DB 구성에 관한 형상이 업데이트 된 후에 Write 포인트를 변경합니다. 옮긴 후에도 기존 Master DB의 바이너리 로그 기록이 정지하기 전까지는 절대 기존 Master/Slave 구성에 손을 대면 안되겠죠.^^

Move Write to New Slave

5) 기존 Master와 신규 Master 서버 간 Replication 끊기

기존 Master의 바이너리 로그 기록이 중지되었다면, 이제 필요없는 DB들을 제거하도록 합니다.

Remove Previous DB Servers

이제 모든 데이터는 새로운 Slave 아니 새로운 Master에서 Read/Write 서비스되겠죠. ^^

6) 신규 Master 데이터 보정

분할된 Shard된 조각에서 잘못 복제된 데이터를 제거하는 작업이 필요합니다.

그러나 절대적으로 하나의 커다란 단위의 Delete 작업은 피하도록 합니다. Master/Slave 간 데이터 동기화 지연을 최소화하기 위한 방안이죠.

그러므로 Delete 작업도 조금씩 끊어서 수행하도록 유도합니다.

Conclusion

정리를 하자면, “서비스에 투입하지 않은 Slave에서 빠르게 복제 서버를 두 대 추가하고, Master 데이터를 각각 반으로 나눠서 분리 저장한다” 고 보면 되겠습니다. Tumblr에서는 내부적으로 조금더 상세한 내용을 공유하지 않았지만, 적어도 데이터를 Shard하는 동안에는 서비스 Downtime이 없다는 것입니다.

Scale-Out 방안이 다양하지 않은 MySQL에서 이러한 방식으로 신속하게 Scale Out할 수 있었던 기법에서 많은 점을 배운 것 같네요.^^

대용량 데이터 관리를 위한 어플리케이션 개발도 필요하겠지만, 전체적인 데이터 흐름을 아는 것이 무엇보다 중요하다고 생각합니다.

<참고 사이트>
Tumblr – Massively Sharded MySQL
Efficiently copying files to multiple destinations
Tumblr Architecture – 15 Billion Page Views A Month And Harder To Scale Than Twitter

MySQL에서 테이블 스키마를 “무중단”으로 변경해보자!!

Overview

MySQL은 단순 쿼리 처리 능력은 탁월하나 테이블 스키마 변경 시에는 상당히 불편합니다. 일단 테이블 스키마 변경 구문을 실행하면 임시 테이블 생성 후 데이터를 복사하고, 데이터를 복사하는 동안에는 테이블에 READ Lock이 발생하여 데이터 변경 작업을 수행하지 못합니다. (Table Lock이 걸리죠.)

이 같은 현상은 인덱스, 칼럼 추가/삭제 뿐만 아니라 캐릭터셋 변경 시에도 동일하게 발생합니다. (최근 5.5 버전에서는 인덱스 추가/삭제에서는 임시 테이블을 생성하지 않습니다.)

얼마 전 서비스 요구 사항 중 테이블 칼럼을 무중단으로 변경하는 것이 있었는데, 이에 관해 정리 드리겠습니다.^^

요구사항

서비스 요구사항과 개인적인 요구 사항은 다음과 같습니다.

  1. 최대한 서비스 중단 없이 가능해야 함
  2. 테이블에 Lock이 발생하지 말아야 함
  3. 빠르게 구현해야 함(개인 요구 사항)
  4. 재사용 가능해야 함(개인 요구 사항)
  5. 문제 발생 시 복구가 쉬워야 함

개인적으로 빠르게 적용하는 것과 이와 같은 이슈가 추후 재 발생 시 재사용할 수 있도록 모듈화하자는 것이 목표였습니다.

대상 테이블 분석

대상 테이블은 다음과 같은 특징이 있었습니다.

  1. Auto_increment 옵션이 적용되었으며, Primary Key가 존재함
  2. 데이터 변경에는 Insert와 Delete만 발생(Update 없음)
  3. 연관된 프로시저 및 트리거는 없음

무엇보다 Update가 없기 때문에 조금 더 생각을 심플하게 가져갈 수 있었습니다.

작업 시나리오

How to migrate data to different table

트리거로 기존 테이블에 Insert및 Delete 시 변경 분을 별도 테이블에 저장을 합니다. 그리고 임시 테이블을 만들고 해당 테이블에 기존 테이블 데이터를 이관합니다. 물론 임시 테이블에는 원하는 스키마로 변경된 상태겠죠. Import가 마무리되면 트리거에 쌓인 데이터로 변경 분을 적용하고 최종적으로 테이블을 Rename함으로써 프로세스가 마무리됩니다.

조금더 상세하게 풀자면 다음과 같습니다.

  1. 임시 테이블 생성
    • Insert/Delete 시 변경 사항을 저장할 테이블
      (TAB01_INSERT, TAB01_DELETE)
    • 데이터를 임시로 저장할 테이블
  2. 트리거 생성
    • 원본 테이블에 Delete 발생 시 해당 ROW를 임시 테이블에 저장
    • 원본 테이블에 Insert 발생 시 해당 ROW를 임시 테이블에 저장
  3. 원본 테이블 export/import
    • export -> 테이블명 변경 -> import
    • SED로 테이블 명을 임시 테이블 명으로 변경
  4. 원본 테이블과 임시 테이블 RENAME
    • 원본 테이블 : TAB01 -> TAB01_OLD
    • 임시 테이블 : TAB01_TMP -> TAB01
  5. 테이블 변경 분 저장
    • TAB01_INSERT에 저장된 데이터 Insert
    • TAB01_DELETE에 저장된 데이터 Delete
  6. 트리거, 임시테이블 제거
    • 트리거 : Insert트리거, Delete 트리거
    • 테이블 : TAB01_INSERT, TAB01_DELETE

모든 작업을 어느정도 자동화 구현하기 위해 “작업 스크립트”를 만드는 프로시저와 데이터 이관을 위한 Shell 스크립트를 작성하였습니다.

Shell 스크립트는 Export와 동시에 SED 명령을 통해 자동으로 테이블 이름을 변경하여 Import하도록 구현하였습니다.

DB명, 테이블명,  스키마 변경 내용을 다음과 같이 인자 값으로 넘겨서 프로시저를 호출합니다.
(프로시저 소스는 가장 하단 참조)

call print_rb_query('dbatest', 'TAB01', 'MODIFY ACT_DESC VARCHAR(100) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL;');

그러면 아래와 같이 결과가 나오는데 Step1, 2, 3을 순차적으로 실행하면 됩니다. 보기 쉽게 주석을 추가하겠습니다.^^

>> Step 1>  Prepare : SQL <<
## 임시 테이블 생성
DROP TABLE IF EXISTS dbatest.TAB01_INSERT;
CREATE TABLE dbatest.TAB01_INSERT LIKE TAB01;
DROP TABLE IF EXISTS dbatest.TAB01_DELETE;
CREATE TABLE dbatest.TAB01_DELETE LIKE TAB01;
DROP TABLE IF EXISTS dbatest.TAB01_TMP;
CREATE TABLE dbatest.TAB01_TMP LIKE TAB01;

## 임시 테이블 스키마 변경
ALTER TABLE dbatest.TAB01_TMP AUTO_INCREMENT = 10660382;
ALTER TABLE dbatest.TAB01_TMP MODIFY ACT_DESC VARCHAR(100) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL;
DELIMITER $$

## Delete 트리거 생성
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_DELETE$$
CREATE TRIGGER dbatest.TRG_TAB01_DELETE
AFTER DELETE ON dbatest.TAB01
FOR EACH ROW
BEGIN
INSERT INTO dbatest.TAB01_DELETE VALUES(
    OLD.ACT_ID,
    OLD.ACT_UID,
    OLD.ACT_USER_NAME,
    OLD.ACT_TIME,
    OLD.TO_UID,
    OLD.TO_USER_NAME,
    OLD.ACT_TYPE,
    OLD.POSTID,
    OLD.TAB01,
    OLD.ACT_DESC,
    OLD.BEFORE_USER_NAME,
    OLD.PHOTO_LINK,
    OLD.THUMB_URL,
    OLD.FROM_SERVICE);
END$$

## Insert 트리거 생성
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_INSERT$$
CREATE TRIGGER dbatest.TRG_TAB01_INSERT
AFTER INSERT ON dbatest.TAB01
FOR EACH ROW
BEGIN
INSERT INTO dbatest.TAB01_INSERT VALUES(
    NEW.ACT_ID,
    NEW.ACT_UID,
    NEW.ACT_USER_NAME,
    NEW.ACT_TIME,
    NEW.TO_UID,
    NEW.TO_USER_NAME,
    NEW.ACT_TYPE,
    NEW.POSTID,
    NEW.TAB01,
    NEW.ACT_DESC,
    NEW.BEFORE_USER_NAME,
    NEW.PHOTO_LINK,
    NEW.THUMB_URL,
    NEW.FROM_SERVICE);
END$$
DELIMITER ;

>> Step 2>  Data Copy : Shell Script <<
## 데이터 마이그레이션
mig_dif_tab.sh dbatest TAB01 TAB01_TMP

>> Step 3>  Final Job : SQL <<
## 변경분 적용
INSERT INTO dbatest.TAB01_TMP SELECT * FROM dbatest.TAB01_INSERT;
DELETE A FROM dbatest.TAB01_TMP A INNER JOIN dbatest.TAB01_DELETE B ON A.ACT_ID = B.ACT_ID;

## 테이블 Rename(Swap)
RENAME TABLE dbatest.TAB01 TO dbatest.TAB01_OLD, dbatest.TAB01_TMP TO dbatest.TAB01;

## 트리거 제거
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_DELETE;
DROP TRIGGER IF EXISTS dbatest.TRG_TAB01_INSERT;

##변경분 재 적용 (확인 사살)
INSERT INTO dbatest.TAB01 SELECT * FROM dbatest.TAB01_INSERT;
DELETE A FROM dbatest.TAB01 A INNER JOIN dbatest.TAB01_DELETE B ON A.ACT_ID = B.ACT_ID;

## 임시 테이블 제거
DROP TABLE IF EXISTS dbatest.TAB01_INSERT;
DROP TABLE IF EXISTS dbatest.TAB01_DELETE;

임시 테이블에는 Auto_increment 값을 기존보다 1% 높게 설정하여 테이블명 Swap 후 Primary Key 충돌이 없도록 하였습니다. 그리고 Insert ignore문을 사용하여 Primary Key 중복으로 발생하는 오류는 무시하도록 하였고, 테이블 Rename 후 변경분을 재 적용하는 확인사살도 하였습니다. ^^;;

Shell 스크립트

위에서 Step2에서 사용하는 Shell 스크립트는 다음과 같습니다.

#!/bin/sh
if [ $# -ne 3 ]; then
echo "Usage: ${0} <Database_Name> <Orignal_Table> <Target_Table>"
echo "<Example>"
echo "Database Name : snsdb"
echo "Orignal Table : tab01"
echo "Target Table  : tab01_tmp"
echo "==> ${0} snsdb tab01 tab01_tmp"
exit 1
fi
## Declare Connection Info
export DB_CONNECT_INFO="-u계정 -p패스워드"
export REMOTE_DB_HOST="DB호스트URL"
export REMOTE_DB_PORT="3306"
## Exec profile for mysql user
. ~/.bash_profile
## Dump and Insert Data
mysqldump ${DB_CONNECT_INFO}                           \
  --single-transaction                                 \
  --no-create-db                                       \
  --no-create-info                                     \
  --triggers=false                                     \
  --comments=false                                     \
  --add-locks=false                                    \
  --disable-keys=false                                 \
  --host=${REMOTE_DB_HOST}                             \
  --port=${REMOTE_DB_PORT}                             \
  --databases ${1}                                     \
  --tables ${2}                                        \
| sed -r 's/^INSERT INTO `'${2}'`/INSERT INTO `'${3}'`/gi' \
| mysql ${DB_CONNECT_INFO} -h ${REMOTE_DB_HOST} -P ${REMOTE_DB_PORT} ${1}

Export 값을 파이프(|)로 sed로 넘기고, sed에서는 테이블명만 정규식으로 바꿔서 최종적으로 Import하는 구문입니다.

Create Table As Select 혹은 Insert into Select 구문을 사용하지 않은 이유는 Redo 로그가 비대해짐에 따라 시스템 부하가 따르기 때문입니다. tx_isolation 값을 READ-COMMITTED로 설정하여도 데이터가 전체 들어간 시점에 일시적인 테이블 Lock이 발생합니다. (트랜잭션을 마무리하는 과정에서 발생하는 Lock입니다.) “MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법” 을 참고하세요.^^

프로시저

단일 테이블이라면 위에 있는 스크립트를 약간만 수정하면 되겠지만, 제 경우는 변경할 테이블이 꽤 많아서 스크립트를 생성하는 프로시저를 아래와 같이 구현하였습니다.

사실 모든 과정을 Perl 혹은 Java로 구현하면 Step없이 간단했겠지만, DB 접속 서버에 구성하기 위해서는 절차 상 복잡한 부분이 있어서 프로시저로 작성하였습니다. ㅎㅎ

DELIMITER $$
DROP PROCEDURE print_rb_query $$
CREATE PROCEDURE print_rb_query(IN P_DB VARCHAR(255), IN P_TAB VARCHAR(255), IN P_ALTER_STR VARCHAR(1024))
BEGIN
    ## Declare Variables
    SET @qry = '\n';
    SET @ai_num = 0;
    SET @ai_name = '';
    SET @col_list = '';

    ## Get AUTO_INCREMENT Number for Temporary Table
    SELECT CAST(AUTO_INCREMENT*1.01 AS UNSIGNED) INTO @ai_num
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = P_DB
    AND TABLE_NAME = P_TAB;

    ## Get auto_increment Column Name
    SELECT COLUMN_NAME INTO @ai_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = P_DB
    AND TABLE_NAME = P_TAB
    AND EXTRA = 'auto_increment';

    SET @qry = CONCAT(@qry, '>> Step 1>  Prepare : SQL <<\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_INSERT;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_INSERT LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_DELETE LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_TMP;\n');
    SET @qry = CONCAT(@qry, 'CREATE TABLE ',P_DB,'.',P_TAB,'_TMP LIKE ',P_TAB,';\n');
    SET @qry = CONCAT(@qry, 'ALTER TABLE ',P_DB,'.',P_TAB,'_TMP AUTO_INCREMENT = ',@ai_num,';\n');
    SET @qry = CONCAT(@qry, 'ALTER TABLE ',P_DB,'.',P_TAB,'_TMP ',P_ALTER_STR,'\n');

    ## Change Delimiter
    SET @qry = CONCAT(@qry, 'DELIMITER $$\n');

    ## Get Column List for Delete Trigger
    select GROUP_CONCAT('\n    OLD.',COLUMN_NAME) into @col_list
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = P_DB
    and table_name = P_TAB
    order by ORDINAL_POSITION;

    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_DELETE$$\n');
    SET @qry = CONCAT(@qry, 'CREATE TRIGGER ',P_DB,'.TRG_',P_TAB,'_DELETE\n');
    SET @qry = CONCAT(@qry, 'AFTER DELETE ON ',P_DB,'.',P_TAB,'\n');
    SET @qry = CONCAT(@qry, 'FOR EACH ROW\n');
    SET @qry = CONCAT(@qry, 'BEGIN\n');
    SET @qry = CONCAT(@qry, 'INSERT INTO ',P_DB,'.',P_TAB,'_DELETE VALUES(');
    SET @qry = CONCAT(@qry, @col_list);
    SET @qry = CONCAT(@qry, ');\n');
    SET @qry = CONCAT(@qry, 'END$$\n');

    ## Get Column List for Insert Trigger
    select GROUP_CONCAT('\n    NEW.',COLUMN_NAME) into @col_list
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = P_DB
    and table_name = P_TAB
    order by ORDINAL_POSITION;
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_INSERT$$\n');
    SET @qry = CONCAT(@qry, 'CREATE TRIGGER ',P_DB,'.TRG_',P_TAB,'_INSERT\n');
    SET @qry = CONCAT(@qry, 'AFTER INSERT ON ',P_DB,'.',P_TAB,'\n');
    SET @qry = CONCAT(@qry, 'FOR EACH ROW\n');
    SET @qry = CONCAT(@qry, 'BEGIN\n');
    SET @qry = CONCAT(@qry, 'INSERT INTO ',P_DB,'.',P_TAB,'_INSERT VALUES(');
    SET @qry = CONCAT(@qry, @col_list);
    SET @qry = CONCAT(@qry, ');\n');
    SET @qry = CONCAT(@qry, 'END$$\n');

    ## Change Delimiter
    SET @qry = CONCAT(@qry, 'DELIMITER ;\n');
    SET @qry = CONCAT(@qry, '\n\n');

    ## Insert Data
    SET @qry = CONCAT(@qry, '>> Step 2>  Data Copy : Shell Script <<\n');
    SET @qry = CONCAT(@qry, 'mig_dif_tab.sh ',P_DB,' ',P_TAB,' ',P_TAB,'_TMP\n');
    SET @qry = CONCAT(@qry, '\n\n');
    SET @qry = CONCAT(@qry, '>> Step 3>  Final Job : SQL <<\n');

    ## Insert Data into Temporary Table
    SET @qry = CONCAT(@qry, 'INSERT IGNORE INTO ',P_DB,'.',P_TAB,'_TMP SELECT * FROM ',P_DB,'.',P_TAB,'_INSERT;\n');

    ## Delete Data from Temporary Table
    SET @qry = CONCAT(@qry, 'DELETE A FROM ',P_DB,'.',P_TAB,'_TMP A INNER JOIN ',P_DB,'.',P_TAB,'_DELETE B ON A.',@ai_name,' = B.',@ai_name,';\n');

    ## Swap table names
    SET @qry = CONCAT(@qry, 'RENAME TABLE ',P_DB,'.',P_TAB,' TO ',P_DB,'.',P_TAB,'_OLD, ',P_DB,'.',P_TAB,'_TMP TO ',P_DB,'.',P_TAB,';\n');

    ## Drop Triggers
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, 'DROP TRIGGER IF EXISTS ',P_DB,'.TRG_',P_TAB,'_INSERT;\n');

    ## Insert Data
    SET @qry = CONCAT(@qry, 'INSERT IGNORE INTO ',P_DB,'.',P_TAB,' SELECT * FROM ',P_DB,'.',P_TAB,'_INSERT;\n');

    ## Delete Data
    SET @qry = CONCAT(@qry, 'DELETE A FROM ',P_DB,'.',P_TAB,' A INNER JOIN ',P_DB,'.',P_TAB,'_DELETE B ON A.',@ai_name,' = B.',@ai_name,';\n');

    ## Drop Temporary Tables
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_INSERT;\n');
    SET @qry = CONCAT(@qry, 'DROP TABLE IF EXISTS ',P_DB,'.',P_TAB,'_DELETE;\n');
    SET @qry = CONCAT(@qry, '\n\n');
    SELECT @qry;
END$$
DELIMITER ;

Conclusion

결과적으로 서비스 영향없이 무중단으로 테이블 스키마를 변경하였습니다. 만약 Update가 있는 경우라면 “INSERT INTO .. ON DUPLICATE KEY UPDATE..” 문을 사용하면 해결 가능하다는 생각이 드네요.^^ 물론 Update 트리거에도 Insert와 동일한 액션이 취해져야 하겠죠.

서비스 중단없이 DB 스키마를 변경했다는 점에서 큰 의의가 있었네요.^^

MySQL 트랜잭션 Isolation Level로 인한 장애 사전 예방 법

Overview

MySQL에서 전체 데이터를 Scan 하는 쿼리를 질의하여 서비스에 큰 영향이 발생할 수 있습니다.

InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ이기 때문에 발생하는 현상인데, 이것은 세션 변수 일부를 변경하여 문제를 사전에 해결할 수 있습니다.

얼마 전 이와 비슷한 장애가 발생하여 원인 분석 및 해결 방안을 포스팅합니다.

Symptoms

Transaction Isolation Level이 REPEATABLE-READ(MySQL Default) 상태에서 Insert into Select 혹은 Create Table As Select 로 전체 테이블 참조 쿼리 실행 시 참조 테이블에 데이터 변경 작업이 “대기” 상태에 빠지는 현상이 있습니다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

Insert Into Select

세션1

mysql> insert into activity_test_stat2
    -> select
    ->     act_type,
    ->     to_uid,
    ->     act_time,
    ->     to_user_name,
    ->     before_user_name,
    ->     count(*) cnt
    -> from activity_test
    -> group by act_type, to_uid, act_time,
    ->     to_user_name, before_user_name;

세션2 – 테이블에 데이터 변경

mysql> update activity_test set ACT_TYPE = 105 limit 10;

세션3 - update SQL는 “Updating” 상태

mysql> show processlist\G
************************* 1. row *************************
     Id: 255867
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 1
  State: Updating
   Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
     Id: 255962
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 2
  State: Copying to tmp table
   Info: insert into activity_test_stat2 select act_type,

Delete 작업 시 Update와 같이 대기 현상 또는 Dead Lock 오류 발생합니다.

mysql> delete from activity_test limit 10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Create Table As Select

세션1

mysql> create table activity_test_stat as
    -> select
    ->     act_type,
    ->     to_uid,
    ->     act_time,
    ->     to_user_name,
    ->     before_user_name,
    ->     count(*) cnt
    -> from activity_test
    -> group by act_type, to_uid, act_time,
    ->     to_user_name, before_user_name;

세션2 – 테이블에 데이터 변경

mysql> update activity_test set ACT_TYPE = 105 limit 10;

세션3 - update SQL는 “Updating” 상태

mysql> show processlist\G
************************* 1. row *************************
     Id: 255867
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 2
  State: Updating
   Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
     Id: 255962
   User: root
   Host: localhost
     db: snsfeed
Command: Query
   Time: 4
  State: Copying to tmp table
   Info: create table activity_test_stat as select act_type,

Delete 작업 시 Update와 같이 대기 현상 또는 Dead Lock 오류 발생합니다.

mysql> delete from activity_test limit 10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Cause

MySQL InnoDB 스토리지 엔진의 기본 Isolation Level이 REPEATABLE-READ로 설정되어 있기 때문에 발생합니다.

REPEATABLE-READ에서는 현재 Select 버전을 보장하기 위해 Snapshot을 이용하는데, 이 경우 해당 데이터에 관해서 암묵적으로 Lock과 비슷한 효과가 나타납니다.

즉, Select 작업이 종료될 때까지 해당 데이터 변경 작업이 불가합니다.

Transaction Isolation Level

  • READ UNCOMMITTED
    다른 트랜잭션이 Commit 전 상태를 볼 수 있음
    Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)
  • READ-COMMITTED
    Commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 특정 타 트랜잭션이 Commit을 수행하면 해당 데이터를 Read할 수 있음
    Binary Log가 자동으로 Row Based로 기록됨 (Statement설정 불가, Mixed 설정 시 자동 변환)
  • REPEATABLE READ
    MySQL InnoDB 스토리지 엔진의 Default Isolation Level
    Select 시 현재 데이터 버전의 Snapshot을 만들고, 그 Snapshot으로부터 데이터를 조회
    동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함
  • SERIALIZABLE
    가장 높은 Isolation Level로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸림
    다른 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가

Isolation Level에 관한 자세한 정보는 하단 MySQL 매뉴얼을 참조하세요.
http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

Solution

Insert into Select 경우 Isolation Level을 READ-COMMITED나 READ-UNCOMMITED로 변경하여 해결할 수 있습니다.
다음과 같이 세션 설정을 변경 후 Create Table As Select, Insert into Select를 수행하면 문제가 없습니다.

mysql> set tx_isolation = 'READ-COMMITTED';

설정 파일에 영구적으로 transaction isolation 변경 적용하고자 한다면 다음과 같이 설정 후 DB를 재시작 합니다.

$ vi /etc/my.cnf
## [mysqld] 설정에 추가
transaction-isolation           = READ-COMMITTED

비슷한 설정으로 다음과 같이 Isolation을 변경할 수 있습니다.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

모든 것은 Isolation Level이 REPEATABLE READ 이상인 경우 발생하오니, 주의하여 사용하시기 바랍니다.

(설정 자체가 READ COMMITTED에서는 발생하지 않음)

※ 주의사항 ※
커넥션 풀을 사용하는 경우 변경된 세션 값은 해당 커넥션이 재 시작되기 전까지 유지되므로, 반드시 사용 후 원래 설정 값으로 돌려놓아야 합니다.

Conclusion

조금은 어려운 주제일 수 있습니다. 그러나 유독 MySQL에만 국한되는 내용이 아닌, 트랜잭션을 지원하는 DB 사용 시 반드시 알아야할 사항이라고 생각됩니다.

그러나 MySQL 에만 국한되는 내용이 아닐 뿐더러, 대용량 분석 시스템을 구상 중이라면 반드시 알아야할 사항이라고 생각합니다.^^