꾸준2

기존 엑셀 업로드에 사용하는 insert 방식은 saveAll()인데 

프로젝트를 진행하며 최대 1,000건 정도의 insert를 테스트 했기때문에 조금 느리더라도 그러려니 했다.

하지만 10만건, 30만건, 100만건을 테스트 해본 결과 실행 시간이 너무 느리게 느껴졌고

리팩토링을 하는 김에 insert방식을 최적화 해보기로 했다.

현재 saveAll() 방식으로 엑셀 업로드를 했을때 실행 시간을 정리한 표이다.

다양한 대량의 데이터 insert 방식

• 순수 JDBC 기반 : JDBC Batch, JdbcTemplate.batchUpdate()
• MyBatis 기반 : for-each + Mapper, SqlSession + ExecutorType.BATCH
• JPA 기반 : saveAll(), bulk insert (native query)
• 네이티브 SQL 기반 : JdbcTemplate, nativeQuery in JPA
• 외부 라이브러리, Spring Batch, jOOQ, QueryDSL (native)

많은 방식 중에서 이번 프로젝트에서 엑셀 다운로드때 집계함수를 사용해서 mybatis를 사용했기 때문에
saveAll(), for-each + Mapper,SqlSession + ExecutorType.BATCH 3개의 성능을 비교해보려고 한다.

BaseTimeEntity 문제

java.sql.SQLException: Field 'created_at' doesn't have a default value

기존에는 엑셀 업로드를 할때 saveAll()을 사용했는데

saveAll()은 Spring Data JPA가 제공하는 기능이고

내부적으로는 EntityManager.persist()나 merge() 등을 호출한다.
이때 JPA는 @PrePersist, @PreUpdate 같은 엔티티 생명주기 콜백 메서드를 호출한다.

하지만 MyBatis는 순수 SQL 매퍼이기 때문에, JPA처럼 엔티티의 생명주기 콜백을 모르고, 호출하지도 않는다.
즉, @PrePersist 같은 어노테이션은 전혀 작동하지 않아서 문제가 발생했다.

foreach코드

@Mapper
public interface AdminSalaryMapper {
    void excelUploadWithForeach(@Param("salaries") List<Salary> salaries);
}


// xml파일
<insert id="excelUploadWithForeach">
    INSERT INTO salary (employee_id, basic_salary, deduction, net_salary, pay_date, year, created_at, updated_at)
    VALUES
    <foreach collection="salaries" item="salary" separator=",">
        (#{salary.employee.id}, #{salary.basicSalary}, #{salary.deduction}, #{salary.netSalary}, #{salary.payDate}, #{salary.year}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    </foreach>
</insert>

saveAll()로 100만건을 insert할때는 느리지만 메모리 부족 에러가 발생하지 않았는데

foreach는 속도도 빨라서 당연히 메모리 문제가 없을거라고 생각했는데 에러가 발생했다.

그래서 10만건씩 끊어서 insert를 하고 메모리 체크를 해보았다.

10만건씩(엑셀 100만건)

50만건씩(엑셀 100만건)

많은 데이터를 나누어 insert할 때, 각 배치의 메모리 사용량과 실행 시간이 증가하는것을 확인 할 수있고

이로 인해 100만 건을 한 번에 insert할 경우, 할당된 JVM 메모리 용량을 초과하여 사용 중인 메모리가 더 많아져 OOM(Out Of Memory) 오류가 발생한 것으로 예상됌

규칙이 궁금해서 5만건, 1만건 단위로도 확인해보았는데

10만건과 큰 차이가 없는거 같다.

5만건씩(엑셀 100만건)

1만건씩(엑셀 100만건)

10만건과 큰 차이가 없는거 같다.

찾아볼 내용

1. foreach원리는 어떻게 작동하길래 많은 데이터를 한번에 넣을때 메모리 사용량이 증가하는지?

엑셀 파일 읽는 로직

public <T> List<T> parseExcelToObject(MultipartFile file, Class<T> clazz) throws IOException, InterruptedException  {
    IOUtils.setByteArrayMaxOverride(300_000_000); // 레코드 크기 300MB까지 허용

    System.gc(); // GC 유도
    Thread.sleep(100); // GC 안정 시간

    Runtime runtime = Runtime.getRuntime();

    long maxMemory = runtime.maxMemory();          // 최대 힙 메모리
    long totalMemory = runtime.totalMemory();      // 현재 할당된 힙 메모리
    long freeMemory = runtime.freeMemory();        // 사용 가능한 힙 메모리
    long usedMemory = totalMemory - freeMemory;    // 실제 사용 중인 메모리

    System.out.printf("Max Memory: %d MB%n", maxMemory / (1024 * 1024));
    System.out.printf("Total Memory: %d MB%n", totalMemory / (1024 * 1024));
    System.out.printf("freeMemory: %d MB%n", freeMemory / (1024 * 1024));
    System.out.printf("Used Memory: %d MB%n", usedMemory / (1024 * 1024));

    long beforeUsedMem = getUsedMemory();
    System.out.printf("Before: %d MB%n", beforeUsedMem / (1024 * 1024));

    Workbook workbook = WorkbookFactory.create(file.getInputStream());
    Sheet sheet = workbook.getSheetAt(0);

    System.gc();
    Thread.sleep(100); // GC 안정 시간

    long afterUsedMem = getUsedMemory();
    System.out.printf("After: %d MB%n", afterUsedMem / (1024 * 1024));

    long memoryUsedByApi = afterUsedMem - beforeUsedMem;
    System.out.printf("Memory used by API: %d MB%n", memoryUsedByApi / (1024 * 1024));

    parseHeader(sheet, clazz);
    return parseBody(sheet, clazz);
}

    Workbook workbook = WorkbookFactory.create(file.getInputStream());
    Sheet sheet = workbook.getSheetAt(0);

엑셀 100만 건 업로드를 실행했을 때 위 사진과 같이 에러가 발생했다.

디버깅 결과 엑셀 파일을 읽는 로직에서 문제가 발생한 것을 확인했다.

현재 프로젝트에 할당된 메모리 내에서 업로드 가능한 엑셀 데이터의 범위를 파악하기 위해

직접 10만 건, 30만 건의 엑셀 파일을 생성해 메모리 사용량을 측정해보았다.

100만 건을 업로드할 경우 약 4,000MB 이상의 메모리를 사용할 것으로 예상되는데,
현재 프로젝트에 할당된 메모리는 2,048MB로, 이를 초과하기 때문에 문제가 발생한 것으로 보인다.

해결 방안으로는

1. 메모리 할당량을 늘린다.
2. 엑셀 파일을 읽는 로직을 최적화한다.

이 중 근본적인 해결을 위해서는 2번 엑셀 파싱 로직을 최적화하는 방향이 정석이라고 판단했다.

에러 유형

java.lang.OutOfMemoryError

발생 원인

1. 기존에 사용한 엑셀 파싱 로직은 DOM방식으로 엑셀 파일 전체를 파싱해서 메모리에 저장한다.

2. Workbook 전체를 객체로 만들어 다루기 때문에 구조 접근은 편하지만, 대용량 처리에는 취약

문제 해결 시도

DOM방식 -> SAX방식 변경

DOM - > SAX 방식으로 수정

발생한 문제 1

// 호출 부분
private SharedStringsTable sst;
this.sst = reader.getSharedStringsTable();

// poi 5.2.3
public SharedStrings getSharedStringsTable() throws IOException, InvalidFormatException {
        ...
        ...
}

// poi 4.1.2
public SharedStringsTable getSharedStringsTable() throws IOException, InvalidFormatException {
        ...
        ...
}

수정한 코드

InputStream is = file.getInputStream();
Workbook workbook = StreamingReader.builder()
        .rowCacheSize(100)  // 메모리에 유지할 행 수
        .bufferSize(4096)
        .open(is);

Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();

// 1. 헤더 파싱
if (!rowIterator.hasNext()) {
    throw new IllegalStateException("엑셀 파일에 데이터가 없습니다.");
}
Row headerRow = rowIterator.next();
parseHeader(headerRow, clazz);

// 2. 본문 파싱
List<T> data = new ArrayList<>();
int rowIndex = 1;

while (rowIterator.hasNext()) {
    Row row = rowIterator.next();
    if (row == null || row.getPhysicalNumberOfCells() == 0) continue;

    try {
        T instance = clazz.getDeclaredConstructor().newInstance();
        Method method = clazz.getMethod("fillUpFromRow", Row.class);
        method.invoke(instance, row);
        data.add(instance);

    } catch (Exception e) {
        throw new RuntimeException("Row " + rowIndex + " 변환 중 오류", e);
    }
    rowIndex++;
}

결과

 10만 건 기준

• DOM 방식: 약 403MB
• SAX 방식: 약 12MB
• → SAX 방식이 약 97% 더 적은 메모리 사용

30만 건 기준

• DOM 방식: 약 1227MB
• SAX 방식: 약 35MB
• → SAX 방식이 약 97.2% 더 효율적

100만 건 기준 (예상치)

• DOM 방식: 약 4000MB
• SAX 방식: 약 113MB
• → SAX 방식이 약 97.2% 이상 메모리 절감

- 100MB 초과로 인한 엑셀 파싱 실패

org.apache.poi.util.RecordFormatException: Tried to read data but the maximum length for this record type is 100,000,000.
If the file is not corrupt and not large, please open an issue on bugzilla to request 
increasing the maximum allowable size for this record type.
You can set a higher override value with IOUtils.setByteArrayMaxOverride()

- 에러 유형
  - org.apache.poi.util.RecordFormatException

- 발생 원인

  - excel 파일을 읽는 도중, 허용된 최대 크기(100MB)를 초과한 데이터를 포함한 레코드를 읽으려고 함

- 제한 사항

  - Apache POI는 기본적으로 1개의 레코드에 대해 100MB(100,000,000 바이트)까지만 읽도록 설정되어 있음.

레코드란??
내부적으로는 파일을 구성하는 여러 XML 혹은 바이너리 블록 단위
ex) sheet1 , styles, drawing

- 실제 압축 크기와 압축 해제 후 크기 확인

- xl/worksheets/sheet1.xml

  - 압축 크기 : 22.05 MB

  - 압축 해제 크기 : 246.73 MB

public <T> List<T> parseExcelToObject(MultipartFile file, Class<T> clazz) throws IOException {
        // MultipartFile을 임시 파일로 저장
        File tempFile = File.createTempFile("uploaded", ".xlsx");
        file.transferTo(tempFile);

        try (ZipFile zipFile = new ZipFile(tempFile)) {
            Enumeration<? extends ZipEntry> entries = zipFile.entries();
            while (entries.hasMoreElements()) {
                ZipEntry entry = entries.nextElement();
                System.out.println("Entry: " + entry.getName()
                        + ", Compressed Size: " + entry.getCompressedSize()
                        + ", Uncompressed Size: " + entry.getSize());
            }
        } finally {
            // 작업 끝나면 임시 파일 삭제
            tempFile.delete();
        }
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        parseHeader(sheet, clazz);
        return parseBody(sheet, clazz);
    }
    
Entry: xl/drawings/drawing1.xml, Compressed Size: 261, Uncompressed Size: 775
Entry: xl/worksheets/sheet1.xml, Compressed Size: 23125563, Uncompressed Size: 258712875
Entry: xl/worksheets/_rels/sheet1.xml.rels, Compressed Size: 179, Uncompressed Size: 298
Entry: xl/theme/theme1.xml, Compressed Size: 808, Uncompressed Size: 3757
Entry: xl/sharedStrings.xml, Compressed Size: 237, Uncompressed Size: 352
Entry: xl/styles.xml, Compressed Size: 575, Uncompressed Size: 2192
Entry: xl/workbook.xml, Compressed Size: 339, Uncompressed Size: 807
Entry: xl/_rels/workbook.xml.rels, Compressed Size: 234, Uncompressed Size: 697
Entry: _rels/.rels, Compressed Size: 178, Uncompressed Size: 296
Entry: [Content_Types].xml, Compressed Size: 309, Uncompressed Size: 1049

코드 수정

public <T> List<T> parseExcelToObject(MultipartFile file, Class<T> clazz) throws IOException {
        IOUtils.setByteArrayMaxOverride(300_000_000); // 레코드 크기 300MB까지 허용
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        parseHeader(sheet, clazz);
        return parseBody(sheet, clazz);
    }

나중에 찾아볼 내용

xlsx 파일은 사실 ZIP 파일이다??

엑셀 크기는 22MB이지만 실제로는 더 클수있다?