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)에서는 기본적으로 사용할 수 있을 것으로 보인다.
댓글 없음:
댓글 쓰기