mysql 대용량 partition lock 없이 삭제하기

by

1. 대용량 파티션 테이블이란

간혹 대용량 파티션 데이터의 삭제는 테이블의 성격에 따라선 까다로운 작업이 되기도 한다

파티션 테이블은 보통 insert해서 데이터를 저장하는 용도로 사용하지만

때로는 실시간으로 트래픽이 치는 테이블도 파티션으로 구성해서 관리하는 경우도 있다.

파티션 테이블은 partition pruning을 사용하기 위해서 구성하기도 하지만

궁극적으론 lock 없이 데이터를 삭제하기 위한 목적이 크다.

어찌됐든 이런 OLTP 성 테이블을 파티션으로 관리하고 있다면 DBA 입장에선 그닥 유쾌하진 않다

slowquery log에서도 해당 테이블을 조인하는 쿼리는 좀 더 꼼꼼히 관리해야 하고

파티션 삭제 때도 서버 상황에 따라 뜻하지 않은 table lock 이슈가 될 수도 있기 때문에 긴장하게 된다.

mysql 5.6 부터 들어온 exchange partition라는 명령어로 100%는 아니지만 거의 95%이상

이슈 없이 파티션 데이터를 삭제할 수 있는 방법을 알아본다.

  • OLTP(온라인 트랜잭션 처리)는 온라인 뱅킹, 쇼핑, 주문 입력 또는 텍스트 메시지 전송 등 동시에 발생하는 다수의 트랜잭션을 실행하는 데이터 처리
  • 파티션 프루닝(partition pruning)
    하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능. 파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심 원리가 파티션 Pruning에 있다고 할 수 있다.

2. 대용량 파티션 데이터 확인

일단 파티션 데이터의 용량이 얼마나 되는지 확인한다

select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS
where TABLE_NAME = 'TB_MEMBER';

+-----------+----------------+------------+
| TABLE_NAME| PARTITION_NAME | TABLE_ROWS |
+-----------+----------------+------------+
| TB_MEMBER | p202303        |  123203567 |
| TB_MEMBER | p202304        |  130859585 |
| TB_MEMBER | p202305        |   32900369 |
| TB_MEMBER | pExt           |          0 |
+-----------+----------------+------------+
8 rows in set (0.001 sec)

3. 대용량 파티션 삭제방법

1) 파티션 테이블 스키마 복사

CREATE TABLE TB_MEMBER_TMP like TB_MEMBER;

2) 복사한 임시테이블의 파티션 삭제

ALTER TABLE TB_MEMBER_TMP remove partitioning;

3) 파티션 데이터 이동

alter table TB_MEMBER EXCHANGE PARTITION p202303 with TABLE TB_MEMBER_TMP;

4.파티션 데이터 확인

파티션 데이터는 전부 이동한 것을 확인할 수 있고 이제 테이블에는 p202303 스키마만 남아있다.

select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS
where TABLE_NAME = 'TB_MEMBER';

+-----------+----------------+------------+
| TABLE_NAME| PARTITION_NAME | TABLE_ROWS |
+-----------+----------------+------------+
| TB_MEMBER | p202303        |  		0 |
| TB_MEMBER | p202304        |  130859585 |
| TB_MEMBER | p202305        |   32900369 |
| TB_MEMBER | pExt           |          0 |
+-----------+----------------+------------+
8 rows in set (0.001 sec)

5. 나머지 정리작업

1) p202303 파티션 삭제

alter table TB_MEMBER drop partition p202303;
Query OK, 0 rows affected (0.00 sec)

바로 삭제되는 것을 확인할 수 있다. (만약 이렇게 하지 않고 그냥 drop명령어로 직접 삭제할 경우 1억건 기준 1~2분 정도의 시간이 걸렸다)

2) 임시 테이블 데이터 삭제

drop table TB_MEMBER_TMP;
Query OK, 0 rows affected (0.38 sec)

이렇게 하면 대용량 파티션 데이터도 lock 걱정 없이

데이터를 삭제할 수 있다.

끝.

Leave a Comment