대용량 테이블 컬럼추가 (rows 1000만 건 이상)

Table B 컬럼추가

 

테이블의 데이터가 쌓이고 컬럼이 많거나 파티션 테이블의 경우는 한번 생성한 스키마에 인덱스를 걸거나 컬럼을 추가할 이슈가 생기면 상당히 까다로운 작업을 해야한다. 

--> 컬럼이 많은 것과 파티션 테이블 중 어느게 더 반영이슈가 있나 테스트 해본적이 있는데 컬럼 30개, 1억건 rows 파티션 테이블보다 컬럼 200개짜리 1000만 rows 테이블의 online DDL이 반영이 더 오래 걸린다.

간단한 DDL문이라고 생각하면 대규모 장애를 만날 수도 있다.
일단 수행시간이 오래걸리고 alter table ALGORITHM=INPLACE, LOCK=NONE; 을 쓸 수없기 때문에
지금은 모르겠지만 암튼 default인 mysql 5.7.19까지는 ALGORITHM=COPY로만 수행이 가능한 것이라.. 작업이 쉽지 않았다. 거기다 이 테이블은 트리거도 걸려있다...;;; 머리가 아프다.

 

상용서비스 반영 전에 적용방식을 테스트해본다.

 

1) Online DDL

mysql> alter table TESTDB.TB_xxxx
    -> add COLUMN xxxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'xxxxxx',
    -> add COLUMN xxx_xxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'zzzzzz'
    -> ;
Query OK, 0 rows affected (31 min 31.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

Alter table add column 테스트 해보았는데 30분이상 걸렸다.

이대로 online 반영은 어려울 거 같다.

 

2) TABLE 교체

2-1) 컬럼추가할 테이블A와 동일한 테이블B를 하나 더 만든다 스키마 생성할 때 컬럼을 추가해서 만드는 것이다.
2-2) 이전 테이블A의 데이터를 테이블B에 insert 한 후 테이블을 교체한다.

use TESTDB;
Create table TB_xxxx_TMP
(
...
xxxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'xxxxxx',
xxx_xxx_NO bigint(11) unsigned DEFAULT NULL COMMENT 'zzzzzz',
  PRIMARY KEY (`xxx_NO`,`xxx_SEQ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xxxx이력'
;

INSERT INTO TESTDB.TB_xxxx_TMP
(xxxx_NO, xxx_xxx_NO 컬럼 2개를 제외한 이전 테이블A의 컬럼을 순서대로...)
select * from TESTDB.TB_xxxx;
commit;

Query OK, 8833879 rows affected (14 min 45.61 sec)
Records: 8833879  Duplicates: 0  Warnings: 0
// 테스트기는 약900만 상용서비스 DB는 1200만 정도 있음

rename table TB_xxxx_TMP to TB_xxxx_TMP2, TB_xxxx_TMP to TB_xxxx, TB_xxxx_TMP2 to TB_xxxx_TMP;
//테이블교체는 A->C, B->A, C->B 로 진행한다. 바로 교체보다 안전한 방식임

그래도 10분이상 걸린다...
테스트 해본바로는 insert into select ~ 는 select .. into values ~과는 달리 meta Lock이 걸리지 않았고
작업중에도 TB_xxxx에 insert와 select가 가능했다.
하지만 트랜젝션 격리수준이 default인 repeatable-read라서 안정성 측면에서 트리거를 끊고 작업하는 게 속편할 거 같다.  트리거에 덴적이 있어서 더 조심스러울 수도 있지만;;
사업이랑 협의해서 insert는 반영후 hist에 업데이트하고 update는 무시하기로 협의하였다.
고객트래픽이 몰리는 시간에 반영하긴 어렵고 9시 전에 작업하기로 하였다.

 

#최종작업계획 

2-1) insert update 트리거 중단
2-2) 컬럼추가할 테이블A와 동일한 테이블B를 하나 더 만든다 스키마 생성할 때 컬럼을 추가해서 만드는 것이다.
2-3) 이전 테이블A의 데이터를 테이블B에 insert 한 후 테이블을 교체한다.
2-4) insert update 트리거 재생성
2-5) TB_xxxx 가 트리거를 건 테이블의 pk 비교하여 추가된 부분을 insert함

#실제 반영리뷰
테스트기에서 작업은 14분정도 소요되었지만 실제 상용반영은 5분 29초였고
차이는 리눅스 OS와 mysql 버전은 5.7.19로 같은데, DISK가 테스트기는 SATA2이고 상용기는 SSD이다.
SSD가 3배정도 빠른 것을 알 수 있다..ㅎㅎ

2-5) pk 비교는 Left outer join 으로 하여 TB_xxxx가 null 인 것을 찾는 방식으로 작업하였고
수행시간은 30초정도 걸렸다 1000만건이 넘더라도 Pk의 b-tree 인덱스 성능은 강력하다는 것을 알 수 있다.
작업 끝~

 

#Thereafter

mysql 8.0.12 버전부터(MairDB 10.3.7 부터) Game changer가 등장했다.

상용버전을 mysql 8(=5.8) 로 올려야할 강력한 이유가 생겼다.

그것은 algorithm = instant 인데 Online DDL 을 지원한다.

DBA에게 축복과도 같은 기능으로 추후 Data dictionary 좀 더 공부하고 정리해서 올릴 예정..