MySQL에서 무중단으로 스키마 변경하기(Alter) pt-online-schema-change

중단없이 온라인상에서 MySQL의 테이블 스키마 변경 작업에 대해서 알아본다.
무중단으로 스키마를 변경할 수 있는 방법으로는 현재, openark kit의 "oak-online-alter-table", Percona의 "pt-online-schema-change"와 Facebook의 "OSC(Online Schema Change for MySQL)"를 가장 많이 사용할 것 같다.

oak-online-alter-table과 pt-online-schema-change 차이는 oak-online-alter-table에는 지원안하는 포린키와 슬래이브 상태 고려 등을 빼고는 비슷하다. 그래서 좀 더 기능이 보완된 pt-online-schema-change 도구를 가지고 하는 방법을 설명해 본다. Facebook에서 사용하는 툴은 아직 분석하지 못했다. ^^

pt-online-schema-change는 Percona Toolkit의 여러 기능 중에 하나이고, Percona Toolkit은 Maatkit과 Aspersa를 기반으로해서 탄생되었다.
참고로 MySQL 5.6에서는 ALTER TABLE ... ADD INDEX / DROP INDEX 중 이어도 참조·갱신이 함께 실행이 될 수 있다고 하니 참고하면 될 듯 하다.
그리고 TokuDB를 사용할 경우에도 온라인상에서 스키마변경이 자유롭다.

Alter Table의 이슈

Alter Table 명령을 날리면 보통 아래와 같은 순서대로 진행된다.(innodb의 경우)

1. 해당 테이블을 TL_WRITE_ALLOW_READ로 잠근다. 즉, READ는 허용하고 WRITE를 차단한다는 의미이다.
2. 새로운 테이블의 빈 임시 테이블을 만들고.
3. 이전 테이블에서 새 테이블에 데이터를 복사하며.
4. 새 테이블을 이전 테이블과 같은 이름으로 바꾸고 이전 테이블은 삭제한다.
5. 새로운 테이블에 차단했던 WRITE를 푼다.

이렇듯 Alter를 진행하게 되면 해당 테이블의 쓰기 잠금이 발생하여 일반적으로 온라인상에서는 테이블의 스키마 변경은 할 수 없는 구조이다. 스키마 변경을 할 시에는 데이터를 복사하게 되므로 해당 테이블의 사이즈만큼 스토리지 공간이 필요하게 된다.
Alter는 테이블의 행 수가 증가에 따라 선형적으로 시간이 길어지는 것은 아니다. 실제는 더 늦어질 수 있다는 의미이다. 왜냐하면 인덱스 재구성은 O (N log N)의 시간이 걸리기 때문이다. 그래서 테이블의 건수를 기준으로 절대적인 시간을 예측하기 보다는 실제 건수를 만들어서 테스트를 해보기를 권장한다. 거기에 Alter는 한번의 명령에 몰아서 처리해야 테이블 복사를 한번만 할 수 있다. 예를 들면 아래와 같다.

mysql> ALTER TABLE t1 DROP INDEX idx01, DROP INDEX idx02... ADD INDEX (idxN);

pt-online-schema-change의 방식은?

이 방식은 기존 MySQL의 Alter 방식의 프로세스를 수작업으로 자동화(스크립트화)시켰다고 보면 된다. Write 잠금 기능을 제거하고 그 기능을 trigger로 풀어서 원본 테이블의 R/W를 가능하게 했고 수행 순서를 보면 알겠지만, trigger를 통해 데이터 동기화를 이루었다. pt-online-schema-change의 방식의 기본적인 프로세스는 아래와 같다.

1. 새로운 임시 테이블 생성
기존 테이블에 앞에는 prefix 인자를 넣으면 prefix가 붙고 _에 원본 테이블명, 그리고 뒤에는 _new를 붙여 네이밍한 다음 임시 테이블을 만든다.
create_new_table()함수를 보면,

my $prefix = $args{prefix} || '_';
my $suffix = '_new';
my $table_name = $orig_tbl->{tbl}.$suffix;
$table_name = $prefix.$table_name;

2. 새로운 임시테이블 Alter
alter 인자로 주어진 ddl구문을 실행해서 임시 테이블에 칼럼 및 인덱스 등을 생성한다.

my $sql = "ALTER TABLE $new_tbl->{name} $alter";
print $sql, "\n" if $o->get('print');
PTDEBUG && _d($sql);
eval {
  $cxn->dbh()->do($sql);
};

3. 트리거 생성
원본 테이블의 변경사항을 임시 테이블에 반영해서 동기화하는 부분에 해당된다. 원래 테이블에 AFTER INSERT, AFTER UPDATE, AFTER DELETE 등 세 트리거를 만든다.
실행부분은 아래와 같고.

my @trigger_names = eval {
    create_triggers(
    orig_tbl => $orig_tbl,
    new_tbl => $new_tbl,
    columns => \@common_cols,
    Cxn => $cxn,
    Quoter => $q,
    OptionParser => $o,
  );
};

실제로 트리거문 구성을 살펴보면 아래와 같다. 참고로 MySQL의 트리거는 각각의 이벤트에 하나 밖에 만들 수 없기 때문에 이미 트리거 존재하는 테이블에 pt-online-schema-change을 사용할 수 없다.

my $insert_trigger
= "CREATE TRIGGER `${prefix}_ins` AFTER INSERT ON $orig_tbl->{name} "
. "FOR EACH ROW "
. "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";

my $update_trigger
= "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "
. "FOR EACH ROW "
. "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";

my $delete_trigger
= "CREATE TRIGGER `${prefix}_del` AFTER DELETE ON $orig_tbl->{name} "
. "FOR EACH ROW "
. "DELETE IGNORE FROM $new_tbl->{name} "
. "WHERE $del_index_cols";

INSERT 이벤트에 대해서는 REPLACE문, UPDATE 이벤트는 REPLACE 문, DELETE 이벤트에 대해 DELETE 문을 발생시킨다. 그리고 DELETE 문은 임시 테이블에 레코드가 없을 경우 무시해 버리게 해 놨다.

4. 데이터 복사
INSERT LOW_PRIORITY IGNORE INTO ... 구문을 가지고 nibble_iter를 통해 원본 테이블의 데이터를 임시 테이블로 복사를 시작한다.
이렇게하면 응용 프로그램 측의 처리가 잠금 대기 시간에서 실패하는 것을 막으려하고 있다. 또한 시스템이 과부하되는 것을 방지하기 위해 1,000 레코드 처리할 때마다 대기 시간을 가질 수 있게 되어 있다.

5. 테이블 리네임(swap)
원본 테이블과 임시테이블을 교체한다. 기존 테이블은 _기존테이블_old로 바꾸고 신규 임시 테이블은 기존 테이블로 리네임한다.

eval {
  $old_tbl = swap_tables(
    orig_tbl => $orig_tbl,
    new_tbl => $new_tbl,
    suffix => '_old',
    Cxn => $cxn,
    Quoter => $q,
    OptionParser => $o,
  );
};
RENAME TABLE.. TO .. TO ...

6. foreign key 업데이트
존재할 경우 처리.

7. OLD 테이블 DROP

온라인상에서 스키마 변경 테스트

간단하게나마 pt-online-schema-change 살펴보았다. 다음으로는 실제 이 스크립트가 제대로 동작하는 지 살펴볼 차례다. 다음은 pt-online-schema-change을 사용하기 위한 방법을 기술한다.

#perl 모듈 설치(설치 안되었을 경우)
cpan> install DBI
cpan> install DBD::mysql
cpan> install Term::ReadKey
# 해당 스크립트 단운로드
> wget http://www.percona.com/redir/
 downloads/percona-toolkit/LATEST/percona-toolkit-2.1.7.tar.gz

# 테스트 테이블
CREATE TABLE t1 (
username char(25) not null primary key,
pass char(25),
uid integer not null,
gid integer not null
) Engine=InnoDB DEFAULT CHARSET=utf8";

아래는 super-smack을 통해 update 트랜젝션을 일으키면서 실제 온라인상에서 테이블 스키마 변경을 테스트해 본다.

> /database/server/super-smack/bin/super-smack \
./update-select.smack 10 100 >> /samples/data/log5.txt

> /percona-toolkit-2.1.7/bin> ./pt-online-schema-change \
--execute --ask-pass --user=root --chunk-time=1 \
--nodrop-old-table --alter-foreign-keys-method auto \
--alter "add column t_date char(15) default null" D=test,t=t1,h=localhost

Enter MySQL password: 
No foreign keys reference `test`.`t1`; ignoring --alter-foreign-keys-method.
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 90000 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`t1`.

간단하게 옵션에 대한 설명을 곁들인다. 

• alter : 실행 alter 문.
• h : 호스트 정보.
• D : 데이터베이스 명.
• t : 테이블 정보.
• ask-pass : 암호를 표준 입력 가능함.
• alter-foreign-keys-method : 변경 대상 테이블에 외래 키 설정이 되어있는 경우 처리 방법. auto, rebuild_constraints, drop_swap, none을 지정할 수 있음. 외부 참조의 데이터 건수가 많으면 drop_swap 됨.
• nocheck-replication-filters : replication의 filter 설정.
• execute : 이를 지정하지 않으면 실행되지 않음. 또한, dry-run에서 테스트 할 수 있음.

[참고 사이트]

• oak-online-alter-table
• pt-online-schema-change
• Online Schema Change for MySQL