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을 계속 수행하도록 놔둬야 할지 아니면 중간에 멈춰야 할지를 긴 시간 기다리지 않고서도 예측할 수 있을 것으로 보인다.
이 기능이 구체화되면, 다시 공유하도록 하겠다.