글을 작성하게 된 계기

회사에서 수천만 건의 txt 파일을 빠르게 INSERT 해야 하는 상황이 있었고, 이를 어떻게 해결했는지 정리하기 위해 글을 작성하게 되었습니다.

1. 문제 상황

현재 영세/중소 상인들의 카드 수수료를 감면해 주는 시스템 을 만들고 있습니다. 이를 위해서는 여신협회에서는 반기마다 전달하는 영세/중소 상인들의 사업자 번호가 담긴 파일(.txt) 들을 데이터베이스에 저장해야 하는데요, 한 파일에 데이터가 적게는 수만 건 부터 많게는 수백만 건 까지 존재하며, 반기마다 약 400만이 조금 넘는 데이터가 적재되는 상황입니다.

2022년 부터 2025년 까지 데이터를 저장해야 하기 떄문에 전체 건 수는 약 2,200만 건 정도입니다.

데이터 구조는 간단했지만, 파일에 담긴 몇 가지 정보를 토대로 가맹점 매출 구간, 년도, 반기 등 추가적인 정보를 파싱해야 했습니다. 가장 까다로운 조건은 한 파일에 담긴 데이터 개수에 상관없이 하나 파일 저장을 최대 1분 30초 내로 완료 해달라는 요구사항이 이었습니다. 사용할 수 있는 서버는 두 대 까지 고요.

백 오피스에 있는 기능이기 때문에 서버 리소스를 많이 할당해줄 수 없는 환경이었습니다.

또한 개발 환경에서 한 사람당 사용할 수 있는 테스트 데이터베이스커넥션션 개수가 제한 돼 있는데요, 그렇다 보니니 개발자가 마음 편하게테스트하기도 힘든 상황이었습니다.여튼 불만은 그만 토로하고 여기까지의 요구사항을 한 번정리해 보죠죠.

서버는 두 대까지 사용 가능
여신협회에서 받은 파일은 적게는 수 만 건, 많게는 수 백 만건의 데이터가 존재
한 파일 당, 1분 30초 내로 저장할 것
테스트 데이터베이스의 커넥션 개수 제한
데이터 정합성이 지켜질 것

2. 해결책 탐색

요구사항을 처리하기 위해 크게 비동기 Bulk Insert 와 MySQL Load Data 두 가지 방법을 고민해 봤습니다. 각 방법과 장/단점에 대해 살펴보겠습니다.

비동기 Bulk Insert
MySQL Load Data

2-1. 비동기 Bulk Insert

처음 고려한 방식은 비동기 와 Jdbc의 Batch Operation 을 활용한 Chunk 단위 데이터 저장 입니다. 이는 데이터가 많아도 꽤 빠르게, 안정적으로 데이터를 저장할 수 있습니다.

Most JDBC drivers provide improved performance if you batch multiple calls to the same prepared statement. By grouping updates into batches, you limit the number of round trips to the database.

이 방식의 장점은 트랜잭션의 장점을 활용 할 수 있다는 점과, 꽤 빠른 성능, 데이터를 코드 레벨에서 제어할 수 있는 부분이 많다. 는 점입니다.

트랜잭션의 장점 활용
꽤 빠른 성능
데이터를 코드 레벨에서 제어할 수 있는 부분이 많다.

반면 단점은 한 파일에 데이터가 많을 경우, 1분 30초 이내에 완료하지 못하는 경우도 존재한다는 점, 비동기로 여러 파일을 업로드하면 Batch Size 조절에도 메모리 부담 이 증가한다는 점, 커넥션 개수가 금방 고갈될 수 있다는 점, 비동기 환경에서 데이터 유실 추적이 어려운 점 등이 있습니다.

한 파일의 데이터가 많을 경우, 1분 30초 이내에 완료하지 못 하는 경우도 존재
데이터가 많으면 Batch Size를 조절하더라도 여러 파일이 업로드 될 경우, 메모리 부담 증가
비동기를 활용하면 커넥션 개수가 금방 고갈될 수 있다.
비동기 환경에서 데이터 유실 추적이 어렵다.

이전에 RDB를 다룰 때, 데이터를 비동기/병렬로 처리해 성능을 높였던 적이 있는데요, 커넥션이 금방 고갈나고 애플리케이션 메모리가 요동쳤던게 기억났습니다. 따라서 이 방법은 조금 꺼려졌습니다.

분할과 병렬을 이용한 Table Full Scan

그냥 시간이 걸려도 동기적으로 처리 하면 되지 않나? 라고 생각할 수도 있는데요, 한 파일에 약 300만 건 정도가 있을 때, 1분 30초 만에 처리를 못 하는 경우도 존재했습니다. 그리고 DBA가 있었기 때문에 데이터베이스의 설정도 건드리기 힘들었고요. 따라서 요구사항을 충족할 수 있고, 애플리케이션 레벨에서 다양한 테스트를 할 수 있는 확실한 방법을 선택하고 싶었습니다.

데이터가 300만 건 정도가 되면 1분 30초 내에 못 처리하는 경우도 존재
개발자가 데이터베이스의 세부 설정을 건드릴 수 없다.

2-2. MySQL Load Data

두 번째 고려한 방법은 MySQL LOAD DATA 입니다. 이는 MySQL이 제공하는 대량 데이터 적재 명령어로, 외부 텍스트 파일(.txt, .csv 등)에 저장된 데이터를 테이블에 빠르게 삽입할 수 있습니다.

The LOAD DATA statement reads rows from a text file into a table at a very high speed. The file can be read from the server host or the client host, depending on whether the LOCAL modifier is given. LOCAL also affects data interpretation and error handling

이 방식의 장점은 속도/성능 입니다. 수백만 건의 데이터를 삽입할 때, 일반적인 INSERT, Batch Insert보다 훨씬 빠른데요, 텍스트 파일 포맷이 올바르게 준비되어 있다면, 수 백만 건도 수 초 내로 처리 가능 합니다.

매우 빠른 삽입 속도 . JDBC 배치보다도 훨씬 빠르며, 대량 데이터 적재에 최적화
간단한 SQL 한 줄로 처리 가능
테스트 환경에서도 커넥션 리소스 적게 사용. 단일 명령으로 모든 작업을 수행하므로 커넥션 풀에 부담이 적음
단일 쓰레드로 동작해서 수 백만 건을 안전하게 INSERT 가능

반면, 단점도 분명 존재합니다. 가장 큰 단점은 트랜잭션 통제 불가 라는 점인데요. 중간에 오류가 발생해도 명확한 롤백이 어렵고, 파일 포맷이 조금만 틀려도 전체 삽입이 실패하거나 잘못된 값이 저장 될 수 있습니다. 또한, 운영 테이블에 직접 적용하면 잠금이나 성능 저하 문제 가 발생할 수 있습니다.

트랜잭션 제어가 불가능 – 중간 실패 시 부분 롤백이 안 되며, 정합성 확보가 어려움
파일 포맷이 엄격함 – 컬럼 수, 순서, 구분자 등의 사소한 오류로 전체 삽입이 실패
운영 환경에서는 사용 주의 – 테이블에 락이 걸리거나 I/O 부하로 서비스에 영향 가능성 있음
네트워크 I/O 발생

두 방법 중 LOAD DATA를 선택하게 되었는데, 이유는 다음과 같습니다.

파일 크기가 클 경우, Batch Insert로는 1분 30초 이내에 데이터를 저장할 수 없는 경우가 있다.
비동기로 BULK INSERT를 할 경우, 어디가 실패했는지 추적하기 어렵다.
코드 레벨에서 데이터를 파싱/검증하기 때문에, 정확한 데이터를 넣을 수 있다.
커넥션 개수에 대한 부담이 적고, 테스트 환경에서도 마음 편하게 테스트할 수 있다.
정합성이 안 맞을 일은 없지만, 많이 사용하지 않는 테이블이기 때문에 정합성을 맞출 자신이 있다.

3. 구현 및 고려사항

해결책을 선택했으니 어떻게 구현했는지, 추가로 어떤 점을 고려했는지도 살펴보겠습니다.

파일 생성
정렬
정합성
네트워크 I/O

3-1. 파일 생성

먼저 애플리케이션에서 .csv 형태의 파일을 생성합니다. 이를 애플리케이션에서 생성한 이유는 LOAD DATA는 파일 포맷 이 중요한데, 파일 생성 후, 데이터를 저장할 때 올바른 포맷이 아니라면 저장이 실패 하기 때문입니다. 이 과정이 애플리케이션 로그에 남지 않기 때문 에 추적도 어렵고요. 따라서 코드 레벨에서 강한 정합성을 보장 하기 위해 애플리케이션에서 파일을 생성했습니다.

파일 포맷이 올바르지 않으면 데이터 저장이 실패한다.
이 과정이 애플리케이션 로그에 남지 않는다.
코드 레벨에서 강한 정합성을 보장하기 위해 애플리케이션에서 파일을 생성한다.

이때 한 번에 너무 큰 파일을 여러개 올리면 파일을 생성하다 서버가 다운될 수 있기 때문에 적절한 크기를 고려하도록 합니다.

다행히 서버 스펙은 꽤 높았기 때문에 몇 년치 데이터를 한 번에 적재해도 큰 이슈가 없었습니다.

3-2. 정렬

다음으로 고려한 점은 애플리케이션에서 정렬 입니다. 즉, 파일을 생성할 때, 데이터 정렬 을 한 상태로 csv 파일을 만들었는데요, LOAD DATA는 파일에 있는 순서대로 데이터를 삽입 하며, 인덱스 를 고려해야 했기 때문입니다.

“It is recommended to sort the data by the PRIMARY KEY before running LOAD DATA INFILE on InnoDB tables to avoid random inserts.”

인덱스를 고려한 것은 MySQL의 인덱스는 B+Tree 형태로 관리되며, 정렬이 안 된 데이터를 삽입할 때, Page Split 이 발생할 수 있기 때문입니다. 즉, 페이지가 쪼개지며, 이 과정에서 디스크 쓰기, 메타 정보 갱신, 버퍼 플러시 등의 부하를 유발합니다. INSERT 속도 자체도 떨어지고요.

정렬된 데이터는 한 방향으로 삽입되기 때문에 Page Split이 거의 발생하지 않습니다.

또한 InnoDB는 성능 최적화를 위해 버퍼(Insert Buffer)를 사용하는데, 보조 인덱스를 삽입하는 경우, 디스크 접근 대신 메모리에 적재한 후, 나중에 반영합니다. 만약 정렬되지 않은 데이터를 무작위로 삽입하면 페이지를 찾기 위해 디스크 I/O가 자주 발생해 효율이 떨어지겠죠?

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

자바에서 Collection을 활용한 정렬의 시간 복잡도는 O(n log n) 이기 때문에, 수 백만 건 단위는 보통 1초 내에 수행 됩니다. 큰 부담도 없고요. 따라서 애플리케이션에서 정렬한 후, 데이터베이스에 적재 되도록 했습니다.

  
class SorTest {
    @Test
    void sortPerformanceTest() {
        final List<String> bizNumbers = new ArrayList<>();
        final Random random = new Random();

        for (int i = 0; i < 1_000_000; i++) {
            int num = random.nextInt(1_000_000_000);
            String bizNumber = String.format("%010d", num);
            bizNumbers.add(bizNumber);
        }

        final long start = System.currentTimeMillis();
        Collections.sort(bizNumbers);
        final long end = System.currentTimeMillis();
        final long elapsedMillis = end - start;

        log.info("Elapsed: {}ms", elapsedMillis);
        assertTrue(elapsedMillis < 3000);
    }
}

[main] INFO com.example.app.test.buffer.CompressedOopsTest – Elapsed: 332ms

3-3. 정합성

세 번째로 고려한 점은 정합성입니다. LOAD DATA를 사용할 경우, 코드 레벨에서 제어할 수 있는 게 아예 없는데요, 비동기를 사용할 경우, 성공/실패 여부마저 알 수 없습니다. 키 제약조건이 없다면 중복 여부도 알 수 없고요. 따라서 이 부분은 DBA와 데이터 건수를 맞추면서 진행했습니다. 이 부분이 더 오래 걸렸던 것 같네요. 😵‍

LOAD DATA는 중간에 오류가 발생할 경우, 롤백이 되지 않기 때문에 항상 주의해야 합니다.

3-4. 네트워크 I/O

마지막으로 고려한 점은 네트워크 I/O 입니다. LOAD DATA는 파일의 위치에 따라 네트워크 I/O 발생 여부가 달라지는데요, LOAD DATA LOCAL INFILE 명령어를 사용하면, 클라이언트에서 DB 서버로 파일을, 네트워크를 통해 전송하게 됩니다. 이때 다음과 같은 이슈들이 발생할 수 있습니다.

대용량 파일의 경우 네트워크 대역폭을 점유하여 전송 지연 또는 실패 가능성
DB 서버의 처리 시간보다 네트워크 전송 시간이 병목이 될 수 있음
특히 한 대의 서버로 동시 업로드가 많아지면, I/O 병목이 심해짐
일부 보안 설정에서는 LOCAL INFILE이 기본 비활성화되어 있어 에러가 발생하기도 함

반면, LOAD DATA INFILE 를 사용할 경우, 네트워크 I/O는 발생하지 않고 훨씬 빠릅니다. 단, DB 서버가 접근할 수 있는 파일 경로에 있어야 하므로, 보안/경로/권한 에 관한 이슈가 발생할 수 있습니다. 여튼, 테스트 서버에서 몇 번의 테스트를 통해 네트워크 I/O가 거의 없다는 것을 확인한 후, LOAD DATA LOCAL INFILE 명령어를 사용했습니다. 파일 몇 개가 수십 MB라서 걱정했는데, 아예 신경을 안 써도 될 정도로 미비했기 때문입니다.

The non-LOCAL rules mean that the server reads a file named as ./myfile.txt relative to its data directory, whereas it reads a file named as myfile.txt from the database directory of the default database.

결론은 어떻게 됐냐면요, 약 2,200만 건, 모든 데이터를 적재하는데 약 1분 30초 가 걸렸습니다. 요구사항은 파일 하나당 1분 30초였는데, 까마득하게 이를 넘어버렸죠. 사실 데이터 개수를 검증하는 시간이 더 많이 걸린 것 같네요. 여튼 마지막으로 어떤 과정을 거쳤는지 한 번 더 정리해 보겠습니다.

애플리케이션에서 강한 검증으로 데이터가 잘못될 여지를 제거
이 과정에서 데이터를 애플리케이션에서 정렬
애플리케이션에서 .csv 파일을 생성 후, LOAD DATA LOCAL INFILE 로 INSERT

4. 더 좋은 방법은 없었을까?

현재 떠오르는 방법은 파티셔닝(Partitioning) 인데요, 해당 데이터는 반기 라는 명확한 구분 기준이 존재하며, 날짜 단위로 삽입/업데이트/삭제 되기 때문입니다.

┌────────────┬────────────┬────────────┬────────────┐
│ PART_20221 │ PART_20222 │ PART_20231 │ PART_20232 │
├────────────┼────────────┼────────────┼────────────┤   ......
│ 2022년 1반기 │ 2022년 2반기│ 2023년 1반기 │ 2023년 2반기 │
└────────────┴────────────┴────────────┴────────────┘

또한 파티셔닝을 할 경우, 인덱스가 각 파티션 단위로 생성되기 때문에 데이터의 양이 아무리 많아도, 쿼리는 해당 파티션 범위 안에서만 수행됩니다. 예를 들어 2022년 1반기 데이터를 조회할 때 전체 2,200만 건에서 찾는 것이 아니라, 오직 PART_20221 하나만 스캔하면 되기 때문에 성능이 향상됩니다. 다른 프로젝트가 또 잡혀서 실제 하지는 못했는데요, 현재 DBA와 검토 중입니다. 실제 하게 될 때 별도 포스팅을 작성해 보겠습니다.

특정 기간 조회 성능 향상
반기 단위로 데이터 업데이트/삭제 가능

5. 정리

오랜만에 성능 개선 문제를 만나서 재미있었는데요, 뿌듯합니다. 조금 더 성능을 올릴 수 있는 방법이 떠올랐는데, 다른 프로젝트에 치여 다음으로 미뤄야 할 것 같네요. 아쉽지만 어쩌겠습니까.

1분 30초 내에 수천만 건의 여신협회 데이터 저장하기