Last Updated on 7월 9, 2024 by Jade(정현호)
안녕하세요
이번에는 MySQL 의 Partition Exchange 에 대 한 내용을 확인해보도록 하겠습니다.
해당 포스팅은 아래 이전 포스팅에서 이어지는 글입니다.
• MySQL Partition (1) - 정의와 기능 설명 파티션 제약사항
• MySQL Partition (2) - 파티션 종류 와 파티션 테이블 생성 과 변경
사전 테이블 준비
Partition Exchange 는 MySQL 5.6 버전부터 지원되는 기능입니다.
특정 파티션에 대한 삭제 시 락(잠금)이나 부하에 대한 최소화 및 일반 테이블을 특정 파티션으로 하고자 할 때 작업 영향도를 최소화하면서 파티션 작업을 수행할 수 있습니다.
테스트 테이블 생성
기능 확인 및 테스트를 위해서 테스트 테이블 생성 및 데이터를 입력하도록 하겠습니다.
-- 테이블 생성 mysql> CREATE TABLE `tb_range_table` (id int not null, name varchar(10), dept varchar(10), hiredate date not null default '2010-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 Partition by range(YEAR(hiredate)) ( partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB, partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB, partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB, partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB, partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB ); -- 데이터 입력 mysql> insert into `tb_range_table` values(1,'A','A','2013-02-05'), (2,'A','A','2013-03-05'),(3,'A','A','2013-04-05'), (4,'A','A','2013-05-05'),(5,'A','A','2013-06-05');
파티션 정보 조회
위에서 생성된 파티션 정보를 확인해 보도록 하겠습니다.
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME as PART_NAME, PARTITION_ORDINAL_POSITION as PART_POS, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tb_range_table'; +--------------+----------------+-----------+----------+------------+ | TABLE_SCHEMA | TABLE_NAME | PART_NAME | PART_POS | TABLE_ROWS | +--------------+----------------+-----------+----------+------------+ | test | tb_range_table | p0 | 1 | 0 | | test | tb_range_table | p1 | 2 | 0 | | test | tb_range_table | p2 | 3 | 0 | | test | tb_range_table | p3 | 4 | 5 | <-- 5건 확인됨 | test | tb_range_table | p999 | 5 | 0 | +--------------+----------------+-----------+----------+------------+
동일 구조의 일반 테이블을 생성
Exchange Partition 테스트를 위한 테이블 생성 동일 구조의 일반 테이블을 생성합니다.
mysql> create table `tb_single_table` like `tb_range_table`;
모든 파티션 삭제
이전 단계에서 create table like 명령어를 통해서 동일한 구조의 테이블을 생성하였습니다.
파티션 구조도 동일하게 생성 되어있기 때문에 Partition exchange 을 모든 파티션을 삭제하도록 하겠습니다.
mysql> alter table `tb_single_table` remove partitioning;
[참고1] Partition exchange 시 대상 테이블
exchange 할 테이블과 원본 테이블은 구조가 동일해야 하며, exchange 할 테이블이 이미 파티션이 되어있을 경우 다음과 같은 에러가 발생합니다.
ERROR 1732 (HY000): Table to exchange with partition is partitioned: 테이블명
[참고2] remove partitioning과 drop partition의 차이점은 일단 drop은 파티션을 삭제하면서 파티션에 포함한 데이터도 삭제되지만 remove 은 파티션은 삭제하지만 데이터는 남게 됩니다.
remove partitioning는 테이블의 전체 파티션을 없애고 drop 은 특정 파티션만 드랍(삭제) 하게 됩니다.
테스트 테이블 건수 확인
mysql> select count(*) from tb_single_table; +----------+ | count(*) | +----------+ | 0 | +----------+ mysql> select count(*) from tb_range_table; +----------+ | count(*) | +----------+ | 5 | <-- 5건 +----------+
Partition exchange
이전 단계에서 생성한 테스트 테이블을 통해서 Partition exchange 를 진행하도록 하겠습니다.
파티션 exchange 수행
파티션 테이블 과 일반 테이블을 통해서 Partition exchange 를 진행하도록 하겠습니다.
mysql> alter table `tb_range_table` algorithm=inplace, lock=none, exchange partition p3 with table `tb_single_table`;
Partition exchange 작업도 "algorithm=inplace, lock=none" 옵션 사용이 가능 합니다.
일반 테이블 건수 확인
mysql> select count(*) from tb_range_table; +----------+ | count(*) | +----------+ | 0 | <--5건에서 0건으로 변경됨 +----------+ mysql> select count(*) from tb_single_table; +----------+ | count(*) | +----------+ | 5 | <-- 일반 테이블이 0건에서 5건이 됨 +----------+
파티션 정보 조회
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tb_range_table'; +------------+----------------+-----------+----------+----------+ | TAB_SCHEMA | TABLE_NAME | PART_NAME | PART_POS | TAB_ROWS | +------------+----------------+-----------+----------+----------+ | test | tb_range_table | p0 | 1 | 0 | | test | tb_range_table | p1 | 2 | 0 | | test | tb_range_table | p2 | 3 | 0 | | test | tb_range_table | p3 | 4 | 0 | <-- 5건에서 0으로 변경됨 | test | tb_range_table | p999 | 5 | 0 | +------------+----------------+-----------+----------+----------+
파티션 및 임시 테이블 제거
원본 테이블의 파티션 제거 및 임시 테이블 제거합니다.
mysql> alter table `tb_range_table` drop partition p3; mysql> drop table `tb_single_table`;
파티션 테이블로 변경
추가적인 내용으로 Partition exchange 를 통해서 일반 테이블로 변경된(P3 파티션 -> 일반 테이블) 이 후, alter 명령어를 통해서 다시 파티션으로 변경할 수는 있습니다.
일반 테이블에서 파티션으로 변경 명령어 예시
mysql> alter table `tb_single_table` PARTITION BY RANGE(YEAR(hiredate)) ( partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB, partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB, partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB, partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB );
다른 파티션 테이블로의 Exchange
Partition Exchange를 통해서 일반 테이블로 변경된(P3 파티션 -> 일반 테이블) 데이터를 테이블 구조가 같은 다른 파티션 테이블로 다시 Partition Exchange 할 수 있습니다.
이 경우는 파티션 테이블에서 -> 다른 파티션 테이블로 파티션 이동(Exchange) 를 위해서 사용하는 경우가 될 수 있습니다.
위에서 tb_range_table 테이블의 p3 파티션과 tb_single_table 테이블을 exchange 하였습니다. 그 상황에서 아래와 같이 구조가 같은 다른 테이블을 생성하도록 하겠습니다.
새로운 파티션 테이블 생성
mysql> CREATE TABLE `tb_range_table_history` (id int not null, name varchar(10), dept varchar(10), hiredate date not null default '2010-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 Partition by range(YEAR(hiredate)) ( partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB, partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB, partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB, partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB, partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB );
Partition Exchange 실행
tb_single_table 테이블과 tb_range_table_history의 p3 파티션을 Exchange를 수행합니다.
mysql> alter table `tb_range_table_history` algorithm=inplace, lock=none, exchange partition p3 with table `tb_single_table`;
Partition Exchange 실행
-- 일반 테이블 mysql> select count(*) from tb_single_table; +----------+ | count(*) | +----------+ | 0 | +----------+ -- 파티션 테이블 mysql> select count(*) from tb_range_table_history; +----------+ | count(*) | +----------+ | 5 | +----------+
이와 같이 파티션 테이블 -> 일반 테이블 -> 다른 파티션 테이블 순으로 파티션 데이터에 대한 이동으로 사용할 수도 있습니다.
ROW_FORMAT 관련
partition exchange 시 파티션 테이블과 비 파티션 테이블의 ROW_FORMAT 속성이 동일 해야 합니다.
예를들어 한쪽 테이블의 COMPRESS(압축) 기능을 사용하여 ROW_FORMAT이 테이블 간에 서로 다를 경우 2개 테이블 모두 COMPRESS(압축)을 설정하거나 둘다 압축을 사용하지 않거나 와 같이 두개 테이블 간에 같은 ROW_FORMAT 속성을 가지고 있어야 합니다.
다를 경우 partition exchange시 다음과 같은 에러가 발생합니다.
ERROR 1731 (HY000): Non Matching attribute 'ROW_FORMAT' between partition and table
MySQL 압축(COMPRESS) 기능은 다음 포스팅을 참조하시면 됩니다.
INSTANT ALGORITHM 사용할 경우
MySQL online DDL의 algorithm 중에서 INSTANT 방식을 통해 컬럼 추가 또는 삭제를 한 경우 exchange partition 이 불가 합니다.
이 상황은 파티션 테이블 또는 Excange 하려는 일반 테이블 둘 중 한 곳에서라도 컬럼 추가 또는 삭제시 algorithm=instant 로 수행하였다면 exchange partition 시 다음과 같은 오류가 발생합니다.
ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table
MySQL Document 에서도 다음과 같은 내용을 확인할 수 있습니다.
Once one or more columns have been added to a partitioned table using
ALGORITHM=INSTANT, it is no longer possible to exchange partitions with that table.
이와 같이 오류가 발생할 경우 instant 방식으로 수행된 테이블에 대해서 INPLACE 또는 COPY 방식으로 ALTER TABLE을 수행하거나 OPTIMIZE TABLE을 수행하게 되면 exchange partition을 수행할 수 있습니다.
다만 3개의 작업 모두 테이블 사이즈에 따라서 매우 오랜 시간이 소요될 수 있습니다.
파티션이 된 테이블의 경우 통상적으로 데이터 건수가 많으며 테이블 사이즈가 크기 때문에 위와 같은 오류 발생시 initial 작업은 많은 시간이 소요가 예상됩니다. 그래서 exchange partition 작업이 예상될 경우 해당 파티션 테이블에 대해서 add column/drop column 작업 시 instant 방식 대신 시간이 다소 소요되더라도 INPLACE 방식으로 수행하는 것을 고려해야 합니다.
MySQL Online DDL 기능에 대한 내용은 다음의 포스팅을 참조하시면 됩니다.
Summary
MySQL은 파티션 테이블에 대한 다양한 기능을 제공하고 있으며, 다른 RDBMS에서 제공하는 대부분의 파티션 기능을 제공하고 있습니다.
많은 데이터가 존재하는 테이블이 파티션으로 구성 되어있을 경우 drop partition 또는 truncate partition 을 하기 전에 먼저 Partition exchange 를 하게 되면 많은 데이터가 있는 테이블의 drop/truncate 작업에 대해서 부하를 줄이면서 빠르게 진행할 수 있습니다.
(Partition exchange 작업도 Online DDL 이 지원됩니다.)
MySQL 8.0에서 exchange partitions 기능과 관련된 파티션 제약사항이 추가되었으며, 위의 설명한 내용과 같이 파티션된 테이블에 컬럼 추가/삭제시 Online DDL의 ALGORITHM=INSTANT 옵션을 사용하면 테이블 exchange partitions 시 에러가 발생됩니다.
용량이 많고 사용량이 많은 테이블이라면 Partition exchange 를 먼저 진행 후에 exchange 된 일반 테이블을 정리하는 프로세스로 작업하시는 것을 고려 해보시기 바랍니다.
Reference
Reference URL
• dev.mysql.com/5.6/partitioning-management-exchange
• mysql.com/alter-table-partition-operations
연관된 글
파티션 테이블의 종류와 특징Mysql에서는 4가지 파티션 종류를 지원합니다
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
