[MySQL] pt-osc를 통한 일반 테이블 파티셔닝으로 전환

서비스 중 mysql 테이블을 파티셔닝 테이블로 전환이 필요할 때 ONLINE DDL 방식으로 변경이 가능한지 확인해보았다.

pt-osc 툴을 사용하여서 가능하며 툴 기능을 추가하여야한다 (해당 기능은 kakao db team 에서 구현)

 

1. pt-osc 패치 진행

2. pt-osc 로 PK 구성 변경 (--prompt-before-copy 옵션)

3. data copy 전 확인 단계에서 다른 세션으로 new 테이블에 파티셔닝 작업 수행

4. pt-osc로 돌아와 data copy 마저 진행

5. 성능 테스트 6천만건 테이블에 1분 당 3500건 insert (1개 스레드로 350건 입력 + 반복별 0.01초 유휴시간을 주면 분당 약 3500건의 성능이 나옴)

 

ptosc.patch 파일 생성

 

@@ -8464,6 +8464,14 @@
          or warn "Cannot reopen STDOUT to /dev/null: $OS_ERROR";
    }

+  ## Added
+
+   if($o->has('prompt-before-copy') && defined $o->get('prompt-before-copy')){
+       print "  >> prompting user operation : Yes\n";
+   }else{
+       print "  >> prompting user operation : No\n";
+   }
+
    # ########################################################################
    # Connect to MySQL.
    # ########################################################################
@@ -11159,3 +11163,18 @@

+   ## Added - Prompt and Waiting user custom operation
+   if($o->has('prompt-before-copy') && defined $o->get('prompt-before-copy')){
+      my $tmp_user_input = "";
+      print "\n";
+      print "Table copy operation is paused temporarily by user request '--prompt-before-copy'\n";
+      print "pt-online-schema-change utility created new table, but not triggers.\n";
+      print "   ==> new table name : $new_tbl->{name} \n\n";
+      print "So if you have any custom operation on new table, do it now.\n";
+      print "Type 'yes', when you ready to go.\n";
+      do{
+         print "Should I continue to copy [Yes] ? : ";
+         chomp ($tmp_user_input = <STDIN>);
+      }while( !($tmp_user_input =~ m/^yes$/i) );
+   }
+
   my @trigger_names;
   @drop_trigger_sqls = ();
@@ -11948,6 +11971,10 @@

 Prompt for a password when connecting to MySQL.
    
+=item --prompt-before-copy
+
+Prompt before data copy to new temporary table for user custom operation
+
 =item --charset

 short form: -A; type: string

 

pt-osc patch 진행

 patch /usr/bin/pt-online-schema-change < ptosc.patch

 

패치 성공 여부 확인

pt-online-schema-change --help | grep prompt

 

 

테이블 생성 및 데이터 입력

create test_table
(
    id int unsigned auto_increment primary key,
    col1 bigint not null,
    col2 varchar(30) not null,
    col3 varchar(50) null,
    col4 varchar(10) null,
    col5 varchar(200) null,
    col6 bigint unsigned not null,    
    started_at datetime(6) not null,
    ended_at datetime(6) not null,
    created_at datetime(6) not null default current_timestamp(6)
);

 

pt-osc 실행

$  pt-online-schema-change --alter "DROP PRIMARY KEY, ADD PRIMARY KEY( id , ended_at )" D=test_db,t=test_table \
> --no-drop-old-table \
> --no-drop-new-table \
> --chunk-size=1000 \
> --defaults-file=/etc/my.cnf \
> --port=포트\
> --user=계정명 \
> --password='패스워드'\
> --progress=time,30 \
> --max-load="Threads_running=100" \
> --critical-load="Threads_running=200" \
> --chunk-index=PRIMARY \
> --charset=utf8mb4 \
> --set-vars="innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
> --no-check-alter \
> --prompt-before-copy \
> --execute

 

--yes 입력 시 트리거 생성되면서 데이터 복제가 진행되므로 그 전에 파티셔닝 테이블로 alter 처리 해줘야한다

 

파티션으로 테이블 변경

alter table test_table partition by range COLUMNS (ended_at)
    ( partition p202001 values less than ('2020-02-01'),
     partition p202002 values less than ('2020-03-01'),
     partition p202003 values less than ('2020-04-01'),
     partition p202004 values less than ('2020-05-01'),
     partition p202005 values less than ('2020-06-01'),
     partition p202006 values less than ('2020-07-01'),
     partition pMAXVALUE values less than (MAXVALUE));

 

yes 입력하면 바로 트리거 생성이 되면서 데이터 카피가 진행된다.

 

 

1개 스레드로 350건 입력 소요 평균 소요시간 정리

트리거 시작 시간

트리거 이전 입력 소요시간 평균 : 4초

트리거 이후 입력 소요시간 평균 : 6초

 

 

트리거 완료 이후 입력 소요시간 평균 : 4초

 

반복 입력 처리를 위한 mysqlslap 스크립트

#!/bin/sh

a=0
while [ $a -lt 10000 ]
do
        echo $a >> /home/mysql/test.txt
        date "+%Y-%m-%d %H:%M:%S" >> /home/mysql/test.txt
        mysqlslap --user='' --password='' --port= --delimiter=";"  --query="insert into test_table (col1, ended_at, col12, col13, col14, col15, col16, col17, col18) values ('2024-01-29 17:51:29.684056', '2024-01-30 17:51:29.683349', '11', '22', 44, '33', 55, '2024-01-29 17:30:29.683349', '55');" --concurrency=1 --iterations=350 >> /home/mysql/test.txt
        date "+%Y-%m-%d %H:%M:%S" >> /home/mysql/test.txt
        a=`expr $a + 1 `
        sleep 0.01
done

 

 

참조

https://small-dbtalk.blogspot.com/2014/04/pt-online-schema-change-modified-pt.html

https://kimdubi.github.io/mysql/ptosc_partitioning/