안녕하세요 오늘은 베스핀글로벌 D&A실 왕승준님이 작성해 주신 [MySQL] Partition add & drop 자동화 테스트에 대해 알아보겠습니다.
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 환경구성

1-2. Exchange Partition Test


1-3. ALTER DROP Partition

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

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

3. Partition 관리 자동화
신규 파티션 추가와 삭제를 매번 수작업으로 진행할 수는 없으므로 이를 자동으로 관리하기 위한 프로시저 및 이벤트를 생성하는 과정을 진행하였습니다. 파티션 관리 요건 중 가장 중요한 것은 다음의 세가지입니다.
1. 어떠한 이유에서든지 프로시저가 동작하지 않은 경우라도 데이터 Insert 실패가 발생하지 않도록 할 것.
2. 프로시저가 일정기간 동작하지 않고, 다시 재기동 되는 경우에 프로시저가 주기적으로 동작했던 것과 동일한 파티션 구조를 생성할 것.
3. 파티션 삭제시 Table Lock으로 인한 영향을 최소화할 것.
처음 이 파티션 테스트를 진행하기 전에는 파티션의 MAXVALUE가 테이블의 관리나 성능적인 측면에서 필요 없다고 생각하였으나, 1번의 내용과 같이 파티션 관리 프로시저가 며칠 동안 동작하지 않을 경우 서비스 자체가 운영되지 않을 수 있기 때문에 반드시 존재해야 한다고 제 개인적인 생각을 바꾸게 되었습니다. 또한, 앞서 테스트한 경우와 마찬가지로 파티션 drop은 단순한 alter문이 아닌 partition exchange로 진행하여 3번에 해당하는 Table Lock 최소화도 고려하였습니다.
3-1. Partition Create Procedure

3-1-1. Partition Create Procedure






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


▶ Switching한 Temp Table을Drop하는 부분은 현재 진행중인 프로젝트에서 History보관주기가 변경될 수 있어 일단 주석으로 처리한 후 테스트를 진행하였습니다.
3-2-1. Partition Drop Procedure (임시테이블 유지)





3-2-2. Partition Drop Procedure (임시테이블 삭제)
앞서 테스트 한 프로시저는Exchange된 일반 테이블을 drop하지 않고 그대로 남겨 이력을 가져가고자 할 때 사용할 수 있는 프로시저입니다.
일반 테이블이 필요 없을 때에는 아래의 프로시저를 사용하여 즉시 Drop 할 수 있습니다. (앞의 프로시저와 내용이 조금씩 다르므로 구문 전체 복사가 필요합니다.)


3-3. Procedure Event 등록
앞의 Drop & Add 파티션 프로시저를 가지고 이벤트 스케줄러러나 crontab(이벤트 스케줄러 미지원 버전의 경우)에 등록합니다.
이벤트 스케줄러 사용 시, event_scheduler 파라미터 ON 여부도 확인해야 합니다.



감사합니다 🙂

문의: info@bespinglobal.com | 대표번호: 02-1668-1280