MySQL 파티션 DROP 성능개선 완벽 가이드

『 '데이널'의 컨텐츠에 포함된 정보는? 』

 

데이터베이스 운영 중 파티션 삭제 작업이 몇 시간씩 걸려서 답답했던 경험 있으신가요?

 

실제로 많은 DBA들이 파티션 관리에서 가장 큰 어려움으로 '긴 작업 시간'을 꼽습니다. 오늘은 제가 최근에 MySQL 프로젝트를 하면서 발견한 파티션 삭제 성능 개선 방법을 공유해 드리겠습니다.

 

MySQL 파티션 삭제 성능개선 가이드

 

MySQL 파티션 삭제 원리, 왜 느릴까?

사실 Oracle에서는 파티션 drop이 데이터가 많더라도 오래 걸리지 않습니다. 하지만 MySQL에서 데이터 양에 비례하여 속도가 느려지더군요. 

 

MySQL에서 파티션 삭제가 느린 이유는 내부 동작 방식 때문입니다. DROP PARTITION은 실제로 DROP TABLE 작업으로 변환되어 실행되는데요. 이 과정에서 다음과 같은 작업이 발생합니다

 

1. 테이블 전체 잠금

• 파티션 삭제 중에는 전체 테이블에 LOCK(잠금) 
• 다른 트랜잭션 대기 발생

2. 파일시스템 작업

• 물리적 파일 삭제 작업
• 파일 시스템 캐시 정리

과거 사례를 보면 1억 건의 데이터가 있는 파티션을 삭제할 때 기본 방식으로는 1시간이 넘게 걸리기도 합니다. 

 

MySQL 파티션 작업의 특징

파티션 작업은 다음과 같은 특징을 가집니다. 

리소스 사용	영향도	버전별 차이
- CPU 사용량 증가 - I/O 부하 발생 - 메모리 사용량 증가	- 전체 테이블 접근 제한 - 복제 지연 가능성 - 백업 작업 간섭	- MySQL 8.0: 개선된 성능 - MySQL 5.7: 상대적으로 느림

 

MySQL 파티션 삭제 속도를 높이는 방법

MySQL의 파티션 drop을 위한 성능 개선을 할 수 있는 2가지 방법입니다.

1. 파티션 교환 방식 활용

-- 원본 테이블과 동일한 구조 복사 CREATE TABLE new_table LIKE original_table; -- 새로운 테이블에 파티션 구조를 제거 ALTER TABLE new_table REMOVE PARTITIONING; -- 삭제(drop)하려고 했던 파티션을 exchage  ALTER TABLE original_table EXCHANGE PARTITION p_202309 WITH TABLE new_table; -- 원본 테이블 파티션 삭제(drop)  ALTER TABLE original_table DROP PARTITION p_202309; -- 새로 만들었던 테이블 삭제 DROP TABLE new_table;

 

이 방식을 사용하면 1시간 이상 걸리던 작업을 2초 내로 단축할 수 있습니다. 개인적으로 체감하기는 테이블 nename 하는 것처럼 exchage는 빨리 끝납니다. 

 

2. 사전 준비 작업 및 운영시간 고려

사전 준비	운영시간 고려
- 불필요한 인덱스 제거 - 버퍼 풀 최적화 - 파일시스템 정리	- 서버 부하가 적은 시간대에 실행 - 필요한 경우 서버 재시작 후 실행 (실행 시간 단축 가능)

 

실제 운영 환경에서는 데이터 크기와 서버 설정에 따라 수초에서 수분까지 소요될 수 있으므로, 신중한 계획이 필요합니다.

 

파티션 삭제 성능 테스트 및 비교

다음은 아래 테스트 환경에서 3가지 경우에 대해 테스트한 결과입니다. 

1. 테스트 환경

• 데이터 크기: 100GB
• 레코드 수: 1억 건
• MySQL 버전: 8.0.23

 

2. 성능 비교

구분	파티션 drop	파티션 exchange	파티션 truncate  후 drop
소요시간	6시간	1.5초	30분
시스템 부하	높음	낮음	중간
서비스 영향	심각	최소	보통

 

마무리

파티션 관리는 DBA의 업무 중 하나입니다. 하지만 기본적인 원리는 알고 있어야 합니다. 특히 파티션 삭제 작업을 할 때는 신중한 계획과 최적화된 방법을 사용해야 합니다. 이 글에서 소개한 방법들을 활용하면 작업 시간을 획기적으로 단축할 수 있습니다.

 

MySQL 잠금 해결 방법 및 Lock 모니터링

MySQL 잠금 해결 방법 및 Lock 모니터링