파티션 삭제시 Exchange Partition 기능 활용

로그성 데이터를 파티션 테이블로 보관하는 경우가 많은데, 보관주기가 지난 파티션을 drop 하는 경우에 MySQL 에서는 전체 파티션에 테이블 락이 걸리는 문제가 있습니다.

다행히 파티션의 물리적인 파일과 내부 메타 데이터 갱신 정도만 발생하기에 테이블 락 유지 시간이 길지는 않지만, ext3 파일시스템을 사용하거나 파티션 당 데이터 사이즈가 크면 문제가 되는 경우가 있겠습니다.

 

다행히 MySQL 5.6 버전부터는 파티션 Exchange 기능이 지원되어서, 테이블 락으로 인한 영향을 최소화하면서 파티션 삭제를 할 수 있겠습니다. 

* 샘플 데이터

 : 하루에 3200만건(테이블스페이스 파일 사이즈 10GB) 정도 데이터가 저장되는 일자별 파티셔닝 테이블

mysql> show create table ptest\G *************************** 1. row ***************************        Table: ptest Create Table: CREATE TABLE `ptest` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `regdt` date NOT NULL DEFAULT '0000-00-00',   `pad` char(255) DEFAULT NULL,   PRIMARY KEY (`id`,`regdt`) ) ENGINE=InnoDB AUTO_INCREMENT=167108865 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE ( to_days (regdt) ) (PARTITION p20140420 VALUES LESS THAN (735709) ENGINE = InnoDB,  PARTITION p20140421 VALUES LESS THAN (735710) ENGINE = InnoDB,  PARTITION p20140422 VALUES LESS THAN (735711) ENGINE = InnoDB,  PARTITION p20140423 VALUES LESS THAN (735712) ENGINE = InnoDB,  PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec)   mysql> select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME='ptest';       +------------+----------------+------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | +------------+----------------+------------+ | ptest      | p20140420      |   32934515 | | ptest      | p20140421      |   32939678 | | ptest      | p20140422      |   32935000 | | ptest      | p20140423      |   32935471 | | ptest      | pMAXVALUE      |          0 | +------------+----------------+------------+ 5 rows in set (0.01 sec) # 데이터 디렉토리에서 파일을 조회해보면...  -rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:03 ptest#P#p20140420.ibd -rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:08 ptest#P#p20140421.ibd -rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:34 ptest#P#p20140422.ibd -rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:39 ptest#P#p20140423.ibd -rw-rw---- 1 seuis398 seuis398  96K 2014-04-25 22:39 ptest#P#pMAXVALUE.ibd

 

* 테스트1 (파티션 DROP) ==> 테이블 락 유지시간 0.59초 (사이즈에 따라 늘어남)

 : 다행히 테스트에 사용한 시스템은 ext4라서 10GB 삭제하는데 0.6초 정도 밖에 안걸리긴 했습니다.

   물론 파티션 사이즈가 더 커지거나 일자별 파티셔닝이 아니라 월별 파티셔닝을 한다면 삭제시 문제가 발생할 수 있겠습니다.

mysql> alter table ptest drop partition p20140420; Query OK, 0 rows affected (0.59 sec) Records: 0  Duplicates: 0  Warnings: 0

* 테스트2 (파티션 Exchange) ==> 테이블 락 유지시간 : 0.04초 (균일하게 유지)

// 테스트 테이블의 파티션 1개와 동일한 구조의 테이블을 생성 mysql> create table empty_ptest like ptest;  Query OK, 0 rows affected (0.07 sec)    mysql> alter table empty_ptest remove partitioning;    Query OK, 0 rows affected (0.17 sec) Records: 0  Duplicates: 0  Warnings: 0   // 위에서 생성한 빈 테이블과 삭제한 파티션(20140421)을 Exchange mysql> alter table ptest exchange partition p20140421 with table empty_ptest;    Query OK, 0 rows affected (0.04 sec)   mysql>  select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME='ptest'; +------------+----------------+------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | +------------+----------------+------------+ | ptest      | p20140421      |          0 | | ptest      | p20140422      |   32209112 | | ptest      | p20140423      |   32184620 | | ptest      | pMAXVALUE      |          0 | +------------+----------------+------------+ 4 rows in set (0.00 sec)   // 불필요한 데이터 일괄 삭제 mysql> alter table ptest drop partition p20140421; Query OK, 0 rows affected (0.00 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> drop table empty_ptest; Query OK, 0 rows affected (0.58 sec)

http://cafe.naver.com/mysqlpg/426 [출처] 파티션 삭제시 Exchange Partition 기능 활용 (MySQL Power Group) |작성자 seuis398