pt-osc 동작 원리와 시나리오별 테스트
- pt-osc 작업 시, general log 확인
- pt-osc 작업 도중 트리거 제거
- 트래픽 유무에 따른 작업 소요 시간 변경
- Primary Key, Unique Key 유무에 따른 제약
pt-osc 툴을 사용하면서, 툴의 동작 방식과 여러 시나리오별로 어떻게 동작하는지 테스트해본 결과를 정리하려 합니다. 기본적인 내용들은 이전 포스트를 통해 정리하여 혹시 필요하신 내용이 있다면 아래 포스트를 참고 부탁드립니다.
pt-osc 작업 시, general log 확인
pt-osc 작업 시, 어떤 쿼리가 들어오는지 확인하기 위해 General log를 통해 확인했습니다.
테스트 결과, pt-osc 작업으로 인해 실행되는 내부 쿼리는 아래 순서로 쿼리가 실행됐습니다.
- set-vars 옵션을 통해 선언한 세션 변수 세팅
- 원본 테이블 스키마 추출 후, 작업 후 변경될 스키마와 동일한 테이블 생성
- 데이터 copy를 위한, 트리거 생성 (Insert, Update, Delete)
- 다음 Chunk 범위 조건절에서 사용될 키 데이터 추출 (explain 포함)
- 범위 조건절을 통해 chunk 사이즈만큼 타겟테이블로 데이터 copy (insert ignore into … select)
general log를 통해 pt-osc 툴로 인해 실행되는 내부 쿼리를 확인할 수 있어, 테스트를 통해 작동원리를 이해하는데 도움이 됐습니다.
- pt-osc 실행
pt-online-schema-change --alter "[변경 내용]" D=[데이터베이스명],t=[테이블명] \ --host=[호스트명] --port=[포트] \ --user=[유저명] --password=[패스워드] \ --charset=UTF8mb4 \ --analyze-before-swap \ ## swap 이전 타겟 테이블 통계 업데이트 (DEFAULT) --alter-foreign-keys-method=auto \ ## 외래키 복사 --chunk-index=PRIMARY --chunk-size=1000 --chunk-size-limit=4.0 \ ## chunk 기준 설정 --max-load=[시간대 평균 트래픽에 따라 변동] \ ## 평소 대비 높은 트래픽일 경우, pt-osc 작업 일시 중지 --no-drop-new-table \ ## 작업 중간 실패 시, 타겟테이블 보존 (DEFAULT = 삭제) --no-drop-old-table \ ## 기존 테이블 보존 (DEFAULT = 삭제) --progress=time,30 \ ## 30초마다 로그 기록 --swap-tables \ ## 테이블 스위칭 (DEFAULT) --set-vars="wait_timeout=60, lock_wait_timeout=3, innodb_lock_wait_timeout=3" \ ## pt-osc 작업으로 인한 lock 대기 시간이 너무 길어지지 않도록 설정 --execute - general log 확인
-- 세션 환경변수 Setting 값 선언 SET SESSION innodb_lock_wait_timeout=3; SET SESSION lock_wait_timeout=3; SET SESSION wait_timeout=60; -- 원본 테이블 스키마 정보 추출 SHOW CREATE TABLE `sbtest_220822`.`sbtest1`; -- 신규 테이블 스키마 생성 CREATE TABLE `sbtest_220822`.`_sbtest1_new` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=30000001 DEFAULT CHARSET=utf8mb4; -- 트리거 존재 확인 SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'sbtest_220822' AND EVENT_OBJECT_TABLE = 'sbtest1'; SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'sbtest_220822' AND EVENT_OBJECT_TABLE = 'sbtest1'; SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'sbtest_220822' AND EVENT_OBJECT_TABLE = 'sbtest1'; -- 트리거 생성 CREATE TRIGGER `pt_osc_sbtest_220822_sbtest1_del` AFTER DELETE ON `sbtest_220822`.`sbtest1` FOR EACH ROW DELETE IGNORE FROM `sbtest_220822`.`_sbtest1_new` WHERE `sbtest_220822`.`_sbtest1_new`.`id` <=> OLD.`id`; CREATE TRIGGER `pt_osc_sbtest_220822_sbtest1_upd` AFTER UPDATE ON `sbtest_220822`.`sbtest1` FOR EACH ROW BEGIN DELETE IGNORE FROM `sbtest_220822`.`_sbtest1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `sbtest_220822`.`_sbtest1_new`.`id` <=> OLD.`id`;REPLACE INTO `sbtest_220822`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END; CREATE TRIGGER `pt_osc_sbtest_220822_sbtest1_ins` AFTER INSERT ON `sbtest_220822`.`sbtest1` FOR EACH ROW REPLACE INTO `sbtest_220822`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`); ..... ..... ..... -- 다음 Chunk 범위 체크 쿼리 실행 계획 확인 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest_220822`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '5001')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/; -- 다음 Chunk 범위 id 값 추출 SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest_220822`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '5001')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/; -- Chunk 범위에 따른 조회 쿼리 실행 계획 확인 EXPLAIN SELECT `id`, `k`, `c`, `pad` FROM `sbtest_220822`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '5001')) AND ((`id` <= '6000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 20652 copy nibble*/; -- Chunk 범위 데이터 Copy INSERT LOW_PRIORITY IGNORE INTO `sbtest_220822`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest_220822`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '5001')) AND ((`id` <= '6000')) LOCK IN SHARE MODE /*pt-online-schema-change 20652 copy nibble*/; -- 청크 작업별 Thread Running 수 체크 (--max-load="Running_Thread=25") 확인 SHOW GLOBAL STATUS LIKE 'Threads_running';
pt-osc 작업 도중 트리거 제거
실제로 운영 중에 발생할 확률은 극히 적지만, 만약 실시간 data 동기화를 위해 생성된 trigger를 삭제하면 어떤 문제가 발생할 지 테스트를 진행했습니다.
테스트 결과, 원본과 타겟테이블 사이에 3000만건의 데이터 중 약 5300건의 데이터 틀어짐이 발생했습니다. 트리거가 삭제되지 않도록 주의가 필요하다는 사실을 직접 확인했습니다.
- 백그라운드로 pt-osc 실행
nohup pt-online-schema-change --alter "CHANGE c c varchar(120) DEFAULT '' NOT NULL" D=sbtest_220822,t=sbtest1 \ --no-drop-old-table --chunk-size=1000 --chunk-index=PRIMARY \ --host=[호스트 정보] \ --port=6025 \ --user=[DB 계정 정보] \ --password=[DB 패스워드] --progress=time,30 --charset=UTF8mb4 \ --max-load="Threads_running=25" \ --alter-foreign-keys-method=auto --execute & - 작업 진행 중, 트리거 제거
mysql> DROP TRIGGER pt_osc_sbtest_220822_sbtest1_ins; Query OK, 0 rows affected (0.04 sec) mysql> DROP TRIGGER pt_osc_sbtest_220822_sbtest1_upd; Query OK, 0 rows affected (0.03 sec) mysql> DROP TRIGGER pt_osc_sbtest_220822_sbtest1_del; Query OK, 0 rows affected (0.03 sec) - sysbench를 이용한 원본 테이블 트래픽 구성 (DML)
[$PATH]/sysbench \ [$PATH]/sysbench/oltp_read_write.lua \ --mysql-host='[host 정보]' \ --mysql-port=[Port 정보] --mysql-user=[DB 계정 정보] --mysql-password=[DB 패스워드] --mysql-db=sbtest_220822 \ --db-driver=mysql --threads=16 --time=1200 --forced-shutdown --report-interval=1 run - sysbench 실행 확인
mysql> show processlist; +---------+----------+-------------------+---------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+----------+-------------------+---------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+ | 1761661 | master | 10.81.20.86:59136 | sbtest_220822 | Query | 0 | statistics | EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest_220822`.`sbtest1` FORCE INDEX(`PRIMARY`) W | | 1761662 | master | 10.81.20.86:59138 | sbtest_220822 | Sleep | 532 | NULL | NULL | | 1761717 | master | 172.27.1.29:54616 | NULL | Query | 0 | starting | show processlist | | 1761739 | master | 10.81.20.86:59254 | sbtest_220822 | Execute | 0 | updating | DELETE FROM sbtest1 WHERE id=5050 | | 1761740 | master | 10.81.20.86:59256 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761741 | master | 10.81.20.86:59260 | sbtest_220822 | Execute | 0 | updating | UPDATE sbtest1 SET k=k+1 WHERE id=5040 | | 1761742 | master | 10.81.20.86:59258 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761743 | master | 10.81.20.86:59262 | sbtest_220822 | Execute | 0 | starting | COMMIT | | 1761744 | master | 10.81.20.86:59264 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761745 | master | 10.81.20.86:59266 | sbtest_220822 | Execute | 0 | Sending data | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 5038 AND 5137 ORDER BY c | | 1761746 | master | 10.81.20.86:59268 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761747 | master | 10.81.20.86:59270 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761748 | master | 10.81.20.86:59272 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761749 | master | 10.81.20.86:59274 | sbtest_220822 | Sleep | 0 | NULL | NULL | | 1761750 | master | 10.81.20.86:59278 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761751 | master | 10.81.20.86:59276 | sbtest_220822 | Execute | 0 | updating | DELETE FROM sbtest1 WHERE id=5050 | | 1761752 | master | 10.81.20.86:59280 | sbtest_220822 | Execute | 0 | updating | UPDATE sbtest1 SET c='49383389871-28759029460-46414709084-42859700036-88535704466-45735151440-305043' | | 1761753 | master | 10.81.20.86:59282 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | | 1761754 | master | 10.81.20.86:59284 | sbtest_220822 | Execute | 0 | delayed commit ok initiated | COMMIT | +---------+----------+-------------------+---------------+---------+------+-----------------------------+------------------------------------------------------------------------------------------------------+ - 작업 종료 후 데이터 틀어짐 확인
mysql> select count(*) FROM sbtest1 AS A INNER JOIN ____sbtest1_old AS B ON A.id = B.id WHERE A.k <> B.k; +----------+ | count(*) | +----------+ | 5295 | +----------+ 1 row in set (14 min 24.14 sec)
트래픽 유무에 따른 작업 소요 시간 변경
pt-osc 작업 시, mysql 통계에 저장된 테이블 Rows를 기반으로 작업 소요 시간을 예측하여 알려줍니다.
아무런 부하 없이 작업하게 되면 예상 소요시간이 크게 달라지지 않지만, 테스트를 통해 중간에 높은 트래픽을 걸어 예상 소요시간이 크게 변동되는걸 확인할 수 있었습니다.
예상 소요 시간은 어디까지나 불확실한 참고 데이터이며 트래픽이 높은 서비스 환경에서는 해당 시간의 부정확도가 더욱 커질 수 있다는 점을 작업 시 유의해야 합니다.
- pt-osc 실행
## pt-osc 실행 $ nohup pt-online-schema-change --alter "CHANGE c c varchar(120) DEFAULT '' NOT NULL" D=sbtest_220822,t=sbtest1 \ --no-drop-old-table \ --chunk-size=1000 --chunk-index=PRIMARY \ --host=[호스트 정보] --port=[포트 정보] \ --user=[DB 계정 정보] \ --password=[DB 패스워드] \ --progress=time,30 \ --charset=UTF8mb4 \ --max-load="Threads_running=100" --critical-load="Threads_running=200" \ --alter-foreign-keys-method=auto \ --execute > [$PATH]/traffic_test.txt 2>&1 & - pt-osc 작업 중간중간 sysbench를 활용해 부하 생성
/usr/local/sysbench/bin/sysbench \ /usr/local/sysbench/oltp_read_write.lua \ --mysql-host=[호스트 정보] \ --mysql-port=[포트 정보] \ --mysql-user=[DB 계정 정보] --mysql-password=[DB 패스워드] \ --mysql-db=sbtest_220822 \ --db-driver=mysql --threads=32 --time=1200 --forced-shutdown --report-interval=1 run
traffic_test 로그 파일을 살펴보면 트래픽을 발생시킨 시간대마다,예측 소요시간이 3배 증가했다가 감소되는 현상을 확인할 수 있습니다. 이를 통해 트래픽에 따라, 예측 소요시간이 실시간으로 변동된다는 것을 알 수 있습니다.
Creating new table...
Created new table sbtest_220822._sbtest1_new OK.
Altering new table...
Altered `sbtest_220822`.`_sbtest1_new` OK.
2022-08-23T01:30:22 Creating triggers...
2022-08-23T01:30:22 Created triggers OK.
2022-08-23T01:30:22 Copying approximately 29470326 rows...
Copying `sbtest_220822`.`sbtest1`: 1% 28:16 remain
Copying `sbtest_220822`.`sbtest1`: 3% 28:26 remain
Copying `sbtest_220822`.`sbtest1`: 3% 39:23 remain
Copying `sbtest_220822`.`sbtest1`: 3% 48:46 remain
Copying `sbtest_220822`.`sbtest1`: 4% 57:06 remain
Copying `sbtest_220822`.`sbtest1`: 4% 01:03:49 remain
Copying `sbtest_220822`.`sbtest1`: 4% 01:10:20 remain
Copying `sbtest_220822`.`sbtest1`: 4% 01:16:14 remain
Copying `sbtest_220822`.`sbtest1`: 5% 01:21:23 remain
Copying `sbtest_220822`.`sbtest1`: 5% 01:26:10 remain
Copying `sbtest_220822`.`sbtest1`: 5% 01:30:24 remain
Copying `sbtest_220822`.`sbtest1`: 6% 01:33:40 remain ### 부하 발생으로 인한 소요시간 증가 (28분 -> 1시간 33분)
Copying `sbtest_220822`.`sbtest1`: 7% 01:17:42 remain
Copying `sbtest_220822`.`sbtest1`: 9% 01:07:42 remain
Copying `sbtest_220822`.`sbtest1`: 11% 01:00:17 remain
Copying `sbtest_220822`.`sbtest1`: 12% 54:48 remain ### 부하 제거로 30초당, 예상 소요시간 빠르게 감소
Copying `sbtest_220822`.`sbtest1`: 14% 50:28 remain
Copying `sbtest_220822`.`sbtest1`: 16% 47:14 remain
Copying `sbtest_220822`.`sbtest1`: 17% 44:37 remain
Copying `sbtest_220822`.`sbtest1`: 19% 42:29 remain
Copying `sbtest_220822`.`sbtest1`: 20% 40:30 remain
Copying `sbtest_220822`.`sbtest1`: 22% 38:50 remain
Copying `sbtest_220822`.`sbtest1`: 23% 37:14 remain
Copying `sbtest_220822`.`sbtest1`: 25% 35:52 remain
Copying `sbtest_220822`.`sbtest1`: 26% 34:33 remain
Primary Key, Unique Key 유무에 따른 제약
pt-osc툴을 사용하기 위해선, PK / UK 유무에 따른 제약 조건이 있다고 알고는 있지만, 서비스 환경에서 사용하는 테이블에, PK가 없는 경우는 찾기 어려워 제약조건을 제대로 파악하지는 못해 테스트를 진행했습니다.
PK, UK 유무에 따라 케이스를 나누어 pt-osc 툴이 정상 작동하는지 확인해본 결과입니다.
Prmary Key 또는 Unique Key가 테이블에 최소한 하나 존재해야 정상적으로 툴이 동작했으며, 위 조건을 만족하는 경우, chunk 기준을 일반 인덱스로 설정해도 작업이 가능했습니다.
| - | Case 1 | Case 2 | Case 3 | Case 4 |
|---|---|---|---|---|
| Primary Key | X | X | X | X |
| Unique Key | X | X | O | O |
| 인덱스 유무 | X | O | X | O |
| 정상 작동 여부 | X | X | O | O |
케이스에 따른 로그 결과는 다음과 같습니다.
- Case 1 (PK x / UK x / 인덱스 X)
# A software update is available: Cannot chunk the original table `sbtest_220822`.`sbtest_no_pk`: There is no good index and the table is oversized. at /usr/local/bin/pt-online-schema-change line 5882. - Case 2 (PK x / UK x / 인덱스 O)
Altering new table... Altered `sbtest_220822`.`_sbtest_no_pk_new` OK. 2022-08-26T00:15:59 Dropping new table... 2022-08-26T00:15:59 Dropped new table OK. `sbtest_220822`.`sbtest_no_pk` was not altered. The new table `sbtest_220822`.`_sbtest_no_pk_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. Please check you have at least one UNIQUE and NOT NULLABLE index. - Case 3 (PK x / UK O)
Altering `sbtest_220822`.`sbtest_no_pk`... Creating new table... Created new table sbtest_220822._sbtest_no_pk_new OK. Altering new table... Altered `sbtest_220822`.`_sbtest_no_pk_new` OK. 2022-08-26T01:07:50 Creating triggers... 2022-08-26T01:07:50 Created triggers OK. 2022-08-26T01:07:50 Copying approximately 29589048 rows... Copying `sbtest_220822`.`sbtest_no_pk`: 2% 19:19 remain Copying `sbtest_220822`.`sbtest_no_pk`: 5% 18:40 remain Copying `sbtest_220822`.`sbtest_no_pk`: 7% 18:49 remain Copying `sbtest_220822`.`sbtest_no_pk`: 9% 18:31 remain - Case 4 (PK x / UK O / 인덱스 X / chunk 기준: 인덱스)
Altering `sbtest_220822`.`sbtest_no_pk`... Creating new table... Created new table sbtest_220822._sbtest_no_pk_new OK. Altering new table... Altered `sbtest_220822`.`_sbtest_no_pk_new` OK. 2022-08-26T05:38:07 Creating triggers... 2022-08-26T05:38:07 Created triggers OK. 2022-08-26T05:38:07 Copying approximately 29470326 rows... Copying `sbtest_220822`.`sbtest_no_pk`: 0% 01:46:22 remain Copying `sbtest_220822`.`sbtest_no_pk`: 1% 01:36:37 remain Copying `sbtest_220822`.`sbtest_no_pk`: 1% 01:25:53 remain