MySQL Online DDL 진행 상태와 Row log buffer 사용량 확인

MySQL 5.6과 MariaDB 10.0의 Online DDL 기능은 많은 사용자들이 기다려왔던 기능인데, 막상 기능은 추가되었지만 Online DDL이 어느정도 진행되었는지를 알려주지 않는다는 새로운 문제점이 있다.
Online DDL은 ALTER가 진행되는 동안 사용자로부터 유입되는 데이터 변경을 임시 메모리 공간(Row log buffer)에 담아두어야 하는데, Online DDL이 어느 정도 진행되었는지를 알 수 없으므로 Row log buffer가 부족해서 ALTER가 실패할지 사용자가 예측을 할 수 없다.
그래서 결국 사용자는 Row log buffer가 부족해질 때까지 기다려보는 방법밖에 없다. 만약 Online DDL이 완료되기까지 2시간을 기다려서 "Online DDL 실패"라는 메시지만 확인해야 할 수도 있다는 것이다.
Online DDL의 진행상황뿐만 아니라 Row log buffer가 얼마나 사용되었는지, 그리고 Row log buffer에 얼마나 많은 변경 레코드가 저장되어 있는지도 알려주지 않는다. 단순히 초당 유입되는 쿼리 수는 MySQL 서버 전체적인 것이므로, 지금 ALTER 테이블에만 유입되는 DML을 판단하기는 쉽지 않다.

그래서 Online DDL의 진행 상황이나 Row log buffer의 사용량은 Online DDL이 성공할지 실패할 지를 예측할 수 있도록 해주는 중요한 자료라고 생각된다.
진행상황이나 사용량을 사용자에게 알려주는 가장 좋은 방법은 Online DDL을 실행한 터미널의 화면에 표시해주는 것(MariaDB의 ALTER TABLE 명령과 같이)이지만, Online DDL은 그렇게 구현하기가 쉽자 않다. Online DDL은 기존 Copy 스타리의 ALTER TABLE과는 달리 스토리지 엔진(InnoDB)에서 처리되기 때문이다. 그래서 MySQL 서버에 3개의 새로운 상태 변수를 추가하는 방법을 선택하게 되었다. 이때 상태 변수는 전역(Global) 상태 변수를 사용하게 되는데, 세션 상태 변수인 경우 Online DDL을 실행한 세션외에는 확인할 수 없기 때문에 도움이 되지 않기 때문이다.
또한 이 상태 변수는 전역이기 때문에, 한번에 2개 이상의 컨넥션에서 Online DDL을 실행하는 경우에는 서로 진행 상태와 사용량이 섞여 버리기 때문에 주의해야 한다.

• Innodb_onlineddl_rowlog_rows
  Row log buffer에 얼마나 많은 변경 레코드들이 저장되어 있는지를 보여준다.
• Innodb_onlineddl_rowlog_pct_used
  Row log buffer의 현재 사용량을 보여준다. 이 수치는 0부터 10000까지 값을 가지며, 실제 버퍼 사용량에 * 100을 한 값이다. 즉 9529란 값은 95.29% 만큼 사용되었음을 의미한다.
• Innodb_onlineddl_pct_progress
  Online DDL이 얼마나 진행되었는지를 보여준다. 실제 Online DDL 진행 상황은 디스크의 성능과 버퍼 풀의 워밍 업 상태에 따라서 많은 차이가 나기 때문에, 이 진행 상태는 정확하지 않을 가능성도 있다. 하지만 여전히 이 값을 이용해서 전체적인 진행 상황을 예측할 수는 있으며, 아무런 지표가 없는 것보다는 훨씬 도움이 될 것이다.

Innodb_onlineddl_pct_progress는 예측 값이지만, Innodb_onlineddl_rowlog_rows와 Innodb_onlineddl_rowlog_pct_used는 정확한 값이므로 Online DDL을 계속 진행할지 중간에 멈출지 판단하는데 더 많은 도움이 될 것으로 생각된다.
Innodb_onlineddl_pct_progress 상태 변수가 레포팅하는 진행 상황(%)는 정확하지 않을 수도 있는데, 이는 Inplace Alter(Online DDL)이 InnoDB 버퍼 풀의 워밍 업 정도와 디스크의 성능에 매우 의존적이기 때문이다. Innodb_onlineddl_pct_progress 상태 변수는 이런 모든 변수를 고려하진 않았으며, 단순히 해당 테이블의 각 인덱스의 페이지 개수와 경험을 기반으로 진행 상황을 보고한다.

Online DDL은 크게 다음 2가지 작업(주로 시간이 소모되는)으로 생각해볼 수 있다.

1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
2. 읽은 레코드를 이용해서 프라이머리 키와 세컨드리 인덱스 빌드

Online DDL이 실행되면 1번 작업은 단 한번만 실행되지만, 2번 작업은 해당 테이블에 정의된 인덱스의 개수만큼 수행되어야 한다.
여기에서 단순히 이 두 가지 메인 작업에 대해서 비중(Weight)을 1.0으로 할당(2번 작업의 경우 각 인덱스별로 1.0 할당)했다. 그리고 2번 작업의 경우 고정(Fixed weight)된 비중과 동적 비중(Dynamic weight)을 0.5씩 할당했다.
모든 세컨드리 인덱스는 기본적으로 최소 정적 비중인 0.5 만큼의 비중을 가지고 되고, 인덱스의 크기(페이지 수)에 따라서 동적 비중을 추가로 가지게 된다. 그래서 두 번째 작업은 아래와 같이 2개의 서브 작업으로 구분될 수 있다.

2-1. 버퍼의 내용을 정렬하고 병합
2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

각 서브 작업은 디스크의 성능과 InnoDB 버퍼 풀의 워밍 업 상태에 매우 의존적이어서, 경험적으로 첫번째 서브 작업에 40%를 할당하고 2번째 서브 작업에 60%의 시간 소모(비중)을 할당했다.
Online DDL 작업은 각 작업과 서브 작업이 독립적으로 처리되기 때문에, Online DDL의 전체 진행률을 계산하기 위해서는 이렇게 비중을 할당해야 한다.

예를 들어서, 아래와 같이 인덱스를 가지고 있는 테이블에 컬럼을 하나 추가하는 Online DDL을 생각해보자.
(그리고 프라이머리 키는 100개의 페이지로 구성되어 있고, ix_fd2라는 세컨드리 인덱스는 50개의 페이지로 구성되어 있다고 가정)

CREATE TABLE tb_test(
  fdpk int,
  fd1  varchar(10),
  fd2  bigint,
  primary key (fdpk),
  index ix_fd2(fd2)
) ENGINE=InnoDB;

ALTER TABLE tb_test ADD fd3 DATETIME, LOCK=NONE, ALGORITHM=INPLACE;

위에서 설명했던 비중에 따라서, 전체 Online DDL 작업의 비중은 3.0이 될 것(1번 작업 = 1.0, 2번 작업 = 2.0)이며, 그중에서 전체 동적 비중은 1.0(각 인덱스마다 0.5)이 될 것이다.
그래서 프라이머리 키는 1.1667이라는 비중[ = 0.5(fixed weight) and 0.6667(dynamic weight, 1.0 * 100/(100+50)) ]을 가지고 되고, 세컨드리 인덱스는 0.8333[ = 0.5(fixed weight) and 0.3333(dynamic weight, 1.0 * 50/(100+50)) ] 비중을 가지게 될 것이다.
그리고 각 세부 작업별로 비중은 아래와 같이 할당될 것이다.

[weight:1.0000] 1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
[weight:1.1667] 2. 프라이머리 키 리빌드
  [weight:40% of 1.1667] 2-1. 버퍼의 내용을 정렬하고 병합
  [weight:60% of 1.1667] 2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장
[weight:0.8333] 3. 세컨드리 인덱스(ix_fd2) 리빌드
  [weight:40% of 0.8333] 3-1. 버퍼의 내용을 정렬하고 병합
  [weight:60% of 0.8333] 3-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

최종적으로 각 작업과 서브 작업에 할당된 시간 비중은 아래와 같이 계산할 수 있다.

[Time:33.33%] 1. 스키마 변경 대상 테이블의 프라이머리 키(클러스터링 키)를 스캔
[Time:38.89%] 2. 프라이머리 키 리빌드
  [Time:15.56%] 2-1. 버퍼의 내용을 정렬하고 병합
  [Time:23.33%] 2-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장
[Time:27.78%] 3. 세컨드리 인덱스(ix_fd2) 리빌드
  [Time:11.11%] 3-1. 버퍼의 내용을 정렬하고 병합
  [Time:16.67%] 3-2. 정렬된 버퍼의 내용을 실제 인덱스 트리에 저장

그래서 만약 Online DDL이 현재 2-2 서브 작업까지 끝냈다면, 현재 Online DDL의 진행상황은 72.22%가 되는 것이다.

그리고 상태 변수로 추가된 진행 상황이 정확하지 않은 경우를 대비해서, 아래와 같이 각 작업과 서브 작업이 시작되고 완료되는 시점에 MySQL 서버의 에러 로그에 그 내용을 기록하도록 했다.

140921 13:58:44 [Warning] Online DDL : Start

140921 13:58:44 [Warning] Online DDL : Start reading clustered index of the table and create temporary files
140921 14:01:08 [Warning] Online DDL : End of reading clustered index of the table and create temporary files

140921 14:01:08 [Warning] Online DDL : Start merge-sorting index PRIMARY (1 / 2), estimated cost : 15.5547%
140921 14:03:24 [Warning] Online DDL : End of merge-sorting index PRIMARY (1/ 2)
140921 14:03:24 [Warning] Online DDL : Start building index PRIMARY (1 / 2), estimated cost : 23.3321%
140921 14:07:21 [Warning] Online DDL : End of building index PRIMARY (1 / 2)
140921 14:07:21 [Warning] Online DDL : Completed

140921 14:07:21 [Warning] Online DDL : Start merge-sorting index ix1 (2 / 2), estimated cost : 11.1119%
140921 14:09:44 [Warning] Online DDL : End of merge-sorting index ix1 (2 / 2)
140921 14:09:44 [Warning] Online DDL : Start building index ix1 (2 / 2), estimated cost : 16.6679%
140921 14:13:12 [Warning] Online DDL : End of building index ix1 (2 / 2)
140921 14:13:12 [Warning] Online DDL : Completed

Online DDL의 진행 상황 레포팅 기능을 확인하기 위해서 간단한 테스트를 해보았다.

-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 13GB
-- // index size : 7GB
-- // ---------------------------
CREATE TABLE tb_onlineddl1 (
  pk1 int(11) NOT NULL,
  pk2 bigint(20) NOT NULL,
  fd1 bigint(20) DEFAULT NULL,
  fd2 bigint(20) DEFAULT NULL,
  fd3 datetime DEFAULT NULL,
  fd4 text,
  fd5 varchar(50) DEFAULT NULL,
  fd6 bigint(20) DEFAULT NULL,
  fd7 bigint(20) DEFAULT NULL,
  PRIMARY KEY (pk1, pk2),
  UNIQUE KEY ux1 (pk2, pk1),
  KEY ix1 (fd6, fd7)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- // ---------------------------
-- // total rows : 141,577,818
-- // data size : 4GB
-- // index size : 2.5GB
-- // ---------------------------
CREATE TABLE tb_onlineddl2 (
  pk1 int(11) NOT NULL,
  pk2 bigint(20) NOT NULL,
  PRIMARY KEY (pk1, pk2),
  KEY ix1 (pk2, pk1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE tb_onlinedd1 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;

ALTER TABLE tb_onlinedd2 ADD x VARCHAR(5), LOCK=NONE, ALGORITHM=INPLACE;

아래의 그래프는 "Innodb_onlineddl_pct_progress" 상태 변수가 레포팅하는 Online DDL의 진행 상태를 1초 단위로 수집해서 그래프를 그려본 것이다.

그래프의 첫 부분에서는 조금 각도가 부자연스럽기는 하지만, 나머지 부분은 상당히 일관되기 진행 상태가 올라가는 것을 확인할 수 있다. ^^;


관련 소스 코드는 Kakao Github에 확인할 수 있으며, 또한 MariaDB 10.1(MDEV-6812)에서는 기본적으로 사용할 수 있을 것으로 보인다.

MySQL 5.6과 MariaDB 10.0의 Online DDL

MySQL Online DDL

MySQL 5.6과 MariaDB 10.0의 Online DDL(Online schema change)은 많은 사용자들이 오랜시간동안 기다려왔던 기능이다.
Online DDL은 아래와 같이 크게 4단계 작업(간단히 MySQL 코드상의 함수 이름으로 표시함)으로 처리된다.

1) InnoDB::ha_prepare_inplace_alter_table(..)
2) InnoDB::ha_inplace_alter_table(..)
3) InnoDB::ha_commit_inplace_alter_table(..)
4) mysql_rename_table(..)

MySQL Online DDL은 테이블 변경 작업이 진행되는 동안 사용자로부터 유입되는 데이터 변경을 별도의 메모리 공간에 저장해두는데, 이때 사용되는 메모리 공간을 Row log buffer라고 한다.
Online DDL의 1번 단계에서는 Row log buffer 공간을 할당하고, 변경될 구조의 임시 테이블을 생성하기 위해서 원본 테이블에 잠깐동안 잠금을 걸게 된다.
Row log buffer는 sort_buffer_size 시스템 변수에 정의된 크기만큼씩 할당하며, 최대 innodb_online_alter_log_max_size 시스템 변수에 정의된 크기만큼 증가하게 된다.
2번 단계에서는 백그라운드로 기존 테이블의 레코드를 모두 읽어서 (스키마 변경 사항이 적용된) 새로운 테이블을 리빌드하게 된다. 새로운 테이블의 리빌드가 완료되면 row log buffer에 수집된 그동안의 데이터 변경 사항을 모아서 한번에 새로운 테이블에 적용하게 된다.
이때(Row log buffer에 수집된 변경 데이터를 적용하는 동안)에는 해당 테이블의 데이터 변경이 허용되지 않는다. 그래서 innodb_online_alter_log_max_size 시스템 변수를 너무 크게 설정하는 것은 좋지 않다. 참고로 innodb_online_alter_log_max_size의 기본값은 128MB이다.
3번과 4번 단계에서는 변경 사항을 COMMIT하고 기존 테이블과 신규 테이블의 이름을 바꾸고, 기존 테이블은 삭제하게 된다.

Online DDL의 4단계 작업 절차에서 에러가 발생하는 케이스는 여러가지가 있는데, 그중에서 가장 예측 불가능한 부분은 아마도 Row log buffer 의 메모리 공간이 부족해서 Online DDL이 실패하는 경우일 것이다.
만약 Online DDL이 실행되는 동안 사용자로부터의 DML이 너무 많아서 Row log buffer가 꽉 차게되면, MySQL 서버는 에러를 발생시키고 지금까지 했던 테이블 스키마 변경 작업을 ROLLBACK하게 된다.
문제는 이것이 Row log buffer가 부족할 것으로 판단되면 멈추는 것이 아니라, Row log buffer가 부족한 시점에 멈추기 때문에 1~2시간동안 열심히 작업하다가 갑자기 실패할 수 있음을 의미한다. 만약 여러분이 사용량이 없는 새벽 시간에 이 작업을 했다면, 이런 문제는 더 괴로운 상황이 될 것이다.
하지만 현재 MySQL 서버에는 Row log buffer가 현재 얼마나 사용되었는지 그리고 Online DDL은 현재 얼마나 진행되었는지 얼마나 더 실행되어야 하는지에 대한 정보를 하나도 보여주지 않는다. DBA의 새벽 작업이 한순간에 물거품이 되어 버릴 수 있는 것이다.

DML 문장은 Row log buffer를 얼마나 사용하는가?

그래서 이런 문제를 피해가기 위해서는 우선 INSERT나 UPDATE 그리고 DELETE 문장들이 얼마나 Row log buffer 사용량에 영향을 미치는지를 알아야 할 것이다.
간단히 Row log buffer의 사용량을 예측할 수 있는지 예제로 살펴보도록 하자.

아래와 같이 단순한 테스트 케이스에 대해서 Row log buffer의 크기가 얼마나 증가하는지 살펴보자.
------------------------------------------------------------
Session1 : ALTER TABLE tb_onlineddl ..
Session2 : INSERT INTO tb_onlineddl ..
Session2 : UPDATE tb_onlineddl SET ..
Session2 : DELETE FROM tb_onlineddl WHERE ..
------------------------------------------------------------

CASE-1 : 컬럼 추가시

CREATE TABLE `tb_onlineddl` (
  `fdpk` int(11) NOT NULL AUTO_INCREMENT,
  `fd1` char(100) NOT NULL,
  `fd2` char(100) NOT NULL,
  `fd3` char(100) NOT NULL,
  `fd4` char(100) NOT NULL,
  `fd5` char(100) NOT NULL,
  PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=latin1

MariaDB [test]> alter table tb_onlineddl add fd6 char(100), lock=none, algorithm=inplace;

MariaDB [test]> insert into tb_onlineddl values (null, 'insert', 'insert', 'insert', 'insert', 'insert');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> update tb_onlineddl set fd1='update' where fdpk=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [test]> delete from tb_onlineddl where fdpk=4;
Query OK, 1 row affected (0.00 sec)

(아래 내용은 MariaDB 10.0에서 메모리 사용량을 출력하도록 수정한 것임)
>> DEBUG for INSERT :: online ddl row log buffer usage : Current: 519 , Total: 519,  Max: 134217728
>> DEBUG for UPDATE :: online ddl row log buffer usage : Current: 519 , Total: 1038, Max: 134217728
>> DEBUG for DELETE :: online ddl row log buffer usage : Current: 22 ,  Total: 1060, Max: 134217728
(in byte)

이 테스트 케이스에서 DELETE인 경우에는 Row log buffer에 단순히 PK만 저장되어서 아주 적은 메모리 공간을 사용한다는 것과 INSERT나 UPDATE는 레코드 전체를 Row log buffer에 저장한다는 것을 알 수 있다.

CASE-2 : 컬럼 삭제시

CREATE TABLE `tb_onlineddl` (
  `fdpk` int(11) NOT NULL AUTO_INCREMENT,
  `fd1` char(100) NOT NULL,
  `fd2` char(100) NOT NULL,
  `fd3` char(100) NOT NULL,
  `fd4` char(100) NOT NULL,
  `fd5` char(100) NOT NULL,
  `fd6` char(100) NOT NULL,
  PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=latin1

Connection1 [test]> alter table tb_onlineddl drop fd6, lock=none, algorithm=inplace;

Connection2 [test]> insert into tb_onlineddl values (null, 'insert', 'insert', 'insert', 'insert', 'insert', 'insert');
Query OK, 1 row affected (0.01 sec)

Connection2 [test]> update tb_onlineddl set fd1='update' where fdpk=11;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Connection2 [test]> delete from tb_onlineddl where fdpk=2;
Query OK, 1 row affected (0.00 sec)

>> DEBUG for INSERT :: online ddl row log buffer usage : Current: 620 , Total: 620,  Max: 134217728
>> DEBUG for UPDATE :: online ddl row log buffer usage : Current: 520 , Total: 1140, Max: 134217728
>> DEBUG for DELETE :: online ddl row log buffer usage : Current: 21 ,  Total: 1161, Max: 134217728

이 경우에도 첫 번째 테스트 케이스와 비교했을 때, INSERT와 UPDATE가 사용하는 Row log buffer가 조금 다르다는 것 이외에는 큰 차이는 없다는 것을 알 수 있다. 

CASE-3 : 컬럼 삭제시 (TEXT/BLOB 타입의 컬럼이 있는 경우) 

CREATE TABLE `tb_onlineddl` (
  `fdpk` int(11) NOT NULL AUTO_INCREMENT,
  `fd1` char(100) NOT NULL,
  `fd2` char(100) NOT NULL,
  `fd3` char(100) NOT NULL,
  `fd4` char(100) NOT NULL,
  `fd5` char(100) NOT NULL,
  `fd6` char(100) NOT NULL,
  PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=latin1

Connection1 [test]> alter table tb_onlineddl drop fd6, lock=none, algorithm=inplace;

Connection2 [test]> insert into tb_onlineddl values (null, 'insert', 'insert', 'insert', 'insert', 'insert', 'insert');
Query OK, 1 row affected (0.01 sec)

Connection2 [test]> update tb_onlineddl set fd1='update' where fdpk=10;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Connection2 [test]> delete from tb_onlineddl where fdpk=6;
Query OK, 1 row affected (0.00 sec)

CREATE TABLE `tb_onlineddl` (
  `fdpk` int(11) NOT NULL AUTO_INCREMENT,
  `fd1` char(100) NOT NULL,
  `fd2` char(100) NOT NULL,
  `fd3` char(100) NOT NULL,
  `fd4` char(100) NOT NULL,
  `fd5` text,
  `fd6` char(100) DEFAULT NULL,
  PRIMARY KEY (`fdpk`)
) ENGINE=InnoDB AUTO_INCREMENT=131072 DEFAULT CHARSET=latin1


>> DEBUG for INSERT :: online ddl row log buffer usage : Current: 527 , Total: 527, Max: 134217728
>> DEBUG for UPDATE :: online ddl row log buffer usage : Current: 425 , Total: 952, Max: 134217728
>> DEBUG for DELETE :: online ddl row log buffer usage : Current: 22  , Total: 974, Max: 134217728

이 테스트 케이스에서 BLOB나 TEXT 타입 컬럼 또한 Row log buffer에 저장되기 때문에 Row log buffer의 사용량 변화가 크게 다르지 않다.
BLOB/TEXT 타입의 Large Object Type의 컬럼이라고 해서 별도의 메모리 저장 공간을 사용하지 않는다는 것을 확인할 수 있다.

CASE-4 : 컬럼 추가시 (추가 인덱스가 있는 경우)

CREATE TABLE `tb_onlineddl` (
  `fdpk` int(11) NOT NULL AUTO_INCREMENT,
  `fd1` char(100) NOT NULL,
  `fd2` char(100) NOT NULL,
  `fd3` char(100) NOT NULL,
  `fd4` char(100) NOT NULL,
  `fd5` char(100) NOT NULL,
  PRIMARY KEY (`fdpk`),
  KEY `ix_fd1` (`fd1`),
  KEY `ix_fd1_fd2` (`fd1`,`fd2`)
) ENGINE=InnoDB AUTO_INCREMENT=131073 DEFAULT CHARSET=latin1

Connection1 [test]> alter table tb_onlineddl add fd6 char(100), lock=none, algorithm=inplace;

Connection2 [test]> insert into tb_onlineddl values (null, 'insert', 'insert', 'insert', 'insert', 'insert');
Query OK, 1 row affected (0.02 sec)

Connection2 [test]> update tb_onlineddl set fd1='update' where fdpk=11;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Connection2 [test]> delete from tb_onlineddl where fdpk=8;
Query OK, 1 row affected (0.01 sec)

>> DEBUG for INSERT :: online ddl row log buffer usage : Current: 519 , Total: 519 , Max: 134217728
>> DEBUG for UPDATE :: online ddl row log buffer usage : Current: 519 , Total: 1038, Max: 134217728
>> DEBUG for DELETE :: online ddl row log buffer usage : Current: 22  , Total: 1060, Max: 134217728

이 테스트 케이스에서는 세컨드리 인덱스가 Row log buffer의 전체 사용량에 영향을 미치지 않는다는 것을 확인할 수 있다.
MySQL 서버의 Online DDL에서 Row log buffer를 대상 테이블에 병합할 때에는 Row log buffer의 레코드를 읽어서 프라이머리 키와 추가적으로 가지고 있는 인덱스들을 순서대로 적용하기 때문에 세컨드리 인덱스 엔트리들을 별도로 Row log buffer에 이중 기록을 하지 않는 것이다.

Row log buffer의 내용을 대상 테이블에 적용하는 시점은 테이블의 쓰기를 막고 실행되기 때문에, 대상 테이블에 세컨드리 인덱스가 많으면 많을수록 테이블 잠금 시간이 길어질 것이라는 것을 예측할 수 있다.

Online DDL 실행시 Row log buffer 공간이 얼마나 필요할지 예측해보기
지금까지 살펴본 테스트 케이스를 이용하면, Online DDL이 실행되는 동안 얼마나 Row log buffer가 필요할지 (반대로 지정된 Row log buffer 크기에서 INSERT와 UPDATE 그리고 DELETE가 얼마나 허용될 수 있는지)를 예측할 수 있게 되었다.

CASE-1

• 10개 컬럼을 가지고, 평균 Row의 크기가 350 바이트인 테이블
• innodb_online_alter_log_max_size 시스템 변수가 128MB로 설정되어 있을 때,
• 만약 Online DDL이 10분 소요된다면, 최대 초당 633( = 128MB/350Bytes/60sec/10min)개의 UPDATE와 INSERT 문장을 허용할 수 있다. 즉 초당 INSERT와 UPDATE 문장이 633개 이상씩 실행되는 MySQL 서버에서는 Online DDL이 Row log buffer 부족으로 실패할 가능성이 높은 것이다.
• 만약 Online DDL이 30분 소요된다면, 최대 초당 211개의 UPDATE와 INSERT 문장을 허용할 수 있다.
• 만약 Online DDL이 60분 소요된다면, 최대 초당 105개의 UPDATE와 INSERT 문장을 허용할 수 있다.

CASE-2

• 80개의 컬럼을 가지고, 평균 Row의 크기가 1.2KB인 테이블
• innodb_online_alter_log_max_size 시스템 변수가 128MB로 설정되어 있을 때,
• 만약 Online DDL이 10분 소요된다면, 최대 초당 183개의 UPDATE와 INSERT 문장을 허용할 수 있다.
• 만약 Online DDL이 30분 소요된다면, 최대 초당 61개의 UPDATE와 INSERT 문장을 허용할 수 있다.
• 만약 Online DDL이 60분 소요된다면, 최대 초당 30개의 UPDATE와 INSERT 문장을 허용할 수 있다.

물론 이 계산 자체도 쉽지 않을 것이다. 하나의 MySQL 서버에 유입되는 쿼리가 현재 Online DDL을 실행하는 테이블에 적용되는 테이블에만 영향을 미치는 것이 아니기 때문에, 쿼리의 내용을 분석해서 대상 테이블의 레코드를 변경하는 것들만 개수를 파악해야 하기 때문이다.
하지만 지금 수준에서는 이보다 나은 방법이 없다. 또한 이 계산에서는 DELETE 문장에 대한 고려와 Row log buffer의 오버헤드는 전혀 고려하지 않았다.

TODO

가장 좋은 솔루션은 Online DDL이 실행되는 동안 Row log buffer의 사용량과 Online DDL의 진행 상태를 화면에 보여주는 것이다. 
MariaDB 5.5와 10.0 버전에서는 ALTER 명령이 실행될 때 대략의 진행 상황을 보여주고 있다. 하지만 예전 방식의 COPY 스타일로 ALTER가 실행될 때에만 출력되며, Online DDL로 실행되는 경우에는 진행 상황을 보여주지 않는다.
MariaDB 10.0의 Online DDL에서 (기존의 ALTER 명령을 실행한 콘솔 화면에 출력되는 것처럼) 진행 상황을 보여주는 것은 사실 조금 까다로운 문제가 있다.
MariaDB 5.5와 MySQL 5.5에서 COPY 스타일의 ALTER는 사실 InnoDB 스토리지 엔진에서 처리하는 것이 아니라 MySQL 엔진이 처리하기 때문에 진행 상황(프로그레스)을 출력할 수 있었다. 하지만 MariaDB 10.0과 MySQL 5.6의 Online DDL은 이제 InnoDB 스토리지 엔진에서 처리되기 때문에 MySQL 클라이언트로 진행 상황을 출력해주기가 어려운 구조로 바뀌어 버린 것이다.

그래서 Online DDL에서 진행 상황을 사용자에게 알려주기 위한 가장 좋은 방법은, 아래와 같이 글로벌 상태 변수를 이용해서 현재 Online DDL의 진행 상태와 Row log buffer의 사용량을 표시하는 것을 생각해 보았다.

• Innodb_onlineddl_rowlog_rows :: Row log buffer에 저장된 레코드 수(INSERT와 UPDATE 그리고 DELETE에 의해서 변경된)
• Innodb_onlineddl_rowlog_used :: Row log buffer의 사용량 (%)
• Innodb_onlineddl_pct_progress :: Online DDL 진행 상황 (%)

물론 이 방법도 글로벌 상태 변수를 사용하기 때문에 동시에 여러 개의 Online DDL이 실행된다면 상태 변수의 값이 일관되지 않아서 사용자에게 도움이 되지 않을 것이다.
하지만 일반적으로 서비스에 사용중인 MySQL 서버에서 대용량의 테이블 변경 작업을 동시에 여러 개를 실행하지는 않으므로, 글로벌 상태 변수를 이용하는 방법은 괜찮을 솔루션이 될 것으로 생각된다.

이 상태 변수만 있어도, 사용자는 현재 진행중인 Online DDL을 계속 수행하도록 놔둬야 할지 아니면 중간에 멈춰야 할지를 긴 시간 기다리지 않고서도 예측할 수 있을 것으로 보인다.
이 기능이 구체화되면, 다시 공유하도록 하겠다.

pt-online-schema-change 개선

Percona Toolkit에는 많은 훌륭한 도구들이 있지만, 그 중에서도 pt-online-schema-change는 아마도 대 부분의 사용자들이 경험해보았을 정도로 유용한 툴이다.
하지만 아무리 좋은 도구도 모든 사용자의 요건을 만족하기는 어려우며, pt-online-schema-change도 우리의 요건을 만족하기에 조금은 부족한 부분이 있었다.
우선 급하게 아래와 같이 필수적인 부분들 위주로, 조금 보완해서 pt-online-schema-change 도구를 개선해보았다. 

1. 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우
2. 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기
3. pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

위의 기능들을 위해서 pt-online-schema-change 툴에 아래와 같이 4개의 파티미터가 추가되었다.

• --prompt-before-copy
  "--ask-pass"와 같이 별도의 파라미터 값을 필요로 하지 않으며, 이 파라미터가 정의되면 pt-online-schema-change 스크립트가 복사용으로 사용할 테이블을 생성하고 사용자의 확인을 기다리게 된다. 만약 새로운 테이블에 데이터가 복사되기 전에, 새로운 테이블의 구조를 확인한다거나 추가로 변경해야 할 사항이 있다면 이 옵션을 사용하면 된다. 대표적으로 파티션을 생성하는 작업을 위해서 프라이머리 키의 구조를 변경해야 할 때도 있다. 하지만 하나의 ALTER 구문으로는 프라이머리 키를 변경하는 작업과 파티셔닝을 수행하는 작업을 동시에 처리할 수 없다. pt-online-schema-change도구에서도 이 제약은 동일하게 적용된다. 이럴 때에는 --prompt-before-copy 옵션을 활성화하고, pt-online-schema-change가 실행을 잠깐 멈추었을 때 추가로 스키마 변경 작업을 해줄 수 있다.
• --skip-copy-columns
  pt-online-schema-change 도구가 서비스중인 테이블에서 새로운 테이블로 레코드를 복사할 때, 구조 변경 전의 테이블과 구조 변경후의 테이블에서 공통되는 이름의 컬럼은 자동으로 값을 복사하게 된다. 하지만 때로는 이름이 동일한 컬럼이라 하더라도, 기존 테이블의 컬럼 값을 새로운 테이블로 가져오지 말아야 할 때도 있다. 이런 경우에는 --skip-copy-columns 옵션에 무시할 컬럼들을 ","로 구분해서 나열하면 된다.
• --sleep-time-us
  pt-online-schema-change는 기본적으로 MySQL 서버의 시스템 부하 정도를 판단해서 스키마 변경 작업(레코드 복사)의 처리 속도를 조절할 수 있도록 제공하고 있다. 하지만 아주 가벼운 형태의 쿼리가 빈번하게 처리되는 MySQL 서버에서는 이 규칙은 별로 도움이 되지 않는다. 그래서 --sleep-time-us 옵션을 이용하면 pt-onine-schema-change 툴이 청크(chunk) 단위로 복사를 수행하고 나서 일정 시간동인 대기(Sleep)하도록 해서, 서비스용 쿼리들이 거의 지연되지 않고 처리될 수 있도록 할 수 있다. --sleep-time-us 는 마이크로 단위(1/1,000,000)의 초를 파라미터로 받기 때문에, 실제 pt-online-schema-change를 이용하는 경우에는 --sleep-time-us는 1000(1밀리 초) ~ 50000(50 밀리 초) 정도의 값이 적절해 보인다.
• --print-sql
  pt-onine-schema-change 툴은 내부적으로 기존의 테이블은 변경하지 않고, 변경하고자 하는 내용을 포함하는 새로운 테이블을 생성하고 트리거를 이용해서 기존 테이블의 레코드를 새로운 테이블로 복사하는 형태로 처리된다. 이때 pt-onine-schema-change가 생성하는 트리거나 신규 테이블의 구조가 궁금할 때도 있다. 이런 경우에는 --print-sql 옵션을 명시해주면, 생성되는 트리거와 신규 테이블의 구조를 화면에 출력한다. 물론 오리지널 버전의 pt-online-schema-change에도 디버깅 모드로 실행해서 모든 내용을 출력하도록 할 수 있다. 하지만 이는 너무 많은 불필요한 내용들이 출력되므로, 꼭 필요한 내용만 간략히 확인하고자 할 때에는 --print-sql 옵션이 더 도움이 될 것이다.

개선된 pt-online-schema-change에서 위의 옵션들이 명시되면, 아래와 같이 명시된 옵션이 추가로 표기되므로 재확인할 수 있다.
-- Additional parameters ----------------------------------
  >> skip columns : Not specified
  >> sleep time (us) : 50000
  >> prompting user operation : Yes
-----------------------------------------------------------

추가된 기능들을 간단히 살펴보았으니, 이제 위의 1~3번 케이스에 대해서 추가된 기능들을 어떻게 사용할 수 있는지 예제로 살펴보자.

1) 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우

CREATE TABLE test.test_partition (
  id INT AUTO_INCREMENT,
  fd1 VARCHAR(10),
  fd2 DATETIME,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

ALTER TABLE test.test_partition DROP PRIMARY KEY, ADD PRIMARY KEY(id, fd2);
ALTER TABLE test.test_partition PARTITION ...

위의 2개 ALTER 문장은 하나로 묶어서 실행할 수 없으며, pt-online-schema-change 도구를 사용하는 경우에도 마찬가지이다.
그래서 이런 경우에는 아래와 같이 (두개의 ALTER 중에서) 프라이머리 키 변경만 pt-online-schema-change 도구 옵션으로 명시하고, pt-online-schema-change를 --prompt-before-copy 옵션과 함께 실행하는 것이다.

/usr/bin/pt-online-schema-change --alter "DROP PRIMARY KEY, ADD PRIMARY KEY(id, fd2)" D=test,t=test_partition \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--no-check-alter \
--sleep-time-us=50000 \
--prompt-before-copy \
--print-sql \
--execute

--prompt-before-copy 옵션이 명시되었기 때문에, pt-online-schema-change는 새로운 테이블을 생성하고 사용자가 별도의 사인을 줄때까지 기다리게 된다.
...
-- Additional parameters ----------------------------------
  >> skip columns : Not specified
  >> sleep time (us) : 50000
  >> prompting user operation : Yes
-----------------------------------------------------------
...
-- Create Triggers ---------------------------------------
CREATE TRIGGER `pt_osc_test_test_partition_ins` AFTER INSERT ON `test`.`test_partition` FOR EACH ROW REPLACE INTO `test`.`_test_partition_new` ...
CREATE TRIGGER `pt_osc_test_test_partition_upd` AFTER UPDATE ON `test`.`test_partition` FOR EACH ROW REPLACE INTO `test`.`_test_partition_new` ...
CREATE TRIGGER `pt_osc_test_test_partition_del` AFTER DELETE ON `test`.`test_partition` FOR EACH ROW DELETE IGNORE FROM `test`.`_test_partition_new` WHERE `test`.`_test_partition_new`.`id` <=> OLD.`id`;
----------------------------------------------------------

Table copy operation is paused temporarily by user request '--prompt-before-copy'.
pt-online-schema-change utility created new table, but not triggers.
   ==> new table name : `test`.`_test_partition_new`

So if you have any custom operation on new table, do it now.
Type 'yes', when you ready to go.
Should I continue to copy [Yes] ? : <== pt-online-schema-change는 새로운 임시 테이블 _test_partition_new를 생성한 상태에서 처리를 멈추고, 사용자의 입력을 기다리게 된다.


이때 다른 터미널을 이용해서 MySQL 서버에 로그인하여 새로운 테이블(_test_partition_new)을 ALTER TABLE PARTITION 명령으로 파티션을 생성하고, pt-online-schema-change 터미널에서 "Yes"를 입력하면
프라이머리 키도 변경되고 파티션도 추가된 테이블로 데이터를 복사하게 된다. 이 이후 과정은 오리지널 버전의 pt-online-schema-change와 동일하게 작동하게 된다. 

ALTER TABLE _test_partition_new
PARTITION BY RANGE COLUMNS(CRT_DT)
(
 ...
 PARTITION PF_20140420 VALUES LESS THAN ('2014-04-21 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140421 VALUES LESS THAN ('2014-04-22 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140422 VALUES LESS THAN ('2014-04-23 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140423 VALUES LESS THAN ('2014-04-24 00:00:00') ENGINE = InnoDB
);

참고로, 이렇게 프라이머리 키가 변경되는 경우에는 pt-online-schema-change는 그냥 경고 메시지와 함께 멈춰버리게 되는데, 이 때에는 --no-check-alter 옵션으로 강제 진행되도록 해야 할 필요가 있다. 물론 --no-check-alter 옵션을 사용하는 경우에는 새로 복사되는 테이블의 데이터가 문제없이 복사된다는 것을 사용자 입장에서 검증해야 할 것이다.

2) 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기

아래와 같은 테이블에서 fd1 컬럼의 기본 값을 'Y'에서 'N'으로 변경하고자 한다. 그런데 이때 pt-online-schema-change로 스키마가 변경된 이후 test_defaultvalue 테이블의 fd1 컬럼은 모두 'N' (새로운 기본값)으로 업데이트되어야 한다고 가정해보자. (물론 당연히 test_defaultvalue 테이블의 fd1 컬럼은 'Y'와 'N'이 혼재되어서 저장되어 있는 상황)

CREATE TABLE test.test_defaultvalue (
  id INT AUTO_INCREMENT,
  fd1 CHAR(1) DEFAULT 'Y',
  fd2 DATETIME,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

ALTER TABLE test.test_defaultvalue MODIFY fd1 CHAR(1) DEFAULT 'N';
UPDATE test.test_defaultvalue SET fd2='N' WHERE fd2='Y';

이때 pt-onine-schema-change 툴을 아래와 같이 실행하면, 새로 생성되는 테이블의 fd1 컬럼의 기본 값은 'N'으로 바뀌겠지만 실제 컬럼의 저장된 값은 이전 테이블의 fd1 컬럼의 값을 그대로 복사해서 가져오게 된다.

/usr/bin/pt-online-schema-change --alter "MODIFY fd1 CHAR(1) DEFAULT 'N'" D=test,t=test_defaultvalue \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--execute

그래서 오리지널 버전의 pt-online-schema-change 도구를 사용하면, fd1 컬럼의 값을 새로운 기본 값인 'N'으로 채울수가 없다. 그래서 결국 아래오 같이 UPDATE 문장으로 전체 업데이트를 해야 할 것이다. 물론 이런 류의 UPDATE 문장은 인덱스를 이용하지 못해서 테이블의 모든 레코드에 대해서 배타적 잠금을 걸 가능성이 높아서 실제 스키미 변경 만큼이나 영향도가 큰 작업이 되어버리게 된다.

이런 경우에는 개선된 pt-online-schema-change 툴의 --skip-copy-columns 옵션을 이용해서 기존 테이블에서 신규 테이블로 복사할 컬럼에서 일부를 배제시키는 것이 가능하다.
아래와 같이 pt-online-schema-change 를 실행하면, fd1 컬럼의 값을 기존 테이블에서 새로운 테이블로 복사를 하진 않지만 fd1 컬럼의 기본 값이 'N'이기 때문에 fd1 컬럼의 모든 값을 'N'으로 초기화까지 할 수 있게 된다.

/usr/bin/pt-online-schema-change --alter "MODIFY fd1 CHAR(1) DEFAULT 'N'" D=test,t=test_defaultvalue \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--sleep-time-us=50000 \
--skip-copy-columns='fd1' \
--prompt-before-copy \
--print-sql \
--execute

이때 --prompt-before-copy 옵션과 --print-sql 옵션을 사용하면, --skip-copy-columns에 의해서 적용된 트리거와 INSERT .. SELECT .. 복사 SQL 문의 내용을 확인하고 문제가 없으면 작업을 진행하도록 할 수 있다.

3) pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

pt-online-schema-change 도구 자체적으로 부하를 조절하면서 복사하는 기능을 가지고 있다는 것은 잘 알고 있을 것이다. 하지만 이미 간단히 언급했듯이, MySQL 서버가 디스크를 읽지 않고 PK나 인덱스 기반으로 1~2건 레코드를 메모리만 읽어서 (아주 빠르게) 서비스하는 경우에는 경우에는 오리지널 버전의 pt-online-schema-change가 가지고 있는 부하 제어 기능은 부족할 수도 있다.
이런 경우에는 개선된 pt-online-schema-change의 --sleep-time-us 옵션으로 하나의 청크(chunk)가 완료되면 일정 시간동안 pt-online-schema-change의 복사 작업이 잠깐 Sleep하도록 할 수 있다.
--sleep-time-us 옵션에는 숫자 값으로 마이크로 단위의 초를 입력해주면 된다. 즉 "--chunk-size=500 --sleep-time-us=5000" 옵션이 사용되면 500개의 레코드를 복사하고 5 밀리초를 Sleep하게 되는 것이다.

물론 이렇게 매 청크마다 일정 시간동안 Sleep을 수행하면 스키마 변경 시간이 더 길어지게 되는 문제점이 있다. 하지만 pt-online-schema-change 도구 자체의 특성상 다른 트랜잭션의 처리를 잠금 차원에서 방해하거나 리두 로그나 언두 레코드를 대량으로 발생시키는 작업이 아니므로, pt-online-schema-change 자체의 완료 시간이 길어진다 하더라도 데이터의 정합성을 훼손하지는 않으므로 크게 걱정하지 않아도 된다. 또한 pt-online-schema-change를 이용해서 온라인 스키마 변경을 한다 하더라도 사용량이 적은 시간대를 골라서 수행하므로, 소요 시간이 더 걸린다고 해서 크게 문제되지는 않을 것이다. 물론 테이블의 전체 레코드 건수를 기반으로 Sleep할 시간을 결정해야 할 필요가 있지만, 여전히 그만큼의 가치는 있는 작업이 될 것이다.
오리지널 버전의 pt-online-schema-change에서는 --max-load 옵션이 최적으로 설정되지 않으면, 쉬지 않고 레코드를 새로운 테이블로 복사하게 될 수도 있다. 하지만 이는 InnoDB의 Checkpoint age를 급증시켜서 InnoDB 서버를 Sync나 Async 모드로 전환시켜버릴 수도 있다. 하지만 --sleep-time-us 옵션을 적절히 설정해준다면, 천천히 데이터를 복사하므로 Checkpoint age가 급증하는 현상을 막을 수도 있다. 

많은 프로그램들이 초단위나 밀리 초 단위의 값을 입력받기 때문에, 가끔 --sleep-time-us에 입력되는 값을 밀리초로 착각하고 사용할 수도 있으므로 주의할 필요가 있다. --sleep-time-us 옵션을 사용하는 방법은 위의 예제를 참조하도록 하자.

설치 및 패치

1. Download percona toolkit percona-toolkit-2.2.7-1.noarch.rpm from http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/
2. Install percona toolkit 2.2.7-1
   $ rpm -Uvh percona-toolkit-2.2.7-1.noarch.rpm
3. Patch modified code to original pt-online-schema-change
   $ patch /usr/bin/pt-online-schema-change < pt-online-schema-change_kakao.patch

첨부된 패치 파일은 Percona toolkit 2.2.7-1 버전을 기준으로 생성되었습니다.