pt-online-schema-change PK & DROP Test

테스트 목적

Before와 같은 제약조건의 테이블을 pt-osc로 After와 같이 변경할 때 이슈없는지 확인

(pt-osc 작업 수행 중에 유입되는 DML로 인해 데이터의 일관성이 깨지는지 확인)

테스트 이유

(1) PK 제약조건과 Unique 제약 조건이 동시에 존재하여 ON DUPLICATE문을 사용할 때 데드락 발생

(2) 데드락이 발생한 문제로 절차적 방식으로 코드 롤백

(절차적 방식: 중복되는 값이 존재하는지 DB에서 확인 후, 있으면 UPDATE, 없으면 INSERT)

(3) 제약조건이 하나만 존재하면 (2)번과 같은 방식을 사용할 필요 없이 ON DUPLICATE문을 사용하면 됨 --> 데드락 해소

(4) 다만 온라인으로 PK를 변경해야 하기 때문에 절차적 방식에도 적용될 수 있는 시나리오인지 확인 필요

(5) 4번 과정에서 문제가 없었다면 PK 변경 후 응용 프로그램 코드를 ON DUPLICATE로 배포

##############
##  Before  ##
##############
CREATE TABLE `test0` (
  `id` bigint NOT NULL,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint NOT NULL,
  `device_id` varchar(128) NOT NULL,
  `revoke_time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_userid_deviceid` (`user_id`,`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#############
##  After  ##
#############
CREATE TABLE `test0` (
  `id` bigint NOT NULL,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint NOT NULL,
  `device_id` varchar(128) NOT NULL,
  `revoke_time` timestamp NOT NULL,
  PRIMARY KEY (`user_id`, `device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ i

테스트

현재 응용 프로그램 코드에서 1개 트랜잭션 내에서 발생할 수 있는 DML문은 INSERT 또는 UPDATE이기 때문에 각각 20개 정도의 동시 스레드로 나누어 한쪽은 INSERT, 한쪽은 UPDATE 수행 (Total: 40 Threads)

데이터가 없는 경우와 중복되는 경우를 5:5 비율로 나눈 것임

*데이터가 없는 경우: 데이터가 없으면 INSERT

*데이터가 있는 경우: 데이터가 있으면 UPDATE

테스트 테이블은 실테이블의 스키마와 동일하게 구성

CREATE TABLE `test0` (
  `id` bigint NOT NULL,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint NOT NULL,
  `device_id` varchar(128) NOT NULL,
  `revoke_time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_userid_deviceid` (`user_id`,`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 

INSERT Thread ▼

INSERT 데이터 예시는 아래와 같으며, 스노우 플레이크로 생성되는 id는 순차적으로 증가하기 때문에 테스트 id 값은 단순하게 n+1로 삽입

20,000개 레코드는 미리 INSERT 해놓은 상태에서 id=20001 ~ 40000까지 INSERT

INSERT INTO test0 (id, created_at, updated_at, deleted_at, user_id, device_id, revoke_time) 
VALUES (0+1, now(), now(), null, floor(rand()*1000000), uuid(), now())
...
...
INSERT INTO test0 (id, created_at, updated_at, deleted_at, user_id, device_id, revoke_time) 
VALUES (0+20001, now(), now(), null, floor(rand()*1000000), uuid(), now())

 

UPDATE Thread ▼

UPDATE 예시는 아래와 같으며, 테이블에 존재하는 id 값을 5001 ~ 15000까지 순차적으로 업데이트

UPDATE test0 
SET updated_at='1990-04-25', 
deleted_at='1990-04-25', 
user_id=floor(rand()*1000000), 
device_id=uuid(), 
revoke_time='1990-04-25' 
WHERE id = 0+5001

 

pt-online-schema-change command ▼

DROP PRIMARY KEY 구문이 포함된 경우 작업이 중단되기 때문에 --no-check-alter 옵션 사용

pt-online-schema-change 
--no-check-alter 
--alter "DROP PRIMARY KEY, 
ADD PRIMARY KEY(user_id, device_id), 
DROP INDEX ux_userid_deviceid, 
ADD INDEX ix_id(id)" 
D=test,t=test0 
--no-drop-old-table 
--no-drop-new-table 
--chunk-size=500 
--chunk-size-limit=2 
--host=test-dba-main-silver-cluster.cusieftkzlyg.ap-northeast-2.rds.amazonaws.com 
--user=admin 
--progress=time,30 
--max-load="Threads_running=20" 
--critical-load="Threads_running=100" 
--chunk-index=PRIMARY 
--charset=UTF8MB4 
--password='{패스워드}' 
--sleep=0.05 
--execute

 

ps-osc 트리거 ▼

## INSERT 트리거
CREATE DEFINER=`admin`@`%` TRIGGER `pt_osc_test_test0_ins` AFTER INSERT ON `test0` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `test`.`_test0_new` (`id`, `created_at`, `updated_at`, `deleted_at`, `user_id`, `device_id`, `revoke_time`) VALUES (NEW.`id`, NEW.`created_at`, NEW.`updated_at`, NEW.`deleted_at`, NEW.`user_id`, NEW.`device_id`, NEW.`revoke_time`);END

## UPDATE 트리거
CREATE DEFINER=`admin`@`%` TRIGGER `pt_osc_test_test0_upd` AFTER UPDATE ON `test0` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_test0_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_test0_new`.`id` <=> OLD.`id`; REPLACE INTO `test`.`_test0_new` (`id`, `created_at`, `updated_at`, `deleted_at`, `user_id`, `device_id`, `revoke_time`) VALUES (NEW.`id`, NEW.`created_at`, NEW.`updated_at`, NEW.`deleted_at`, NEW.`user_id`, NEW.`device_id`, NEW.`revoke_time`); END

## DELETE 트리거
CREATE DEFINER=`admin`@`%` TRIGGER `pt_osc_test_test0_del` AFTER DELETE ON `test0` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_test0_new` WHERE `test`.`_test0_new`.`id` <=> OLD.`id`; END

 

Load Generator ▼

기존에 사용하던 부하 스크립트에서 INSERT와 UPDATE 부분만 수정하여 사용

​

수행 순서 ▼

1. INSERT & UPDATE 동시 스레드 40개 수행

2. pt-osc로 DROP PK, ADD PK​

테스트 결과

조건-1

- INSERT 20 Thread

- UPDATE 20 Thread

(PK 변경 전)
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+
..
..
..(Inserting & Updating)
..
..(pt-osc for PK)
..
..
(PK 변경 후)
## 20,000건 INSERT 확인
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|    40000 |
+----------+

## 10,000건 UPDATE 확인
mysql> SELECT COUNT(*) FROM test0 WHERE id > 5000 AND revoke_time = '1990-04-25 00:00:00';
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+

--> 데이터 건 수가 적어서 오류 없이 진행

 

조건-2

- INSERT 40 Thread

- UPDATE 40 Thread

(PK 변경 전)
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
..
..
..(Inserting & Updating)
..
..(pt-osc for PK)
..
..
(PK 변경 후)
## 50,000건 INSERT 확인
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+

## 20,000건 UPDATE 확인
mysql> SELECT COUNT(*) FROM test0 WHERE id > 5000 AND revoke_time = '1990-04-25 00:00:00';
+----------+
| count(*) |
+----------+
|    19979 |
+----------+

--> UPDATE 스레드에서 Deadlock 21건 발생
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ERROR: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

--> PK로 구성된 user_id, device_id를 업데이트 하면서 데드락이 발생하는 것으로 보임

 

조건-3

- INSERT 40 Thread

- UPDATE 40 Thread

- UPDATE문에서 user_id, device_id 업데이트를 제외(중복되면 제약조건을 다시 업데이트할 필요 없음)

(PK 변경 전)
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
..
..
..(Inserting & Updating)
..
..(pt-osc for PK)
..
..
(PK 변경 후)
## 50,000건 INSERT 확인
mysql> SELECT COUNT(*) FROM test0;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+

## 20,000건 UPDATE 확인
mysql> SELECT COUNT(*) FROM test0 WHERE id > 5000 AND revoke_time = '1990-04-25 00:00:00';
+----------+
| count(*) |
+----------+
|    20000 |
+----------+

--> PK 업데이트 부분 제거 후 Deadlock 없이 PK 삭제 및 생성 완료

 

테스트 후 라이브 적용

운영 환경은 조건-3으로 작업 진행하였으며, PK DROP --> PK ADD --> 코드 배포 --> Old Primary Key(id) 제거까지 온라인으로 이상없이 작업 완료