파티션 테이블 운영 중 필요한 작업에 대하여 영향도 테스트를 진행해보았습니다.
ALGORITHM = INSTANT DDL 이후 EXCHANGE PARTITION
CREATE TABLE minjae_partition_test (
partition_seq BIGINT NOT NULL AUTO_INCREMENT,
dummy_data1 varchar(100),dummy_data2 varchar(100),
created_at datetime(3),
PRIMARY KEY (partition_seq, created_at)
)PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-02-01')),
PARTITION p20250201 VALUES LESS THAN (TO_DAYS('2025-03-01')),
PARTITION p20250301 VALUES LESS THAN (TO_DAYS('2025-04-01')),
PARTITION p20250401 VALUES LESS THAN (TO_DAYS('2025-05-01')),
PARTITION p20250501 VALUES LESS THAN (TO_DAYS('2025-06-01')),
PARTITION p20250601 VALUES LESS THAN (TO_DAYS('2025-07-01')),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE minjae_partition_test_tmp (
partition_seq BIGINT NOT NULL AUTO_INCREMENT,
names varchar(10),
created_at datetime(3),
PRIMARY KEY (partition_seq, created_at)
);
--EXCHANGE 정상 수행
ALTER TABLE minjae_partition_test EXCHANGE PARTITION p20250101 WITH TABLE minjae_partition_test_tmp;
--INSTANT DDL 수행
alter table minjae_partition_test add column test varchar(1);
--EXCHANGE 에러 발생 (테이블 컬럼이 다름)
ALTER TABLE minjae_partition_test EXCHANGE PARTITION p20250201 WITH TABLE minjae_partition_test_tmp WITHOUT VALIDATION;
ERROR 1736 (HY000): Tables have different definitions
--TMP 테이블에도 INSTANT DDL 수행
alter table minjae_partition_test_tmp add column test varchar(1);
--EXCHANGE 에러 발생 (INSTANT DDL이 수행된 컬럼이 있는 에러)
ALTER TABLE minjae_partition_test EXCHANGE PARTITION p20250201 WITH TABLE minjae_partition_test_tmp WITHOUT VALIDATION;
1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table
--INPLACE DDL 이후에는 EXCHANGE 정상 수행 됨
alter table minjae_partition_test_tmp add column test2 varchar(1), algorithm = inplace;
왜 안될까?
문서 상에는 INSTANT DDL 이후 EXCHANGE 구문 자체 사용이 불가능하다는 내용이 있었고
코드를 기반으로 좀 더 확인해보았습니다.
--해당 에러는 향후 수정 계획은 없음
https://bugs.mysql.com/bug.php?id=104970
--아래 코드를 확인하였을 때 INSTANT DDL 수행 이력이 있으면 항상 에러가 발생함
handler0alter.cc
ha_innopart::exchange_partition_low()
...
if (dd_table_has_instant_cols(*part_table) ||
dd_table_has_instant_cols(*swap_table)) {
my_error(ER_PARTITION_EXCHANGE_DIFFERENT_OPTION, MYF(0),
"INSTANT COLUMN(s)");
return true;
}
DROP PARTITION
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| minjae_test | minjae_partition_table | p20240901 | 739495 | 131633040 | 52208.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20241001 | 739525 | 128811723 | 52240.98 | 6133.97 |
| minjae_test | minjae_partition_table | p20241101 | 739556 | 127656761 | 52238.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20241201 | 739586 | 131258843 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250101 | 739617 | 124008978 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250201 | 739648 | 131054810 | 52234.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250301 | 739676 | 130446226 | 52240.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250401 | 739707 | 128344879 | 52253.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250501 | 739737 | 120194090 | 52184.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250601 | 739768 | 131478774 | 52204.00 | 6133.97 |
| minjae_test | minjae_partition_table | pmax | MAXVALUE | 381795479 | 153998.00 | 18398.00 |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
--p20241201에 해당하는 데이터 입력하면서 테스트 진행
--데이터 입력 대상이 아닌 파티션 빠르게 처리됨
MySQL [minjae_test]> ALTER TABLE minjae_partition_table DROP PARTITION p20240901;
Query OK, 0 rows affected (0.070 sec)
Records: 0 Duplicates: 0 Warnings: 0
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| minjae_test | minjae_partition_table | p20241001 | 739525 | 128811723 | 52240.98 | 6133.97 |
| minjae_test | minjae_partition_table | p20241101 | 739556 | 127656761 | 52238.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20241201 | 739586 | 131260636 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250101 | 739617 | 124008978 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250201 | 739648 | 131054810 | 52234.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250301 | 739676 | 130446226 | 52240.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250401 | 739707 | 128344879 | 52253.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250501 | 739737 | 120194090 | 52184.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250601 | 739768 | 131478774 | 52204.00 | 6133.97 |
| minjae_test | minjae_partition_table | pmax | MAXVALUE | 381795479 | 153998.00 | 18398.00 |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
--데이터 입력 대상이 되는 파티션 빠르게 처리됨
MySQL [minjae_test]> ALTER TABLE minjae_partition_table DROP PARTITION p20241201;
Query OK, 0 rows affected (0.065 sec)
Records: 0 Duplicates: 0 Warnings: 0
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| minjae_test | minjae_partition_table | p20241001 | 739525 | 128811723 | 52240.98 | 6133.97 |
| minjae_test | minjae_partition_table | p20241101 | 739556 | 127656761 | 52238.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250101 | 739617 | 124010328 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250201 | 739648 | 131054810 | 52234.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250301 | 739676 | 130446226 | 52240.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250401 | 739707 | 128344879 | 52253.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250501 | 739737 | 120194090 | 52184.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250601 | 739768 | 131478774 | 52204.00 | 6133.97 |
| minjae_test | minjae_partition_table | pmax | MAXVALUE | 381795479 | 153998.00 | 18398.00 |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
--크기가 큰 파티션도 빠르게 삭제됨
MySQL [minjae_test]> ALTER TABLE minjae_partition_table DROP PARTITION pmax;
Query OK, 0 rows affected (0.076 sec)
Records: 0 Duplicates: 0 Warnings: 0
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_SIZE(MB) | INDEX_SIZE(MB) |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
| minjae_test | minjae_partition_table | p20241001 | 739525 | 128811723 | 52240.98 | 6133.97 |
| minjae_test | minjae_partition_table | p20241101 | 739556 | 127656761 | 52238.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250101 | 739617 | 124008978 | 52187.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250201 | 739648 | 131054810 | 52234.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250301 | 739676 | 130446226 | 52240.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250401 | 739707 | 128344879 | 52253.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250501 | 739737 | 120194090 | 52184.00 | 6133.97 |
| minjae_test | minjae_partition_table | p20250601 | 739768 | 131478774 | 52204.00 | 6133.97 |
+--------------+------------------------+----------------+-----------------------+------------+---------------+----------------+
-- 테이블 크기, 파티션 사용 유무와 상관 없이 비슷하게 매우 짧은 시간이 소요됨
일반적으로 테이블(파티션) 사이즈가 클 수록 DROP 구문의 경우 처리 시간이 증가하여야 하는데 여러 상황에서 성능이 일관적으로 보여 해당 내용에 대해 좀 더 확인해보았습니다.
Aurora 클러스터 볼륨의 특징
클러스터 볼륨은 데이터를 동적 크기 조정이라는 방식으로 관리합니다. 동적 크기 조정 동작은 기본 테이블스페이스가 삭제되거나 공간이 더 적게 필요하도록 재구성할 때 발생합니다.
동적 크기 조정은 클러스터 볼륨 내의 테이블스페이스를 물리적으로 제거하거나 크기를 조정하는 작업에 적용됩니다. 따라서 DROP TABLE, DROP DATABASE, TRUNCATE TABLE 및 ALTER TABLE ... DROP PARTITION과 같은 SQL 문에 적용됩니다.
DELETE 문을 사용하여 행을 삭제하는 경우에는 적용되지 않습니다. 테이블에서 많은 수의 행을 삭제하는 경우 이후에 Aurora MySQL OPTIMIZE TABLE 확장이나 Aurora PostgreSQL pg_repack 문을 실행해 테이블을 재구성하여 클러스터 볼륨의 크기를 동적으로 조정할 수 있습니다.
동적 크기 조정 기능은 테이블스페이스의 테이블이 삭제될 때 즉시 공간을 회수하는 것이 아니라 하루에 약 10TB의 속도로 공간을 점진적으로 확보합니다.
→ 파티션 삭제 쿼리가 논리적으로 완료된 후 실제 물리적 데이터 정리는 스토리지 계층에서 백그라운드로 진행됩니다. 이 과정은 서비스 성능에 영향을 주지 않도록 하루 약 10TB의 속도로 점진적으로 확보됩니다.
| 데이터베이스 엔진 | 동적 크기 조정 기능이 있는 버전 |
| Aurora MySQL |
|
| Aurora PostgreSQL | 지원되는 모든 버전 |
중간 위치 파치션 삭제
CREATE TABLE test_1 (
id int NOT NULL AUTO_INCREMENT,
col1 varchar(100),
createdAt datetime(6) NOT NULL,
PRIMARY KEY (id,createdAt)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
partition by range (to_days(createdAt))
(
partition test_1_202501 values less than (to_days('2025-01-01')),
partition test_1_202502 values less than (to_days('2025-02-01')),
partition test_1_202503 values less than (to_days('2025-03-01')),
partition test_1_202504 values less than (to_days('2025-04-01')),
partition test_1_202505 values less than (to_days('2025-05-01')),
partition test_1_202506 values less than (to_days('2025-06-01'))
);
CREATE TABLE test_2 (
id int NOT NULL AUTO_INCREMENT,
col1 varchar(100),
createdAt datetime(6) NOT NULL,
PRIMARY KEY (id,createdAt)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(createdAt)
(
PARTITION test_2_202501 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION test_2_202502 VALUES LESS THAN ('2025-02-01') ENGINE = InnoDB,
PARTITION test_2_202503 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION test_2_202504 VALUES LESS THAN ('2025-04-01') ENGINE = InnoDB,
PARTITION test_2_202505 VALUES LESS THAN ('2025-05-01') ENGINE = InnoDB,
PARTITION test_2_202506 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB);
insert into test_1 (col1,createdAt) values ('1','2025-01-01 00:00:00');
insert into test_1 (col1,createdAt) values ('2','2025-01-31 00:00:00');
insert into test_1 (col1,createdAt) values ('3','2025-02-01 00:00:00');
insert into test_1 (col1,createdAt) values ('4','2025-02-28 00:00:00');
insert into test_1 (col1,createdAt) values ('5','2025-03-01 00:00:00');
insert into test_1 (col1,createdAt) values ('6','2025-03-31 00:00:00');
insert into test_1 (col1,createdAt) values ('8','2025-04-01 00:00:00');
insert into test_1 (col1,createdAt) values ('9','2025-04-30 00:00:00');
insert into test_1 (col1,createdAt) values ('10','2025-05-01 00:00:00');
insert into test_1 (col1,createdAt) values ('11','2025-05-31 00:00:00');
insert into test_2 (col1,createdAt) values ('1','2025-01-01 00:00:00');
insert into test_2 (col1,createdAt) values ('2','2025-01-31 00:00:00');
insert into test_2 (col1,createdAt) values ('3','2025-02-01 00:00:00');
insert into test_2 (col1,createdAt) values ('4','2025-02-28 00:00:00');
insert into test_2 (col1,createdAt) values ('5','2025-03-01 00:00:00');
insert into test_2 (col1,createdAt) values ('6','2025-03-31 00:00:00');
insert into test_2 (col1,createdAt) values ('8','2025-04-01 00:00:00');
insert into test_2 (col1,createdAt) values ('9','2025-04-30 00:00:00');
insert into test_2 (col1,createdAt) values ('10','2025-05-01 00:00:00');
insert into test_2 (col1,createdAt) values ('11','2025-05-31 00:00:00');
1:38
MySQL > alter table test_1 drop partition test_1_202503;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL > alter table test_2 drop partition test_2_202503;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL > select * from test_1;
+----+------+----------------------------+
| id | col1 | createdAt |
+----+------+----------------------------+
| 1 | 1 | 2025-01-01 00:00:00.000000 |
| 2 | 2 | 2025-01-31 00:00:00.000000 |
| 5 | 5 | 2025-03-01 00:00:00.000000 |
| 6 | 6 | 2025-03-31 00:00:00.000000 |
| 7 | 8 | 2025-04-01 00:00:00.000000 |
| 8 | 9 | 2025-04-30 00:00:00.000000 |
| 9 | 10 | 2025-05-01 00:00:00.000000 |
| 10 | 11 | 2025-05-31 00:00:00.000000 |
+----+------+----------------------------+
8 rows in set (0.03 sec)
MySQL > select * from test_2
-> ;
+----+------+----------------------------+
| id | col1 | createdAt |
+----+------+----------------------------+
| 1 | 1 | 2025-01-01 00:00:00.000000 |
| 2 | 2 | 2025-01-31 00:00:00.000000 |
| 5 | 5 | 2025-03-01 00:00:00.000000 |
| 6 | 6 | 2025-03-31 00:00:00.000000 |
| 7 | 8 | 2025-04-01 00:00:00.000000 |
| 8 | 9 | 2025-04-30 00:00:00.000000 |
| 9 | 10 | 2025-05-01 00:00:00.000000 |
| 10 | 11 | 2025-05-31 00:00:00.000000 |
+----+------+----------------------------+
8 rows in set (0.03 sec)
신규 파티션 추가
MySQL [minjae_test]> show create table minjae_partition_table\G
*************************** 1. row ***************************
Table: minjae_partition_table
Create Table: CREATE TABLE `minjae_partition_table` (
`partition_seq` bigint NOT NULL AUTO_INCREMENT,
`names` varchar(10) DEFAULT NULL,
`dummy_data1` varchar(100) DEFAULT NULL,
`dummy_data2` varchar(100) DEFAULT NULL,
`created_at` datetime(3) NOT NULL,
PRIMARY KEY (`partition_seq`,`created_at`),
KEY `minjae_partition_test_idx1` (`dummy_data1`)
) ENGINE=InnoDB AUTO_INCREMENT=158721890 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`created_at`))
(PARTITION p20250201 VALUES LESS THAN (739676) ENGINE = InnoDB,
PARTITION p20250301 VALUES LESS THAN (739707) ENGINE = InnoDB,
PARTITION p20250401 VALUES LESS THAN (739737) ENGINE = InnoDB,
PARTITION p20250501 VALUES LESS THAN (739768) ENGINE = InnoDB,
PARTITION p20250601 VALUES LESS THAN (739798) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.002 sec)
MySQL [minjae_test]> ALTER TABLE minjae_partition_table ADD PARTITION (PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-12-01')));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
MySQL [minjae_test]> ALTER TABLE minjae_partition_table ADD PARTITION (PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-11-01')));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
MySQL [minjae_test]> ALTER TABLE minjae_partition_table REORGANIZE PARTITION pmax INTO (
PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-02-01'))
, PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2025-01-01'))
, PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-12-01'))
, PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-11-01'))
, PARTITION pmax VALUES LESS THAN (MAXVALUE) );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
MySQL [minjae_test]> ALTER TABLE minjae_partition_table REORGANIZE PARTITION p20250201 INTO (
PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-02-01'))
, PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2025-01-01'))
, PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-12-01'))
, PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-11-01')));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
MySQL [minjae_test]> ALTER TABLE minjae_partition_table REORGANIZE PARTITION p20250201 INTO (
PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-10-01'))
, PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-11-01'))
, PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-01'))
, PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-01-01')));
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
MySQL [minjae_test]> ALTER TABLE minjae_partition_table REORGANIZE PARTITION p20250201 INTO (
PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-10-01'))
, PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-11-01'))
, PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-01'))
, PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-01-01'))
, PARTITION p20250201 VALUES LESS THAN (TO_DAYS('2025-02-01')));
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
MySQL [minjae_test]> ALTER TABLE minjae_partition_table
PARTITION BY RANGE (to_days(created_at)) (
, PARTITION p20241001 VALUES LESS THAN (TO_DAYS('2024-10-01'))
, PARTITION p20241101 VALUES LESS THAN (TO_DAYS('2024-11-01'))
, PARTITION p20241201 VALUES LESS THAN (TO_DAYS('2024-12-01'))
, PARTITION p20250101 VALUES LESS THAN (TO_DAYS('2025-01-01'))
, PARTITION p20250201 VALUES LESS THAN (TO_DAYS('2025-02-01'))
, PARTITION p20250301 VALUES LESS THAN (TO_DAYS('2025-03-01'))
, PARTITION p20250401 VALUES LESS THAN (TO_DAYS('2025-04-01'))
, PARTITION p20250501 VALUES LESS THAN (TO_DAYS('2025-05-01'))
, PARTITION p20250601 VALUES LESS THAN (TO_DAYS('2025-06-01'))
, PARTITION pmax VALUES LESS THAN MAXVALUE
);
--> 가장 오래된 날짜의 파티션을 추가하기 위해선 전체 파티션 재구성이 필요
REORGANIZE
+-----------------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+-----------------------+----------------+------------+
| minjae_partition_test | p20250201 | 13075578 |
| minjae_partition_test | p20250301 | 13075155 |
| minjae_partition_test | p20250401 | 12760526 |
| minjae_partition_test | p20250501 | 13076237 |
| minjae_partition_test | p20250601 | 13076522 |
| minjae_partition_test | pmax | 26153891 |
+-----------------------+----------------+------------+
6 rows in set (0.008 sec)
-- REORGANIZE 작업 간 MetadataLock으로 인한 DML LOCK 발생
ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected (4 min 30.949 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [(none)]> select * from performance_schema.processlist;
JECT_NAME AS `ta+------+-----------------+--------------------+-------------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | EXECUTION_ENGINE |
+------+-----------------+--------------------+-------------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| 276 | dba_minjaeko | 172.31.1.176:37686 | minjae_test | Query | 239 | altering table | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) | PRIMARY |
| 1427 | dba_minjaeko | 172.31.1.176:39068 | NULL | Query | 0 | executing | select * from performance_schema.processlist | PRIMARY |
| 1072 | dba_minjaeko | 172.31.1.176:32960 | minjae_test | Query | 175 | Waiting for table metadata lock | insert into minjae_partition_test (names,created_at) values ('7월', '2025-07-01 00:01:00') | PRIMARY |
+------+-----------------+--------------------+-------------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
+--------+-----------------------+-------+-----------+-----------+-------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trx_id | table | index | lock_type | lock_mode | lock_status | thread_id | query |
+--------+-----------------------+-------+-----------+-----------+-------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 543075 | minjae_partition_test | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
| 543075 | minjae_partition_test | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
| 543075 | SDI_34 | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
| 543075 | SDI_35 | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
| 543075 | tablespace_files | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
| 543075 | tablespaces | NULL | TABLE | IX | GRANTED | 304 | ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250801 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) ) |
+--------+-----------------------+-------+-----------+-----------+-------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MySQL [minjae_test]> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = 'minjae_test';
+-----------------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+-----------------------+----------------+------------+
| minjae_partition_test | p20250401 | 12249480 |
| minjae_partition_test | p20250501 | 12565632 |
| minjae_partition_test | p20250601 | 12534912 |
| minjae_partition_test | p20250801 | 12746640 |
| minjae_partition_test | pmax | 0 |
+-----------------------+----------------+------------+
--REORGANIZE 이후 ANALYZE 작업을 해주지 않으면 파티션 건수가 정확하게 보이지 않음
MySQL [minjae_test]> ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20250901 VALUES LESS THAN (TO_DAYS('2025-09-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected (2 min 13.803 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [minjae_test]> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = 'minjae_test';
+-----------------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+-----------------------+----------------+------------+
| minjae_partition_test | p20250401 | 13075200 |
| minjae_partition_test | p20250501 | 12746617 |
| minjae_partition_test | p20250601 | 13075200 |
| minjae_partition_test | p20250801 | 12746617 |
| minjae_partition_test | p20250901 | 13075200 |
| minjae_partition_test | pmax | 0 |
+-----------------------+----------------+------------+
6 rows in set (0.002 sec)
--0건일 때는 빠르게 처리되는 모습
MySQL [minjae_test]> ALTER TABLE minjae_partition_test REORGANIZE PARTITION pmax INTO (PARTITION p20251001 VALUES LESS THAN (TO_DAYS('2025-10-01')), PARTITION pmax VALUES LESS THAN (MAXVALUE) );
Query OK, 0 rows affected (0.097 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [minjae_test]> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = 'minjae_test';
+-----------------------+----------------+------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |
+-----------------------+----------------+------------+
| minjae_partition_test | p20250401 | 13075200 |
| minjae_partition_test | p20250501 | 12746617 |
| minjae_partition_test | p20250601 | 13075200 |
| minjae_partition_test | p20250801 | 12746617 |
| minjae_partition_test | p20250901 | 13075200 |
| minjae_partition_test | p20251001 | 0 |
| minjae_partition_test | pmax | 0 |
+-----------------------+----------------+------------+'공부 > DATABASE' 카테고리의 다른 글
| [Aurora, Aws DocumentDB] I/O Optimized (0) | 2026.01.24 |
|---|---|
| [Aurora PostgreSQL] VACUUM 이후 일시적인 입력 지연 (0) | 2026.01.18 |
| [ElastiCache] Redis CrossSlot 에러 (0) | 2026.01.18 |
| [PostgreSQL] GIN 인덱스 특징 (0) | 2026.01.18 |
| [PostgreSQL, MySQL] ADD COLUMN Default (0) | 2026.01.04 |