[MySQL] 테이블 대용량파티션 삭제시 Table Lock 회피 방법

MySQL에서 파티션 삭제시 조심해야 할 사항이 있습니다. 바로 대용량의 파티션을 DROP하는 일입니다.

파티션 테이블을 사용하는 이유는 아마 대부분 로그성 데이터 때문일 것입니다. 이 로그성 테이블은 회사마다 틀리겠지만 보통 기가단위로 되어 있을 것입니다.

댜량의 트랜잭션이 발생되고 있는 운영 데이터베이스에서 파티션 테이블의 특정 파티션 삭제 시도시 테이블이 순간 Freezing되는 현상이 발생할 수 있습니다. 크기에 따라 달라지긴 하겠지만 큰 사이즈의 파티션일 경우 몇초가 걸릴수도 있습니다. 또한 파일시스템의 종류에 따라 발생하는 문제가 조금 상이할 수 있습니다.

이는 운영환경에서 사용시 크게 문제가 발생할 수 있습니다. Drop중일때 해당 테이블에 Table Lock을 걸기 때문에 다른 트랜잭션이 해당 테이블을 사용할 때 문제가 되기 때문입니다. 즉 테이블을 사용할 수 없는 문제가 발생합니다.

이럴때 사용하는것이 Exchange Partition입니다. 이 기능은 특정 테이블의 특정 파티션을 다른 테이블의 파티션으로 옮기는 기능입니다. 이 기능을 활용하여 발생하는 문제를 회피할 수 있습니다. 또한 데이터가 없는 파티션을 삭제하기 때문에 metalock이 걸리더라도 순식간에 작업이 완료됩니다.

지금부터 사용방법을 알아보도록 하겠습니다.

 

▶︎ 테이블 및 데이터 준비

• 파티션 테이블생성.

mysql> CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=utf8
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (30000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (60000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (90000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (120000000) ENGINE = InnoDB);

 

• 테이블 생성정보를 확인

mysql> show create table sbtest1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (30000000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (60000000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (90000000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (120000000) ENGINE = InnoDB) */ |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

• 파티션 상태 조회

mysql> select * from information_schema.partitions where table_schema='sbtest';           
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def           | sbtest       | sbtest1    | p1             | NULL              |                          1 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 30000000              |   29009412 |            227 |  6607077376 |            NULL |    617594880 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p2             | NULL              |                          2 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 60000000              |   28258225 |            233 |  6606028800 |            NULL |    618643456 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p3             | NULL              |                          3 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 90000000              |   28095288 |            227 |  6399459328 |            NULL |    618659840 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p4             | NULL              |                          4 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 120000000             |    9727313 |            227 |  2216689664 |            NULL |    233586688 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
4 rows in set (0.00 sec)

 

• 테스트 데이터 준비

데이터를 넣습니다. 테스트 데이터는 sysbench를 이용해서 넣었습니다. row갯수는 100000000로 했습니다. 약 23g정도 나옵니다.

파티션당 7기가 3개,  2.5기가 1개가 나옵니다.

[root@singledb sbtest]# ls -al
total 24363028
drwxr-xr-x  2 mysql mysql        135 Nov  5 00:28 .
drwxr-xr-x. 7 mysql mysql       4096 Nov  5 09:31 ..
-rw-r-----  1 mysql mysql         65 Nov  4 22:25 db.opt
-rw-r-----  1 mysql mysql       8632 Nov  4 22:55 sbtest1.frm
-rw-r-----  1 mysql mysql 7470055424 Nov  5 00:30 sbtest1#P#p1.ibd
-rw-r-----  1 mysql mysql 7470055424 Nov  5 00:30 sbtest1#P#p2.ibd
-rw-r-----  1 mysql mysql 7474249728 Nov  5 00:30 sbtest1#P#p3.ibd
-rw-r-----  1 mysql mysql 2533359616 Nov  5 00:30 sbtest1#P#p4.ibd
[root@singledb sbtest]# du -sh sbtest1#P#p1.ibd 
7.0G    sbtest1#P#p1.ibd

 

• 파티션 테이블을 상태 조회

mysql> select * from information_schema.partitions where table_schema='sbtest';           
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def           | sbtest       | sbtest1    | p1             | NULL              |                          1 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 30000000              |   29009412 |            227 |  6607077376 |            NULL |    617594880 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p2             | NULL              |                          2 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 60000000              |   28258225 |            233 |  6606028800 |            NULL |    618643456 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p3             | NULL              |                          3 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 90000000              |   28095288 |            227 |  6399459328 |            NULL |    618659840 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | sbtest       | sbtest1    | p4             | NULL              |                          4 |                          NULL | RANGE            | NULL                | id                   | NULL                    | 120000000             |    9727313 |            227 |  2216689664 |            NULL |    233586688 |         0 | 2020-11-05 00:28:57 | NULL        | NULL       |     NULL |                   | default   | NULL            |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
4 rows in set (0.00 sec)

 

▶︎ 파티션 이동

이제 Exchange Partiton 기능으로 원본 테이블에서 특정 파티션을 다른 테이블 파티션으로 이동시켜보겠습니다.

 

먼저 이동할 테이블을 만듭니다.

mysql> create table sbpart like sbtest1;
Query OK, 0 rows affected (0.04 sec)

 

그리고 파티션을 삭제합니다.

mysql> alter table sbpart remove partitioning;

 

먼저 원본 테이블의 데이터 Row수를 확인해 봅니다.

mysql> select table_schema, table_name, partition_name, table_rows from information_schema.PARTITIONS where TABLE_NAME='sbtest1';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| sbtest       | sbtest1    | p1             |   29009412 |
| sbtest       | sbtest1    | p2             |   28258225 |
| sbtest       | sbtest1    | p3             |   28095288 |
| sbtest       | sbtest1    | p4             |    9727313 |
+--------------+------------+----------------+------------+
4 rows in set (0.00 sec)

 

이제 파티션을 이동해 보겠습니다.

mysql> alter table sbtest1 exchange partition p4 with table sbpart; 
Query OK, 0 rows affected (0.01 sec)

 

파티션이 실제로 옮겨졌는지 원본 테이블을 한번 조회해 보겠습니다.

mysql> select table_schema, table_name, partition_name, table_rows from information_schema.PARTITIONS where TABLE_NAME='sbtest1';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| sbtest       | sbtest1    | p1             |   29009412 |
| sbtest       | sbtest1    | p2             |   28258225 |
| sbtest       | sbtest1    | p3             |   28095288 |
| sbtest       | sbtest1    | p4             |          0 |
+--------------+------------+----------------+------------+
4 rows in set (0.00 sec)

원본테이블의 p4 파티션은 0으로 되어있습니다.

 

이제 타겟테이블을 조회해 보겠습니다.

mysql> select table_schema, table_name, partition_name, table_rows from information_schema.PARTITIONS where TABLE_NAME='sbpart';
+--------------+------------+----------------+------------+
| table_schema | table_name | partition_name | table_rows |
+--------------+------------+----------------+------------+
| sbtest       | sbpart     | NULL           |    9727313 |
+--------------+------------+----------------+------------+
1 row in set (0.00 sec)

 

파티션 테이블이 아니니 일반 테이블 조회로 조회해 보겠습니다.

mysql> select table_schema, table_name, table_rows from information_schema.`TABLES` where table_schema='sbtest' and table_name='sbpart';
+--------------+------------+------------+
| table_schema | table_name | table_rows |
+--------------+------------+------------+
| sbtest       | sbpart     |    9727313 |
+--------------+------------+------------+
1 row in set (0.00 sec)

 

데이터가 모두 넘어갔으니 이제 필요없는 원본 테이블의 파티션을 Drop해 줍니다.

mysql> alter table sbtest1 drop partition p4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

넘어온 데이터를 가지고 있는 임시 테이블은 용량 확보를 위해 삭제해 줍니다.

mysql> drop table sbpart;
Query OK, 0 rows affected (0.10 sec)