대용량 엑셀 업로드 비동기 파이프라인 구축기
Intro
동기 방식의 엑셀 업로드를 비동기 파이프라인으로 전환하여 API 응답 시간, DB 부하, 메모리 문제를 개선한 과정을 소개합니다.
✅ 결론
비동기 아키텍처 + SAX 파싱 + JDBC Batch INSERT를 도입하여 기존 동기 방식의 핵심 문제들을 개선했습니다.
API 응답 시간
~243초→540ms로 개선 (즉시 반환)1,000건 처리 시간
~243초→~7초로 약 97% 단축Apache POI DOM 방식의
OOM 위험을 SAX 스트리밍으로 해소동시 DB 커넥션을 워커 풀 크기로 고정하여
DB 커넥션 고갈 방지Redis 분산 락으로
중복 데이터 생성 차단
✅ 기존 방식
기존 엑셀 업로드는 API 요청 스레드가 파싱 → 검증 → INSERT를 모두 동기적으로 처리하는 구조였습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
클라이언트
│
▼ POST /api/v2/item/excel/upload
┌──────────────────────────────────────┐
│ Tomcat Thread (blocked) │
│ │
│ 1. MultipartFile 수신 │
│ 2. DOM 파싱 (전체 메모리 로드) │
│ ⚠ 대용량 파일 → OOM 위험 │
│ 3. Row별 검증 + 1건씩 DB INSERT │
│ 4. 에러 파일 생성 │
│ │
│ ⏱ 1,000건 기준: ~243초 │
│ 🔌 DB 커넥션: 요청당 1개 점유 │
└──────────────────────────────────────┘
│
▼ ~243초 후 응답
클라이언트
1️⃣ Tomcat 스레드 + DB 커넥션 장시간 점유
API 스레드가 처리 완료까지 블로킹됩니다. 1,000건 기준 ~243초 동안 Tomcat 스레드 1개 + DB 커넥션 1개가 점유됩니다. 동시 업로드 사용자가 늘어나면 Tomcat 스레드와 DB 커넥션이 고갈되어 일반 API까지 마비됩니다.
1
2
3
4
20명 동시 업로드 → Write 커넥션 20개 × 100초+ 점유
50명 동시 업로드 → Write 커넥션 50개 × 100초+ 점유 (경합으로 더 느려짐)
→ 일반 API (주문 처리, 재고 확인 등)가 커넥션 대기 상태 진입
→ HikariPool connection timeout (30초 기본) → 500 에러
2️⃣ DOM 파싱으로 인한 OOM
Apache POI DOM 방식은 엑셀 전체를 메모리에 트리 구조로 로드합니다. 1,000건 기준 ~19MB를 점유하며, 대용량 파일 업로드 시 OutOfMemoryError가 발생합니다.
3️⃣ JPA 단건 INSERT 성능
Row마다 REQUIRES_NEW 트랜잭션으로 1건씩 JPA INSERT를 수행합니다. 1,000건에 ~243초가 소요되며, 행별로 DB 조회 + INSERT가 반복되어 비효율적입니다.
4️⃣ 동시성 환경에서 중복 검증 불가
SKU와 바코드는 셀러별로 유니크해야 하지만, 기존 테이블에 중복 데이터가 존재하여 DB UNIQUE 제약을 걸 수 없는 상황이었습니다. 애플리케이션 레벨에서 중복 검증을 수행하지만, 동시 요청 시 검증과 INSERT 사이의 타이밍 갭으로 중복 데이터가 생성될 수 있었습니다.
✅ 개선 과정
1️⃣ 동기 → 비동기
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
클라이언트
│
▼ POST /api/v2/item/excel/upload
┌────────────────────────────────┐
│ Tomcat Thread (즉시 반환) │
│ │
│ 1. S3 업로드 (파일 저장) │
│ 2. Job 테이블 INSERT │
│ 3. jobId 응답 │
│ │
│ ⏱ ~540ms │
└────────────────────────────────┘
│
▼ 540ms 후 jobId 응답
클라이언트 (폴링 시작)
┌─────────────────────────────────────┐
│ Scheduler (3초 폴링) │
│ → Worker Pool (5 스레드) │
│ │
│ 1. S3 다운로드 │
│ 2. SAX 스트리밍 파싱 │
│ 3. Phase 1 + 1.5 검증 │
│ 4. Phase 2 JDBC Batch INSERT │
│ 5. 에러 파일 S3 업로드 │
│ 6. Job 상태 업데이트 │
└─────────────────────────────────────┘
API 요청 시에는 파일을 S3에 저장하고 Job만 등록한 뒤 즉시 응답합니다. 실제 파싱/검증/INSERT는 백그라운드 워커 스레드풀에서 처리합니다. 클라이언트는 폴링 API로 처리 상태를 확인합니다.
2️⃣ SKIP LOCKED를 활용한 Job 큐 구현
스케줄러가 3초마다 폴링하면서 같은 Job을 여러 번 가져가 중복 처리하는 문제가 발생할 수 있습니다. 일반적인 SELECT FOR UPDATE는 다른 트랜잭션이 같은 행을 잠그고 있으면 대기(blocking)합니다. 스케줄러가 3초마다 폴링하는 구조에서 이전 폴링이 아직 처리 중이면 다음 폴링이 블로킹되어 전체 시스템이 정체될 수 있습니다.
SKIP LOCKED는 이미 잠긴 행을 건너뛰고 나머지만 가져옵니다.
1
2
3
4
5
6
7
8
9
10
11
-- 셀러별 가장 오래된 PENDING Job 1건씩, 최대 5건
SELECT j.* FROM excel_upload_job j
INNER JOIN (
SELECT MIN(job_id) AS job_id
FROM excel_upload_job
WHERE status = 'PENDING'
GROUP BY seller_id
ORDER BY MIN(cre_dt) ASC
LIMIT 5
) t ON j.job_id = t.job_id
FOR UPDATE SKIP LOCKED;
GROUP BY seller_id로 셀러당 1건만 가져오게 합니다. 셀러A가 5건, 셀러B가 2건 대기 중이어도 각 셀러에서 가장 오래된 1건씩만 가져와 서로 다른 셀러의 Job을 병렬 처리합니다. 같은 셀러의 Job이 동시에 처리되어 중복 검증이 무력화되는 것을 원천 차단합니다.
3️⃣ Redis 분산 락
GROUP BY seller_id만으로는 완벽하지 않습니다. 분산 환경(CGS 인스턴스 3대)에서 각 인스턴스의 스케줄러가 동시에 같은 셀러의 Job을 가져갈 수 있습니다.
SKU와 바코드는 셀러별로 유니크해야 하지만, 소프트 삭제(delete_yn) 패턴과 기존 중복 데이터로 인해 DB UNIQUE 제약을 걸 수 없는 상황이었습니다. 따라서 애플리케이션 레벨에서 중복 검증을 수행하되, 동시 요청 시 검증과 INSERT 사이의 타이밍 갭으로 중복이 발생하는 것을 막기 위해 Redis 분산 락을 도입했습니다.
구체적으로는 셀러ID Lock-Key로 사용하여, 같은 셀러의 Job은 반드시 순차 처리되도록 보장하였습니다.
1
2
1차 방어: GROUP BY seller_id → 같은 인스턴스 내 셀러별 1건 처리
2차 방어: Redis SETNX + TTL → 분산 환경에서 셀러별 순차 처리 보장
워커 스레드가 비정상 종료되어 unlock()이 호출되지 않더라도, TTL(10분)이 만료되면 Redis 키가 자동 삭제되어 다른 스레드가 락을 획득할 수 있습니다.
4️⃣ SAX 스트리밍 파서 — OOM 해소
Apache POI DOM 방식에서 SAX 스트리밍 방식으로 전환했습니다.
| 방식 | 메모리 | 설명 |
|---|---|---|
| DOM (XSSFWorkbook) | ~19MB (1,000건) | 엑셀 전체를 메모리에 트리 구조로 로드 |
| SAX (SaxUploadRow) | ~700B × row 수 | row당 셀 문자열만 보관 |
DOM 방식 힙덤프 (1,000건)
XSSFSheet 하나가 Retained Size 19.32MB를 차지합니다. 엑셀 전체를 트리 구조로 메모리에 올리기 때문에 행 수에 비례하여 메모리가 급증합니다.
SAX 방식 힙덤프 (1,000건)
SaxUploadRow 객체 하나당 Retained Size가 696~752B에 불과합니다. row별로 셀 문자열만 보관하므로 메모리 사용량이 일정하게 유지됩니다.
1,000건 기준 메모리 사용량:
1
2
3
SaxUploadRow 1,000개 × ~700B = ~700 KB
컨테이너 배열 = ~409 KB
합계 ≈ ~1.1 MB
19.32MB -> 1.1MB DOM 방식 대비 약 1/17 수준으로 메모리 사용량이 줄었습니다.
5️⃣ JDBC Batch INSERT — 성능 최적화
JPA 단건 INSERT에서 JDBC Batch INSERT로 전환했습니다. 100건 단위 chunk로 item_mst, item_uom_dtl, item_barcode_dtl 3개 테이블을 일괄 저장합니다.
1
2
3
4
5
6
7
Phase 1.5 검증: SELECT 1회 (전체 1,000건 한번에)
Phase 2 INSERT:
chunk 1 (100건) → INSERT item_mst + UPDATE item_cd + INSERT item_uom_dtl + INSERT item_barcode_dtl = 4 쿼리
chunk 2 (100건) → 4 쿼리
...
chunk 10 (100건) → 4 쿼리
단일 트랜잭션으로 감싸서 실패 시 전체 rollback합니다.
✅ 부하 테스트
k6로 10명 동시 요청(VU 10)으로 테스트했습니다. 각 VU마다 다른 셀러 ID를 할당하여 바코드 중복을 방지했습니다.
1,000 Row x 10 동시 요청
| 항목 | 결과 |
|---|---|
| API 응답 시간 (평균) | 354ms (min 323ms / max 388ms) |
| 전체 처리 시간 (평균) | 13.3s (min 6.9s / max 22.1s) |
| 업로드 성공률 | 100% (10/10) |
| 최대 동시 DB 커넥션 | 5개 (워커 풀 크기로 제한) |
| 배치 | 셀러 수 | 완료 시간 | Job당 처리 시간 |
|---|---|---|---|
| 1차 | 4~5개 동시 | ~7초 | ~7초 |
| 2차 | 3~5개 | ~15~17초 | ~9~11초 |
| 3차 | 1개 | ~22초 | ~5초 |
k6 원본 출력
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
INFO[0000] [VU 10] 셀러=30 Job 등록: jobId=54, 323ms source=console
INFO[0000] [VU 9] 셀러=29 Job 등록: jobId=52, 323ms source=console
INFO[0000] [VU 6] 셀러=26 Job 등록: jobId=53, 330ms source=console
INFO[0000] [VU 7] 셀러=27 Job 등록: jobId=55, 330ms source=console
INFO[0000] [VU 8] 셀러=28 Job 등록: jobId=56, 330ms source=console
INFO[0000] [VU 5] 셀러=25 Job 등록: jobId=59, 374ms source=console
INFO[0000] [VU 2] 셀러=22 Job 등록: jobId=57, 375ms source=console
INFO[0000] [VU 4] 셀러=24 Job 등록: jobId=61, 387ms source=console
INFO[0000] [VU 1] 셀러=21 Job 등록: jobId=58, 387ms source=console
INFO[0000] [VU 3] 셀러=23 Job 등록: jobId=60, 388ms source=console
INFO[0006] [VU 6] 셀러=26 완료: submit=330ms, total=6923ms, success=700, error=300 source=console
INFO[0006] [VU 8] 셀러=28 완료: submit=330ms, total=6931ms, success=700, error=300 source=console
INFO[0006] [VU 9] 셀러=29 완료: submit=323ms, total=6931ms, success=700, error=300 source=console
INFO[0006] [VU 7] 셀러=27 완료: submit=330ms, total=6931ms, success=700, error=300 source=console
INFO[0015] [VU 10] 셀러=30 완료: submit=323ms, total=15542ms, success=700, error=300 source=console
INFO[0015] [VU 2] 셀러=22 완료: submit=375ms, total=15916ms, success=700, error=300 source=console
INFO[0015] [VU 1] 셀러=21 완료: submit=387ms, total=15933ms, success=700, error=300 source=console
INFO[0017] [VU 5] 셀러=25 완료: submit=374ms, total=17796ms, success=700, error=300 source=console
INFO[0017] [VU 3] 셀러=23 완료: submit=388ms, total=17834ms, success=700, error=300 source=console
INFO[0022] [VU 4] 셀러=24 완료: submit=387ms, total=22091ms, success=700, error=300 source=console
█ THRESHOLDS
http_req_duration
✓ 'p(95)<120000' p(95)=384.94ms
http_req_failed
✓ 'rate<0.1' rate=0.00%
█ TOTAL RESULTS
checks_total.......: 10 0.452642/s
checks_succeeded...: 100.00% 10 out of 10
checks_failed......: 0.00% 0 out of 10
✓ upload status is 200
CUSTOM
submit_duration................: avg=354.7ms min=323ms med=352ms max=388ms p(90)=387.1ms p(95)=387.55ms
total_duration.................: avg=13.28s min=6.92s med=15.72s max=22.09s p(90)=18.25s p(95)=20.17s
upload_success.................: 10 0.452642/s
HTTP
http_req_duration..............: avg=213.65ms min=69.76ms med=170.71ms max=456.76ms p(90)=378.33ms p(95)=384.94ms
{ expected_response:true }...: avg=213.65ms min=69.76ms med=170.71ms max=456.76ms p(90)=378.33ms p(95)=384.94ms
http_req_failed................: 0.00% 0 out of 69
http_reqs......................: 69 3.123228/s
EXECUTION
iteration_duration.............: avg=13.28s min=6.92s med=15.72s max=22.09s p(90)=18.25s p(95)=20.17s
iterations.....................: 10 0.452642/s
vus............................: 1 min=1 max=10
vus_max........................: 10 min=10 max=10
NETWORK
data_received..................: 30 kB 1.4 kB/s
data_sent......................: 1.4 MB 61 kB/s
running (00m22.1s), 00/10 VUs, 10 complete and 0 interrupted iterations
default ✓ [======================================] 10 VUs 00m22.1s/10m0s 10/10 shared iters