본문 바로가기

MySQL/MySQL Tip & Tech

[MySQL] 사용자 로그 테이블 - (2) 파티션 관리


이 글에서는 로그 테이블의 Daily 파티션을 관리하는 일반적인 방법을 소개합니다.


파티션 추가하기

현재 `sample_log` 테이블에 3개의 파티션이 있다고 가정하겠습니다.


1
2
3
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'sample_log';
cs


(결과)


로그 테이블에 새로운 로그를 계속 적재하기 위해 정기적으로 Daily Partition을 추가해 주어야 합니다.



마지막 파티션인 p_20180703을 두 개의 파티션으로 Reorganize 하면 됩니다.


1
2
3
4
ALTER TABLE `sample_log` REORGANIZE PARTITION p_20180703 INTO (
    PARTITION p_20180703 VALUES LESS THAN ('2018-07-04'ENGINE = InnoDB,
    PARTITION p_20180704 VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);
cs


잠깐!!

마지막 파티션에 LESS THAN MAXVALUE 를 설정하지 않는 방법도 있습니다.

이 경우 Reorganize 대신 ALTER TABLE .. ADD PARTITION .. 구문을 사용해 p_20180704 파티션을 추가하게 되는데요. 하지만 이 방법은, 새 파티션을 추가하기 전에 2018년 7월 4일의 데이터를 INSERT 하는 경우, INSERT 문이 실패하는 위험이 존재합니다.


따라서 LESS THAN MAXVALUE 를 설정하고 Reorganize 하는 방식으로 새 파티션을 추가하는 것이 정신 건강에 이롭습니다.


보관 기간이 지난 로그 데이터 삭제하기

만약, 로그를 파티션 없이 적재했다면 만료된 로그를 어떻게 삭제해야 할까요?

네, 선택의 여지가 없습니다. 점검을 걸고 언제 끝날지 모르는(?) DELETE 문을 용감하게 실행해야합니다.


네, 이것이 바로 대량 로그 테이블에 파티션을 적용해야하는 가장 중요한 이유 중 하나입니다.

로그가 적재된 파티션을 통째로 날림으로써 시스템에 거의 부하를 주지 않고 만료된 로그를 삭제할 수 있기 때문이죠.


구체적인 방법을 살펴 보기 위해 예를 들겠습니다.


sample_log의 보관 기간이 2일이고 현재 2018년 7월 4일의 로그가 적재되고 있다면, 7월 1일의 로그는 버려야 합니다. (또는 저렴한 Storage에 Archiving 합니다.)


가장 간단한 구문은 ALTER TABLE .. DROP PARTITION .. 구문을 사용하는 것입니다. 



로그 테이블 잠금 최소화하기

사실 위에 소개한 방법은 완벽하지 않습니다.


만약 drop 하려는 로그 파티션의 크기가 매우 크다면?? 위에서 예로 든 p_20180701 파티션의 .ibd 파일이 완전히 삭제될 때까지 테이블이 잠기는 문제가 있습니다.


비록 1초 내외로 로그 테이블이 잠기는 정도의 문제지만, 시스템 아키텍처에 따라 그마저도 허용할 수 없다면 어떨까요?


그런 경우까지 고려해야한다면 다음에 소개하는 절차에 따라 파티션을 삭제해야 합니다.


1. 로그 테이블과 동일한 레이아웃의 trash 테이블 생성하기

CREATE TABLE .. LIKE .. 구문을 사용해, 비어 있지만 구조가 같은  `trash` 테이블을 만듭니다.


2. trash 테이블에서 모든 파티션을 제거하기

다음 3항의 작업을 진행하기 위해 `trash` 테이블은 파티션이 없어야 합니다.

ALTER TABLE .. REMOVE PARTITIONING 구문을 사용합니다.


3. sample_log 테이블의 p_20180701 파티션에 있는 데이터와 trash 테이블의 빈 데이터를 서로 교환하기

MySQL은 파티션을 직접 옮겨 주는 기능을 제공하지 않습니다.

대신 ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE .. 문을 사용해서 비슷한 효과를 낼 수 있습니다.

특정 파티션의 .ibd 파일을 다른 테이블의 .ibd 파일과 교환하는 - 아마도 file rename - 작업이라 매우 빠르게 완료됩니다.


4. sample_log 테이블에서 비어 있는 파티션인 p_20180701을 삭제하기

이제 ALTER TABLE .. DROP PARTITION .. 문으로 p_20180701 파티션의 .ibd 파일을 삭제합니다.

여기서도 파일 I/O가 발생하긴 하지만, p_20180701 파티션이 비어 있기 때문에 작업이 빠르게 완료됩니다.


5. 보관 기간이 초과한 로그를 실제로 삭제하기

이제 2018년 7월 1일 로그는 `trash` 테이블에 있습니다.

`trash` 테이블을 DROP 해 줍니다.


잠깐!!

보존 기간이 지난 로그를 삭제하지 않고 별도의 저렴한 스토리지에 archiving 하기도 합니다.

이 경우 `trash` 테이블의 .ibd 파일을 원격지의 archiving 스토리지로 copy 한 후, ALTER TABLE .. IMPORT PARTITION .. TABLESPACE 문으로 archiving 테이블에 병합합니다.

그리고 마지막으로 `trash` 테이블을 Drop 하는 것으로 작업을 마치면 됩니다.


6. 완료

모든 작업이 끝나면 `sample_log` 테이블은 아래와 같은 모습이 됩니다.



주의!!

이 글에서는 이해 하기 쉽도록 단순한 방식. 즉, 매번 `trash` 테이블을 create하고, 마지막에 drop 하는 방식으로 소개하고 있습니다.

하지만 원본 로그 테이블에 파티션이 수십개 이상 있는 환경에서는 CREATE TABLE .. LIKE .. 구문은 빠르게 완료되지 않죠.

따라서 원본 로그 테이블 별로, 원본과 똑같은 구조에 파티션만 없는 전용 trash 테이블을 미리 만들어 놓고, 마지막에 drop이 아닌 truncate table로 로그를 삭제해야 합니다.

이 내용은 이어지는 다음 글에 소개할 Stored Procedure에 포함되어 있습니다.


로그 보관 기간 설정하기

로그의 성격에 따라 보관하는 기간이 다를 수 있습니다.

예를 들면 C/S 로그는 90일 ~ 180일, 빌링 로그는 1년 ~ 3년


아래와 같은 테이블을 만들어 로그 테이블의 메타 데이터와 함께 보관 기간을 Day 단위로 기록해 두고 만료된 파티션을 자동으로 삭제하도록 관리하는 것이 좋습니다.



맺으며

로그 테이블에서 Daily 파티션을 관리하는 방법에 대해 간단히 살펴봤습니다.

다음 글에서는 이런 일련의 작업을 자동화하는 방법에 대해 소개하겠습니다.

1개의 Stored Procedure와 1개의 Event를 사용할 예정입니다.