[Spring/Java] 대용량 엑셀 다운로드 시 OOM(OutOfMemoryError) 해결 : Apache POI XSSF에서 SXSSF로 리팩토링

---

[Spring/Java] 대용량 엑셀 다운로드 시 OOM(OutOfMemoryError) 해결 : Apache POI XSSF에서 SXSSF로 리팩토링

목차

1. Apache POI란
2. 문제발생 : XSSF 방식의 메모리 한계
2-1. 개선 : SXSSFWorkbook 기반 스트리밍 방식으로 개선
3. 추가문제 : DB 조회 방식
4. DB 조회 개선 (페이징 조회)
5. 최종개선

1. Apache POI란

Apache POI는 Java에서 Microsoft Office 파일을 읽고/쓰기 위한 라이브러리로, 특히 Excel(.xlsx) 파일을 Java 객체 형태로 다룰 수 있도록 제공함. Excel 파일은 내부적으로 XML 기반 구조를 가지며, Apache POI는 이를 workbook -> Sheet -> Row -> Cell 구조로 추상화하여 DB 데이터를 파일로 변환할 수 있도록 함.

 

파이널 프로젝트에서 Excel 파일 생성(호텔 예약내역 엑셀 다운로드)은 Controller에서 다음과 같은 순서로 진행되었음. (1. Workbook 생성 -> 2. Sheet 생성 -> 3. Row 생성 -> 4. Cell에 데이터 입력 -> 5. OutputStream으로 파일 출력). 상세 순서는 프론트에서 버튼 클릭 후, Controller 에서 검색조건(RequestParam) 포함 후 Service -> DAO -> DB 조회 후 Controller로 반환됨.(이 때 HTTP 응답으로 파일 다운로드 진행) 

Apache POI 라이브러리 이용하여 호텔 예약 내역 엑셀 다운로드 기능 구현 완료

---

2. 문제발생 : XSSF 방식의 메모리 한계

■ Apache POI는 Excel 생성 방식에 따라 XSSF와 SXSSF방식을 제공하는데, 기존의 XSSF 방식은 구현이 단순하지만, 데이터가 증가할 경우 메모리 사용량이 급격이 증가하는 문제가 존재하였음. 

- XSSF(메모리방식) : DB -> List 조회 (모든 row/cell을 JAVA 객체로 메모리에 올린 후 -> 한번에 파일 생성함). 이 경우 데이터가 많아질수록 -> Heap 메모리 급증 -> GC 발생 증가 -> 심하면 OutOfMemoryError로 귀결됨.

 

※ Heap = 객체가 저장되는 메모리 영역(new로 만 객체는 모두 Heap에 저장됨.)

※ GC(Garbage Collection) = Java는 더이상 사용하지 않는 객체를 자동으로 삭제해 메모리를 확보하는 기능이 있음.(자동 메모리 관리)

 

즉, 전체 데이터를 메모리에 올려놓고 한 번에 처리하는 구조이기 때문에, 최악의 경우 OOM(OutOfMemoryError) 발생 가능성 있었음.

 

이를 해결하기 위해 SXSSFWorkbook 기반 스트리밍 방식으로 리팩토링 진행함.(대용량 데이터 처리 시 메모리 사용량을 효과적으로 제어할 수 있도록 개선)

 

※ XSSF(XML SpreadSheet Format) : XML 전체를 파싱함. 즉, XML 문서를 DOM처럼 다루는 것.

※ SXSSF(Streaming XML SpreadSheet Format) : DOM방식(전체 로드)이 아니라 SAX/스트리밍방식으로 처리함.

 

기존 : XSSF 방식

// 엑셀 다운로드
//@PreAuthorize("hasRole('ADMIN_HQ')")
@GetMapping("/excel")
public void downloadExcel(@RequestParam(value="name", required=false) String name,
        		  @RequestParam(value="status", required=false) String status,
        	          HttpServletResponse response) throws Exception {

    Map<String,Object> param = new HashMap<>();
    param.put("name", name);
    param.put("status", status);

    List<Map<String,Object>> reservationList =
            reservationService.selectAdminReservationList(param);

    Workbook wb = new XSSFWorkbook();  # 문제가 되는 부분---> 전체 엑셀 row/cell 객체 메모리 다 올림
    Sheet sheet = wb.createSheet("예약목록");

    int rowNo = 0;

    Row header = sheet.createRow(rowNo++);
    header.createCell(0).setCellValue("예약번호");
    header.createCell(1).setCellValue("회원");
    header.createCell(2).setCellValue("호텔");
    header.createCell(3).setCellValue("객실타입");
    header.createCell(4).setCellValue("체크인");
    header.createCell(5).setCellValue("체크아웃");
    header.createCell(6).setCellValue("가격");
    header.createCell(7).setCellValue("결제상태");
    header.createCell(8).setCellValue("예약상태");

    for(Map<String,Object> r : reservationList){

        Row row = sheet.createRow(rowNo++);

        row.createCell(0).setCellValue(String.valueOf(r.get("RESERVATION_ID")));
        row.createCell(1).setCellValue(String.valueOf(r.get("NAME")));
        row.createCell(2).setCellValue(String.valueOf(r.get("HOTEL_NAME")));
        row.createCell(3).setCellValue(String.valueOf(r.get("ROOM_TYPE_ID")));
        row.createCell(4).setCellValue(String.valueOf(r.get("CHECKIN_DATE")));
        row.createCell(5).setCellValue(String.valueOf(r.get("CHECKOUT_DATE")));
        row.createCell(6).setCellValue(String.valueOf(r.get("TOTAL_PRICE")));
        row.createCell(7).setCellValue(String.valueOf(r.get("PAYMENT_STATUS")));
        row.createCell(8).setCellValue(String.valueOf(r.get("RESERVATION_STATUS")));
    }

    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition",
            "attachment;filename=reservation_list.xlsx");

    wb.write(response.getOutputStream());
    wb.close();
}

 

---

2. 개선(SXSSF 방식)

- SXSSF(스트리밍방식) : DB -> List 조회 (row 생성 -> 일정 개수 row만 메모리에 유지 -> 나머지는 디스크 temp 파일로 flush -> 마지막에 merge 후 Excel 파일 생성)

// 엑셀 다운로드
@Override
public void downloadReservationExcel(List<Map<String,Object>> list,
                                      HttpServletResponse response) throws Exception {

    SXSSFWorkbook wb = new SXSSFWorkbook(100);   # 개선 --> 100개 row만 메모리에 저장
    wb.setCompressTempFiles(true);               // ← 추가

    try {
        SXSSFSheet sheet = wb.createSheet("예약목록");
        sheet.trackAllColumnsForAutoSizing();    // ← 추가

        // 헤더 스타일
        CellStyle headerStyle = wb.createCellStyle();
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);

        // 헤더 행
        Row header = sheet.createRow(0);
        for (int i = 0; i < HEADERS.length; i++) {
            Cell cell = header.createCell(i);
            cell.setCellValue(HEADERS[i]);
            cell.setCellStyle(headerStyle);
        }

        // 데이터 행
        int rowNo = 1;
        for (Map<String,Object> r : list) {
            Row row = sheet.createRow(rowNo++);
            row.createCell(0).setCellValue(String.valueOf(r.get("RESERVATION_ID")));
            row.createCell(1).setCellValue(String.valueOf(r.get("NAME")));
            row.createCell(2).setCellValue(String.valueOf(r.get("HOTEL_NAME")));
            row.createCell(3).setCellValue(String.valueOf(r.get("ROOM_TYPE_ID")));
            row.createCell(4).setCellValue(String.valueOf(r.get("CHECKIN_DATE")));
            row.createCell(5).setCellValue(String.valueOf(r.get("CHECKOUT_DATE")));
            row.createCell(6).setCellValue(String.valueOf(r.get("TOTAL_PRICE")));
            row.createCell(7).setCellValue(String.valueOf(r.get("PAYMENT_STATUS")));
            row.createCell(8).setCellValue(String.valueOf(r.get("RESERVATION_STATUS")));
        }

        // autoSizeColumn은 데이터 다 넣은 후에 호출
        for (int i = 0; i < HEADERS.length; i++) {
            sheet.autoSizeColumn(i);             // ← 데이터 다 넣고 나서 호출
        }

        // 응답
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=reservation_list.xlsx");

        wb.write(response.getOutputStream());

    } finally {
        wb.dispose();  // # 개선 -> SXSSFWorkbook 은 임시파일 정리 필수
        wb.close();
    }

}

 

개선 방식은 상기와 같다. SXSSF 방식으로 전환함.(100개 row만 메모리에 유지하고, 나머지는 임시파일에 flush 하는 스트리밍 방식).

 

※ flush : 메모리에 있던 데이터를 디스크 파일로 내보내는 것.

※ 스트리밍 방식 : 데이터를 한 번에 모아서 처리하는 것이 아니라, 조금씩 흘러보내면서 처리하는 방식.

 

즉, 메모리에 100개 행 저장해두고, 일부 행 1~?? 을 디스크로 내보내면서 Heap 사용량을 제한하는 방식임.

[메모리]
Row 1 ~ 100
→ 101번째 들어옴
[메모리 꽉참]

→ Row 1~?? 일부를 디스크로 flush

[디스크(temp 파일)]
Row 1 ~ 50

[메모리]
Row 51 ~ 101

→ 반복

 

이를 통해 전체 데이터를 메모리에 적재하지 않고, Heap 사용량을 일정 수준으로 제한하면서 대용량 데이터를 안정적으로 처리할 수 있도록 개선함.

XSSF vs SXSSF 비교 (출처 : 클로드코드)

---

3. 추가 문제 : DB 조회 방식

List<Map<String,Object>> reservationList =
        reservationService.selectAdminReservationList(param);

excelService.downloadReservationExcel(reservationList, response);

 

Excel 생성 방식을 SXSSF로 개선했지만, 엑셀 파일 생성하기 전 이미 DB 조회 결과가 Java 메모리에 모두 올라온 상태(DB 전체 조회 -> List<Map>에 전체 데이터 적재 -> excelService로 전달 -> SXSSFWorkbook으로 Excel 생성)이기 때문에 데이터가 10만 건 이상 증가하면 조회 결과 List 자체가 Heap 메모리를 많이 차지하게 됨.

 

즉, SXSSF는 Excel 생성 과정의 메모리 문제는 줄여주지만, DB 조회 결과를 한 번에 메모리에 적재하는 문제까지 해결해주지는 못함.

앞단의 조회 단계에서 이미 Heap이 가득찬 상태일 수 있음.( DB 조회도 개선 필요함)

---

4. DB 조회 개선 (페이징 조회)

int page = 1;
int pageSize = 1000;  # 3으로 두고 테스트

List<Map<String,Object>> allList = new ArrayList<>();

while (true) {

    int offset = (page - 1) * pageSize;

    param.put("offset", offset);
    param.put("limit", pageSize);

    List<Map<String,Object>> list =
            reservationService.selectAdminReservationListForExcel(param);

    if (list.isEmpty()) {
        break;
    }

    allList.addAll(list);

    if (list.size() < pageSize) {
        break;
    }

    page++;
}

excelService.downloadReservationExcel(allList, response);

 

전체 데이터를 한 번에 조회하지 않고, 페이징 처리로 3건씩 나누어 조회 후 (pageSize = 3 으로 테스트) allList에 다시 모으는 구조로 변경함. (DB 페이징 조회 -> allList에 누적 -> excelService로 전달)

 

다만, DB에서 한 번에 안 가져오도록 페이징 처리(3건씩) 했지만, 결국 allList에 계속해서 저장하는 구조이므로 완전한 해결 방식은 아님.

DB 3건 조회 → allList에 저장
DB 다음 3건 조회 → allList에 추가
DB 다음 3건 조회 → allList에 추가
마지막에 allList 전체를 ExcelService로 전달
ExcelService에서 Row 생성

 

페이징을 함으로써 DB에서 한 번에 가져오는 데이터 양은 줄었지만, 누적하는 구조이기 때문에 결국 최종적으로 Heap에 올라오는 총량은 동일하므로 문제 해결이 되지 않음. (큰수조에물을 채울 때, 호스로 한꺼번에 퍼붓던 걸, 작은 바가지로 여러 번 나눠 붓는 방식으로 바꿨다고 생각하면 됨.)

---

See the Pen Untitled by L ee1123 (@L-ee1123) on CodePen.

 

 

 

5. 최종개선

5. 최종 개선 : allList 누적 제거 (조회한 페이지 데이터를 즉시 Excel Row로 작성)

for (Map<String, Object> r : list) { // 즉시 작성
                    Row row = sheet.createRow(rowNo++);

                    row.createCell(0).setCellValue(String.valueOf(r.get("RESERVATION_ID")));
                    row.createCell(1).setCellValue(String.valueOf(r.get("NAME")));
                    row.createCell(2).setCellValue(String.valueOf(r.get("HOTEL_NAME")));
                    row.createCell(3).setCellValue(String.valueOf(r.get("ROOM_TYPE_ID")));
                    row.createCell(4).setCellValue(String.valueOf(r.get("CHECKIN_DATE")));
                    row.createCell(5).setCellValue(String.valueOf(r.get("CHECKOUT_DATE")));
                    row.createCell(6).setCellValue(String.valueOf(r.get("TOTAL_PRICE")));
                    row.createCell(7).setCellValue(String.valueOf(r.get("PAYMENT_STATUS")));
                    row.createCell(8).setCellValue(String.valueOf(r.get("RESERVATION_STATUS")));
                }

 

Controller에는 검색 조건만 넘기고, ExcelService 안에서 while 문을 돌리도록 변경함. 흐름은 하기와 같이 변경됨.

DB 3건 조회 → 바로 Excel Row 생성
DB 다음 3건 조회 → 바로 Excel Row 생성
DB 다음 3건 조회 → 바로 Excel Row 생성
마지막에 wb.write()

 

즉, 초기 페이징 개선에서는 Controller에서 offset/limit 기반으로 데이터를 나누어 조회했지만, 각 페이지의 조회 결과를 allList에 누적한 뒤 ExcelService에 전달함. 따라서 DB 조회는 페이징 처리되었지만, 애플리케이션 메모리에는 최종적으로 전체 데이터가 다시 적재되는 한계가 있었음.

최종 리팩토링에서는 페이징 조회 로직과 Excel Row 생성 로직을 ExcelService 내부로 이동시킴. 각 페이지의 데이터를 조회한 직후 해당 list를 바로 SXSSFWorkbook의 Row로 작성하고, 별도의 allList에 누적하지 않도록 변경함.
(즉, 페이지 단위 조회 결과를 누적하지 않고 즉시 Excel Row로 소비하도록 처리 흐름을 변경함.)

 

See the Pen Untitled by L ee1123 (@L-ee1123) on CodePen.

 

 

 

결론 : 구조 개선을 통해 대용량 데이터 처리 시 서버 메모리 사용량을 일정 수준으로 유지할 수 있으며, OOM같은 장애를 예방할 수 있도록 개선됨.(XSSF -> SXSSF -> 페이징 조회 -> allList 제거 및 SXSSFWorkbook의 row 즉시 처리하는 구조로 리팩토링함.)

 

이번 리팩토링은 메모리 사용량을 줄이기 위한 구조 개선에 초점을 맞춤.

 

고민 지점 : 다만 구조 개선만으로 충분하지 않고, 동시 사용자 요청이 증가했을 때 API가 어느 정도 성능을 유지하는지에 대한 검증이 필요함... 따라서 다음 단계에서는 k6를 활용하여 엑셀 다운로드 API에 대한 부하 테스트를 수행, 응답 시간(p95), 처리량(Throughput), 실패율 등을 기반으로 리팩토링 전/후 성능을 정량적으로 비교해보고자 함.

 

https://lhk9311.tistory.com/8

[K6] 대용량 엑셀 다운로드 API 부하 테스트 - XSSF vs SXSSF 성능 비교