[Java] 대용량 엑셀파일 다운로드 (Sxssf, ResultHandler)

약 3주 간의 엑셀 다운로드 오류 해결, 개선 과정에 대한 포스팅이다.
말로만 듣던 OOM을 직접 겪고 CPU와 메모리 점유율을 모니터링하면서 주요 로직을 왜 백엔드에 위치해야 하는지 직접 체감한 경험이 되었다. 😅
 
 

초기 코드

 

문제점

 
기존 코드는 전체 데이터 조회, 엑셀 출력 시 화면 단의 라이브러리를 활용했고, 페이징이나 스트리밍 방식이 적용되지 않았다.
 
이때 데이터 1개월 치(약 30만 건 데이터, 약 15MB)를 조회/출력하면 하단과 같은 현상이 발생했다.

• 조회
  • 데이터 로딩 속도가 느리고 클라이언트의 CPU, 메모리 점유율이 증가함
  • => 페이징 없이 한번에 대용량 데이터를 불러오면서 메모리 위에 계속 누적되기 때문
• 출력
  • 출력할 데이터가 메모리에 올라가는 동안 CPU, 메모리 점유율이 계속 상승하다가 OOM 오류가 발생함
  • => 클라이언트의 브라우저에 할당된 메모리를 초과했기 때문

 
 

개선 방안

• 페이징 적용
• 서비스 로직을 화면에서 백엔드로 이동

위 사항들을 해결하기 위해 페이징을 적용해 조회 시 부하가 오지 않도록 했고, 클라이언트의 자원이 아닌 개발/운영 서버의 자원을 사용하고 관리하도록 하기 위해 서비스 로직을 백엔드 사이드로 이동했다.
 
 
 

1차 배포 (적용 X)

 

1. 페이징 구현 ✅

 
기존 사용하던 데이터테이블을 유지하되 serverside 옵션을 지정해서 백엔드로 페이징을 구현하도록 했다. 엔티티 필드 추가, 쿼리 수정 등 백엔드 로직을 수정했다.
그리고 사용자 경험을 위한 화면 기능도 추가적으로 구현했다. (검색 가능한 날짜 범위 제한, 로딩 시 검색 버튼 비활성화)
 
 

2. MSSQL bcp 유틸리티를 이용한 csv 파일 저장 ⚠️

EXEC master..xp_cmdshell 'bcp "[select문]" queryout "[파일저장경로]" -c -U [유저아이디] -P [비밀번호]'

 
사용자 PC 리소스가 아닌 배포 서버의 리소스를 사용하여 점유율을 관리할 수 있도록 하려고 시도했다. 가장 먼저 채택하려고 한 방식은 MSSQL에서 bcp 유틸리티를 사용해 csv 파일을 내보내는 방식이었다. 테스트했을 때 30만 행의 데이터를 csv 파일로 저장하는 데 약 2분이 소요됐다. 단점은 기존 방식과 달리 제목, 컬럼 헤더, 데이터 형식 등을 커스터마이징할 수 없다는 것이었다. 
 
 

• 문제점

개발 서버에서 적용해보니 보안 정책에 따라 설정한 DBMS 계정 권한을 변경해야 bcp 유틸리티 기능을 사용할 수 있었다.
보안 정책을 위반하고, 권한을 상승시킨 후 위험도가 높아져 해당 방법은 포기했다.
 
 
 

2차 배포 (실패)

poi 최신 라이브러리를 활용한 xlsx 파일 다운로드 ✅

엑셀 다운로드 기능을 자바 백엔드 단에서 SxssfWorkbook 객체를 중심으로 구현했다. SxssfWorkbook은 XssfWorkbook과 달리 스트리밍 형식으로 데이터를 메모리에 올리면서 설정한 행 수를 초과하면 flush해가는 방식이다. OOM 오류 방지에 효과가 매우 좋은 것으로 알려져 있어서 해당 방식을 채택해 코드를 구현했다.
 
 

오류 1. POI 의존 라이브러리 전체 반입 실패 ⚠️

개발 서버, 운영 서버 모두 폐쇄망이었기 때문에 라이브러리 jar 파일들을 직접 반입하고 설치해야 했다. 이때 Sxssf 방식을 위한 최신 버전의 poi 라이브러리만 반입하면 바로 성공할 줄 알았다. 하지만 maven update를 해도 계속 SxxsfWorkbook 객체는 import되지 않았다. 스택오버플로우에서 검색해보니 poi, poi-ooxml 외에도 하단의 여러 라이브러리들이 필요했다. 
 

// 버전은 각자 버전에 맞춰 다시 설정해야 함
commons-collections4-4.4.jar
commons-compress-1.20.jar
log4j-api-2.14.1.jar
log4j-core-2.14.1.jar
poi-ooxml-5.0.0.jar
poi-ooxml-lite-5.0.0.jar 
xmlbeans-5.0.0.jar

 
 

오류 2. Maven Clean 오류 ⚠️

외부 라이브러리를 등록한 이후에도 오류가 발생했는데 target 디렉토리의 파일들이 제대로 clean되지 않는 것이 원인이었다. IDE를 껐다 키고 계속 Maven clean, install, update를 반복하니 어느 순간 clean이 적용이 돼 문제를 해결할 수 있었다.
 
 
 

3차 배포 (실패)

배포 후 운영 환경에서 OOM 발생 ⚠️

라이브러리 문제를 해결하고 배포 환경에서 다른 포트를 열어 한번 로컬 테스트를 해봤다. 이때 1주치 데이터(3.5만 행)는 2분, 1달치 데이터(25만 행, 15MB) 10분 정도가 소요됐다. 느리기는 했지만 우선 기능 구현이 완료된 것으로 파악해 배포를 했다. 하지만 테스트와 달리 운영 환경에서 Timeout, OOM 오류가 발생했다.
 

• 원인

배포 전후로 사용하는 리소스의 출처가 다르다. 로컬 테스트를 할 때에는 로컬 PC의 자원을 사용하고, 배포 후에는 서비스로 올린 톰캣의 자원을 사용한다. 따라서 톰캣의 자원 가용량이 적어 발생한 문제라고 판단했다.
 
톰캣 메모리 설정을 확인해보니 힙메모리가 최소 128MB, 최대 256MB로 설정돼있었다. VM 서버 물리 메모리를 늘릴 여유가 있어 물리 메모리를 늘리고 Tomcat heap 메모리 공간도 늘리기로 했다.
 
 
 

4차 배포 (성공)

메모리 증설  ✅

1. 서버 물리 메모리 증설 (16G -> 64G)
2. 톰캣 heap 메모리 증설 (128MB, 256MB -> 2048MB, 2048MB) 

 
 

• 결과

OOM 오류가 해결되었지만 다운로드 속도는 그대로였다.
 
 
 

5차 배포(성능 개선)

로그 설정 (logback-spring.xml) 변경 ✅

 

• 상황

Sxssf, ResultHandler를 사용한 엑셀 파일 다운로드 기능을 구현한 다른 블로거들을 보니 훨씬 속도가 빨랐다.
관련 이슈를 구글링하면서 가능한 코드 최적화는 다 시도해봤다. CellStyle 선언을 최소화하고 autoSizeColumn()도 사용하지 않으려고 했는데도 유의미한 속도 변화가 없었다. 그러다 엑셀 구현 코드를 다른 분 로컬 PC에서 실행해봤는데 훨씬 빠른 속도로 다운로드가 됐다. 이때 설정 파일들을 분석해보며 차이점을 알아내려고 했다.
 
 

• 원인

가장 눈에 띄는 차이점은 내가 기존의 application.properties 파일과 기타 로그 관련 설정 파일들을 정리 차원에서 삭제했다는 것이었다. application.yml, logback-spring.xml 2개의 파일로 로그 관련 설정을 적용하고 있었다. 하지만 기존 설정 파일이 존재했던 해당 로컬 PC에는 application.properties, logback-spring.xml, logback-{level}.properties 등 다양한 로그 설정 파일이 적용되고 있었다. 
 
그리고 설정 파일 우선순위에 따라 로그 설정은 하단 순서대로 적용되었다.

application.properties → logback-spring.xml → logback-info.properties

 
내 코드에서는 logback-spring.xml에만 로그 관련 주요 설정이 작성돼있었다. 그리고 root 태그가 없어 log4jdbc 내부 로거들이 모두 debug 레벨로 자동 적용되고 있었다. 반면 기존 코드에서는 다양한 설정 파일이 적용되고 있었고 마지막으로 적용되는 logback-info.properties 내부 설정에 의해 여러 로거들이 off 되어 해당 코드로는 기능 구현 속도가 훨씬 빨라진 것이었다.
 

 

• 결과

yml 설정을 그대로 하고 logback-spring.xml 에 root 태그를 추가해 level=off 처리했다. 선택한 logger의 로그만 실행되도록 하니, 10분이 넘게 걸리던 다운로드가 (15MB, 30만건) 약 20초로 대폭(약 96.6%) 감소했다. 설정 파일과 로그의 문제였던 것이다.

 
 

코드

• Service

public void requestExcel(ExcelRequestDto excelRequestDto, HttpServletResponse res) throws SQLException{
  ExcelUtil handler = null;
    try {
        // 유틸 클래스에 미리 엑셀 정보(헤더, 파일명 등) 세팅
        ExcelInfo excelInfo = matchInfo(excelRequestDto);
        handler = new ExcelUtil(excelInfo, res);

        // ResultHandler로 데이터 조회 -> Sxssf 방식 xlsx 다운로드
        ExcelDao.requestExcel(excelRequestDto, handler);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 엑셀 파일 생성 및 연결 종료
        if (handler != null) {
            try {
                handler.makeExcel();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

 
 
 

• Util Class

public class ExcelUtil implements ResultHandler {

    private final HttpServletResponse res;
    private final SXSSFWorkbook workbook = new SXSSFWorkbook(5000);
    private final CellStyle bodyStyle;
    private final ExcelInfo excelInfo; // 제목, 헤더 등 엑셀 파일에 미리 입력할 정보
    private final SXSSFSheet sheet = workbook.createSheet();

    private int ROWNUM = 0;

    public ExcelUtil(ExcelInfo excelInfo, HttpServletResponse res) {
        this.bodyStyle = createBodyStyle(); // handleResult()에서 데이터 불러오기 전에 스타일 미리 지정
        this.res = res;
        this.excelInfo = excelInfo;

        workbook.setCompressTempFiles(true);

        setInfo(excelInfo); // 제목, 조건, 헤더 세팅
    }

    @Override
    public void handleResult(ResultContext resultContext) {
        if (resultContext.getResultObject() == null) return;

        setBody((Map<String, Object>) resultContext.getResultObject());
    }

    public void setInfo() {
        String sheetName = excelInfo.getSheetName;
        String[] condition = excelInfo.getCondition;
        String[] headerName = excelInfo.getHeaderName;

        // 스타일 지정
        CellStyle titleStyle = createTitleStyle(workbook);
        CellStyle conditionStyle = createConditionStyle(workbook);
        CellStyle headerStyle = createHeaderStyle(workbook);

        // 데이터 입력
        setTitle(sheet, titleStyle, headerName.length-1 , sheetName);
        setCondition(sheet, conditionStyle, condition, headerName.length-1);
        setHeader(sheet, headerStyle, headerName);
    }

    private CellStyle createTitleStyle(){
        CellStyle cellStyle = workbook.createCellStyle();

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 폰트
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        font.setFontName("맑은 고딕");
        cellStyle.setFont(font);

        return cellStyle;
    }

    private CellStyle createConditionStyle(){
        CellStyle cellStyle = workbook.createCellStyle();

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return cellStyle;
    }

    private CellStyle createHeaderStyle(){
        CellStyle cellStyle = workbook.createCellStyle();

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 폰트
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.index);
        cellStyle.setFont(font);

        // 셀 테두리
        cellStyle.setBottomBorderColor(IndexedColors.WHITE.index);
        cellStyle.setTopBorderColor(IndexedColors.WHITE.index);
        cellStyle.setLeftBorderColor(IndexedColors.WHITE.index);
        cellStyle.setRightBorderColor(IndexedColors.WHITE.index);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        // 배경색
        cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return cellStyle;
    }

    private CellStyle createBodyStyle(){
        CellStyle cellStyle = workbook.createCellStyle();

        // 정렬
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return cellStyle;
    }

    private void setTitle(CellStyle cellStyle, int headerLen, String sheetName) {
        Row row = sheet.createRow(ROWNUM++);
        row.setHeight((short) 1000);

        Cell cell = row.createCell(0);
        cell.setCellValue(sheetName);
        cell.setCellStyle(cellStyle);

        for(int i=1;i<headerLen;i++){
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
        }

        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, headerLen)); // 셀 병합
    }

    private void setCondition(CellStyle cellStyle, String[] condition, int headerLen) {
        Row row;
        Cell cell;

        for(int i=0; i<condition.length; i++) {
            row = sheet.createRow(ROWNUM++);
            row.setHeight((short)0x190);

            cell = row.createCell(0);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(condition[i]);
        }

    }

    private void setHeader(CellStyle cellStyle, String[] headerName) {
        Row row;
        Cell cell;

        row = sheet.createRow(ROWNUM++);
        row.setHeight((short)0x350);

        for(int i=0;i<headerName.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(headerName[i]);
            cell.setCellStyle(cellStyle);

            sheet.setDefaultColumnStyle(i, bodyStyle);
            sheet.setColumnWidth(i, 3000);
        }
    }

    private void setBody(Map<String, Object> record) {
        Row row = sheet.createRow(ROWNUM++);
        
        String[] columnNames = excelInfo.getColumnName();
        
        Cell cell;
        for(int i=0;i<columnNames.length;i++){
            cell = row.createCell(i);

            if (record.get(columnNames[i]) == null) { // 빈 값
                cell.setBlank();
            } else { // 문자열
                cell.setCellValue(record.get(columnNames[i]).toString());
            }
        }
    }

    // 연결 종료
    public void makeExcel() {
        // 응답 헤더 세팅
        setResponseHeader();

        // excel 출력
        ServletOutputStream servletOutputStream = null;

        try{
            servletOutputStream = res.getOutputStream();
            workbook.write(servletOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                servletOutputStream.close();
                workbook.dispose();
                workbook.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    private void setResponseHeader(){
        String fileName = "file";
        
        if (!excelInfo.getFileName().isEmpty()) fileName = excelInfo.getFileName();
       
        try {
            res.setHeader("Content-Disposition", "attachment;filename=" + fileName +".xlsx");
        catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
      
        res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    }

}

 
 
 
 

개선점

현 서버 아키텍처는 모놀리식이다. 한 서버의 자원 리소스가 WAS, DB의 작업들을 모두 감당하고 있다. 실제로 물리 메모리를 증설하기 전까지만 해도 Tomcat, SSMS이 백그라운드에서 실행될 때 메모리 점유율은 기본 80%였다. 이번 작업에서는 메모리를 늘리는 스케일 업 방안을 적용했지만 해당 방법을 문제가 발생할 때마다 사용하기에는 한계가 있다. 자원 사용량 외에도 확장성, 유지보수 측면에서도 3계층(WEB-WAS-DB)으로 서버들을 분리하는 게 알맞아 보인다.
 
 
 

회고 

분석, 설계 단계의 중요성을 깨달은 경험이었다. 나는 개발 단계에 중도 투입돼서 해당 단계에서의 자세한 내용은 잘 모르지만, 타 사이트에서는 한 달 치 데이터를 뽑은 적이 없어 이런 실수가 발생한 것 같다. 앞으로는 발생 가능한 오류들을 최대한 예측하고 데이터 사용량도 유연하게 예측해야겠다.
또 코드 외에도 인프라 지식이 중요하다는 것을 느꼈다. 아무리 코드 리팩토링을 하고 최적화를 해도 오류가 해결되지 않을 때에는 절망감이 들 정도였다. 그러다가 코드 외의 부분에서 오류를 찾아가고 다른 분들께 여쭤보며 답을 찾아갈 수 있었다. 이번 경험으로 우주 앞의 먼지가 되는 기분을 맛보기도 했는데, 한편으론 CS 지식에 대한 궁금증과 필요성을 절실히 느끼게 해준 값진 경험이 되었다.