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