[MySQL] 파티션(Partition) 심화 - Partition Exchange

작성일: 2022-12-30 (금)

오늘의 주제는 “Partition Exchange” 입니다.

파티션 테이블의 경우, 이전 게시글에서 다룬 내용이지만 오늘은 Partition Exchange 기능에 대해서 알아보려 합니다.

Partition Exchange

• MySQL 5.6 부터 지원되는 기능으로써 특정 파티선 삭제 시 발생하는 락이나 부하에 대한 최소화
• 일반 테이블을 특정 파티션으로 수정하고자 할 때 작업 영향도를 최소화

Partition Exchange 기능을 사용해야 하는 이유

• 파티션 테이블은 파티션마다 OS에서 .ibd 파일이 생성됨
• 파티션의 물리적인 파일과 내부 메타 데이터 갱신 정도의 작업에서 테이블 락 유지 시간이 길지는 않지만, 서비스에 따라 위험도가 높은 경우가 존재
• Partition Exchange 기능을 사용할 경우 OS 파일 삭제 과정 없이 메타 데이터만 변경하기 때문에 리소스 사용이 적으며, 소요 시간이 적다는 장점

select TABLE_NAME
     , PARTITION_NAME
     , TABLE_LOWS
  from infomation_schema.PARTITIONS
 where TABLE_SCHEMA = 'test'
   and TABLE_NAME = 'test01'
;
/*
+------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+------------+----------------+------------+
| test01     | p_20221127     |     982053 |
| test01     | p_20221128     |       9922 |
| test01     | p_20221129     |          0 |
+------------+----------------+------------+
3 rows in set (0.01 sec)
*/

[root@905c91ef6de5 test]# ls -lh
total 199M
-rw-r----- 1 mysql mysql  92M Nov 27 05:46 test01#p#p_20221027.ibd
-rw-r----- 1 mysql mysql  10M Nov 27 05:44 test01#p#p_20221028.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:44 test01#p#p_20221029.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:59 test_1#p#p_20221028.ibd
-rw-r----- 1 mysql mysql 144K Nov 27 05:47 test_1#p#p_20221029.ibd
-rw-r----- 1 mysql mysql  96M Nov 27 05:59 st.ibd

• data 디렉토리에서 파일을 조회해보면 각 파티션별로 테이블 스페이스가 생성된 것을 확인할 수 있으며,
  데이터가 가장 많았던 27일자 데이터(98만건)의 경우 약 92MB 정도의 크기를 사용하고 있습니다.
• DROP PARTITION 방식으로 27일 자의 데이터를 삭제했을 때와 PARTITION EXCHAGNE 방식을 사용했을 때를 비교해 보겠습니다.

# 1.
alter table test01 drop partition p_20221027
; -- 0.53s

# 2.
create table test02 like test01
;
alter table test02 remove partitioning
;
alter table test01 exchange partition p_20221027 with table test02
; -- 0.08s
alter table test01 drop partition p_20221027
; -- 0.07s

• DROP PARTITION 방식의 경우 약 0.53초의 시간이 소요됨을 확인할 수 있었으며,
  PARTITION EXCHAGNE 방식의 경우 약 6배 빠른 0.08초의 시간이 소요됨을 확인할 수 있었습니다.
• PARTITION EXCHAGNE 방식을 통해 데이터가 존재하지 않기 때문에 삭제 작업이 매우 빠른 것 또한 확인이 가능합니다.

PARTITION EXCHANGE 작업 순서

1. 임시 테이블 생성
   create table [temp_table_name] like [table_name] )
2. 임시 테이블 파티션 삭제
   alter table [temp_table_name] remove partitioning
   • 파티션을 삭제할 때는 remove / drop 명령어가 존재
   • remove partitioning: 파티션만 삭제하고 해당 데이터는 유지
   • drop partition: 파티션과 데이터 모두 삭제
3. 임시 테이블로 파티션 이동
   alter table [table_name] exchange partition [partition_name] with table [temp_table_name] )
4. 임시 테이블 삭제
   drop table [temp_table_name]

CREATE TABLE AS SELECT 구문을 사용하면 안되는 이유!

1. CREATE TABLE test.temp (
   idx int(11) NOT NULL,
   name varchar(50) NOT NULL,
   PRIMARY KEY(idx),
   UNIQUE KEY(name)
);
2. CREATE TABLE test.temp_a AS SELECT * FROM test.temp ;
3. CREATE TABLE test.temp_b LIKE test.temp ;
4. SHOW CREATE TABLE test.temp_a ;
   CREATE TABLE test.temp_a (
       idx int(11) NOT NULL,
       name varchar(50) NOT NULL
   );
5. SHOW CREATE TABLE test.temp_b ;
   CREATE TABLE test.temp_b (
       idx int(11) NOT NULL,
       name varchar(50) NOT NULL,
       PRIMARY KEY(idx),
       UNIQUE KEY(name)
    );

create table like 방식과 달리 create table as select 방식은
기존 테이블의 키를 가져오지 않습니다.

exchange partition 사용 시, 두개의 테이블 구조가 모두 동일하지 않으면 에러 발생하기 때문에 create table like 방식을 통해서 구조가 동일한 테이블을 생성해야 합니다.

🚨 마지막 파티션(1개 이하) 테이블에 [REMOVE | DROP] PARTITION 명령어를 사용할 경우 Cannot remove all partitions, use DROP TABLE instead 에러가 발생한다.
자세한 원인은 알 수 없으나, MySQL의 오류가 아닐까 싶네요.

<해결방법>

ALTER TABLE [TABLE NAME] REMOVE PARTITIONING ;

파티션별 데이터 로우 확인 쿼리

select TABLE_SCHEMA
     , TABLE_NAME
     , PARTITION_NAME
     , PARTITION_ORDINAL_POSITION
     , TABLE_ROWS
  from information_schema.partitions
 where TABLE_SCHEMA = ''
   and TABLE_NAME = ''
;

💡 오늘은 파티션 테이블에 존재하는 데이터를 Partition Exchange 기능을 통해 안전하게 삭제하는 법을 알아보았습니다.
Partition Exchange 기능을 통해서 파티션 테이블의 데이터를 삭제하더라도 항상 서버의 상황을 고려하여 최대한 안전한 상황에서 작업을 진행해야 한다는 것을 잊지 말아야 합니다.