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) 제거까지 온라인으로 이상없이 작업 완료

'MySQL' 카테고리의 다른 글

MySQL 버그 리포트 찾는 방법(MySQL 8.0 Default Collation) (2)	2023.10.28
MySQL RAND 함수의 난수 생성 원리 (0)	2023.08.20
Aurora MySQL Fragmentation Test & Online DDL (0)	2023.08.19
MySQL 인덱스 스킵 스캔(Index Skip Scan)의 원리 (0)	2023.08.13
Aurora Version & Status 터미널 대시보드 (0)	2023.08.11

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

MySQL Internal

pt-online-schema-change PK & DROP Test

테스트 목적

테스트 이유

테스트

테스트 결과

'MySQL' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	##############
	## 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

	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 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
	--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

	## 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

	(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 \|
	+----------+

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

pt-online-schema-change PK & DROP Test

테스트 목적

테스트 이유

테스트

테스트 결과

'MySQL' 카테고리의 다른 글

'MySQL' Related Articles

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역