DB데이터 대용량 조회 다운로드에서 OutOfMemory 피하기

대용량 DB데이터를 조회해서 엑셀파일 등으로 다운로드하는 경우, 대개 다운로드할 DB데이터를 처음부터 끝까지 힙메모리에 올렸다가 한꺼번에 이어서 내려주는 방식으로 동작한다. 이 과정에서 DB데이터를 모두 올리다가 힙메모리가 부족하면 OutOfMemoryError가 발생하게 된다. WAS의 힙메모리를 늘리면 어느 정도 해결이 되지만 이런 일이 발생할 때마다 힙메모리를 늘리다 보면 10GB로 늘리게 될 수도 있다.
이런 경우를 자주 보다가 몇 년 전에 생각해낸 방법이, 대용량 DB데이터를 힙메모리에 올리는 대신 읽는대로 임시파일로 생성한 후에 클라이언트로 데이터를 보낼 때는 이 임시파일을 읽는 것이었다. 아이디어만 갖고 있다가 며칠 전에 갑자기 해야겠다고 결심하고 해 보니 이틀 정도가 걸린 것 같다.

구현방법은 이렇다.
1. DB에서 데이터를 조회할 때 Cursor 방식으로 받는다.
2. Cursor에서 차례로 데이터를 읽어 읽는대로 임시파일에 쓴다.
3. 임시파일을 읽어 클라이언트로 보내 주고, 임시파일을 삭제한다.
4. 프로그램 수행 도중 오류로 임시파일이 삭제되지 않고 쌓이는 것을 방지하기 위해, 주기적으로 프로그램이 생성 후 일정시간이 지난 임시파일을 삭제한다.

Spring Boot로 구현했고, 실제 코드는 아래와 같다. 맨끝에 이클립스 프로젝트를 첨부하니 받아서 돌려볼 수 있다.

Mapper XML

    <select id="selectUsersCursor" resultType="hashMap" fetchSize="1000">
        <![CDATA[
        select * from user
        ]]>
    </select>

테이블에 12만건의 데이터를 밀어 넣고, 조건 없이 모든 데이터를 읽는 SQL을 작성했다. fetchSize는 대량 데이터를 빠르게 읽어 오기 위해서 1000으로 설정했다. 디폴트(얼마인지 모르지만)일 때보다 몇 십배는 빠르게 조회되었다.

Repository

<Dao 방식>

    public Cursor<HashMap<String, Object>> getUsersCursor() {
        return sqlSession.selectCursor("com.example.demo.sample.UserMapper.selectUsers");
    }

sqlSession의 selectCursor를 호출해서 List 대신 Cursor 타입을 리턴하도록 수정했다.

<Mapper 방식>

    Cursor<HashMap<String, Object>> selectUsersCursor();

단순히 리턴타입만 List 대신 Cursor로 수정해 주면 Cursor 방식 조회로 바뀐다.

SQL 실행로그를 남기기 위해서 log4jdbc를 사용하는 경우, lazeluke의 라이브러리를 사용하면 Cursor 방식으로 조회하다가 에러가 발생하는 것 같다. 그래서, org.bgee.log4jdbc-log4j2의 라이브러리로 바꾸니 오류 없이 잘 작동했다.

Service

    @Transactional
    public File getUsersMapper() {
        Cursor<HashMap<String, Object>> users = userMapper.selectUsersCursor();
        
        File tempFile = writeToTempFile(users);
        
        return tempFile;
    }

    @Transactional
    public File getUsersDao() {
        Cursor<HashMap<String, Object>> users = userDao.getUsersCursor();
        
        File tempFile = writeToTempFile(users);
        
        return tempFile;
    }

    private File writeToTempFile(Cursor<HashMap<String, Object>> users) {
        String fileName = String.format("%s/%s.%s", 
                    tempDir, 
                    UUID.randomUUID().toString().substring(0, 13), 
                    System.currentTimeMillis());
        File file = new File(fileName);
        
        BufferedWriter bw = null;
    
        try {
            bw = new BufferedWriter(new FileWriter(file));
            
            int cnt = 0;
            for (HashMap<String, Object> user : users) {
                bw.append(user.toString()).append("\n");
            }
            
            bw.flush();
        } catch (Throwable t) {
            System.out.println("failed to write temp file");
            t.printStackTrace();
        } finally {
            if (bw != null) {
                try {
                    bw.close();
                } catch (Exception e) {}
            }
        }
        
        return file;
    }

Dao와 Mapper로부터 Cursor를 받아서 루프 돌면서 읽은 데이터를 임시파일에 쓰고, 파일 객체를 Controller에 리턴한다.
중요한 것은 메소드에 @Transactional을 붙이는 것이다. 이것이 없으면 Cursor가 바로 닫혀서 데이터를 읽을 수 없다.
임시파일의 확장자는 생성시간으로 했다. 이유는 뒤에 Scheduled Job 부분에 설명되어 있다.

Controller

    @GetMapping("/usersMapper")
    public void getUsers(HttpServletResponse response) {
        File file = null;
        FileInputStream fis = null;
        OutputStream out = null;
        
        try {
            file = userService.getUsersMapper();
            
            response.setHeader("Content-Disposition", "attachment;filename=userlist.txt");
            
            fis = new FileInputStream(file);
            out = response.getOutputStream();
            
            int read = 0;
            byte[] buffer = new byte[1024];
            while ((read = fis.read(buffer)) != -1) {
                out.write(buffer, 0, read);
            }

            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (Exception e) {}
            }
            
            if (file != null && file.exists()) {
                file.delete();
            }
        }
    }

Service로부터 임시파일의 File 객체를 리턴받은 후에 이 파일의 내용을 읽어 클라이언트로 보내 주고 임시파일을 삭제(delete)한다. Service의 selectUsersDao 메소드를 호출하는 코드도 있지만, 그외 내용이 모두 같아서 여기서는 생략했다.

 

Scheduled Job

    @Scheduled(fixedDelay = 10000)
    public void deleteTempFile() {
        System.out.println("scheduled job run");
        
        File tempDirFile = new File(tempDir);
        
        if (tempDirFile == null || !tempDirFile.exists() || !tempDirFile.isDirectory()) {
            System.out.println("Cannot read temp directory : " + tempDir);
            return;
        }
        
        File[] files = tempDirFile.listFiles();
        if (files != null && files.length > 0) {
            for (File tempFile : files) {
                String filename = tempFile.getName();
                if (filename.indexOf(".") > 0) {
                    String createTimeStr = filename.substring(filename.indexOf(".")+1).trim();
                    
                    try {
                        long createTime = Long.parseLong(createTimeStr);
                        
                        if (System.currentTimeMillis() > (createTime + 60 * 1000)) {
                            System.out.println("deleting file : " + filename);
                            boolean deleted = tempFile.delete();
                            if (deleted)
                                System.out.println("deleted file : " + filename);
                        }
                    } catch (Exception e) {}
                }
            }
        }
    }

주기적으로 돌면서 임시 디렉토리에 있는 파일들의 확장자를 확인해서, 생성 후 일정시간 이상 지난 파일은 삭제한다.

 

샘플 프로젝트에는 Cursor를 사용하지 않고 List를 조회하는 코드도 포함되어 있으니 비교해 볼 수 있다. URI로 /users를 호출하면 List 방식으로 DB에서 조회한 데이터를 한꺼번에 힙메모리에 올려서 내려받는 방식이고, /usersDao나 /usersMapper는 Cursor 방식으로 조회해서 임시파일을 생성한 후 내려받는 방식이다. 이 방식이 속도도 훨씬 빠르다.

List 방식과 Cursor 방식을 비교하려면 같은 조건에서 Cursor 방식에서 OutOfMemoryError가 발생하지 않는데 List 방식에서 OutOfMemoryError가 발생하도록 해야 했다. 그래서, 서버를 기동할 때 VM arguments에 -Xmx64m 를 입력해 주었고 List 방식에선 쉽게 OutOfMemoryError를 발생시킬 수 있었다. DB테이블의 데이터도 많을수록 비교가 쉬워진다. Cursor 방식은 힙메모리를 짧게 사용하고 GC가 되는 것으로 보이고, 적은 힙메모리에서도 문제 없이 수행되었다.

아래는 위의 코드가 포함되고 테스트에 사용했던 이클립스 프로젝트

bulk-file-download.zip

0.06MB

Autowired나 Slf4j가 잘 작동되지 않아서 마음에 안 들지만, 찾아서 고치기가 귀찮아서 못 고쳤으니 이해해 주시길...