최신 리포트 다운로드 지금 바로 문의하기
최신 리포트 다운로드 지금 바로 문의하기

[MySQL] Partition add & drop 자동화 테스트

BlogTechTODAY'S PICKS

[MySQL] Partition add & drop 자동화 테스트

TECH

by Sangmi Park

1. Partition Drop

월 별 혹은 년도 별 Partition Table을 관리할 때, 과거 파티션을 삭제하는 과정을 크게 두 가지의 방법으로 테스트해 보았습니다. 

해당 테스트를 진행한 이유는 두 가지 방법에 대해 효용성을 체크하기 위함이며, 
alter drop partition 보다 exchange partition이 수행 속도가 빠르다면 Partition Table에 걸리는 Lock을 최소화하여 서비스를 원활하게 할 수 있지 않을까라는 기대를 예상했습니다.

첫 번째는 별도의 일반 테이블(0건)을 만들고, Drop할 파티션과 Exchange를 한 후 일반 테이블을 삭제하는 방법입니다. 
이 경우 일반 테이블에 과거 테이블을 이력으로도 남길 수 있다는 장점이 있습니다. 

두 번째는 대상 Partition Table에 ALTER DROP PARTITION 을 수행하여 바로 DDL을 날리는 과정이며, online DDL을 수행하는 과정에서 DML을 따로 받을 수 있습니다.

1-1. TEST 환경구성

항목설 명

Database

RDS MySQL 8.0.23

Sysbench

[ec2-user@ip-10-0-130-172 ~]$ sysbench –version

sysbench 1.0.20

Test Table

1. Description

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);

2. Data 적재

Sysbench를 이용해 sbtest1 테이블에 1억건 적재

sysbench –db-driver=mysql –mysql-host=bjh-test-partition.cnaj6ucovzx2.ap-northeast-2.rds.amazonaws.com –mysql-user=admin –mysql-password=qkswlgus –mysql-db=sysbench –table-size=100000000 –tables=1 /usr/share/sysbench/oltp_read_only.lua prepare

1-2. Exchange Partition Test

시나리오

설 명

1) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+——————+———————–+————+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS |

+————–+————+—————-+—————————-+——————+———————–+————+

| sysbench     | sbtest1    | p2             |                          1 | RANGE            | 60000000              |   29592144 |

| sysbench     | sbtest1    | p3             |                          2 | RANGE            | 90000000              |   29592576 |

| sysbench     | sbtest1    | p4             |                          3 | RANGE            | 120000000             |    9863928 |

+————–+————+—————-+—————————-+——————+———————–+————+

2) 원본 테이블 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 |

+————–+————+—————-+————+

| sysbench     | sbtest1    | p2             |   29592144 |

| sysbench     | sbtest1    | p3             |   29592576 |

| sysbench     | sbtest1    | p4             |    9863928 |

+————–+————+—————-+————+

4 rows in set (0.01 sec)

 

 

3) Drop Partition 수행

mysql> alter table sbtest1 drop partition p2;

MySQL [sysbench]> alter table sbtest1 drop partition p2;

Query OK, 0 rows affected (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0

1-3. ALTER DROP Partition

시나리오

설 명

1) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+——————+———————–+————+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS |

+————–+————+—————-+—————————-+——————+———————–+————+

| sysbench     | sbtest1    | p2             |                          1 | RANGE            | 60000000              |   29592144 |

| sysbench     | sbtest1    | p3             |                          2 | RANGE            | 90000000              |   29592576 |

| sysbench     | sbtest1    | p4             |                          3 | RANGE            | 120000000             |    9863928 |

+————–+————+—————-+—————————-+——————+———————–+————+

2) 원본 테이블 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 |

+————–+————+—————-+————+

| sysbench     | sbtest1    | p2             |   29592144 |

| sysbench     | sbtest1    | p3             |   29592576 |

| sysbench     | sbtest1    | p4             |    9863928 |

+————–+————+—————-+————+

4 rows in set (0.01 sec)

 

 

3) Drop Partition 수행

mysql> alter table sbtest1 drop partition p2;

MySQL [sysbench]> alter table sbtest1 drop partition p2;

Query OK, 0 rows affected (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0

2. Partition Add

파티션 테이블에 maxvalue 파티션을 가지고 있을 경우와 없는 경우 add partition 을 할 수 있는 방법또한 달라집니다. maxvalue 파티션이 없을 경우에는 단순히 add partition이 가능하지만 있을 경우에는 add partition을 진행할 경우 Error Code : 1481이 발생하게 됩니다.

2-1. Maxvalue 파티션이 없을 경우

시나리오설 명

1) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+———————–+————+————–+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | INDEX_LENGTH |

+————–+————+—————-+—————————-+———————–+————+————–+

| sysbench     | sbtest1    | p3             |                          1 | 90000000              |   29592576 |    624902144 |

| sysbench     | sbtest1    | p4             |                          2 | 120000000             |    9863928 |    248266752 |

| sysbench     | sbtest1    | p5             |                          3 | 150000000             |          0 |        16384 |

+————–+————+—————-+—————————-+———————–+————+————–+

 

2) 파티션 추가

mysql> alter table sbtest1 add partition (partition p6 values less than (180000000));

MySQL [sysbench]> alter table sbtest1 add partition (partition p6 values less than (180000000));

Query OK, 0 rows affected (0.12 sec)

Records: 0  Duplicates: 0  Warnings: 0

3) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+———————–+————+————–+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | INDEX_LENGTH |

+————–+————+—————-+—————————-+———————–+————+————–+

| sysbench     | sbtest1    | p3             |                          1 | 90000000              |   29592576 |    624902144 |

| sysbench     | sbtest1    | p4             |                          2 | 120000000             |    9863928 |    248266752 |

| sysbench     | sbtest1    | p5             |                          3 | 150000000             |          0 |        16384 |

| sysbench     | sbtest1    | p6             |                          4 | 180000000             |          0 |        16384 |

+————–+————+—————-+—————————-+———————–+————+————–+

2-2. Maxvalue 파티션이 있을 경우

시나리오설 명

1) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+———————–+————+————–+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | INDEX_LENGTH |

+————–+————+—————-+—————————-+———————–+————+————–+

| sysbench     | sbtest1    | p3             |                          1 | 90000000              |   29592576 |    624902144 |

| sysbench     | sbtest1    | p4             |                          2 | 120000000             |    9863928 |    248266752 |

| sysbench     | sbtest1    | p5             |                          3 | 150000000             |          0 |        16384 |

| sysbench     | sbtest1    | p6             |                          4 | 180000000             |          0 |        16384 |

| sysbench     | sbtest1    | p7             |                          5 | MAXVALUE              |          0 |        16384 |

+————–+————+—————-+—————————-+———————–+————+————–+

 

2) 파티션 추가 (에러발생)

mysql> alter table sbtest1 add partition (partition p6 values less than (200000000));

MySQL [sysbench]> alter table sbtest1 add partition (partition p6 values less than (200000000));

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

3) REORGANIZE 파티션

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

MySQL [sysbench]>  ALTER TABLE sbtest1

    -> REORGANIZE PARTITION p7 into (

    ->   partition p7 values less than (200000000) engine=innodb,

    ->   PARTITION p8 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB

    -> );

Query OK, 0 rows affected (0.12 sec)

Records: 0  Duplicates: 0  Warnings: 0

4) 파티션테이블 상태 조회

mysql> select * from information_schema.partitions where table_schema=’sysbench’;

+————–+————+—————-+—————————-+———————–+————+————-+

| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH |

+————–+————+—————-+—————————-+———————–+————+————-+

| sysbench     | sbtest1    | p3             |                          1 | 90000000              |   29592576 |  6740246528 |

| sysbench     | sbtest1    | p4             |                          2 | 120000000             |    9863928 |  2248146944 |

| sysbench     | sbtest1    | p5             |                          3 | 150000000             |          0 |       16384 |

| sysbench     | sbtest1    | p6             |                          4 | 180000000             |          0 |       16384 |

| sysbench     | sbtest1    | p7             |                          5 | 200000000             |          0 |       16384 |

| sysbench     | sbtest1    | p8             |                          6 | MAXVALUE              |          0 |       16384 |

3. Partition 관리 자동화

신규 파티션 추가와 삭제를 매번 수작업으로 진행할 수는 없으므로 이를 자동으로 관리하기 위한 프로시저 및 이벤트를 생성하는 과정을 진행하였습니다. 파티션 관리 요건 중 가장 중요한 것은 다음의 세가지입니다.

1. 어떠한 이유에서든지 프로시저가 동작하지 않은 경우라도 데이터 Insert 실패가 발생하지 않도록 할 것.

2. 프로시저가 일정기간 동작하지 않고, 다시 재기동 되는 경우에 프로시저가 주기적으로 동작했던 것과 동일한 파티션 구조를 생성할 것.

3. 파티션 삭제시 Table Lock으로 인한 영향을 최소화할 것.

처음 이 파티션 테스트를 진행하기 전에는 파티션의 MAXVALUE가 테이블의 관리나 성능적인 측면에서 필요 없다고 생각하였으나, 1번의 내용과 같이 파티션 관리 프로시저가 며칠 동안 동작하지 않을 경우 서비스 자체가 운영되지 않을 수 있기 때문에 반드시 존재해야 한다고 제 개인적인 생각을 바꾸게 되었습니다. 또한, 앞서 테스트한 경우와 마찬가지로 파티션 drop은 단순한 alter문이 아닌 partition exchange로 진행하여 3번에 해당하는 Table Lock 최소화도 고려하였습니다.

3-1. Partition Create Procedure

Create Partition Procedure 문 (Terminal 수행을 위해 DELIMITER 변경 후 진행)

DELIMITER $$

CREATE PROCEDURE create_partition(p_dbname varchar(255), p_tbname varchar(255), p_future INT, p_interval INT)

SQL SECURITY INVOKER

BEGIN

   DECLARE x, max_pdesc, new_pdesc INT;

   DECLARE pname VARCHAR(64);

   DECLARE alter_cmd VARCHAR(1024);

  — 현재 테이블의 파티션의 가장 마지막 파티션의 HIGH VALUE 값을 구함

   SELECT MAX(PARTITION_DESCRIPTION) – TO_DAYS(current_date()) INTO x FROM information_schema.PARTITIONS

   WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION != ‘MAXVALUE’ ;

   — 파티션 미리 생성할 기한까지 지정한 interval에 맞도록 파티션 추가

   — 파티션 추가는 ADD PARTITION이 아닌 MAXVALUE 파티션의 REORGANIZE로 처리됨

   WHILE x <= p_future DO

      SELECT

         CONCAT(‘p’, DATE_FORMAT(current_date() + interval MAX(PARTITION_DESCRIPTION) – TO_DAYS(current_date()) day, ‘%Y%m%d’)),

         MAX(PARTITION_DESCRIPTION),

         MAX(PARTITION_DESCRIPTION) + p_interval

      INTO pname, max_pdesc, new_pdesc

      FROM information_schema.PARTITIONS

      WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!=’MAXVALUE’;

      IF max_pdesc < new_pdesc THEN

         SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘.’, p_tbname, ‘ REORGANIZE PARTITION pMAXVALUE INTO (‘,

                          ‘PARTITION ‘, pname, ‘ VALUES LESS THAN (‘, new_pdesc, ‘), PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE)’ );

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

      END IF;

      SET x = x + p_interval;

   END WHILE;

   — 파티션 정보 출력 (옵션)

   SELECT current_date() + interval MAX(PARTITION_DESCRIPTION) – TO_DAYS(current_date()) – 1 day AS Last_Date,

          COUNT(*) AS Partitions_For_Future

   FROM information_schema.PARTITIONS

   WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION != ‘MAXVALUE’

      AND PARTITION_DESCRIPTION > TO_DAYS(current_date()) + 1;

END $$

DELIMITER ;

3-1-1. Partition Create Procedure

시나리오설 명

1) 파티션테이블 생성

Range Partition Create 구문 (Maxvalue 파티션 필요)

CREATE TABLE partitiontest (

  id int NOT NULL AUTO_INCREMENT,

  some_data varchar(100),

  createdAt datetime(6) NOT NULL,

  modifiedAt datetime(6) DEFAULT NULL,

  PRIMARY KEY (id,createdAt)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

partition by range (to_days(createdAt))

(

partition p20221201 values less than (to_days(‘2022-12-02’)) ENGINE = InnoDB,

partition p20221202 values less than (to_days(‘2022-12-03’)) ENGINE = InnoDB,

partition p20221203 values less than (to_days(‘2022-12-04’)) ENGINE = InnoDB,

partition p20221204 values less than (to_days(‘2022-12-05’)) ENGINE = InnoDB,

partition p20221205 values less than (to_days(‘2022-12-06’)) ENGINE = InnoDB,

partition p20221206 values less than (to_days(‘2022-12-07’)) ENGINE = InnoDB,

partition p20221207 values less than (to_days(‘2022-12-08’)) ENGINE = InnoDB,

partition p20221208 values less than (to_days(‘2022-12-09’)) ENGINE = InnoDB,

partition p20221209 values less than (to_days(‘2022-12-10’)) ENGINE = InnoDB,

partition p20221210 values less than (to_days(‘2022-12-11’)) ENGINE = InnoDB,

partition p20221211 values less than (to_days(‘2022-12-12’)) ENGINE = InnoDB,

partition p20221212 values less than (to_days(‘2022-12-13’)) ENGINE = InnoDB,

partition p20221213 values less than (to_days(‘2022-12-14’)) ENGINE = InnoDB,

partition p20221214 values less than (to_days(‘2022-12-15’)) ENGINE = InnoDB,

partition p20221215 values less than (to_days(‘2022-12-16’)) ENGINE = InnoDB,

partition p20221216 values less than (to_days(‘2022-12-17’)) ENGINE = InnoDB,

partition p20221217 values less than (to_days(‘2022-12-18’)) ENGINE = InnoDB,

partition p20221218 values less than (to_days(‘2022-12-19’)) ENGINE = InnoDB,

partition p20221219 values less than (to_days(‘2022-12-20’)) ENGINE = InnoDB,

partition pMAXVALUE values less than (maxvalue) ENGINE = InnoDB

);

2) 파티션 조회

MySQL [bjh]> select * from information_schema.partitions where table_schema=’bjh’ and table_name=’partitiontest’;

+————–+—————+—————-+—————–+———————-+———————–+———————+

| TABLE_SCHEMA | TABLE_NAME    | PARTITION_NAME |PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | CREATE_TIME         |

+————–+—————+—————-+—————–+———————-+———————–+———————+

| bjh          | partitiontest | p20221201      |RANGE            | to_days(`createdAt`) | 738856                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221202      |RANGE            | to_days(`createdAt`) | 738857                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221203      |RANGE            | to_days(`createdAt`) | 738858                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221204      |RANGE            | to_days(`createdAt`) | 738859                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221205      |RANGE            | to_days(`createdAt`) | 738860                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221206      |RANGE            | to_days(`createdAt`) | 738861                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221207      |RANGE            | to_days(`createdAt`) | 738862                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221208      |RANGE            | to_days(`createdAt`) | 738863                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221209      |RANGE            | to_days(`createdAt`) | 738864                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221210      |RANGE            | to_days(`createdAt`) | 738865                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221211      |RANGE            | to_days(`createdAt`) | 738866                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221212      |RANGE            | to_days(`createdAt`) | 738867                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221213      |RANGE            | to_days(`createdAt`) | 738868                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221214      |RANGE            | to_days(`createdAt`) | 738869                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221215      |RANGE            | to_days(`createdAt`) | 738870                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221216      |RANGE            | to_days(`createdAt`) | 738871                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221217      |RANGE            | to_days(`createdAt`) | 738872                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221218      |RANGE            | to_days(`createdAt`) | 738873                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221219      |RANGE            | to_days(`createdAt`) | 738874                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | pMAXVALUE      |RANGE            | to_days(`createdAt`) | MAXVALUE              | 2022-12-19 04:47:35 |

+————–+—————+—————-+—————–+———————-+———————–+———————+

3) 프로시저 수행

MySQL [mysql]> call create_partition(‘bjh’, ‘partitiontest’, 1, 1);

프로시저 해석 : bjh.partitiontest 테이블을 금일 날짜(테스트 날짜 :2022-12-19) 기준으로 1일 후까지 1일 간격으로 파티션을 생성.

+————+———————–+

| Last_Date  | Partitions_For_Future |

+————+———————–+

| 2022-12-20 |                     1 |

+————+———————–+

1 row in set (0.25 sec)

Query OK, 0 rows affected (0.25 sec)

3-1) 파티션 결과 조회

MySQL [mysql]> select * from information_schema.partitions where table_schema=’bjh’ and table_name=’partitiontest’;

+————–+—————+—————-+—————–+———————+———————–+———————+

| TABLE_SCHEMA | TABLE_NAME    | PARTITION_NAME |PARTITION_METHOD |PARTITION_EXPRESSION | PARTITION_DESCRIPTION | CREATE_TIME         |

+————–+—————+—————-+—————–+———————+———————–+———————+

| bjh          | partitiontest | p20221201      |RANGE            |to_days(`createdAt`) | 738856                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221202      |RANGE            |to_days(`createdAt`) | 738857                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221203      |RANGE            |to_days(`createdAt`) | 738858                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221204      |RANGE            |to_days(`createdAt`) | 738859                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221205      |RANGE            |to_days(`createdAt`) | 738860                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221206      |RANGE            |to_days(`createdAt`) | 738861                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221207      |RANGE            |to_days(`createdAt`) | 738862                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221208      |RANGE            |to_days(`createdAt`) | 738863                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221209      |RANGE            |to_days(`createdAt`) | 738864                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221210      |RANGE            |to_days(`createdAt`) | 738865                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221211      |RANGE            |to_days(`createdAt`) | 738866                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221212      |RANGE            |to_days(`createdAt`) | 738867                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221213      |RANGE            |to_days(`createdAt`) | 738868                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221214      |RANGE            |to_days(`createdAt`) | 738869                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221215      |RANGE            |to_days(`createdAt`) | 738870                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221216      |RANGE            |to_days(`createdAt`) | 738871                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221217      |RANGE            |to_days(`createdAt`) | 738872                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221218      |RANGE            |to_days(`createdAt`) | 738873                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221219      |RANGE            |to_days(`createdAt`) | 738874                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | p20221220      |RANGE            |to_days(`createdAt`) | 738875                | 2022-12-19 05:09:59 |

| bjh          | partitiontest | pMAXVALUE      |RANGE            |to_days(`createdAt`) | MAXVALUE              | 2022-12-19 05:09:59 |

+————–+—————+—————-+—————–+———————+———————–+———————+

4) 프로시저 수행

MySQL [mysql]> call create_partition(‘bjh’, ‘partitiontest’, 7, 1);

프로시저 해석 : bjh.partitiontest 테이블을 금일 날짜(테스트 날짜 :2022-12-19) 기준으로 7일 후까지 1일 간격으로 파티션을 생성.

+————+———————–+

| Last_Date  | Partitions_For_Future |

+————+———————–+

| 2022-12-26 |                     7 |

+————+———————–+

1 row in set (1.52 sec)

Query OK, 0 rows affected (1.52 sec)

4-1) 파티션 결과 조회

MySQL [mysql]> select * from information_schema.partitions where table_schema=’bjh’ and table_name=’partitiontest’;

+————–+—————+—————-+—————–+———————-+———————–+———————+

| TABLE_SCHEMA | TABLE_NAME    | PARTITION_NAME |PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | CREATE_TIME         |

+————–+—————+—————-+—————–+———————-+———————–+———————+

| bjh          | partitiontest | p20221201      |RANGE            | to_days(`createdAt`) | 738856                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221202      |RANGE            | to_days(`createdAt`) | 738857                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221203      |RANGE            | to_days(`createdAt`) | 738858                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221204      |RANGE            | to_days(`createdAt`) | 738859                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221205      |RANGE            | to_days(`createdAt`) | 738860                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221206      |RANGE            | to_days(`createdAt`) | 738861                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221207      |RANGE            | to_days(`createdAt`) | 738862                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221208      |RANGE            | to_days(`createdAt`) | 738863                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221209      |RANGE            | to_days(`createdAt`) | 738864                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221210      |RANGE            | to_days(`createdAt`) | 738865                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221211      |RANGE            | to_days(`createdAt`) | 738866                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221212      |RANGE            | to_days(`createdAt`) | 738867                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221213      |RANGE            | to_days(`createdAt`) | 738868                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221214      |RANGE            | to_days(`createdAt`) | 738869                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221215      |RANGE            | to_days(`createdAt`) | 738870                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221216      |RANGE            | to_days(`createdAt`) | 738871                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221217      |RANGE            | to_days(`createdAt`) | 738872                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221218      |RANGE            | to_days(`createdAt`) | 738873                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221219      |RANGE            | to_days(`createdAt`) | 738874                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221220      |RANGE            | to_days(`createdAt`) | 738875                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221221      |RANGE            | to_days(`createdAt`) | 738876                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221222      |RANGE            | to_days(`createdAt`) | 738877                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221223      |RANGE            | to_days(`createdAt`) | 738878                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221224      |RANGE            | to_days(`createdAt`) | 738879                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221225      |RANGE            | to_days(`createdAt`) | 738880                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | p20221226      |RANGE            | to_days(`createdAt`) | 738881                | 2022-12-19 05:11:32 |

| bjh          | partitiontest | pMAXVALUE      |RANGE            | to_days(`createdAt`) | MAXVALUE              | 2022-12-19 05:11:32 |

+————–+—————+—————-+—————–+———————-+———————–+———————+

3-1-2. 알게된 점

해당 프로시저를 수행할 때, 파티션에 중복 (시나리오3), 4)번 과정에서는2022-12-20의 파티션이 중복 수행됨.)이 있다고 하더라도 오류없이 파티션이 정상적으로 추가가 되었습니다. 또한, information_schema.partitions 테이블의 create_time 컬럼을 조회할 때 추가된 컬럼에 한해서만 갱신이 되는 것이 아니라, 파티션 전체에 대해 파티션을 추가한 시간으로 동일하게 갱신되어 있었습니다.

 

3-2. Partition Drop Procedure

Drop Partition Procedure 문 (Terminal 수행을 위해 DELIMITER 변경 후 진행)

DELIMITER $$

CREATE PROCEDURE delete_partition(p_dbname varchar(255), p_tbname varchar(255), p_del_date INT)

SQL SECURITY INVOKER

BEGIN

   DECLARE done INT;

   DECLARE pname VARCHAR(64);

   DECLARE alter_cmd VARCHAR(1024);

   DECLARE deleted_partition VARCHAR(1024);

  — 삭제할 파티션 목록 취합

   DECLARE cur CURSOR FOR

      SELECT PARTITION_NAME FROM information_schema.PARTITIONS

      WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!=’MAXVALUE’

        AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) – p_del_date ;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   SET done = 0;

   — 삭제 대상 파티션 목록 확인 및 작업 완료 후 출력 (옵션)

   SELECT GROUP_CONCAT(PARTITION_NAME) INTO deleted_partition

   FROM information_schema.PARTITIONS

   WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!=’MAXVALUE’

     AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) – p_del_date ;

   OPEN cur;

   FETCH cur INTO pname;

   WHILE done = 0 DO

     — MySQL 5.6 버전 이상인 경우 동일 스키마로 빈 테이블을 만들어서 PARTITION EXCHANGE 처리 후 DROP PARTITION

     — 파티션 삭제 처리 시간 지연으로 인한 Table Lock 영향을 최소화하기 위함

      IF left(version(),3) >= ‘5.6’ THEN

         — make empty table for exchange

         SET @alter_sql := CONCAT(‘CREATE TABLE ‘, p_dbname, ‘._exchange_’, p_tbname,’_’,pname, ‘ LIKE ‘, p_dbname, ‘.’ , p_tbname);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘._exchange_’, p_tbname,’_’,pname, ‘ REMOVE PARTITIONING’);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         — exchange

         SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘.’, p_tbname, ‘ EXCHANGE PARTITION ‘, pname, ‘ WITH TABLE ‘, p_dbname, ‘._exchange_’, p_tbname, ‘_’, pname);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         — drop tmp table

         — SET @alter_sql := CONCAT(‘DROP TABLE ‘, p_dbname, ‘._exchange_’, p_tbname);

         — PREPARE alter_cmd FROM @alter_sql;

         — EXECUTE alter_cmd;

         — DEALLOCATE PREPARE alter_cmd;

      END IF;

      — 파티션 삭제

      SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘.’, p_tbname, ‘ DROP PARTITION ‘, pname);

      PREPARE alter_cmd FROM @alter_sql;

      EXECUTE alter_cmd;

      DEALLOCATE PREPARE alter_cmd;

      FETCH cur INTO pname;

   END WHILE;

   CLOSE cur;

   — 삭제 처리한 파티션 목록 출력 (옵션)

   SELECT deleted_partition AS Deleted_Partitions ;

END $$

DELIMITER ;

▶ Switching한 Temp Table을Drop하는 부분은 현재 진행중인 프로젝트에서 History보관주기가 변경될 수 있어 일단 주석으로 처리한 후 테스트를 진행하였습니다.

 

3-2-1. Partition Drop Procedure (임시테이블 유지)

시나리오설 명

1) 파티션테이블 생성

Range Partition Create 구문

CREATE TABLE partitiontest (

  id int NOT NULL AUTO_INCREMENT,

  some_data varchar(100),

  createdAt datetime(6) NOT NULL,

  modifiedAt datetime(6) DEFAULT NULL,

  PRIMARY KEY (id,createdAt)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

partition by range (to_days(createdAt))

(

partition p20221201 values less than (to_days(‘2022-12-02’)) ENGINE = InnoDB,

partition p20221202 values less than (to_days(‘2022-12-03’)) ENGINE = InnoDB,

partition p20221203 values less than (to_days(‘2022-12-04’)) ENGINE = InnoDB,

partition p20221204 values less than (to_days(‘2022-12-05’)) ENGINE = InnoDB,

partition p20221205 values less than (to_days(‘2022-12-06’)) ENGINE = InnoDB,

partition p20221206 values less than (to_days(‘2022-12-07’)) ENGINE = InnoDB,

partition p20221207 values less than (to_days(‘2022-12-08’)) ENGINE = InnoDB,

partition p20221208 values less than (to_days(‘2022-12-09’)) ENGINE = InnoDB,

partition p20221209 values less than (to_days(‘2022-12-10’)) ENGINE = InnoDB,

partition p20221210 values less than (to_days(‘2022-12-11’)) ENGINE = InnoDB,

partition p20221211 values less than (to_days(‘2022-12-12’)) ENGINE = InnoDB,

partition p20221212 values less than (to_days(‘2022-12-13’)) ENGINE = InnoDB,

partition p20221213 values less than (to_days(‘2022-12-14’)) ENGINE = InnoDB,

partition p20221214 values less than (to_days(‘2022-12-15’)) ENGINE = InnoDB,

partition p20221215 values less than (to_days(‘2022-12-16’)) ENGINE = InnoDB,

partition p20221216 values less than (to_days(‘2022-12-17’)) ENGINE = InnoDB,

partition p20221217 values less than (to_days(‘2022-12-18’)) ENGINE = InnoDB,

partition p20221218 values less than (to_days(‘2022-12-19’)) ENGINE = InnoDB,

partition p20221219 values less than (to_days(‘2022-12-20’)) ENGINE = InnoDB,

partition pMAXVALUE values less than (maxvalue) ENGINE = InnoDB

);

2) 파티션 조회

MySQL [bjh]> select * from information_schema.partitions where table_schema=’bjh’ and table_name=’partitiontest’;

+————–+—————+—————-+—————–+———————-+———————–+———————+

| TABLE_SCHEMA | TABLE_NAME    | PARTITION_NAME |PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | CREATE_TIME         |

+————–+—————+—————-+—————–+———————-+———————–+———————+

| bjh          | partitiontest | p20221201      |RANGE            | to_days(`createdAt`) | 738856                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221202      |RANGE            | to_days(`createdAt`) | 738857                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221203      |RANGE            | to_days(`createdAt`) | 738858                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221204      |RANGE            | to_days(`createdAt`) | 738859                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221205      |RANGE            | to_days(`createdAt`) | 738860                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221206      |RANGE            | to_days(`createdAt`) | 738861                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221207      |RANGE            | to_days(`createdAt`) | 738862                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221208      |RANGE            | to_days(`createdAt`) | 738863                | 2022-12-19 04:47:35 |

         … 중략 …

| bjh          | partitiontest | p20221214      |RANGE            | to_days(`createdAt`) | 738869                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221215      |RANGE            | to_days(`createdAt`) | 738870                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221216      |RANGE            | to_days(`createdAt`) | 738871                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221217      |RANGE            | to_days(`createdAt`) | 738872                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221218      |RANGE            | to_days(`createdAt`) | 738873                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | p20221219      |RANGE            | to_days(`createdAt`) | 738874                | 2022-12-19 04:47:35 |

| bjh          | partitiontest | pMAXVALUE      |RANGE            | to_days(`createdAt`) | MAXVALUE              | 2022-12-19 04:47:35 |

+————–+—————+—————-+—————–+———————-+———————–+———————+

3) 프로시저 수행

MySQL [mysql]> call mysql.delete_partition(‘bjh’, ‘partitiontest’, 16);

프로시저 해석 : bjh.partitiontest 테이블을 금일 날짜(테스트 날짜 :2022-12-19) 기준으로 16일이 경과한 파티션을 삭제.

+———————+

| Deleted_Partitions  |

+———————+

| p20221201,p20221202 |

+———————+

1 row in set (5.46 sec)

Query OK, 0 rows affected (5.46 sec)

3-1) 파티션 결과 조회

MySQL [mysql]> select * from information_schema.partitions where table_schema=’bjh’ and table_name=’partitiontest’;

+————–+—————+—————-+——————+———————-+———————–+———————+

| TABLE_SCHEMA | TABLE_NAME    | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | CREATE_TIME         |

+————–+—————+—————-+——————+———————-+———————–+———————+

| bjh          | partitiontest | p20221203      | RANGE            | to_days(`createdAt`) | 738858                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221204      | RANGE            | to_days(`createdAt`) | 738859                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221205      | RANGE            | to_days(`createdAt`) | 738860                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221206      | RANGE            | to_days(`createdAt`) | 738861                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221207      | RANGE            | to_days(`createdAt`) | 738862                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221208      | RANGE            | to_days(`createdAt`) | 738863                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221209      | RANGE            | to_days(`createdAt`) | 738864                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221210      | RANGE            | to_days(`createdAt`) | 738865                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221211      | RANGE            | to_days(`createdAt`) | 738866                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221212      | RANGE            | to_days(`createdAt`) | 738867                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221213      | RANGE            | to_days(`createdAt`) | 738868                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221214      | RANGE            | to_days(`createdAt`) | 738869                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221215      | RANGE            | to_days(`createdAt`) | 738870                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221216      | RANGE            | to_days(`createdAt`) | 738871                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221217      | RANGE            | to_days(`createdAt`) | 738872                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221218      | RANGE            | to_days(`createdAt`) | 738873                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | p20221219      | RANGE            | to_days(`createdAt`) | 738874                | 2022-12-19 06:32:45 |

| bjh          | partitiontest | pMAXVALUE      | RANGE            | to_days(`createdAt`) | MAXVALUE              | 2022-12-19 06:32:45 |

+————–+—————+—————-+——————+———————-+———————–+———————+

4) 임시테이블 존재 확인

MySQL [bjh]> show tables;

+———————————–+

| Tables_in_bjh                     |

+———————————–+

| _exchange_partitiontest_p20221201 |

| _exchange_partitiontest_p20221202 |

| partitiontest                     |

+———————————–+

3 rows in set (0.00 sec)

4-1) 임시테이블 존재 확인

MySQL [bjh]> describe _exchange_partitiontest_p20221202;

MySQL [bjh]> describe _exchange_partitiontest_p20221202;

+————+————–+——+—–+———+—————-+

| Field      | Type         | Null | Key | Default | Extra          |

+————+————–+——+—–+———+—————-+

| id         | int          | NO   | PRI | NULL    | auto_increment |

| some_data  | varchar(100) | YES  |     | NULL    |                |

| createdAt  | datetime(6)  | NO   | PRI | NULL    |                |

| modifiedAt | datetime(6)  | YES  |     | NULL    |                |

+————+————–+——+—–+———+—————-+

4 rows in set (0.00 sec)

3-2-2. Partition Drop Procedure (임시테이블 삭제)

앞서 테스트 한 프로시저는Exchange된 일반 테이블을 drop하지 않고 그대로 남겨 이력을 가져가고자 할 때 사용할 수 있는 프로시저입니다.

일반 테이블이 필요 없을 때에는 아래의 프로시저를 사용하여 즉시 Drop 할 수 있습니다. (앞의 프로시저와 내용이 조금씩 다르므로 구문 전체 복사가 필요합니다.)

Drop Partition Procedure 문 (Terminal 수행을 위해 DELIMITER 변경 후 진행)

DELIMITER $$

CREATE PROCEDURE delete_partition(p_dbname varchar(255), p_tbname varchar(255), p_del_date INT)

SQL SECURITY INVOKER

BEGIN

   DECLARE done INT;

   DECLARE pname VARCHAR(64);

   DECLARE alter_cmd VARCHAR(1024);

   DECLARE deleted_partition VARCHAR(1024);

  — 삭제할 파티션 목록 취합

   DECLARE cur CURSOR FOR

      SELECT PARTITION_NAME FROM information_schema.PARTITIONS

      WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!=’MAXVALUE’

        AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) – p_del_date ;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   SET done = 0;

   — 삭제 대상 파티션 목록 확인 및 작업 완료 후 출력 (옵션)

   SELECT GROUP_CONCAT(PARTITION_NAME) INTO deleted_partition

   FROM information_schema.PARTITIONS

   WHERE TABLE_SCHEMA = p_dbname AND TABLE_NAME = p_tbname AND PARTITION_DESCRIPTION!=’MAXVALUE’

     AND PARTITION_DESCRIPTION<=TO_DAYS(current_date()) – p_del_date ;

   OPEN cur;

   FETCH cur INTO pname;

   WHILE done = 0 DO

     — MySQL 5.6 버전 이상인 경우 동일 스키마로 빈 테이블을 만들어서 PARTITION EXCHANGE 처리 후 DROP PARTITION

     — 파티션 삭제 처리 시간 지연으로 인한 Table Lock 영향을 최소화하기 위함

      IF left(version(),3) >= ‘5.6’ THEN

         — make empty table for exchange

         SET @alter_sql := CONCAT(‘CREATE TABLE ‘, p_dbname, ‘._exchange_’, p_tbname, ‘ LIKE ‘, p_dbname, ‘.’ , p_tbname);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘._exchange_’, p_tbname, ‘ REMOVE PARTITIONING’);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         — exchange

         SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘.’, p_tbname, ‘ EXCHANGE PARTITION ‘, pname, ‘ WITH TABLE ‘, p_dbname, ‘._exchange_’, p_tbname);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

         — drop tmp table

         SET @alter_sql := CONCAT(‘DROP TABLE ‘, p_dbname, ‘._exchange_’, p_tbname);

         PREPARE alter_cmd FROM @alter_sql;

         EXECUTE alter_cmd;

         DEALLOCATE PREPARE alter_cmd;

      END IF;

      — 파티션 삭제

      SET @alter_sql := CONCAT(‘ALTER TABLE ‘, p_dbname, ‘.’, p_tbname, ‘ DROP PARTITION ‘, pname);

      PREPARE alter_cmd FROM @alter_sql;

      EXECUTE alter_cmd;

      DEALLOCATE PREPARE alter_cmd;

      FETCH cur INTO pname;

   END WHILE;

   CLOSE cur;

   — 삭제 처리한 파티션 목록 출력 (옵션)

   SELECT deleted_partition AS Deleted_Partitions ;

END $$

DELIMITER ;

 

3-3. Procedure Event 등록

앞의 Drop & Add 파티션 프로시저를 가지고 이벤트 스케줄러러나 crontab(이벤트 스케줄러 미지원 버전의 경우)에 등록합니다.

이벤트 스케줄러 사용 시, event_scheduler 파라미터 ON 여부도 확인해야 합니다.

사전 준비사항

SET GLOBAL activate_all_roles_on_login=ON; — 해당 파라미터 ON 확인

— 이벤트 생성 및 실행에 필요한 권한 부여

MySQL [mysql]> grant event on *.* to `admin`@`%`;

MySQL [mysql]> grant execute on procedure  mysql.delete_partition to `admin`@`%`;

MySQL [mysql]> grant execute on procedure  mysql.create_partition to `admin`@`%`;

MySQL [mysql]> flush privileges;

Drop & Add Partition Event Scheduler (Terminal 수행을 위해 DELIMITER 변경 후 진행)

DELIMITER $$

drop event if exists evt_partition_management $$

CREATE DEFINER=`admin`@`%` EVENT evt_partition_management

ON SCHEDULE EVERY ‘1’ DAY STARTS ‘2022-12-20 01:00:00’    — 스케쥴러 시작 시점은 반드시 현재 날짜 기준으로 미래 시점이어야 함

DO

BEGIN

  call mysql.delete_partition(‘bjh’, ‘partitiontest’, 10);

  call mysql.create_partition(‘bjh’, ‘partitiontest’, 3, 1);

END $$

DELIMITER ;

Event 등록 확인

MySQL [mysql]> show events;

+——-+—————————+——————–+—————-+—————-+———————+——+———-+

| Db    | Name                      | Definer            | Interval value | Interval field | Starts              | Ends | Status   |

+——-+—————————+——————–+—————-+—————-+———————+——+———-+

| mysql | evt_partition_management  | admin@%            | 1              | DAY            | 2022-12-20 01:00:00 | NULL | ENABLED  |

| mysql | ev_rds_gsh_collector      | rdsadmin@localhost | 5              | MINUTE         | 2022-12-19 04:19:42 | NULL | DISABLED |

| mysql | ev_rds_gsh_table_rotation | rdsadmin@localhost | 7              | DAY            | 2022-12-26 04:19:42 | NULL | DISABLED |

+——-+—————————+——————–+—————-+—————-+———————+——+———-+

방금 읽은 인사이트를 실무에 직접 적용하고 싶으시다면?
지금 바로 베스핀글로벌에 문의하세요.

베스핀글로벌 문의하기
문의하기 베스픽 구독하기
궁금한 점이 있다면 클릭해주세요.