글을 작성하게 된 계기
영중소 차액 정산 프로젝트를 하면서 작게는 몇만 건, 많게는 수천만 건의 데이터를 다루고 있습니다. 그러다 보니 배치 처리를 할 때, 어떻게 멱등성을 보장하고, 실패 시 롤백을 가능하게 하는 것에 대해 고민하게 되었고, 이 과정에서 알게 된 내용을 정리하기 위해 글을 작성하게 되었습니다.
1. 백업 데이터의 필요성과 방법
백업 데이터를 만드는 이유는 작업이 실패 했을 때 데이터를 원복 할 수 있도록 하기 위함입니다. 배치 작업을 하다 보면 트랜잭션 타임아웃, 잘못된 데이터 문제 등과 같은 예상치 못한 이슈가 생각보다 많이 발생하거든요. 백업 데이터가 있다면, 작업 수행 전 원본 데이터를 안전하게 보관하고 언제든 원본 데이터로 복구할 수 있겠죠? 🚀
배치 작업은 정적 데이터로 수행하는 경우가 많기 때문에, 원복할 데이터만 남아 있다면 어느 정도 이를 만회할 수 있죠.
여기에는 백업 테이블, 파일 백업, 스냅샷 등 몇 가지 방법이 있는데 이에 대해 간단히 살펴보겠습니다.
백업 테이블: 배치 대상 테이블과 동일한 구조의 백업 테이블을 만들어두고 데이터를 사전에 복사하는 방식파일 백업: 배치 대상 데이터를 CSV, JSON, Parquet 등의 포맷으로 파일로 덤프한 뒤, 외부 스토리지에 백업하는 방식스냅샷: 클라우드에서 제공하는 스냅샷 기능을 활용해 전체 데이터를 빠르게 복구하는 방식
1-1. 백업 테이블
가장 단순하고 보편적인 방법입니다. 배치 대상 테이블과 동일한 구조의 백업 테이블 을 만들어두고 데이터를 사전에 복사 해두는 방식입니다. 문제 발생 시, 해당 테이블로부터 빠르게 원복할 수 있습니다. 작업 도중 장애가 발생했을 때 빠르게 복구할 수 있다는 장점이 있지만, 동일한 DB 스토리지를 사용하기 때문에 디스크 손상 같은 물리적인 문제에는 대응하지 못한다는 한계가 있습니다.
1
2
INSERT INTO target_table_backup
SELECT * FROM target_table;
1-2. 파일로 백업
배치 대상 데이터를 CSV, JSON, Parquet 등의 포맷으로 파일로 덤프한 뒤, S3, NFS, NAS 등의 외부 스토리지에 백업하는 방식입니다. 원본 테이블을 직접 건드리지 않으면서, 안정적으로 장기간 보관하거나 DR(Disaster Recovery) 용도로도 활용할 수 있습니다. 또한 S3와 같은 오브젝트 스토리지는 내구성을 보장하므로, 데이터 유실 우려를 줄일 수 있습니다.
1
2
3
4
5
6
SELECT *
FROM target_table
INTO OUTFILE '/tmp/target_table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
1
$ aws s3 cp ${LOCAL_DIRECTORY}:${AWS_S3_BUCKET}/target_table.csv
1-3. 스냅샷 백업
클라우드를 사용할 경우, 스냅샷을 사용할 수도 있습니다. 스냅샷 방식은 전체 데이터를 빠르고 안전하게 복구할 수 있지만 복구 시 전체 DB를 다시 복원해야 하므로 시간이 오래 걸릴 수 있습니다. 물론 스냅샷을 새로운 인스턴스로 복구해 무중단으로 운영 중인 서비스와 병행해 복원하는 방법도 가능합니다.
1
2
3
aws rds create-db-snapshot \
--db-instance-identifier mydb-instance \
--db-snapshot-identifier mydb-snapshot-20250715
1
2
3
aws rds restore-db-instance-from-db-snapshot \
--db-snapshot-identifier mydb-snapshot-20250715 \
--db-instance-identifier mydb-restore-20250715
이 외에도
배치성 데이터베이스 분리,CDC,mysqldump등 다양한 방법이 있습니다.
2. 어떤 방식이 적절할까?
많은 방법이 있겠지만 개인적으로 가장 적절한 방법은 백업 테이블에 복사 하는 것입니다. 별도의 외부 스토리지나 파일 시스템을 사용하지 않고, 데이터베이스 내에서 관리할 수 있기 때문에 관리가 용이하며, 데이터베이스의 트랜잭션 기능을 활용할 수 있기 때문입니다. 여기서도 몇 가지 팁이 존재하는데, 이에 대해 살펴보겠습니다.
백업 테이블에 복사: 데이터베이스 내에서 관리 가능, 트랜잭션 기능 활용 가능파일로 백업: 공간과 비용 측면에서 유리, 데이터 원본 테이블을 건드리지 않음
2-1. 최초 백업 테이블 생성
CREATE TABLE ... AS SELECT ... 는 기존 테이블에서 데이터를 복사할 때 자주 사용되며, INSERT INTO ... SELECT ... 보다 훨씬 빠른 경우가 많습니다. 이는 새로운 테이블을 생성하면서 데이터를 한 번에 물리적으로 기록하기 때문입니다. 이 과정에서는 기존 테이블처럼 인덱스, 제약조건(PK, FK), 트랜잭션 Undo/Redo 로그, Unique 검사 등을 수행하지 않고, 순수하게 데이터를 파일에 바로 적재합니다.
1
2
CREATE TABLE backup_table AS
SELECT * FROM target_table;
반면 INSERT INTO ... SELECT ... 는 target_table_backup 이 이미 존재하고 인덱스나 PK가 잡혀 있다면, 복사하는 데이터 하나하나에 대해 인덱스 갱신과 무결성 검사를 수행하게 되어 성능이 느려질 수 있습니다.
1
2
INSERT INTO backup_table
SELECT * FROM target_table;
압도적으로 성능이 빠르지만 CREATE TABLE ... AS SELECT ... 는 테이블이 없을 때만 사용할 수 있기 때문에, 백업 테이블을 처음 생성할 때는 이 방법을 사용하고, 이후에는 INSERT INTO ... SELECT ... 를 사용하는 것이 좋습니다.
매 번 테이블을 새로 생성하는 것은 시간이 조금 걸리는 것 보다 훨씬 번거로울 수 있습니다. 🐧
2-2. 병렬 처리
대량 데이터를 백업하거나 이관할 때는 단일 쿼리로 처리하기보다는, 데이터를 범위별로 분할해 병렬로 수행하면 더 빠르게 처리할 수 있습니다. 특히 테이블 크기가 수백만 건을 넘어가면 하나의 커넥션에서 순차적으로 처리하는 것보다, PK나 ID 범위를 나누어 여러 쓰레드에서 동시에 INSERT를 수행하면 병목을 줄일 수 있습니다.
1
2
3
4
5
6
7
8
9
10
11
-- 첫 번째 커넥션
INSERT INTO backup_table
SELECT * FROM target_table
WHERE id BETWEEN 1 AND 1000000;
-- 두 번째 커넥션
INSERT INTO backup_table
SELECT * FROM target_table
WHERE id BETWEEN 1000001 AND 2000000;
-- ... (동일하게 여러 구간으로 병렬 분할)
2-3. InnoDB Buffer Pool 조정
버퍼 풀(Buffer Pool)은 InnoDB 스토리지 엔진이 디스크 I/O를 줄이기 위해 데이터를 메모리 상에 올려두고 캐시하는 공간입니다. 이 공간이 충분하지 않으면, 데이터를 읽거나 쓰는 작업마다 디스크 I/O가 반복적으로 발생해 배치 작업 속도가 느려지게 됩니다.
대량의 데이터를 읽거나 쓰는 배치 작업에서는 버퍼 풀 크기를 늘리면 더 많은 데이터를 메모리에 적재할 수 있어, 디스크 접근을 최소화하고 전체 작업 속도를 개선할 수 있습니다. 일반적으로 MySQL 서버 메모리의 60~80% 수준으로 innodb_buffer_pool_size 를 조정하는 것이 권장됩니다.
1
SET GLOBAL innodb_buffer_pool_size = 2147483648;
버퍼 풀을 변경해도 이미 쿼리가 실행되고 있거나 세션에 연결된 상태라면 즉시 효과가 반영되지 않을 수 있습니다.
3. 정말 최선일까?
테이블 백업 방식이 좋다고 소개했는데, 여기에도 몇 가지 한계가 존재합니다. 이에 대해서도 살펴보겠습니다.
너무 많은 데이터를 백업 해야할 때: 수억 건의 데이터를 복사하는 작업은 I/O와 CPU를 과도하게 사용합니다.락: 여러 커넥션에서 동시 잠금으로 인한 데드락 발생 위험이 존재합니다.
3-1. 너무 많은 데이터를 백업 해야할 때
테이블 백업 방식은 수 백만 건까지는 수 분 내에 잘 처리할 수 있습니다. 단, 수억 건의 데이터를 INSERT ... SELECT ... 로 복사하는 작업은 I/O와 CPU를 사용합니다. 이로 인해 운영 서비스의 다른 쿼리들이 응답 지연을 겪거나 최악의 경우 타임아웃이 발생할 수 있습니다. 즉, 배치 작업 때문에 전체 서비스가 느려질 수 있기 때문에 조심해야 합니다.
데이터가 정말 많을 경우, MySQL LOAD DATA를 활용하는 방식이 훨씬 효율적입니다.
3-2. 락으로 인한 문제
여러 커넥션에서 동시에 INSERT ... SELECT ... 구문을 실행하면 잠금으로 인한 데드락 발생 위험이 존재합니다. InnoDB는 데이터의 무결성을 보장하기 위해 레코드 락(Record Lock), 갭 락(Gap Lock), 넥스트 키 락(Next-Key Lock) 과 같은 다양한 잠금 방식을 사용합니다. 특히 PK나 인덱스 범위가 겹치는 구간에 대해 여러 커넥션이 동시에 삽입을 시도하는 경우, 각 커넥션이 서로 필요한 잠금을 보유한 채 대기하는 상황이 발생할 수 있습니다.
락 점유 순서가 꼬이게 되고, 결국 MySQL이 데드락을 감지하여 트랜잭션을 강제로 종료하게 됩니다.
3-3. 복제 지연
대량 데이터를 다루는 배치 작업을 수행할 경우, MySQL 복제 구조에서는 복제 지연 문제가 발생할 수 있습니다. MySQL의 복제는 기본적으로 Primary에서 발생한 변경 사항을 바이너리 로그(Binary Log)에 기록하고, 복제 서버는 이 바이너리 로그를 가져와 릴레이 로그(Relay Log)에 적재한 후 순차적으로 실행합니다.
배치 작업에서 수백만 건 이상의 데이터를 한 번에 삽입하거나 갱신하는 경우, 프라이머리(Primary)에서는 하나의 큰 트랜잭션으로 처리되어 바이너리 로그가 빠르게 작성되지만, 복제 서버에서는 이 릴레이 로그를 순차적으로 실행해야 하므로 처리 속도 차이가 발생하게 됩니다. 이 과정에서 복제 서버가 한 트랜잭션을 끝낼 때까지 뒤따르는 바이너리 로그를 처리하지 못해 Seconds_Behind_Master 값이 급격하게 증가하는 현상이 발생할 수 있습니다.
예를 들어, 프라이머리에서 500만 건을 1초 만에 처리했다고 하더라도, 복제 서버는 이 500만 건을 하나하나 순차적으로 실행하며 수십 분 이상 소요될 수 있습니다. 이 경우 복제 서버가 프라이머리를 따라잡지 못하고 복제 지연이 발생하게 됩니다.
따라서 이를 해결하기 위해서는 배치 작업을 작은 단위로 나누어 수행하도록 합니다.
1
2
3
4
5
6
7
8
9
10
11
-- 첫 번째 커넥션
INSERT INTO backup_table
SELECT * FROM target_table
WHERE id BETWEEN 1 AND 1000000;
-- 두 번째 커넥션
INSERT INTO backup_table
SELECT * FROM target_table
WHERE id BETWEEN 1000001 AND 2000000;
-- ... (동일하게 여러 구간으로 병렬 분할)
4. 정리
많은 데이터를 다루는 작업은 생각보다 꽤 어렵습니다. 전체 데이터를 다 확인하기 어려운 경우 가 많고, 타임아웃, 데이터 누락 과 같은 예상치 못한 이슈가 발생하기도 합니다. 따라서 배치 작업을 수행하기 전에는 반드시 백업을 해둡시다. 🚀