[26.09.21] 챌린지 API의 횟수 원장 통합을 위한 보관과 조회 방식 비교하기

기능마다 테이블을 만들지 않기 위한 횟수 이력 통합 검토하기

사용자별로 기능을 하루에 사용할 수 있는 횟수를 제한합니다. 사용 이력을 원장 형식으로 쌓고, 이 기록으로 일일 사용 횟수를 계산합니다.

지금은 기능별로 횟수 테이블을 두는데, 기능 출시가 잦아지면서 새 테이블을 만드는 일이 부담이 됐습니다. 같은 역할의 테이블을 하나로 통합하는 방안을 검토하고 있고, 이관은 기존 테이블과 통합 테이블에 함께 기록하는 Dual Write 방식으로 계획했습니다.

통합하면 한 테이블에 쌓이는 데이터가 많아집니다. 일일 사용 횟수를 계산하는 쿼리의 실행 시간이 얼마나 늘어나는지, 계속 쌓이는 원장의 저장 공간을 어떤 기준으로 정리할지 확인해야 합니다.

과거 이력의 저장 공간을 줄이기 위한 압축 위치 비교하기

일자별 파티션을 기준으로 과거 이력을 보관하고 삭제하는 두 방안을 검토합니다. PARTITION BY RANGE COLUMNS(...)로 날짜 범위를 정의하면 데이터는 입력 시 해당 파티션에 저장되고, 이후 날짜의 파티션을 미리 추가하는 관리만 필요합니다. (MySQL 파티션 관리 문서)

MySQL 8.4 InnoDB는 특정 파티션만 압축하거나 시간이 지나면 자동으로 압축하는 기능이 없습니다. ROW_FORMAT=COMPRESSED는 테이블 전체에 적용되므로 최근 이력과 인덱스도 압축됩니다. 따라서 전체를 압축해 한 테이블로 관리할지, 과거 이력만 별도 테이블로 옮겨 압축할지를 비교합니다. (MySQL 테이블 정의, 압축 적용 범위)

운영 테이블 전체 압축과 일자별 파티션 유지

• 흐름: COMPRESSED 운영 테이블의 일자별 파티션에 기록 후 보관 → 일주일 뒤 외부 파일로 추출 후 검증 → 해당 파티션 DROP
• 검토 이유: 별도 보관 테이블과 압축 전환 작업이 없고, 테이블을 줄이려는 처음 목적에도 맞습니다.
• 선택 기준: 최근 이력의 삽입과 일일 횟수 조회에도 압축과 해제 비용이 붙습니다. 실제 요청의 CPU와 응답 시간을 감당할 수 있을 때 선택합니다.

별도 보관 테이블로 이동

• 흐름: DYNAMIC 운영 테이블의 과거 파티션을 EXCHANGE PARTITION으로 보관 테이블에 옮김 → COMPRESSED로 전환 → 일주일 보관 → 외부 파일로 추출 후 검증 → 보관 테이블 DROP
• 검토 이유: 일일 횟수 계산에 쓰지 않는 과거 이력에만 압축을 적용합니다. 대신 테이블 관리와 압축 전환 작업이 늘고, 전환 작업은 운영 요청과 같은 DB의 CPU와 I/O를 공유합니다.

파티션 교환은 양쪽 테이블의 구조와 Row Format이 같아야 하므로, 비압축 파티션을 같은 형식의 테이블로 옮긴 뒤 압축합니다. 대상 날짜 선택, 교환, 압축은 배치로 실행합니다. (MySQL 파티션 교환 조건)

아래 그림은 두 번째 방안입니다. ‘보관 테이블로 이동’은 일자별 파티션을 운영 테이블 밖으로 옮기는 단계입니다.

EXCHANGE PARTITION의 내부 동작과 요청 영향

EXCHANGE PARTITION은 행을 복사하고 삭제하는 비용은 없지만, 운영 테이블과 보관 테이블의 Metadata Lock(MDL)이 필요합니다. 과거 이력을 조회하지 않더라도 교환 중에는 오늘의 횟수 조회와 기록이 대기할 수 있고, 범위 검증 옵션은 교환 후 조회 결과에 영향을 줍니다. MySQL 8.4.11 구현을 기준으로 정리했습니다.

파티션과 보관 테이블의 교환 순서

usage_history가 PARTITION BY RANGE COLUMNS(used_date)로 일자별 파티션 p20260913을 갖고 있다는 전제입니다.

CREATE TABLE usage_archive_20260913 LIKE usage_history;
ALTER TABLE usage_archive_20260913 REMOVE PARTITIONING;

ALTER TABLE usage_history
    EXCHANGE PARTITION p20260913
    WITH TABLE usage_archive_20260913
    WITH VALIDATION;

LIKE로 구조와 파티션 정의만 복제한 뒤 파티션 구성을 제거해 같은 구조의 빈 보관 테이블을 만듭니다. 교환 후 과거 이력은 보관 테이블에 있고, p20260913은 빈 파티션으로 남습니다. 압축 전환과 파티션 삭제는 별도 작업입니다. (MySQL 파티션 교환 문서)

1. 구조와 범위 검증: 두 테이블에 MDL_SHARED_NO_WRITE를 확보하고 구조, 인덱스, Row Format의 호환성을 확인합니다. WITH VALIDATION은 파티션으로 들어올 보관 테이블의 행만 날짜 범위와 대조하며, 밖으로 꺼낼 과거 파티션은 검사하지 않습니다.
2. 배타 잠금 전환: 보관 테이블, 운영 테이블 순으로 MDL_EXCLUSIVE를 확보합니다. 두 테이블을 사용 중인 트랜잭션이 있으면 끝날 때까지 기다립니다.
3. InnoDB 파일과 메타데이터 교환: 파티션 파일과 보관 테이블 파일의 이름을 서로 바꾸고, Data Dictionary의 인덱스, 테이블스페이스 연결 정보와 엔진 내부 식별자를 교환합니다. 행을 다시 삽입하거나 인덱스를 재구축하지 않고, 압축도 이 단계에서 하지 않습니다.
4. 커밋과 잠금 해제: Data Dictionary 변경과 DDL을 Binary Log에 기록하고 커밋합니다. 문장이 끝나면서 잠금이 해제되고, 이후 요청은 교환된 구성을 사용합니다.

MDL은 서버 계층(SQL 계층 구현)이, 파일과 메타데이터 교환은 InnoDB(InnoDB 교환 구현)가 담당합니다.

단계별 읽기와 쓰기 영향

표는 잠금 읽기를 제외한 일반 SELECT를 기준으로 합니다. 배타 잠금 전환 시 대기 조건은 다음과 같습니다.

• 기존 트랜잭션이 MDL을 보유한 경우: 교환 명령은 해제될 때까지 기다립니다. 조회가 끝났어도 트랜잭션이 열려 있으면 잠금은 유지됩니다.
• 배타 잠금이 대기 중일 때 새 읽기가 MDL을 요청한 경우: 새 읽기는 배타 잠금 뒤에서 기다립니다. 이미 가진 MDL을 재사용하는 읽기는 기다리지 않습니다.

1단계의 검증 잠금도 충돌하는 MDL을 가진 쓰기 트랜잭션이 있으면 기다립니다. 교환 작업 시간과 잠금 획득 대기 시간은 구분해야 합니다. (MySQL MDL 문서, 배타 잠금 전환 구현)

잠금 범위는 과거 파티션만이 아니라 운영 테이블 전체입니다. 오늘 파티션만 읽는 쿼리도 MDL을 먼저 확보해야 하므로, 파티션 프루닝으로 이 대기를 피할 수 없습니다.

검증 옵션과 교환 후 조회 결과

1단계의 범위 검증은 MDL_SHARED_NO_WRITE를 확보한 뒤 실행되므로, 검증 시간은 운영 테이블의 쓰기 대기 시간에 포함됩니다. (MySQL 파티션 교환 문서)

• 문서 측정값: 100만 행 테이블을 파티션으로 넣을 때 WITH VALIDATION 약 740ms, WITHOUT VALIDATION 약 10ms
• 행을 채운 테이블을 파티션에 넣는 경우: 행 수에 비례해 쓰기 대기가 길어집니다.
• 빈 보관 테이블로 과거 파티션을 꺼내는 경우: 검사할 행이 없어 두 옵션의 비용이 같습니다.

검증을 생략하면 무엇을 포기하는지 MySQL 8.4.11에서 재현했습니다. id 기준 p0 VALUES LESS THAN (50), p1 VALUES LESS THAN (100)으로 나눈 테이블의 p0과 id=51이 든 테이블을 교환했습니다.

• WITH VALIDATION: ERROR 1737 Found a row that does not match the partition로 교환이 거부됩니다.
• WITHOUT VALIDATION: 교환이 성공하고 id=51이 p0에 남습니다. 전체 조회에는 보이지만 WHERE id = 51, WHERE id BETWEEN 50 AND 60, 같은 조건의 DELETE는 p1만 읽어 0건입니다.
• PK 중복과 복구: id=51을 다시 INSERT하면 고유 인덱스가 파티션 단위로 검사돼 p1에 들어갑니다. CHECK PARTITION p0은 위반 행을 찾지만, REPAIR PARTITION p0은 p1의 중복 행과 충돌해 실패합니다.

프루닝은 데이터를 읽지 않고 파티션 정의로 대상을 계산하므로, 정의를 위반한 행은 조건 조회와 변경에서 보이지 않습니다.

• 행을 채운 테이블을 파티션에 넣는 경우: 적재 쿼리의 범위 조건으로 위반 행을 막을 수 있을 때 WITHOUT VALIDATION을 검토하고, 교환 직후 CHECK PARTITION으로 확인합니다.
• 빈 보관 테이블로 과거 파티션을 꺼내는 경우: 절감할 비용이 없어 기본값을 유지합니다.

운영 요청 지연을 줄일 기준

• 오래 열린 트랜잭션 확인: 조회를 끝내고 커밋하지 않은 트랜잭션도 MDL을 유지합니다. performance_schema.metadata_locks에서 두 테이블의 GRANTED와 PENDING을 확인합니다.
• 배치 세션의 잠금 대기 제한: SET SESSION lock_wait_timeout = 3처럼 대기를 제한하고, 실패하면 다음 배치에서 재시도합니다. 이 값은 개별 MDL 획득 대기의 제한이며 명령 전체의 시간 제한은 아닙니다. (MySQL 설정 문서)
• 실제 요청과 함께 확인: 교환 중 일일 횟수 조회와 기록의 지연과 타임아웃을 확인합니다. 아래 압축 실험은 잠금 시간을 측정한 것이 아닙니다.

InnoDB 압축 설정 비교

보관 위치를 정하기 위해 압축 설정별 용량과 처리 시간을 로컬에서 합성 데이터로 실험했습니다. 일괄 적재와 파일 추출만 비교했으므로, 운영 테이블 전체 압축을 선택하려면 삽입과 횟수 조회가 함께 실행되는 조건도 확인해야 합니다.

실험 조건

• 환경: MySQL 8.4.11, CPU 한도 2개, Buffer Pool 512MiB
• 압축 조건: 기본 페이지 16KiB, innodb_compression_level=6으로 고정하고 압축 페이지 크기만 변경
• 데이터: 정수, 날짜, 16바이트 요청 ID로 구성한 동일한 합성 원장 50만 행
• 인덱스: 기본 키, 요청 ID(request_id) 고유 인덱스, (user_id, feature_id, used_at) 복합 인덱스를 모든 설정에 동일하게 적용
• 측정 방식: 적재 3회, 미리 읽은 뒤 재추출 20회, DB 재시작 후 첫 추출 5회. 각 회차의 설정 비교 순서는 무작위로 변경
• 용량: 데이터, 인덱스, 파일 내 여유 공간을 포함한 .ibd 파일 크기
• 적재 시간: 테이블 생성을 제외하고 트랜잭션 완료까지
• 추출 시간: 전체 행을 기본 키 순서로 읽어 호스트의 TSV 파일에 저장하는 시간. 클라이언트 시작과 파일 쓰기를 포함하고, 원격 저장소 업로드와 fsync는 제외

비압축 ROW_FORMAT=DYNAMIC과 ROW_FORMAT=COMPRESSED의 KEY_BLOCK_SIZE 8, 4, 2, 1을 비교했습니다. KEY_BLOCK_SIZE는 압축 페이지 크기로, 압축 레벨과 다르며 8로 설정해도 용량이 절반이 되지는 않습니다. (MySQL 압축 테이블 문서)

KEY_BLOCK_SIZE별 측정 결과

적재는 Binary Log를 끄고, 추출은 binlog_format=ROW, sync_binlog=1로 켜고 따로 측정했습니다. 첫 추출은 Buffer Pool 자동 복원을 끄고 DB를 재시작한 뒤 측정했고, OS와 스토리지 캐시는 비우지 않았습니다.

아래는 중앙값입니다. DB CPU는 첫 추출 중 mysqld의 누적 CPU 시간이며, 사용률이나 피크가 아닙니다.

• 작은 압축 페이지가 항상 유리하지는 않았습니다. 4KiB 아래로는 용량 절감 없이 적재 시간과 첫 추출의 압축 해제, CPU 시간이 늘었습니다.
• 재추출은 비압축과 8KiB, 4KiB, 2KiB의 차이가 작았습니다. 실행별 변동 안에 있어 같은 설정이 매번 빠르지 않았습니다. 첫 추출은 2KiB와 1KiB가 5회 모두 나머지보다 느렸습니다.
• 이번 데이터에서는 8KiB와 4KiB가 후보입니다. 적재 비용을 우선하면 8KiB, 저장 용량을 우선하면 4KiB입니다.

다섯 테이블이 512MiB Buffer Pool을 공유했고, 1KiB는 미리 읽은 뒤에도 압축 해제가 다시 발생했습니다. 재추출 결과에는 메모리에 남은 페이지 형태도 영향을 줬습니다.

Append-only 데이터 관리 사례

보관한 데이터를 정리하고 다시 조회하는 방법은 두 사례에서 살펴봤습니다. Percona는 오래된 데이터를 분리하는 단위, Airtable은 외부로 옮긴 이력을 조회하는 구조입니다.

Percona: 파티션 단위 이력 분리와 삭제

Smit Arora의 Quick Data Archival in MySQL Using Partitions를 읽었습니다.

• 배경: 행 단위 DELETE는 작업량과 Binary Log가 많고, 삭제한 만큼 데이터 파일이 바로 줄지도 않습니다.
• 접근 방식: 날짜 범위 파티션을 같은 구조의 빈 테이블과 EXCHANGE PARTITION으로 교환해 운영 테이블에서 분리한 뒤 정리합니다.
• 배운 점: 함께 보관하고 함께 삭제할 범위를 정하면 그 기간의 이력을 한 묶음으로 관리할 수 있습니다. 횟수 원장의 보관과 삭제 단위도 같은 관점으로 정합니다.
• 키 설계 확인: MySQL은 파티션 키를 모든 UNIQUE KEY와 PRIMARY KEY에 포함하도록 요구합니다. request_id 고유 제약에 날짜가 들어가면 중복을 막는 범위가 달라지므로 키 설계와 함께 검토합니다. (MySQL 파티션 키 제약)

Airtable: 과거 이력의 외부 보관과 조회

Matthew Jin의 How we reduced archive storage costs by 100x and saved millions을 읽었습니다.

• 배경: 변경 이력이 쌓여 MySQL 저장 비용과 용량 한계에 대응해야 했습니다. 오래된 이력은 조회가 드물고 거의 수정되지 않지만, 사용자가 열면 빠르게 보여야 했습니다.
• 저장 구조: 최근 이력은 MySQL, 과거 이력은 S3의 Parquet 파일입니다. 조회 단위인 Base별로 파일을 나누고 ID 순으로 정렬해 필요한 범위만 읽습니다.
• 조회 구조: 파일 위치는 DynamoDB로 관리하고, DataFusion으로 읽어 MySQL의 최근 이력과 합쳐 제공합니다.
• 이관 검증: 원본과 대량 비교하고, 실제 요청을 기존 경로와 신규 경로로 실행해 결과를 대조했습니다.
• 결과와 범위: 데이터 크기와 바이트당 비용이 각각 약 10분의 1이 되어 저장 비용이 약 100분의 1로 줄었습니다. 앞의 InnoDB 압축률과는 비교 조건이 다릅니다. 자동 증분 아카이빙은 글 작성 시점의 후속 과제였습니다.
• 배운 점: 과거 이력의 조회 주체와 용도가 저장 구조를 정합니다. 횟수 원장도 장애 조사 때 복구해 확인하면 되는지, 사용자 요청에 바로 응답해야 하는지에 따라 외부 저장 형식과 조회 구조가 달라집니다. 일주일 보관 기간도 필요한 조회와 재처리 범위로 판단합니다.

삭제 후에도 이력을 조회하기 위한 보관 범위와 검증 기준 정하기

처음에는 기능마다 만드는 테이블을 줄이려 했지만, 압축 실험과 두 사례를 거치며 통합 테이블에 남길 범위와 밖으로 옮긴 이력의 관리 방법까지 함께 정해야 한다는 생각이 들었습니다. 파티셔닝이나 외부 조회 엔진을 고르기 전에 정할 기준은 다음과 같습니다.

• 운영 테이블에 남길 범위: 일일 횟수 계산이 끝난 뒤에도 지연 요청이나 재처리로 이력이 바뀔 수 있는지 확인합니다.
• 보관 기간과 조회 방식: 일주일 동안 DB에 보관할 이유를 확인하고, 외부 추출 이후 누가 조회하며 어떤 응답 시간이 필요한지 정합니다.
• 보관 위치와 공간 회수: 운영 테이블의 파티션으로 둘지 별도 보관 테이블로 옮길지 비교합니다. 이관과 압축 전환에 필요한 여유 공간과 삭제 후 실제 디스크 사용량도 확인합니다.
• 삭제를 시작할 검증 기준: 추출 범위, 행 수, 파일 해시, 저장 위치를 기록하고 외부 이력을 다시 읽을 수 있는지 확인합니다. 추출이 실패하거나 재실행돼도 검증된 범위만 삭제합니다.