mysql 파티션 추가 삭제할 때 REORGANIZE 쓰시나요

by

대용량 테이블에 파티션을 새로 생성할 때

REORGANIZE 명령어를 사용한다면
Lock 대환장 파티를 볼 수도 있다

1.파티션 테이블 용도 확인

파티션이 구성된 테이블의 현재 OLTP 인지 OLAP 로 쓰는 테이블인지
테이블 성격을 일단 확인해야 한다

그리고 OLTP라면 REORGANIZE 명령어는 원만하면 쓰지 말자

2. 파티션 추가시 LOCK 발생 실제 사례

테이블의 data size :3.3GB 정도 되는 대용량 테이블이었다.

CREATE TABLE `TB_TEST_CUST_MONTH` (
 `MONTH` varchar(6) NOT NULL COMMENT '마감월',
 ...
 .
 .
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='누적데이타관리'
 PARTITION BY RANGE  COLUMNS(`MONTH`)
(PARTITION `p202011` VALUES LESS THAN ('202012') ENGINE = InnoDB,
 PARTITION `p202012` VALUES LESS THAN ('202101') ENGINE = InnoDB,
 PARTITION `p202101` VALUES LESS THAN ('202102') ENGINE = InnoDB,
 PARTITION `p202102` VALUES LESS THAN ('202103') ENGINE = InnoDB,
 PARTITION `p202103` VALUES LESS THAN ('202104') ENGINE = InnoDB,
 PARTITION `p202104` VALUES LESS THAN ('202105') ENGINE = InnoDB,
 PARTITION `p202105` VALUES LESS THAN ('202106') ENGINE = InnoDB,
 PARTITION `p202106` VALUES LESS THAN ('202107') ENGINE = InnoDB,
 PARTITION `p202209` VALUES LESS THAN ('202210') ENGINE = InnoDB,
 PARTITION `p202305` VALUES LESS THAN ('202306') ENGINE = InnoDB,
 PARTITION `p202309` VALUES LESS THAN ('202310') ENGINE = InnoDB,
 PARTITION `p202310` VALUES LESS THAN ('202311') ENGINE = InnoDB,
 PARTITION `pExt` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

#여기서 MAXVALUE 파티션을 사용하는 이유는?
어떤 이유에서든 서버에서 전달하는 값이 range를 넘어서는 경우가 발생할 수도 있기 때문에
에러가 나서 쌓이지 않는 경우는 예방차원에서 MAXVAULE 는 사용하는 것이 좋다.

배치가 돌았는데 p202310 파티션의 데이터가 문제가 생겨서 파티션을 통째로 날려달라는 요청이 들어왔다.

그럼 p202309 -> p202310 <- pExt 가운데 파티션을 날려야하는데

파티션은 MAXVALUE이 있다면 중간 파티션을 삭제할 시 아래서부터 순차적으로 삭제해야 한다.

#삭제 적용
ALTER TABLE TB_TEST_CUST_MONTH DROP PARTITION pExt, p202310;


#테이블 스키마 확인
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='누적마감데이타관리'
 PARTITION BY RANGE  COLUMNS(`MONTH`)
(PARTITION `p202011` VALUES LESS THAN ('202012') ENGINE = InnoDB,
 PARTITION `p202012` VALUES LESS THAN ('202101') ENGINE = InnoDB,
 PARTITION `p202101` VALUES LESS THAN ('202102') ENGINE = InnoDB,
 PARTITION `p202102` VALUES LESS THAN ('202103') ENGINE = InnoDB,
 PARTITION `p202103` VALUES LESS THAN ('202104') ENGINE = InnoDB,
 PARTITION `p202104` VALUES LESS THAN ('202105') ENGINE = InnoDB,
 PARTITION `p202105` VALUES LESS THAN ('202106') ENGINE = InnoDB,
 PARTITION `p202106` VALUES LESS THAN ('202107') ENGINE = InnoDB,
 PARTITION `p202209` VALUES LESS THAN ('202210') ENGINE = InnoDB,
 PARTITION `p202305` VALUES LESS THAN ('202306') ENGINE = InnoDB,
 PARTITION `p202309` VALUES LESS THAN ('202310') ENGINE = InnoDB);

p202310 파티션은 원하는대로 삭제했다.

여기까지는 간단했는데

이제 하나 더 작업이 남아있다.

MAXVALUE은 다시 살려야 한다는건데

구글링해보니 가장 간단한 방법은 REORGANIZE 명렁어였다.

ALTER TABLE TB_TEST_CUST_MONTH REORGANIZE PARTITION pExt INTO 
(
    PARTITION p202310 VALUES LESS THAN ('202311'),
    PARTITION pExt VALUES LESS THAN MAXVALUE
);

다만 OLTP성 테이블에 이걸 사용했을 시 락파티를 볼 수도 있다고 하는데

3.3GB 얼마 되지도 않는데 괜찮겠지 하고 수행을 시작했는데…

show processlist; 로 확인해보니

| 34316205 | xxxadm      | 192.168.1.101:35593 | xxxDB | Query   |  302 | Setup                    | ALTER TABLE TB_TEST_CUST_MONTH REORGANIZE PARTITION pExt INTO 
(
    PARTITION p202310 VALUES |    0.000 |
| 34316262 | xxxadm      | 192.168.1.17:38094  | xxxDB | Sleep   |  114 |                          | NULL                                                                                                 |    0.000 |
| 34316269 | xxxadm      | 192.168.1.17:38110  | xxxDB | Sleep   |  112 |                          | NULL                                                                                                 |    0.000 |
| 34316275 | xxxadm      | 192.168.1.17:38122  | xxxDB | Sleep   |  110 |                          | NULL                                                                                                 |    0.000 |
| 34316290 | xxxadm      | 192.168.1.17:38166  | xxxDB | Sleep   |  107 |                          | NULL                                                                                                 |    0.000 |
| 34316297 | xxxadm      | 192.168.1.17:38180  | xxxDB | Sleep   |  106 |                          | NULL                                                                                                 |    0.000 |
| 34316304 | xxxadm      | 192.168.1.17:38206  | xxxDB | Sleep   |   84 |                          | NULL

Setup 이라는 명령어와 함께 끝날 생각이 없어보이고

슬슬 하나 둘 meta gap lock 이 들어온다 ;;

바로 중단시키고 

kill 34316205;

좀 찾아보니 REORGANIZE 를 사용하면

(ALTER TABLE … REORGANIZE PARTITION을 사용하면 파티션이 다시 작성(rebuilt) 된다고 한다)

LOCK 유발하는 REORGANIZE 사용을 멈추고

아래와 같이 DROP, ADD만 사용해서 파티션을 작성해서 문제를 해결했다.

3. 파티션 추가시 LOCK 해결 방법

/* 삭제 적용 */
ALTER TABLE TB_TEST_CUST_MONTH DROP PARTITION pExt, p202310;

/* MAX 파티션 생성 */
ALTER TABLE TB_TEST_CUST_MONTH ADD PARTITION
(
    PARTITION pExt VALUES LESS THAN MAXVALUE
);

/*테이블 스키마 확인 */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='누적마감데이타관리'
 PARTITION BY RANGE  COLUMNS(`MONTH`)
(PARTITION `p202011` VALUES LESS THAN ('202012') ENGINE = InnoDB,
 PARTITION `p202012` VALUES LESS THAN ('202101') ENGINE = InnoDB,
 PARTITION `p202101` VALUES LESS THAN ('202102') ENGINE = InnoDB,
 PARTITION `p202102` VALUES LESS THAN ('202103') ENGINE = InnoDB,
 PARTITION `p202103` VALUES LESS THAN ('202104') ENGINE = InnoDB,
 PARTITION `p202104` VALUES LESS THAN ('202105') ENGINE = InnoDB,
 PARTITION `p202105` VALUES LESS THAN ('202106') ENGINE = InnoDB,
 PARTITION `p202106` VALUES LESS THAN ('202107') ENGINE = InnoDB,
 PARTITION `p202209` VALUES LESS THAN ('202210') ENGINE = InnoDB,
 PARTITION `p202305` VALUES LESS THAN ('202306') ENGINE = InnoDB,
 PARTITION `p202309` VALUES LESS THAN ('202310') ENGINE = InnoDB,
 PARTITION `pExt` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

DROP 과 ADD PARTITION 명령어를 사용해서 LOCK을 없앴다.

쓸데없이 REORGANIZE 명령어를 사용해서 파티션 테이블이

rebulid 되는 일이 없도록 하자.

4. 마지막으로 실수했다면

혹시라도 테이블의 사이즈를 확인하지 않고

REORGANIZE 를 사용했다고 가정해보자

setup 명령어와 함께 파티션 추가나 삭제가 지연되는 것을

로그상 확인했다면

빠른시간 내에 session을 kill 시켜야 한다.

판단이 늦으면 늦을수록 rollback시간은 길어지고

장애시간도 늘어날 뿐이다.

끝.

Leave a Comment