MySQL 5.6 파티션 삭제시 Exchange Partition 기능 활용 방안

MySQL 5.6 파티션 삭제시 Exchange Partition 기능 활용 방안

[출처] 파티션 삭제시 Exchange Partition 기능 활용 방안 (in MySQL 5.6)|작성자 돌고래사육사

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

물론 파티션의 물리적인 파일과 내부 메타 데이터 갱신 정도만 발생하기에 테이블 락 유지 시간이 길지는 않지만, 서비스 성격에 따라 문제가 되는 경우도 있다.

 

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)

 

MySQL 데이터 디렉토리에서 파일을 조회해 보면 각 파티션별로 10GB 정도의 테이블 스페이스가 생성된 것을 확인할 수 있다.

-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

 

이 상태에서, 파티션 DROP 방식으로 2014-04-20일의 파티션을 삭제했을 때, 해당 파티션의 테이블 스페이스(10GB) 삭제 작업과 테이블의 메타 정보 갱신에 대략 0.6초 정도 소요되는 것을 확인할 수 있었다. 

10GB 정도의 파일을 삭제하는 작업은 ext4 파일 시스템에서는 크게 문제가 되지 않는 작업이라 다행이 0.6초만에 처리가 되긴 했지만, 만약 ext3 파일 시스템을 사용하고 있었거나 월별 파티셔닝 등으로 파티션의 사이즈가 더 컸다면 파티션 삭제에 소요되는 시간은 더 늘어나게 되고, 이때는 락으로 인한 문제가 발생하게 된다.

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

 

MySQL 5.6 버전에서는 파티션 Exchange 기능을 사용해서 락으로 인한 영향을 최소화하는 것이 가능하다.

// 테스트 테이블의 파티션 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)

 

Posted By 김득은

자료출처   :   http://seuis398.blog.me/70039224614    

[출처] 파티션 삭제시 Exchange Partition 기능 활용 방안 (in MySQL 5.6)|작성자 돌고래사육사