[Spring] Apache Poi 엑셀 다운로드 개선 및 성능 확인 (+ SXSSF)

Apache Poi를 활용하여 엑셀 다운로드를 개선해보자 ❕

 

 

 

1. 문제

---

지난 프로젝트에서 엑셀 관련 개발을 진행한 적이 있었다. 대부분의 엑셀 파일 용량은 50KB 이하였기 때문에, 파일 크기만 제한시켜두는 방식으로 진행했었다. 근데 최근 스터디에서 대용량 엑셀 파일을 다뤄본 적이 있냐는 질문을 받았고, 이 부분은 개발 당시에 고려하지 못했던 부분이라 이번 기회에 대용량 엑셀 파일을 처리하면서 개선해 보려고 한다.

 

 

먼저, 5만개 row를 가지는 1.6MB 크기의 파일을 생성했다.

 

 

해당 파일을 업로드 했더니 다음과 같은 에러가 발생했다.

스프링은 기본적으로 1MB 까지의 파일을 업로드할 수 있도록 제한되어 있기 때문이다.

 

 

spring.servlet.multipart.max-file-size=2MB  # 한 파일의 최대 사이즈
spring.servlet.multipart.max-request-size=5MB  # 한 요청의 최대 사이즈 (다중 업로드 시 필요한 설정)

따라서 다음 두 값을 설정해줬다. (지금은 위와 같이 1.6MB 파일을 허용하기 위해 다음과 같이 설정했지만, 이 값들은 진행하는 서비스 특성에 맞춰서 결정해야 한다)

 

설정 후, 다시 실행한다면 엑셀 업로드가 정상적으로 완료된다.

 

 

하지만 업로드 API의 응답속도는 약 3초, 다운로드 API의 응답속도는 약 7초 정도 소요된다.

 

 

만약 지금보다 더 큰 사이즈의 엑셀 파일이 업로드된다면 계속해서 file size를 늘려줘야 하는 걸까? 그렇다면 사용자 입장에서는 응답속도가 느려질 것이고 서버 리소스 또한 부족해질 수 있다. 이 부분에 대해서 대용량 엑셀 파일을 처리할 때는 어떻게 최적화해야 하는지 고민해보면서 풀어가보려고한다.

 

 

 

2. Apache Poi & MultipartFile

---

해당 프로젝트는 Spring에서 Apache Poi 라이브러리와 MultipartFile 인터페이스를 사용하고, DB 저장 없이 하나의 엑셀 데이터를 업로드 및 다운로드 해 response에 반환하는 방식이다.

 

 

Apache Poi

• Microsoft 오피스 파일을 Java로 읽고 쓰는 기능을 제공하는 라이브러리
• 셀 스타일, 포맷팅, 수식 등을 제어 가능
• java 환경에서 MS office 기능을 다루는 가장 안정적인 라이브러리
• 대용량 파일 처리 시 메모리 부족 문제
• 특정 함수에서 성능 이슈가 발생될 수 있다.

 

MultipartFile

• spring에서 파일 업로드를 처리하기 위한 인터페이스
• content-type이 multipart/form-data 형식인 파일을 다루기 쉽게 만들어짐
  • 기존의 form 방식은 바이너리 데이터를 인코딩하여 전송하는 방식이었는데, multipart 타입은 인코딩하지 않고 다양한 형식을 분리해서 전달하는 방식이다.
  • 스프링에서 파일을 자동으로 multipart 객체로 변환해준다.
• 특정 사이즈 이상으로는 JVM 힙 메모리에 올라가는 것이 아니라, 서버의 디스크에 저장된다. (기본 스트림 버퍼 작동 방식과 동일)
  • file_size_threshold 설정값에 따라 메모리에 저장할지 서버 디스크에 저장할지 결정된다.
  • 디스크에 저장되는 파일은 원래 업로드된 파일의 바이너리 데이터가 그대로 저장
• 파일 데이터를 읽어올 때, 메모리 사용량을 줄이기 위해 데이터를 한 번에 모두 로드하지 않고 필요한 만큼만 읽어서 효율적이다.
• 내부적으로 I/O 스트림을 사용한다. (InputStream, OutputStream)
  • 보통 4KB~8KB의 버퍼 크기를 가져, 부분적으로 메모리에 올리는 부분적으로 스트리밍 처리를 한다.

 

 

그렇다면, 이제 발생할 수 있는 문제와 해결 방법을 다시 생각해보자

 

문제 예측

1. 대용량 엑셀 업로드 시 응답 시간 지연
2. 디스크 및 메모리 부족

 

해결 방법 고민

1. 대용량 엑셀 업로드 시 응답 지연
   • 위 방법처럼 max-file-size을 설정해 파일 크기를 제한하기
   • client에서 나눠서 업로드를 처리하는 방식
2. 디스크 및 메모리 부족
   • 실제로 MultipartFile 관련 글들을 찾아보면 디스크에 삭제되지 않고 남아있는 경우가 있다고 한다. 이 경우 디스크 저장공간 부족으로 이어질 수 있는 위험이 있어서, 지정된 경로에 충분한 디스크 공간을 확보하고 주기적인 정리 작업을 해줘야 한다. (참고로, 임시 파일은 기본적으로 운영체제의 기본 /tmp 디렉토리에 저장하는데, 운영 체제의 기본 임시 디렉토리는 다른 애플리케이션이나 사용자도 접근할 수 있기 때문에 보안상에 좋지 않다고 한다)

 

 

따라서 다음과 같은 방법들을 생각해보았고

 

클라이언트

• 업로드 크기 제한 및 나눠서 업로드 요청 (대용량 엑셀 업로드를 허용하는 것이 아닌, 제한하는 방법)

 서버

• 파일 서버를 따로 구축
• 비동기 방식 (여러 엑셀 파일 업로드 시)
• 빈번히 사용하는 데이터를 캐싱 (Redis)

이번 시간에는 기존의 프로젝트에서 ^응답 속도 개선와 리소스를 적게 사용하는 방법^으로 개선해보고자 한다.

 

 

 

3. 엑셀 업로드 제한

---

다음 설정값들을 통해 업로드 시 파일 크기에 제한을 두었고, multipartfile 작업을 메모리가 아닌 디스크에 저장해 처리하는 방식으로 설정했다.

spring.servlet.multipart.file-size-threshold=0B  # 파일을 디스크에 저장하지 않고 메모리에 저장하는 최소 크기
spring.servlet.multipart.location=/Users/chaego/Desktop/temp  # 업로드된 파일이 임시로 저장되는 디스크 위치
spring.servlet.multipart.max-file-size=1MB  # 한 파일의 최대 사이즈
spring.servlet.multipart.max-request-size=1MB  # 한 요청의 최대 사이즈

이 방법은 대용량 파일을 허용하는 것이 아닌, 제한시키는 것이다.

 

이제 엑셀 파일 사이즈가 1MB 이상인 요청은 스프링에서 거절될 것이고, 디스크에 저장되는 파일을 쉽게 처리할 수 있을 것이다.

 

 

(참고)

실제로 파일 업로드를 진행했을 때, 임시 파일이 생성되고 파일 업로드가 종료되면 자동으로 없어지는 것을 확인할 수 있다. 만약 업로드 중에 스프링을 종료한다면 이 파일들이 제거되지 않고 남아 있는 문제도 확인할 수 있었다.

 

 

 

4. SXSSF를 활용해 엑셀 다운로드 처리 개선

---

다운로드 관련 apache poi 자료를 찾다가 SXSSF라는 것이 있다는 것을 알게 됐다.

 

 

XSSF

• 읽기, 쓰기 가능
• 메모리에 파일데이터를 쌓아두고 사용하기 때문에 용량이 큰 경우 OOM(Out Of Memory) 에러가 발생할 수 있다.

 

SXSSF

• 쓰기만 가능
• 지정된 수(버퍼 크기)만큼만 메모리에서 작업하고, 작업이 완료되면 디스크에 저장하고 메모리를 비운다.
• 디스크에 임시 파일을 생성하여 메모리를 적게 사용한다. 따라서 XSSF보다 큰 사이즈를 다룰 수 있다.

 

^즉, XSSF는 모든 row를 디스크에 올려놓는 방식인 반면 SXSSF는 지정한 row만큼 메모리에 올려두고 지정한 row까지 데이터를 입력하면 디스크에 기록하고 메모리를 비워주는 방식이다.^

 

 

엑셀 파일을 업로드 할 때는 MultipartFile을 사용해 디스크 - - > 메모리로 옮겨 부분만 처리하지만, 다운로드 시에는 바로 XSSF를 사용해 엑셀 파일을 생성하기 때문에 SXSSF를 사용하도록 변경해줬다.

 

 

@PostMapping("/download/{headerId}")
public void downloadExcelFile(HttpServletResponse response, @PathVariable UUID headerId, @RequestBody List<UploadExcelDto> dtos) {        
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment");

    try{
       // Workbook workbook = new XSSFWorkbook()     // 기존 방식
       Workbook workbook = new SXSSFWorkbook(1000);     // .xlsx, 메모리에 1000개 행 유지
       service.setWorkbookForTranslatedData(workbook, headerId, dtos);
       workbook.write(response.getOutputStream());
       workbook.close();
       ((SXSSFWorkbook)workbook).dispose();    // 디스크 임시 파일 제거
    } catch (IOException e) {
       throw new IllegalArgumentException();
    }
}

 

수정하고 다운로드를 진행해보니, 기존 7초에서 600ms로 단축된 것을 확인할 수 있다.

 

 

(트러블 슈팅)

Attempting to write a row[0] in the range [0,49895] that is already written to disk.

XSSF에서 SXSSF로 변경하고 실행해보니 OMG.. 이런 오류가 떴다.

 

확인해보니 SXSSFWorkbook은 메모리 사용량을 줄이기 위해 지정된 수(버퍼 크기) 이상의 행을 자동으로 디스크로 플러시하기 때문에 한 번 디스크로 플러시된 행은 다시 메모리로 올라오지 않으며, 이미 플러시된 행을 수정하려고 한다면 위와 같은 오류가 발생한다는 것이다.

 

 

이 문제는 Case1과 같은 다운로드 과정에서는 발생하지 않지만, 나는 Case2와 같이 동작하도록 구현해놨기때문에 해당 오류가 발생한 것이다.

 

Case1. 한 row를 다 채워야 다음 row로 이동

 

Case2. 한 col을 다 채워야 다음 col로 이동

 

그래서 이 부분 로직을 다시 Case1과 같이 동작하도록 수정했다. 그리고 테스트 해보니, XSSF를 사용했을 때에도 Case2보다 Case1처럼 로직을 구성하면 다운로드가 훨씬 빠르게 수행된다. (XSSF를 사용해 Case2처럼 실행한다면 1분이 지나도 다운로드가 수행되지 않았다...)

 

 

 

5. 응답 속도 확인

---

먼저, 스레드 개수는 총 20개로 설정하고 테스트를 진행했다. 아래의 테스트에서는 약 천개의 row, 40KB 크기인 엑셀 파일을 사용했다.

server.tomcat.threads.max=20

 

 

jemter, visual vm을 통해 응답속도와 서버 자원, jvm 힙 메모리를 확인해볼 것이다.

visual vm에서 visual gc 플러그인 설치

 

 

두 상황을 가정해서 테스트를 진행해보려고 한다. 먼저 새로운 API를 만들어, 엑셀 업로드 후 바로 다운로드까지 실행되도록 했다.

Case1. 5초 동안 진행하며, 1초마다 30명의 유저가 연속으로 파일을 요청했을 때

Case2. 1초에 100명의 유저가 동시에 파일을 요청했을 때

 

 

Case1. 5초 동안 진행하며, 1초마다 30명의 유저가 연속으로 요청했을 때

 

1. XSSF을 활용해 업로드 & XSSF을 활용해 다운로드

 

2. XSSF을 활용해 업로드 & SXSSF을 활용해 다운로드

 

→ ^평균 응답 속도는 1300ms에서 270ms로 단축되었고 TPS는 18.6에서 63.1로 올라갔다. 단순히 다운로드할 때 SXSSF를 활용하는 것만으로도 응답시간과 처리량이 올라가는 것을 확인할 수 있었다.^

 

 

Case2. 1초에 100명의 유저가 동시에 요청했을 때

 

1. XSSF을 활용해 업로드 & XSSF을 활용해 다운로드

 

2. XSSF을 활용해 업로드 & SXSSF을 활용해 다운로드

 

→ ^평균 응답 속도는 2.8s에서 1.2s로 단축되었고 TPS는 19.1에서 37.2로 올라갔다.^

 

 

+ Case2에서 서버 자원 사용량도 확인해보면

 

1. XSSF을 활용해 업로드 & XSSF을 활용해 다운로드

 

2. XSSF을 활용해 업로드 & SXSSF을 활용해 다운로드

 

기본적으로 GC는 Eden → (S0 ↔ S1) → Old 로 수행되는데, Old 영역이 꽉차면 OOM이 발생한다.

위의 결과를 보았을 때, SXSSF를 사용할 때 서버 CPU와 JVM GC의 대상인 Eden과 Old영역을 덜 사용하는 것을 볼 수 있다. 따라서 지금보다 더 많은 요청이 발생했을 때, SXSSF를 사용하는 것이 안전할 것이다. 위에서 공부했던 내용들을 실제로 모니터링하며 결과가 측정되니 신기하다.

 

 

 

6. 마무리

---

이전에 작업했던 프로젝트를 리팩토링하면서, 대규모 파일 처리를 대응하는 방법에 대해 고민해보고 개선을 진행해보았다. 저번부터 엑셀관련 로직을 다시 만져야 겠다고 생각했었는데, 이번 기회에 비효율적인 로직도 수정하고 응답 시간도 단축시키면서 사소한 코드에서 더 나은 서비스를 만들 수 있다는 것을 느꼈다.

 

 

또, 공부하다 좋은 글이 있어서 일부 긁어왔다.

Korea Spring User Group Q&A  - KwonNam Son님

 

위 글을 읽고, 실무에서는 대량 업로드를 가능하게 해야 한다고 생각했던 것에 아차! 싶었다. 오히려 대규모 시스템이라면 대량의 데이터를 처리하는 것을 우선적으로 생각하는 것이 아니라, 시스템에서 어떻게 활용될지를 파악하고 이를 효율적인 방법으로 응답하는 것이 중요했던 것이다.

 

 

 

 

참고 자료 😃

https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html

https://kth990303.tistory.com/461

https://dev-coco.tistory.com/191

https://groups.google.com/g/ksug/c/UaQv4yFgTTA?pli=1

https://techblog.woowahan.com/11392/