하지만 아무리 좋은 도구도 모든 사용자의 요건을 만족하기는 어려우며, pt-online-schema-change도 우리의 요건을 만족하기에 조금은 부족한 부분이 있었다.
우선 급하게 아래와 같이 필수적인 부분들 위주로, 조금 보완해서 pt-online-schema-change 도구를 개선해보았다.
- 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우
- 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기
- 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 옵션을 사용하는 방법은 위의 예제를 참조하도록 하자.
설치 및 패치
- Download percona toolkit percona-toolkit-2.2.7-1.noarch.rpm from http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/
- Install percona toolkit 2.2.7-1
$ rpm -Uvh percona-toolkit-2.2.7-1.noarch.rpm - 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 버전을 기준으로 생성되었습니다.
