mysql partition 활용(데이터 컷팅)
1.현황
- log관련 테이블중 abc_xxx_log 과 같은 대용량 테이블(mysql기준 수십~수백GB)들은 조건절 컬럼에 인덱스가 있어도
일정구간 이상을 탐색하면 full scan 을 하고 있어 ETL시 mysql 부하 경감 및 수행시간 단축이 필요해보임.
- mysql의 abc_xxx_log 테이블은 1일 조회시에만 인덱스를 사용하고 2일 이상 부터 인덱스를 사용안하고 있음
(ETL재처리등도 있어서 2일정도는 해야하는 상황을 가정)
- mysql에서 보관주기(2주)를 지키기 위해 delete 구문으로 처리되고 있음(로그데이터 생성팀 혹은 개인)
- abc_xxx_log 이관작업은(2일기준) 20분이상 수행
- 스트리밍으로 이관을해야하는데 전체 스캔을 하다보니 수분~수십분 정도 대기하다 fetch를 시작
2.결론
- 이관 : 수행시간 단축 및 데이터 이관시 지연없는 이관 가능(fetch시간도 단축)
- 수행시간 : 10분내외
- 데이터 보관주기 부하 : 개선됨 하지만 drop partition 사용시 몇초간 lock 점유(5.6 이후 부터는 대기시간 거의 없음)
DML을 최소사용하여 데이터 보관주기 관리 및 데이터 이관관련 성능관련에대해 이점이 있었음
- 스토리지엔진 : Myisam 이 약간 빨랐지만 하나의 db에 여러개의 스토리지 엔진을 가지고 가면 안 좋다고 하니 그냥 innodb로..
3.시나리오
버티카로 데이터를 이관하는 테스트 및 mysql에서 데이터 보관주기(컷팅) 테스트
(이관은 talend를 사용함)
- 파티셔닝기법을 활용 :
= 장점
mysql의 log류 table 보관주기 관리를 위한 데이터 컷팅이 유리(대용량 delete 회피)
기간별 조회 혹은 데이터이관시 파티션 푸르닝 기대
부하 경감 및 수행시간 감소
인덱스관련 관리포인트 감소(업무가 단순하고 배치로만 사용한다면 인덱스를 제거해도됨)
= 단점 혹은 제한사항
데이터 컷팅시 ddl 문장 권한 관련
파티션은 테이블당 1024개 까지 생성가능함
5.5이하는 drop partition 사용시 몇초간 lock 점유를 하기도 하고 exchange partition 구현이 되어있지않음
- 스토리지 엔진 :
= Myisam 스토리지 엔진 테스트
innodb 스토리지 엔진과 이관 후 성능부분에서 유의미한 결과가 나오면 고려
4.실행계획 확인
-- 일반테이블(원본) EXPLAIN SELECT * FROM abc_xxx_log_ytkim_0 a where reg_time >= current_date-interval 2 day and reg_time < current_date-interval 0 day ; -- 파티션테이블 EXPLAIN PARTITIONS SELECT * FROM abc_xxx_log_ytkim_2 a where reg_time >= current_date-interval 2 day and reg_time < current_date-interval 0 day ; |
5.데이터 컷팅
- drop partition 을 사용하는 방법
-- 수초간 수행(lock 점유) ALTER TABLE abc_xxx_log_ytkim_2 drop partition p_20190101; |
- exchange partition 을 사용하는 방법(5.6부터 지원되어서 5.5 이하에서는 사용불가)
-- 절차가 상대적으로 추가되지만 원본 lock점유시간 감소
(참고로 오라클같은 경우 해당 파티션에대한 dml이 없으면 exchange partition , drop partition 둘다 바로 적용됨
dml때문에 ddl이 방해를 받는다면 ddl_lock_timeout 파라메터를 조정)
-- 빈테이블 생성 CREATE TABLE abc_xxx_log_ytkim_drop LIKE abc_xxx_log_ytkim_2 ; ALTER TABLE abc_xxx_log_ytkim_drop REMOVE PARTITIONING ; ALTER TABLE abc_xxx_log_ytkim_2 exchange partition p_20190201 WITH TABLE abc_xxx_log_ytkim_drop ; ALTER TABLE abc_xxx_log_ytkim_2 drop partition p_20190201; DROP TABLE abc_xxx_log_ytkim_drop ; --drop 전 한번 심호흡 후 수행 |
6.테이블 생성구문 예제
CREATE TABLE abc_xxx_log_ytkim_2 ( `reg_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `col1` VARCHAR(250) NULL DEFAULT NULL, ... ... `coln` VARCHAR(256) NULL DEFAULT NULL ) ENGINE=Myisam -- 지정하지 않으면 innodb 스토리지엔진 PARTITION BY RANGE ( UNIX_TIMESTAMP(reg_time) ) ( PARTITION p_20190101 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-02 00:00:00') ), PARTITION p_20190102 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-03 00:00:00') ), PARTITION p_20190103 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-04 00:00:00') ), PARTITION p_20190104 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-05 00:00:00') ), PARTITION p_20190105 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-06 00:00:00') ), .... PARTITION p_20210926 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-09-27 00:00:00') ) ) ;
-- 파티션 문장 생성구문은 버티카에서 수행(mysql에서 만들어도 무방 저는 이게 편해서....) select 'PARTITION p_'||to_char(curdate,'yyyymmdd')||' VALUES LESS THAN ( UNIX_TIMESTAMP('''||curdate+1|| ' 00:00:00'') ),' from (select current_date- row_number() over() curdate from columns a) a --데이터가 존재하는 아무테이블이나. where curdate >= '2019-01-01' --파티션 시작시점 order by 1 limit 1000 ; |