대용량 테이블 데이터 클렌징

* 개요

: 대용량 테이블의 관리 차원에서 과도하게 적제되는 데이터는 (콜드 디스크)이관 또는 삭제 해줄 필요가 있다. 이 과정에서 해당 테이블이 서비스와 직접적으로 연결된 경우 대용량의 트랜젝션의 발생은 장애로 직결 될 수 있다.

 

* 시나리오

: 로그인 시 Audit Table 에 감사 로그를 남겨 누가 언제 접속했는지를 관리하고 있다. 이 테이블의 사이즈가 무한정으로 커지고 있고, 디스크 용량 관리 차원에서 오래된 데이터는 배치로 삭제 해줘야 할 필요가 있다. 다만 이 과정에서 (삭제) 테이블 잠금이 발생될 수 있고, 이로 인해 로그인을 못하는 경우가 발생될 수 있다.

 

* 솔루션

: 로그 테이블 삭제 시 로그인 장애를 예방하려면, 배치 프로시저에서 트랜잭션 잠금을 최소화하고 삭제 작업을 분할하여 한 번에 대량의 레코드를 삭제하지 않도록 하는 것이 중요하다. 이를 위해 작은 청크 단위로 데이터를 삭제하거나, 지연 삭제 방식으로 오래된 데이터를 정리하면 데이터 삭제로 인한 잠금 시간과 리소스 점유를 줄일 수 있다.

 

1. 배치 프로시저를 청크 단위로 분할

- 잘못된 예시

DELIMITER //

CREATE PROCEDURE DeleteAuditLogs()
BEGIN
    DELETE * FROM LoginLogTable
END //

DELIMITER ;

- 개선 된 예시

DELIMITER //

CREATE PROCEDURE DeleteAuditLogs()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;

    -- 조건에 따라 삭제할 로그 기준을 설정합니다 (예: 90일 이상 된 로그).
    WHILE rows_affected > 0 DO
        -- 청크 단위로 오래된 로그를 삭제합니다.
        DELETE FROM LoginLogTable
        WHERE login_time < DATE_SUB(NOW(), INTERVAL 90 DAY)
        LIMIT 1000;

        -- 방금 수행한 삭제로 인해 영향을 받은 행 수를 가져옵니다.
        SET rows_affected = ROW_COUNT();

        -- 잠시 대기하여 다른 트랜잭션이 처리될 수 있도록 합니다 (1초 대기).
        DO SLEEP(1);
    END WHILE;
END //

DELIMITER ;

 

2. 테이블 파티셔닝 (초기 아키텍팅 시)

: 특정 컬럼 값을 기준으로 테이블을 분할하여 데이터 검색, 삽입, 삭제 등의 성능을 개선할 수 있다. 단일 파티셔닝에서는 하나의 파티션 방식만 사용할 수 있으며, (8.0부터) 서브 파티셔닝 없이 단일 파티셔닝 방법 중 하나를 선택해 데이터를 분할해야함.

CREATE TABLE LoginLogTable (
    log_id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    login_time DATE,
    PRIMARY KEY(log_id, login_time)
)
PARTITION BY RANGE (YEAR(login_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

주의사항

• 테이블이 작을 경우 파티셔닝의 오버헤드로 인해 오히려 성능이 저하될 수 있다.
• 파티션 관리가 복잡해질 수 있으므로, 주기적으로 데이터를 유지 관리할 방안을 마련하는 것이 좋음.
• 단일 파티셔닝에서 지원하는 방식 외에는 MySQL 8.0에서 서브 파티셔닝이 불가능하므로, 대체 방법으로 테이블을 수동으로 나누거나 스크립트를 사용해 자동 관리할 필요가 있을 수 있다.