Framework Cursor (cursor in MySql)

이전글에서 커서의 일반 지식과 오라클과 MSSQL의 커서 방식에 대해 얘기 했으니 3번째로 MySQL의 커서 방식을 이야기해보자. 사실 개인적으로 MySQL은 상대적으로 자주 사용하는 DB가 아니다. MySQL은 "거의" 무료지만 다른 2개의 디비에 비해 기능과 지원이 부족하고 다른 '완전한' 무료에 비해 그리 많이 좋지도 않는 어정쩡한 위치에 있기 때문이다.

거의 1년만이므로..
다시 기본조건을 상기해보자.

JDBC로 stmt.execQuery("select * from millonRow_tblc") ; 를 실행했을때..


Large ResultSet의 Handling에 관한 문제이다.


우리가 먼저 시작해야 할 점은 사용자는 과연 모든 결과가 필요할까? 라는 점이다.

물론 100만개의 Row가 모두 필요한 프로그램일수도 있다. 하지만 그 1%의 확률로 모든 Row가 필요한 프로그램이라고 하더라도 그 중에 다시 99% 이상은 DB에서 연산이 가능하다. DB는 다른 어떤 통계 프로그램보다 많은 연산자와 빠른 속력을 지원해주기 때문에 정말 화면에 모든 Rows를 뿌려야 하는 0.01%의 확률의 프로그램이 아니라면 연산을 DB에서 하는게 좋다.

어쨌거나 지금 그 0.01%의 확률의 프로그램을 만들어야 한다고 가정하자.
즉 당신은 이유야 어쨌거나 "select * from millonRow_tblc" 모든 결과 셋이 필요하다.


DB가 MySQL일때
pstmt = conn.prepareStatement("Select * ....", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rs = pstmt.execQuery() ;
를 실행해보자.

아마도 특별히 Java의 Heap 메모리를 늘리지 않았다면 Out of Memory 예외를 보게 될것이다.
단 한행도 rs.next()를 하지 않았음에도 불구하고 말이다.

한행이 약 100byte라고 가정했을때 0.1k * 1000000 = 100M로
Select의 모든 결과셋의 크기가 기본 Java Heap Memory의 64M를 초과하기 때문이다.


MySQL에서 - 비록 공식문서에는 나와 있지 않지만
당신이 특별한 다른 옵션을 사용하지 않았을때 Client Cursor Location에 Static Cursor Type을 사용한다.

즉 JDBC에서 위 예제를 실행했을때
해당 쿼리의 모든 결과셋을 Client(여기서는 Application Server가 Client)에 전송하고 Client에서는 그 결과셋을 모두 받아서 Client 메모리안에 Table 형의 결과셋을 만들어 채우는 과정을 한다. 보통의 다른 Set 알고리즘과 마찬가지로 기본 Set이 거의 채워지면 해당 Set의 기존 Size를 1.5배 늘리고 이 용량이 다시 채워지면 다시 1.5배를 늘리는 동작을 반복한다.
다시 말해서 위의 경우 DB에 던지는 결과셋을 받아서 계속 Set의 용량을 늘리다가 어느순간 한계 Heap Size에 도달하게 되는 것이다.

따라서 정말 millonRow_tblc의 Table의 모든 Row가 필요하다면 Heap Size를 늘려야 한다. 그러나 근본적인 해결책은 아니다. 왜냐하면 Heap의 Size를 10배로 늘렸다고 해도 10명이 동시에 해당 프로그램을 실행하면 역시 Out of Memory Exception을 보게 된다. 백만 Row를 모두 받아서 Clinet에 Set을 구축하기까지는 꽤 오랜(아마도 컴퓨터 성능에 따라 1 - 10분 사이) 시간이 걸리기 때문에 10명이라는 수치는 매우 자주 일어나는 경우가 될것이다. 물론 사용자가 단 1명이더라도 결과를 보기위해서는 1-10분을 기다려야 한다.



공식적인 해결방법은 아니지만 MySQL에서는 일종의 꽁수같은 방법이 있다. 이것은 일종의 버그이므로 앞으로도 계속 지원되는 방법이라고는 보장할수 없다.

pstmt = conn.prepareStatement("Select * ....", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(Integer.MIN_VALUE) ;
psmt.execQuery() ;

위와 같이 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY를 사용하고
FetchSize에 Integer.MIN_VALUE를 설정하면
MySQL은 Client에 더이상 Static Cursor를 사용하지 않는다. 위와 같이 작성할경우 MySQL은 FireHouse Cursor방식을 사용한다. FIreHouse Cursor는 앞에서 나왔던 MSSQL의 기본 cursor이다.

공식적인 cursor Type에서는 언급하지 않지만 인터넷 환경에서는 묵시적으로 자주 사용된다. FireHouse 커서 동작방식에 대해 말하기 전에 MYSQL의 JDBC는 Server Cursor Location을 지원하지 않는다. 물론 근본적으로 JDBC의 API에 Cursor Location을 설정할수 있는 메소드가 없기 때문이기도 하지만 그럼에도 MSSQL에서는 CursorType에 따라 묵시적으로 바뀌는 것과 달리 MySQL에서는 아예 Server Cursor Locatiion을 공식적으로 지원하지 않는다.

따라서 MSSQL의 FireHouse 커서와는 조금 다른 동작을 한다.

pstmt = conn.prepareStatement("Select * ....", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(Integer.MIN_VALUE) ;
rs = psmt.execQuery() ;
for( int i = 0 ; i < 10 ; i++) {
   out(rs.getString(1)) ;
}

와 같이 실행하였다면 반갑게도 바로 결과가 나올것이다. Connection Pool을 사용했다면 0.1초 이내에 말이다.

유레카~ 라고 소리지르기 전에..알아야 할 것이 있다.
FireHouse 커서방식은 Static Cursor Type과 달리 모든 결과셋을 Client에 구축하지 않고 Row Level 단위로 Access할수 있는 장점이 있긴 하지만 단점이 2가지가 있다.
첫번째는 rs는 더이상 previons()나 relative/absolute move를 지원하지 않는다. 이는 FireHouse의 동작방식을 생각해보면 당연한 것이다. FireHouse방식에서는 rs.next()를 호출하는 순간에 더 이상 이전 previous의 커서의 위치나 정보를 보관하지 않는다. next()를 호출할때마다 이전 record의 정보는 메모리에서 날림으로서 Out Of Memory를 피하는 방식이다. 그리고 당연히 rs.getRowCount() 같은 메소드도 사용할수 없다. 만약 RowCount를 알고 싶다면 while(rs.next()) i++ 와 같이 구할수 밖에 없다.

첫번째는 사실 그다지 큰 문제는 아니다. 보다 중요한 것은 두번째 단점이다.
두번째 단점은 MYSQL의 Client Location의 FireHouse Cursor는 멈추지 않는다는 것이다.
for( int i = 0 ; i < 10 ; i++) {
   out(rs.getString(1)) ;
}
와 같이 단지 10행만을 사용한다고 하더라도 MYSQL DB는 그걸 인지할수 없으며 여전히 남은 99만 9990개의 남은 row를 Client에 전송한다.

그래서
pstmt = conn.prepareStatement("Select * ....", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(Integer.MIN_VALUE) ;
rs = psmt.execQuery() ;
for( int i = 0 ; i < 10 ; i++) {
   out(rs.getString(1)) ;
}
까지 0.1초가 걸리더라도..

rs.close() ;
이 문장을 실행하는데 1분 - 10분 정도가 걸린다. FireHouse는 일종의 Data Stream이기 때문에 모든 결과셋을 받기 전까지는 rs를 close 할수 없다. 그렇다고 rs.close()를 하지 않으면 해당 Connection은 미아가 되어 얼마지나지 않아 Connection Pool Exception을 보게 된다.

가끔 특정 사이트를 보면 화면에 내용은 거의 다 뿌려졌는데 브라우저 하단의 Progress Bar는 여전히 진행중인 사이트를 본다면 반이상은 JSP로 위의 방식으로 코드를 작성했기 때문이다. response.flush()로 일단 화면에 보여지지만 JSP코드 하단의 rs.close()를 하는데 오래오래 걸리고 있기 때문이다.

MVC가 머고간에 일단 JSP에서 JDBC를 억세스 해서 close()를 하기전에 일단 Access한것만이라도 flush로 먼저 보낸다면 화면에는 빨리나온것처럼 보이기에 일단 급한 마음에 그렇게 한것이다.

Lazy Initialize 패턴같은 사용방식처럼 보일수도 있지만 이런다고 문제는 해결되지 않는다. 일단 Client에서 빨리 보여지느냐 늦게 보여지느냐에 상관없이 FireHouse Cursor Mode에서 Server인 DB는 여전히 남은 Row 들을 보내고 있다. 그 말은 귀중한 자원인 DB는 Network Channel에 사용하지 않을 Row를 쓰고 있고 여전히 Application Servers는 사용하지 않을 Row을 받고 있다는 얘기이다.

최근의 대부분의 하드는 100Mb read/sec 이상의 속도를 가진다. 그럼에도 대부분의 Network Channel은 고작해야 10Mb / sec이기 때문에 필요도 없는 Row를 Network로 보내느라 DB는 계속 바쁘다.

처음에 언급한바와 같이 상황에 따라 DB에 따라 심지어는 버전에 따라 이와 같은 문제를 해결하는 방식은 다르기에 이전의 Mssql의 방식을 사용할수는 없다.(다시 말하지만 MySQL의 JDBC에서는 Server Cursor Location이 없다.) 해결방법은 다음글에서.. =ㅅ=