[MySQL] 조회 시, 전체건을 한번에 배치 서버 메모리에 올리지 않는 방안 비교

배경

배치 서버에서 수행중인 배치들에서 조회한 데이터 사이즈가 배치 서버의 max heap memory 크기를 넘을 때, OOM(Out Of Memory) exception 발생
- 예를 들어, 배치 서버에서 한개 배치가 수행된다고 가정하고, 해당 배치 서버 max heap memory 가 4GB, 배치 서버에서 조회하는 테이블 용량이 5GB이며, 배치에서 select * from 'table명'으로 조회 시, OOM 이 발생합니다.
  - 서버의 max heap memory 는 java -XX:+PrintFlagsFinal -version 2>&1 | grep -i -E 'heapsize|permsize|version' 로 확인 가능합니다.
  - 참고) 테이블 용량은 아래 쿼리로 확인 가능
  - ```
  SELECT table_name AS "Table", ROUND((data_length) / 1024 / 1024 / 1024, 2) AS "Size (GB)"
  FROM information_schema.tables
  WHERE table_schema = '스키마명' AND table_name in ('테이블명');
```

해결 방안

페이징 쿼리 사용
server cursor-based streaming 사용
DB table 을 file dump 후 file 을 한줄씩 read

해결 방안 비교
- 페이징 쿼리 사용
- 장) DB 서버와 배치 서버 모두 부하가 작음
- 단) page, pageSize 를 관리해야 해서 구현이 다소 복잡
- 사용) 전체 데이터를 DB 메모리에 올렸을 때 부하가 큰 사이즈의 table 배치
- server cursor-based streaming 사용
- 장) 구현이 가장 간단
- 단) DB 에서는 조회 데이터 크기에 따라 DB 서버 부하가 클 수 있어, 특정 table 적용에 대해 DBA 와 상의 필요
- 사용) 전체 데이터를 DB 메모리에 올렸을 때 부하가 크지 않는 사이즈의 table 배치
- DB table 을 file dump 후 file 을 한줄씩 read
- 장) DB 서버와 배치 서버 모두 부하가 작음
- 단) file dupm 와 배치 서버 업로드가 필요해 구현이 다소 번거로움, 주기적인 수행 불가
- 사용) 1회성의 대용량 table 배치 (ex. 이관 배치)

해결 방안 상세 - 페이징 쿼리 사용

방안 상세
- 특정 table 의 index range scan 을 활용할 수 있는 페이징 쿼리 사용
- ex) id 에 auto increment constraint 가 걸려있을 때, id range 로 페이징 처리
- ex) create_datetime 과 같은 timestamp column 에 index 를 설정하고, timestamp range 로 페이징 처리
- 참고) DBA 김부일님이 LIMIT [offset,] row_count 을 활용한 페이징은 DB 에 부하가 커서 추천하지 않는다고 합니다. 예를 들어 LIMIT 1000, 2000 은 총 3000 row 를 조회해서 1000부터 3000까지의 row 를 client로 return 하므로, offset 이 커질 수록 DB 부하가 linear 하게 증가한다고 합니다.
장점
- DB 서버와 배치 서버 모두 부하가 적다.
- page size 만큼만 DB 서버에서 조회해서 배치 서버 메모리에 올려 처리한다.
- 배치 서버에서 해당 데이터 처리 후 해당 데이터 object 들은 보통 garbage collection 됨 (instance variables 이 해당 데이터 object 를 참조하고 있지 않다는 가정)
단점
- 구현이 다소 복잡하다.
- page 와 page size 관리 필요
- for 문을 돌면서 처리하므로, Tasklet 에서 구현하는 것이 보통
  - ItemReader, ItemProcessor, ItemWriter 로 구분해서 처리가 어렵다.
- 특히 id 에 auto increment constraint 가 없을 때 구현이 더 복잡하거나, 기타 성능이 저하된다.
  - 공통
    - 페이지 범위의 create_datetime 에 몇개의 row 가 조회되는지 예측할 수 없어, 범위를 크게 잡을 경우 OOM 의 위험이 있다.
  - create_datetime 과 같은 timestamp column 에 index 설정 시, index 추가에 따른 DML 성능이 저하된다.
  - FK 관계가 있는 다른 table 의 id 가 auto increment 할 경우, join 을 통해 다른 테이블 id range 로 페이징 처리가 가능하나, 구현이 복잡해진다.

해결 방안 상세 - server cursor-based streaming 사용

방안 상세
1. JDBC connection property 에 useCursorFetch=true 설정
2. JdbcCursorItemReader 사용. 이때 한번에 배치 서버로 가져올 row 수 fetchSize property 설정 후 사용
참고) 미리 알면 좋은 개념
- cursor - 버퍼의 현재 row 에 대한 pointer
- ResultSet
- 커서를 조작하는 class
- 현재 row 에 대한 cursor 를 관리하고, next method 호출 시, cursor 를 다음 row 로 이동
- JdbcCursorItemReader
- ResultSet 을 활용하며, DataSource 에서 얻어진 connection 에 대해 SQL 문을 수행
JdbcCursorItemReader 동작 방식
1. Spring Batch framework 가 MySQL 서버에 SQL 문 수행. MySQL 서버는 쿼리 결과를 담는 임시테이블을 생성하고, server cursor 를 엽니다.
2. MySQL 서버는 cursor 정보와 JdbcCursorItemReader 내 fetchSize 에 지정된 row 만큼 배치 서버로 전송
3. JdbcCursorItemReader 내 ResultSet 은 cursor 정보를 관리
4. 해당 row 들이 ItemProcessor, ItemWriter 에 의해 처리되면, JdbcCursorItemReader 은 다음 cursor 를 시작으로 `fetchSize 에 지정된 row 만큼 MySQL 서버에 요청
5. 조회된 row 들이 없을 때까지 2,3,4 반복
장점
- 구현이 비교적 간단하다.
- 쿼리 상 페이징 처리가 없어, page 와 page size 관리 불필요
- 조회를 JdbcCursorItemReader 에서 수행하므로, step 을 ItemReader, ItemProcessor, ItemWriter 로 구분하기 쉽다.
단점
- 조회 데이터 크기에 따라 DB 서버 부하가 클 수 있음
- MySQL 서버에 쿼리 결과를 담는 임시테이블 생성되기 때문
- DBA 와 허용 가능한 임시 테이블 크기에 대해 상의할 필요가 있습니다.
  - DBA 김부일 님께서 MySQL standby 서버(abd002.esnow) 기준 한번에 10GB 의 임시 테이블 생성은 괜찮다고 답변 주셨습니다.
  - 참고로 DB 서버의 메모리 크기는 16GB * 8 입니다.
  - 10GB 가 넘는 크기 테이블은 10GB 씩 조회되도록 페이징한 쿼리로 JdbcCursorItemReader 를 활용한 복수개의 step 으로 처리할 수 있습니다.
참고)

해결 방안 상세 - DB table 을 file dump 후 file 을 한줄씩 read

장점
- DB 서버와 배치 서버 모두 부하가 적다.
- DB table 을 메모리에 올리지 않고, DB 에서 file dump 하므로 DB 서버 메모리 부하가 적다.
- 배치 application 에서 한줄씩 읽으므로 배치 서버 부하가 적다.
단점
- 수작업이 필요해서, 이관과 같은 1회성 배치에서만 사용 가능
- DBA 에게 file dump 를 요청하고, 해당 파일을 배치 서버에 upload 해야 함
참고
- ItemReaders - Flat Files

hyoseok0 / DB

[MySQL] 조회 시, 전체건을 한번에 배치 서버 메모리에 올리지 않는 방안 비교 #3

배경

해결 방안

해결 방안 상세 - 페이징 쿼리 사용

해결 방안 상세 - server cursor-based streaming 사용

해결 방안 상세 - DB table 을 file dump 후 file 을 한줄씩 read