채팅 내용들을 MySQL에 담아두고, 30일 이후 자동 삭제시키기 위한 여정입니다.
꼭 RDB를 써야 하는 상황이 아니라면 NoSQL에서 TTL을 설정하여 작업하시길 추천드립니다.
DB에 대해 아무것도 모르던 학부연구생 시절, MySQL에 TTL을 (좋지 않은 방법으로) 구현해 본 경험이 있습니다.
한 산학협력 과제에서, 특정 장비들의 센서값을 일주일동안 유지시켜 달라는 요청이 있었습니다. 그나마 연구실 내에서 DB를 접해본 건 제가 유일했기에(단지 DB에서 읽어온 값을 화면에 띄워주는 일이었음에도 불구하고) 해당 과제를 맡게 되었습니다.
하룻동안 DB의 기본 내용에 대해 공부한 후, TTL을 구현하기 위해 값 추가를 요청하는 경우 해당 시간 - 7일을 계산하고 DELETE 쿼리를 날렸습니다.
즉 하나의 INSERT 요청마다 추가적인 DELETE 쿼리를 발생시키는 비효율적인 API를 만들게 되었습니다.
그 뿐만이 아니었습니다. 데이터는 1분 주기로 약 600개씩 계속 추가되었고, 이를 한 달 동안 실행시켰으니 600 * 60 * 24 * 30 = 25,920,000으로 한달간 약 2천6백만의 데이터가 생성되었습니다.
아무리 DELETE 쿼리를 동작시켜도 (5.X 버전 기준)인덱스는 정리되지 않았으므로 시간이 지나면 지날수록 DB는 느려져갔고, 그 누구도 이유를 알지 못한 채 해당 과제는 종료되었습니다.
시간이 지나서야 해당 이유를 알게 되었고, 이번에는 같은 실수를 반복하지 않고 싶었습니다. 따라서 좋은 방법을 고민해 보았습니다.
INSERT + DELETE
위에서 잠깐 언급한 방법입니다. 하나의 POST 요청에 대해 의미가 다른 두 개의 쿼리가 발생하는 것이므로, 이번에는 다른 방법을 사용해보고자 했습니다.
EVENT + reIndexing
지금은 해당 방법도 장점이 있다고 생각합니다. 프로젝트의 성격에 맞게 고려해보세요.
매일마다 한번씩 delete 쿼리를 동작시키고, 다시 인덱싱 작업을 하는 방법입니다.
데이터가 엄청나게 많을 경우, 시간이 오래 걸릴 수 있다고 생각이 되었습니다.
PARTITION + EVENT
매일마다 파티션을 생성하고, 오래된 파티션부터 하나씩 제거하는 방법입니다.
데이터 수가 많아도 하나의 파티션을 제거하는 건 DELETE + reIndexing보단 훨씬 리소스를 덜 잡아 먹으리라고 생각했습니다.
삭제하는 순간 테이블 락이 걸릴 수 있겠지만, temp 테이블로 데이터를 옮긴 후 drop하면 락을 회피할 수 있다고 합니다(이번에 적용하지는 않았습니다).
sql
CREATE DATABASE IF NOT EXISTS zelkova;
USE zelkova;
...
CREATE TABLE `chats`
(
`chat_id` BIGINT NOT NULL AUTO_INCREMENT,
`chatroom_id` BIGINT NOT NULL,
`account_id` BIGINT NOT NULL,
`content` TEXT NOT NULL,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME NOT NULL,
PRIMARY KEY (`chat_id`, `created_at`),
INDEX `idx_chatroom_id` (`chatroom_id`),
INDEX `idx_account_id` (`account_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
ALTER TABLE `chats` PARTITION BY RANGE (TO_DAYS(`created_at`)) (
PARTITION `p_max` VALUES LESS THAN MAXVALUE
);
...
DELIMITER $$
DROP PROCEDURE IF EXISTS `partition_delete`$$
CREATE PROCEDURE `partition_delete`(
input_date DATE
)
BEGIN
DECLARE partition_names TEXT;
SET partition_names = (
SELECT GROUP_CONCAT(PARTITION_NAME)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'chats'
AND PARTITION_NAME < CONCAT('p_', DATE_FORMAT(input_date, '%Y%m%d'))
);
IF LENGTH(partition_names) > 0 THEN
SET @drop_stmt = CONCAT('ALTER TABLE chats DROP PARTITION ', partition_names, ';');
PREPARE drop_partitions FROM @drop_stmt;
EXECUTE drop_partitions;
DEALLOCATE PREPARE drop_partitions;
END IF;
END $$
DROP PROCEDURE IF EXISTS `partition_add`$$
CREATE PROCEDURE partition_add(
input_date DATE
)
BEGIN
SET @add_stmt = CONCAT(
'ALTER TABLE `chats` REORGANIZE PARTITION p_max INTO (',
'PARTITION p_', DATE_FORMAT(input_date, '%Y%m%d'), ' VALUES LESS THAN (TO_DAYS("', input_date, '")),',
'PARTITION p_max VALUES LESS THAN (MAXVALUE)',
');'
);
PREPARE add_partitions FROM @add_stmt;
EXECUTE add_partitions;
DEALLOCATE PREPARE add_partitions;
END $$
SET GLOBAL event_scheduler = ON;
DROP EVENT IF EXISTS `ev_daily_partition`$$
CREATE EVENT `ev_daily_partition`
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMPADD(HOUR, 4, CURDATE())
ON COMPLETION NOT PRESERVE ENABLE
DO
BEGIN
DECLARE today DATE;
SET today = CURDATE();
CALL partition_delete(DATE_SUB(today, INTERVAL 30 DAY));
CALL partition_add(DATE_ADD(today, INTERVAL 1 DAY));
END $$
DELIMITER ;이하 sql 설명입니다. 설명하기 쉬운 순서로 진행하겠습니다.
테이블 생성
CREATE TABLE `chats`
(
`chat_id` BIGINT NOT NULL AUTO_INCREMENT,
`chatroom_id` BIGINT NOT NULL,
`account_id` BIGINT NOT NULL,
`content` TEXT NOT NULL,
`created_at` DATETIME NOT NULL,
`updated_at` DATETIME NOT NULL,
PRIMARY KEY (`chat_id`, `created_at`),
INDEX `idx_chatroom_id` (`chatroom_id`),
INDEX `idx_account_id` (`account_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;채팅 내용을 담을 테이블을 생성합니다.
파티션을 나누기 위해서는 PK에 파티션 적용할 컬럼이 포함되어 있어야 합니다. 따라서 created_at이 PK에 포함되어 있습니다.
저는 id-datetime 순으로 담았으나, 만약 날짜를 기준으로 검색이 많이 이루어진다면 순서를 바꾸는 게 좋을 것입니다.
또한 파티션을 나눌 테이블에는 FK가 존재해선 안 됩니다. chatroom_id와 account_id에는 FK가 걸려 있었지만, 이를 해제하도록 했습니다. 대신 INDEX를 걸었습니다.
각 파티션마다 index가 적용되어 있으므로, 테이블 내에서 전체 검색 시 이전보다는 낮은 성능을 보일 것입니다.
기본 파티션 생성
ALTER TABLE `chats` PARTITION BY RANGE (TO_DAYS(`created_at`)) (
PARTITION `p_max` VALUES LESS THAN MAXVALUE
);모든 값들을 담을 기본 파티션을 생성하였습니다.
오늘, 내일의 데이터를 담을 파티션이 당장 필요하다! 하시면 이 과정에서 따로 생성해주시면 되겠습니다.
파티션 생성
CREATE PROCEDURE partition_add(
input_date DATE
)
BEGIN
SET @add_stmt = CONCAT(
'ALTER TABLE `chats` REORGANIZE PARTITION p_max INTO (',
'PARTITION p_', DATE_FORMAT(input_date, '%Y%m%d'), ' VALUES LESS THAN (TO_DAYS("', input_date, '")),',
'PARTITION p_max VALUES LESS THAN (MAXVALUE)',
');'
);
PREPARE add_partitions FROM @add_stmt;
EXECUTE add_partitions;
DEALLOCATE PREPARE add_partitions;
END $$정확히는 파티션 분리라고 말씀드리는 게 좋을 것 같네요. p_max 파티션을 p_yyyyMMdd와 p_max 파티션으로 분리합니다.
p_yyyyMMdd 파티션에는 yyyy년 MM월 dd일까지에 해당하는 데이터들이 담깁니다.
파티션 삭제
CREATE PROCEDURE `partition_delete`(
input_date DATE
)
BEGIN
DECLARE partition_names TEXT;
SET partition_names = (
SELECT GROUP_CONCAT(PARTITION_NAME)
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'chats'
AND PARTITION_NAME < CONCAT('p_', DATE_FORMAT(input_date, '%Y%m%d'))
);
IF LENGTH(partition_names) > 0 THEN
SET @drop_stmt = CONCAT('ALTER TABLE chats DROP PARTITION ', partition_names, ';');
PREPARE drop_partitions FROM @drop_stmt;
EXECUTE drop_partitions;
DEALLOCATE PREPARE drop_partitions;
END IF;
END $$input_date보다 낮은 값들을 지니는 p_yyyyMMdd 파티션들을 가져오고, 해당하는 파티션들을 삭제합니다.
삭제 시 테이블에 락이 걸릴 수 있습니다. 임시 테이블을 생성 후 해당 테이블에 파티션 데이터를 옮기고, 임시 테이블을 삭제한다면 락을 회피할 수 있습니다.
이벤트 발행
CREATE EVENT `ev_daily_partition`
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMPADD(HOUR, 4, CURDATE())
ON COMPLETION NOT PRESERVE ENABLE
DO
BEGIN
DECLARE today DATE;
SET today = CURDATE();
CALL partition_delete(DATE_SUB(today, INTERVAL 30 DAY));
CALL partition_add(DATE_ADD(today, INTERVAL 1 DAY));
END $$매일 새벽 4시에, 30일 전에 해당하는 파티션들을 삭제하고 다음날에 해당하는 파티션을 생성합니다.
마치며
아쉽게도, 해당 방법을 채택하기보다 NoSQL을 적용하게 될 것 같습니다. 채팅 데이터는 추가와 조회가 대부분이기도 하고, TTL을 손쉽게 적용할 수도 있고, 시간이 흐름에 따라 채팅 데이터를 이루는 컬럼들이 계속해서 변할 수도 있기 때문입니다.
정말 DB를 잘 아시는 분들은 수정할 부분이 많이 보일 듯 합니다. 흐름만 잡았기에, 원하시는 부분을 잘 수정하셔서 사용하시면 될 것 같습니다. 댓글로 적어 공유해주시면 더 감사하겠습니다.
Reference
