어드민이나 B2B 백오피스 개발을 하다보면 엑셀 다운로드랑 업로드는 거의 필수 기능이다.
자바 쪽에서는 보통 Apache POI 라이브러리를 많이 쓰는데 처음에는 나도 별 생각 없이 XSSFWorkbook으로 구현해놨었다.
로컬에서 테스트 데이터 몇백건으로 돌려볼때는 너무 잘 돌아갔다.
문제는 운영에 나가고 데이터가 쌓이면서 터졌다.
담당자분이 몇만건에서 십만건 가까이 되는 데이터를 한번에 엑셀로 내려받으려고 버튼을 눌렀는데 화면이 한참 멈춰있더니 결국 서버에 OutOfMemoryError: Java heap space가 뜨면서 뻗어버린 거다.
XSSFWorkbook이 메모리 먹는 이유
원인을 찾아보면서 유명한 배달의민족 기술블로그의 엑셀 다운로드 개선 글이랑 Apache POI 공식 문서를 같이 읽어봤다.
읽어보니 내가 너무 단순하게 생각하고 있었다는걸 알게 됐다.
.xlsx 파일은 사실 내부를 까보면 여러 XML 파일이 압축된 형태다.
그런데 XSSFWorkbook은 엑셀의 모든 행과 셀을 자바 객체로 만들어서 JVM 힙 메모리에 통째로 다 올려놓고 작업한다.
파일 자체는 10MB밖에 안되더라도 메모리 안에서는 XML 객체 트리 때문에 수백 MB에서 1GB 넘게 부풀어 오르는 경우가 흔하다고 한다.
여기에 동시에 두세명만 다운로드를 눌러도 서버 메모리가 버틸 수가 없었다.
다운로드는 SXSSFWorkbook으로
Apache POI에서는 이런 대용량 엑셀 쓰기 문제를 해결하려고 SXSSFWorkbook을 제공한다.
동작 방식은 생각보다 단순하다.
모든 행을 메모리에 다 들고 있지 않고 내가 정한 일정 개수(예: 100행이나 1000행)만 메모리에 유지한다.
그 개수를 넘어가면 오래된 행부터 디스크의 임시 XML 파일로 바로 흘려보내고 메모리에서 비워버린다.
덕분에 데이터가 10만건이든 50만건이든 힙 메모리 사용량이 일정 수준 이상으로 치솟지 않는다.
public void downloadExcel(List<OrderDto> orders, OutputStream out) throws IOException {
// 메모리에 최대 1000행만 유지하고 나머지는 디스크 임시 파일로 flush
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
workbook.setCompressTempFiles(true); // 임시 파일 압축 옵션
try {
Sheet sheet = workbook.createSheet("주문내역");
// 스타일은 반드시 루프 밖에서 한번만 생성해서 재사용
CellStyle headerStyle = createHeaderStyle(workbook);
CellStyle bodyStyle = createBodyStyle(workbook);
// 헤더 작성
Row headerRow = sheet.createRow(0);
String[] headers = {"주문번호", "상품명", "금액", "주문일시"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
sheet.setColumnWidth(i, 5000); // 고정 너비 지정
}
// 본문 데이터 작성
int rowNum = 1;
for (OrderDto order : orders) {
Row row = sheet.createRow(rowNum++);
Cell c0 = row.createCell(0);
c0.setCellValue(order.getOrderNo());
c0.setCellStyle(bodyStyle);
Cell c1 = row.createCell(1);
c1.setCellValue(order.getProductName());
c1.setCellStyle(bodyStyle);
Cell c2 = row.createCell(2);
c2.setCellValue(order.getAmount());
c2.setCellStyle(bodyStyle);
Cell c3 = row.createCell(3);
c3.setCellValue(order.getOrderedAt());
c3.setCellStyle(bodyStyle);
}
workbook.write(out);
} finally {
// 스트림을 닫고 임시 XML 파일 정리 (dispose 안 부르면 디스크에 계속 쌓임)
workbook.close();
workbook.dispose();
}
}
참고로 DB에서 데이터 가져올때도 List<OrderDto>로 10만건을 한번에 다 퍼오면 결국 엑셀 쓰기 전에 DB 조회 단계에서 메모리가 먼저 차버린다.
데이터가 정말 많을때는 페이징으로 1000건씩 끊어서 조회하거나 MyBatis ResultHandler나 JPA 스트림 같은 방식으로 조금씩 가져와서 바로 시트에 써주는게 같이 맞물려야 제대로 효과가 난다.
바꾸면서 실제로 걸렸던 부분들
그냥 클래스 이름만 XSSFWorkbook에서 SXSSFWorkbook으로 바꾼다고 끝나는게 아니었다.
적용해보면서 몇 군데서 실제로 막혔었다.
dispose() 호출을 빼먹으면 디스크가 찬다
SXSSFWorkbook은 메모리를 아끼는 대신 서버 /tmp 같은 임시 디렉토리에 XML 파일을 만들어 둔다.workbook.close()만 호출하면 스트림은 닫히는데 디스크에 남은 임시 파일은 그대로 남을수 있다.
그래서 finally 블록에서 close()와 함께 반드시 workbook.dispose()를 호출해서 임시 파일을 깔끔하게 지워줘야 한다.
반복문 안에서 createCellStyle()을 부르면 안된다
처음 짠 코드에서는 행을 만들때마다 workbook.createCellStyle()을 호출하고 있었다.
근데 엑셀 포맷 자체에 셀 스타일 최대 개수 제한(약 64,000개)이 있어서 데이터가 많아지면 에러가 터지거나 파일이 깨진다.
메모리도 불필요하게 엄청 먹는다.
배민 기술블로그에서도 강조했던 부분인데 헤더용, 본문용, 숫자 포맷용 같이 필요한 스타일 몇개만 미리 만들어두고 캐싱해서 돌려 쓰는게 정석이다.
autoSizeColumn()은 대용량에서 포기하는게 낫다
원래 컬럼 너비 자동으로 맞추려고 sheet.autoSizeColumn(i)를 쓰고 있었는데 SXSSFWorkbook으로 바꾸자마자 에러가 났다.
이미 디스크로 내려보낸 행은 메모리에 없어서 전체 길이를 계산할수 없기 때문이다.trackAllColumnsForAutoSizing() 옵션을 켜면 되긴 하는데 이걸 켜면 모든 셀 너비를 계속 계산하느라 다운로드 속도가 눈에 띄게 느려진다.
대용량 엑셀에서는 그냥 컬럼별로 넉넉한 고정 너비(setColumnWidth)를 주는게 속 편하고 훨씬 빠르다.
업로드는 SAX 파싱이랑 배치 Insert로
다운로드를 고치고 나니 이번에는 대용량 엑셀 업로드가 문제였다.
업로드할때도 WorkbookFactory.create(inputStream)이나 new XSSFWorkbook(inputStream)을 쓰면 파일 전체를 메모리에 올리느라 똑같이 OOM이 터진다.
읽기 쪽에는 SXSSFWorkbook 같은게 따로 없어서 Apache POI의 SAX 파싱 방식을 써야 했다.OPCPackage랑 XSSFReader, XMLReader를 조합해서 XML을 위에서 아래로 한 줄씩 훑고 지나가며 처리하는 방식이다.
코드는 기존 Workbook 방식보다 확실히 길고 투박하지만 메모리 사용량은 비교가 안될 정도로 낮아진다.
그리고 엑셀을 메모리 안 터지게 잘 읽었더라도 DB에 넣을때 한 건씩 insert를 날리면 5만건 넣는데 몇 분씩 걸려버린다.
그래서 파싱하면서 일정 개수(예: 500건이나 1000건)가 모일때마다 리스트를 비워주면서 JDBC batchUpdate로 한꺼번에 밀어넣는 구조로 묶었다.
public class ChunkedExcelRowHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
private static final int BATCH_SIZE = 1000;
private final List<ProductRow> buffer = new ArrayList<>(BATCH_SIZE);
private final ProductBatchRepository batchRepository;
private ProductRow currentRow;
@Override
public void startRow(int rowNum) {
if (rowNum == 0) return; // 헤더 스킵
currentRow = new ProductRow();
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
if (currentRow == null) return;
// 셀 컬럼 위치(A, B, C...)에 따라 currentRow 필드에 매핑
currentRow.setByCellRef(cellReference, formattedValue);
}
@Override
public void endRow(int rowNum) {
if (rowNum == 0 || currentRow == null) return;
buffer.add(currentRow);
if (buffer.size() >= BATCH_SIZE) {
batchRepository.batchInsert(buffer);
buffer.clear();
}
}
public void flushRemaining() {
if (!buffer.isEmpty()) {
batchRepository.batchInsert(buffer);
buffer.clear();
}
}
}
이렇게 스트리밍으로 읽고 청크 단위로 배치 인서트하게 바꾸고 나니까 몇만건짜리 엑셀을 올려도 서버 메모리가 잔잔하게 유지되고 처리 시간도 훨씬 줄어들었다.
엑셀 기능 하나 만드는데 뭐 이렇게 신경 쓸게 많나 싶었는데 막상 메모리 터지는걸 겪고 배민 기술블로그 같은 레퍼런스들 찾아가며 고쳐보니 배운게 꽤 많았다.
POI 내부가 XML 기반이라 힙 메모리를 어떻게 쓰는지 이해하고 나니 비슷한 파일 처리 이슈가 생겨도 감이 잡히기 시작했다.
코드로 최적화하는 것도 좋긴 한데 실무에서는 진짜 이 데이터를 매번 10만건씩 실시간으로 뽑아야 하는지도 한번 더 물어보게 됐다.
조회 기간 제한을 두거나 정말 큰 데이터는 CSV로 내리게 제안하거나 비동기로 만들어서 완료되면 알림을 주는 식으로 업무 흐름 자체를 같이 풀어주면 서버도 편하고 쓰는 사람도 덜 기다리게 된다.