[Apache POI] 다량의 데이터 엑셀 다운로드 처리로 인한 서버 장애 대응 후기

스테이징 QA 도중 갑자기 사이트의 모든 기능이 먹통이 됐다는 소식을 들었습니다.

확인해보니 CPU 사용량이 급증하여 서버가 다운됐던 것이었습니다.

우선 서버를 재부팅하여 먹통 문제는 해결하였고, 원인을 찾아 나섰습니다.

 

지난 새벽 배포 때 다른 서버(노드)에서 몇만건정도의 데이터 엑셀 다운로드를 몇차례 시도했을때도 CPU 사용량이 급증했던 것이 기억나서

그 부분을 이번 서버(자바)에서도 확인해보기로 했습니다. 

엑셀 다운로드 시도할 때마다 CPU 사용량이 급증하는 것을 확인했습니다.

RDS는 이상 없고 EC2만 다운된 걸로 보아 서비스단에서 다량의 데이터를 처리하다 무언가 과부하가 걸렸을 것으로 추측했습니다.

 

로컬에서 상용DB를 연결하여 재현해보니 아래와 같은 에러 로그가 발생했습니다.

org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package : The part /xl/sharedStrings.xml fail to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@155ba7
	at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:479) ~[poi-ooxml-3.9.jar:3.9]
	at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1414) ~[poi-ooxml-3.9.jar:3.9]
	at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:179) ~[poi-ooxml-3.9.jar:3.9]
// 이하 생략

📍 로컬에서 상용 DB를 연결할 땐 주의 또 주의!! 이번 테스트에서는 Read Only DB서버를 연결했습니다.

 

Apache POI는 엑셀 업로드&다운로드 API 구현을 위해 제가 사용한 라이브러리 입니다.

공식 문서 : https://poi.apache.org/

Apache POI - the Java API for Microsoft Documents

사용법보다는 제가 이번 문제에 어떻게 대응했는지만 공유하겠습니다!

 

에러로그를 보니 잘은 모르겠지만 Apache POI 관련 문제이구나를 깨닫고 구글링을 해본 결과,

스텍오버플로우에서 XSSF가 아닌 SXSSF를 사용해보라는 답변을 발견합니다.

 

그게 뭐지....?

• File sizes/Memory usage
  There are some inherent limits in the Excel file formats. These are defined in class SpreadsheetVersion. As long as you have enough main-memory, you should be able to handle files up to these limits. For huge files using the default POI classes you will likely need a very large amount of memory.
  
  There are ways to overcome the main-memory limitations if needed:
  
  • For writing very huge files, there is SXSSFWorkbook which allows to do a streaming write of data out to files (with certain limitations on what you can do as only parts of the file are held in memory).
  • For reading very huge files, take a look at the sample XLSX2CSV which shows how you can read a file in streaming fashion (again with some limitations on what information you can read out of the file, but there are ways to get at most of it if necessary).

출처 : https://poi.apache.org/components/spreadsheet/limitations.html

 

공식문서를 뒤져보니 위와 같은 내용이 있었습니다.

요약하자면, 큰 파일을 write(엑셀 다운로드)하기 위해서는 SXSSFWorkbook을 사용해야한다는 것인데요,

저는 현재 XSSFWorkbook을 사용하고 있었습니다.

📍 XSSF는 .xlsx 포멧을 지원하는 Apache POI의 구현체이고, SXSSF는 적은 메모리를 사용하도록 개선된 버전입니다.

SXSSF는 Write Files만 지원해서 엑셀 다운로드 부분만 코드를 수정했습니다.

 

// 기존 코드
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();

// SXSSF로 변경한 코드
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();

 

하지만 또다시 에러가 발생합니다

org.apache.catalina.connector.ClientAbortException: java.io.IOException: Broken pipe
	at org.apache.catalina.connector.OutputBuffer.realWriteBytes(OutputBuffer.java:356) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
	at org.apache.catalina.connector.OutputBuffer.flushByteBuffer(OutputBuffer.java:825) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
	at org.apache.catalina.connector.OutputBuffer.append(OutputBuffer.java:730) ~[tomcat-embed-core-8.5.32.jar:8.5.32]

확인해보니 클라이언트에서 보낸 요청이 timeout에 걸려서 생긴 문제였습니다. 

참고 : https://born-dev.tistory.com/28

 

프론트엔드 코드를 열어서 axios의 timeout설정을 임시로 늘려봤습니다.

import axios from 'axios';

const instance = axios.create({
  baseURL: process.env.REACT_APP_BASE_URL,
  timeout: 1000 * 30000000, // 1000*30 이었던걸 임시로 늘렸음
});

(로컬에서 확인만 해보려고 한거라 PR은 안올렸습니다.)

 

코드 변경 후 다시 테스트 해보니 CPU사용량이 늘긴 하지만 25%까지만 늘고 다시 떨어지는 것을 확인했습니다.

 

 

어떻게 마무리 해야 할지 모르겠다!

---

SXSSF는 어떻게 많은 양의 데이터를 처리할 수 있게 된걸까?

추가로 알아보고 포스팅하였습니다

https://jibsakim.tistory.com/20

[Apache POI] SXSSF의 메모리 관리법 : 슬라이딩 윈도우(Sliding Window)

 

---

[2023.07.18 업데이트]

언젠가 면접에서 "그래도 CPU가 25%까지는 올랐는데, 데이터가 지금보다 더 늘어나서 다시 CPU가 늘어나게 되면 어떻게 하실 건가요?"라는 질문을 받게 되었습니다.

그때 머리가 멍해져서 엑셀용 서버를 만들겠다고 밖에 답변을 제대로 못했는데요.

다시금 생각해보자면...

 

SXSSF에서 설정한 윈도우 크기 만큼의 메모리를 사용하여 25%까지 치솟은 것 같습니다. sliding window 알고리즘이 메모리를 사용량을 제한하는 것이므로 25% 이상 늘어나는 원인이 다른 곳에 있을 수도 있다 생각합니다. SXSSF의 window 크기를 줄였을 때 CPU 수치가 덜 오르는지도 확인해보고 APM을 모니터링 하는 등 CPU 상승의 구체적인 원인을 더 찾아볼 것 같습니다.

window 크기를 줄이면 메모리 사용량이 줄겠지만 그만큼 file IO의 횟수가 늘어나 이것도 성능에 영향을 끼칠 것 같습니다.

 

대량의 데이터를 하나의 엑셀파일로 실시간으로 생성해서 다운받아야 하는 것이 비즈니스상 아주 중요한 작업이라면, 고사양의 엑셀작업 전용 서버를 두고 그쪽에 위임하는 것도 방법일 것이고

같은 검색조건으로 다운로드 요청이 매일 주기적으로 발생한다면, 트래픽이 적은 새벽에 전날까지의 데이터를 집계하여 미리 엑셀파일로 만들어서 S3로 만들어두는 방법도 있을 것 같습니다.

혹시 1개의 엑셀파일이 아니라 여러개로 끊어서 저장해도 된다면 엑셀 파일 1개당 데이터 수 제한을 걸어서 2개 이상일 경우 .zip파일 등으로 압축해서 S3에 미리 만들어두는 방법도 생각나네요.

 

서버 증설 말고는 모두 엔지니어링적 솔루션이기보단 정책적인 내용인 것 같긴 하네요...🥺

혹시 더 좋은 의견 있으실 경우 공유해주시면 감사하겠습니다 😄

 

사실 그냥 조회 가능한 데이터 수, 또는 날짜 범위에 limit을 걸어주셨으면 좋겠습니다 😅