[MySQL] 테이블 대용량파티션 삭제시 Table Lock 회피 방법
- Databases/MySQL
- 2020. 11. 17.
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)
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] 잠금(Lock)운영 최적화-1 (0) | 2020.11.25 |
|---|---|
| [MySQL][InnoDB] InnoDB 제한사항 (0) | 2020.11.20 |
| [MySQL] Purge Log (0) | 2020.11.12 |
| [MySQL] root 암호 분실시 재설정 방법 (0) | 2020.11.09 |
| [MySQL] Binary Log 관련 명령 (0) | 2020.11.08 |