MySQL partition이란

1. 개요

MySQL에서 Partition 적용 방법에 대해서 다루려고 한다.

 

2. 환경

- InnoDB

- MySQL 8.x

- Docker environment

 

3. Partition이란?

• table 하나를 물리적으로 나누어서 사용하는 방식이다.
• 물리적으로 하나의 테이블에 있는 row들을 분할함으로써 각각의 index size를 줄이기 때문에, 특정 partition에 접근하는 select나 insert등의 성능이 좋아지게 된다.
• type은 아래와 같다.
  • range
    • row 단위로 ~N 의 범위를 잘라서 사용하는 방식이다.
    • 주로 timestamp를 나누어서 사용하거나 id 기반으로 나눌 때 사용한다.
  • list
    • IN query와 유사하게 값에 따라서 나누어서 사용하는 방식이다.
    • 예제에서는 지역 정보를 통해서 IN('Seoul', 'Busan') 등으로 나누는 것을 볼 수 있다.
  • hash
    • hash function 기반으로 partition을 나눌 수 있다.
    • 단순히 hash function에 들어갈 값과, partition 갯수를 지정해서 나눌 수 있다.
    • 기본적으로 mod 연산을 통해 나누고 있으며, MOD(partition_key, num_of_partition) 연산을 수행한다.
    • 추가적으로 LINEAR HASH partition을 제공한다.
  • key
    • 기본적으로 hash와 유사하나, hash는 user가 지정한 방식을 썼다면, key partitioning은 mysql에서 제공하는 hash function을 사용하는 방식으로 동작한다.
    • 기본적으로 NDS는 MD5()를 사용하고, MySQL server는 내장된 hash function을 사용한다.

 

4. 왜 Partition인가?

• 기존의 index를 통해서도 해결 못하는 문제가 발생시

  • index는 태울 경우 성능이 높아질 수 있으나 만약에 사이즈가 커진다면 index 자체를 update 할 때의 비용으로 인해서 insert 성능이 낮아질 수 있다.
  • 또한 index tree 자체가 커진다면 select에도 성능의 저하가 발생할 수 있다.
  • partition은 index를 partition 별로 생성을 하기 때문에, 특정 partition에서 select, insert 등을 수행할 때 성능이 증가할 수 있다. ( partition pruning )
  • 또한 히스토리 데이터는 최근 날짜에 접근하는 경우가 많은 경우가 있다. 이 때 불필요한 과거의 데이터까지 인덱스에 포함이 되어있다면 성능저하의 원인이 될 수 있다.

• 가능한 한 shard를 하지 않으면서 성능의 향상을 꾀하고 싶을 때,

  • shard의 경우 성능 이슈의 좋은 해결책이 될 수 있다.
  • 하지만 shard의 경우 자체적으로 shard에 대한 로직을 application에서 구성할 필요성이 있다.
  • 또한 해당 구현을 application에서 진행해야 되고, 이에 따라 application logic의 복잡도가 늘어날 가능성이 존재한다.

5. 그렇다면 Partition은 만능인가?

5.1 Alter는 여전히 느리다.

• alter의 경우는 mysql 5.6에서 online DDL operation을 지원을 한다.
• 하지만 로직에 따라 다른 algorithm을 택하고 있고, add column의 경우에는 내부적으로 copy table 형태로 지원이 되기 때문에 느리다.
• mysql 8.0.12 버전의 경우에는 INSTANT algorithm이 추가되었는데, 해당 부분은 데이터를 건드리는 것이 아닌 메타데이터 스킴만 바꿈으로써 빠른 속도를 지원한다. 다만 지원되는 DDL operation에 대해 제약 조건이 있으므로( 예를 들어 ADD COLUMN의 경우에는 맨 마지막 칼럼에 추가하는 것만 지원이 되는 제약 조건 + a가 있다. ) 하단의 출처에서 살펴 보는 것이 좋다.

5.2 Repartition의 경우 드는 비용 고려가 필요하다.

• Partition의 종류에 따라 다르지만, partition의 추가 삭제가 자유롭지 않는 partition의 종류일 경우 (hash, key 등등) partition을 재분배 할 때, alter table을 통해서 재구성 해야만 한다.
• 이 때 Online DDL 항목을 참조해서 online이 가능한 수행인지 살펴보아야 한다.
• Partition ADD가 가능한 경우에는 기존의 partition된 데이터들의 재구성이 필요없으므로 빠른 연산이 가능할 수도 있다.

6. 실험

6.1 준비

우선적으로 실험하고자 하는 Partition의 종류가 LIST와 HASH였다.

# without partition
CREATE TABLE `partition_test_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`test_id`)
);

# LIST with MAXVALUE
CREATE TABLE `partition_test_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`test_id`)
) PARTITION BY RANGE  COLUMNS(test_id)
(PARTITION p1 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
 
 # LIST
 CREATE TABLE `partition_test_2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`test_id`)
) PARTITION BY RANGE  COLUMNS(test_id)
(PARTITION p1 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (30000) ENGINE = InnoDB);
 
 # HASH
CREATE TABLE `partition_test_3` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`test_id`)
) PARTITION BY HASH(test_id)
PARTITIONS 3;

이렇게 아래와 같이 분리된 파일이 생성된 모습을 볼 수 있다.

데이터는 아래와 같이 stored procedure를 셋팅 후 셋업하였다.

drop procedure prepare_data;

DELIMITER $$

CREATE PROCEDURE prepare_data(IN tableName VARCHAR(50), IN maxCount int)

BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i < maxCount DO
    SET @sql = CONCAT('INSERT INTO ', tableName, ' (test_id) VALUES (',i,');');
    PREPARE s1 from @sql;
    EXECUTE s1;
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;


call prepare_data('partition_test_0', 30000);
call prepare_data('partition_test_1', 30000);
call prepare_data('partition_test_2', 30000);
call prepare_data('partition_test_3', 30000);

 

 

현재는 Row수가 작고, 또한 local 환경에서 test를 진행하기 때문에, 성능상의 쿼리는 하지 않을 예정이다.

 

6.2 단일 row select시

다음과 같은 query로 partition pruning이 실제로 되는지 확인해보면 아래와 같이 partitions의 값이 포함되어 있는 것을 볼 수 있다.

explain select * from partition_test_1 where test_id = 30;

6.3 range query시

아래와 같이 range query에서는 속한 partition에서 수행되는 것을 확인할 수 있다.

explain select * from partition_test_1 where 0 <= test_id AND test_id <= 15000;

 

하지만 hash의 경우 아래와 같이 partition이 모두 선택되는 것을 볼 수 있다. hash로 인해서 고루 분배되어 있기 때문이다.

 

explain select * from partition_test_3 where 0 <= test_id AND test_id <= 15000;

 

 

6.4 insert시 (MAXVALUE 설정 및 미설정 비교)

다음과 같이 MAX value를 range로 설정한 table에서는 해당 값이 문제없이 들어가는 것을 확인할 수 있다.

insert into partition_test_1(test_id) values (999999)

 

하지만 아래와 같이 MAX value를 설정하지 않은 partition의 경우 다음과 같은 에러를 확인할 수 있다.

insert into partition_test_2(test_id) values (999999)

 

+ 추가글 ( HASH의 경우에는 runtime 시점에 partition이 결정될 수 있어서, 실제 explain에서는 partition 정보가 안나오는 경우를 확인할 수 있다. )

6.5 ADD partition

아래과 같이 MAXVALUE가 설정된 ADD partition을 할 경우 다음과 같은 에러가 발생한다.

ALTER TABLE partition_test_1
    ADD PARTITION ( PARTITION p999 VALUES LESS THAN (100000));

 

하지만 MAXVALUE를 설정하지 않은 경우에는 손쉽게 PARTITION 추가가 RANGE로 가능하다.

ALTER TABLE partition_test_2
    ADD PARTITION ( PARTITION p999 VALUES LESS THAN (100000));

 

그리고 당연하겠지만 hash partition의 경우 다음과 같은 에러가 잡힌다.

ALTER TABLE partition_test_3
    ADD PARTITION ( PARTITION p999 VALUES LESS THAN (100000));

6.6 Reorganize Partition (HASH, LIST with MAXVALUE)

위의 HASH와 LIST에 MAXVALUE를 설정한 테이블의 경우에는 REORGANIZE 또는 PARTITION을 다시 구성해야 될 수도 있다.

 

먼저 HASH의 경우 다음과 같이 PARTITION을 합치거나 재구성이 가능하다.

ALTER TABLE partition_test_3 COALESCE PARTITION 1
# [2020-12-19 23:44:54] completed in 461 ms

ALTER TABLE partition_test_3 PARTITION BY HASH ( test_id ) PARTITIONS 10
# [2020-12-19 23:45:25] 29,999 rows affected in 578 ms

 

그리고 LIST의 MAXVALUE를 설정한 경우 아래와 같이 나눠줄 수 있다.

 

# split
ALTER TABLE partition_test_1 REORGANIZE PARTITION p4 INTO (
    PARTITION p4 VALUES LESS THAN (40000),
    PARTITION p5 VALUES LESS THAN (50000),
    PARTITION p6 VALUES LESS THAN MAXVALUE
)

# merge
ALTER TABLE partition_test_1 REORGANIZE PARTITION p4, p5, p6 INTO (
    PARTITION p456 VALUES LESS THAN MAXVALUE
);

# split with exist values
ALTER TABLE partition_test_1 REORGANIZE PARTITION p2 INTO (
    PARTITION p2 VALUES LESS THAN (15000),
    PARTITION p21 VALUES LESS THAN (20000)
)

 

참고로 해당 부분이 메타데이터에 바로 갱신이 되지 않을 수 있는데, 이는 ANALYZE TABLE partition_test_0;을 통해서 업데이트가 되게 할 수 있다. 하지만 이는 성능상에 문제가 생길수도 있으므로 매 쿼리마다 메타데이터를 갱신하지는 말아야한다.

SELECT DISTINCT * FROM INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME like '%partition_test%'
ANALYZE TABLE  partition_test_0;

 

6.7 Remove partition

아래와 같이 특정 파티션을 제거할 수 있다.

하지만 다만 partition을 즉시 제거하는 것은 문제가 될 수 있으므로 partition exchange를 통해서 다른 table에 옮긴 후 작업을 진행하는 것이 좋아보인다.

 

그리고 TRUNCATE 와 DROP PARTITION이 존재하는데, TRUNCATE는 partition 목록에서만 제외가 되고, 실제 row는 삭제하지 않는다.

하지만 DROP은 partition 파일 자체를 삭제한다.

 

마지막으로 partition exchange 기능이 있다. partition을 말그대로 교환하는 형태이며 반드시 target table은 partitioning이 disable이 되어 있어야 한다.

# 1. remove partition p21 from list
ALTER TABLE partition_test_1 TRUNCATE PARTITION p21;

# 2. remove partition file
ALTER TABLE partition_test_1 DROP PARTITION p2;


# 3. partition exchange
# 3.1. create new table
CREATE TABLE partition_test_1_new like partition_test_1;

# 3.2. exchange partition should disable target table partitioning
alter table partition_test_1_new remove partitioning;

# 3.3 partition exchange
alter table partition_test_1 exchange partition p1 with table partition_test_1_new;

# 3.4 result
SELECT count(*) from partition_test_1 PARTITION (p1); # 0
SELECT count(*) from partition_test_1_new; # 9999

7. 마치며

 해당 부분을 작성하면서 mysql 버전별 동작 차이나, 내부 구조 등을 좀 더 알아갈 수 있어서 좋았다. 또한 각각의 partition 방식에 대한 이해나 활용에 대해서 좀 더 고민해 볼 수 있는 시간을 가져볼 수 있었다. 하지만 이번에는 동작에 초점을 맞추었고, 대용량으로 테스트를 해서 실제 insert와 select의 저하되는 지점 등등을 테스트 할 수 있었으면 좋았을텐데라는 아쉬움은 있다. 이런 부분들은 추후에 다시 보고 테스트를 해보아야겠다. 또한 partition limit글을 좀 더 살펴보고, 제약사항들도 글을 써봐야겠다.

 

8. 출처

table meta inf refresh - stackoverflow.com/questions/53855219/mysql-not-updating-information-schema-unless-i-manually-run-analyze-table-myta

MySQL not updating information_schema, unless I manually run ANALYZE TABLE `myTable`

partition exchange - m.blog.naver.com/PostView.nhn?blogId=theswice&logNo=221167500999&proxyReferer=https:%2F%2Fwww.google.com%2F

partition exchange(mysql 5.6)

8.0 버전 online ddl operation - dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

MySQL :: MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations

5.6 버전 online ddl operation - dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

MySQL :: MySQL 5.6 Reference Manual :: 14.13.1 Online DDL Operations

aws.amazon.com/ko/blogs/database/planning-and-optimizing-amazon-aurora-with-mysql-compatibility-for-consolidated-workloads/

How to plan and optimize Amazon Aurora with MySQL compatibility for consolidated workloads | Amazon Web Services

partition limitation - dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html