All about JDBC Performance Tuning 엑셈컨설팅본부 /APM 팀임대호 1 개요 JDBC 란 Java Database Connectivity 의약어이며, 데이터베이스표준접근 API(Application Programing Interface) 를말한다. JDBC 를사용하면어떤관계형데이터베이스에서도, 각데이터베이스에맞는접근프로그램을따로생성할필요없이사용할수있다. 이 JDBC 는 WAS (Web Application Server) 와 DB Server (Database Server) 사이에서의연결을담당하는중요한역할을수행하고있다. WAS 에서 SQL 을제대로보내고 DB 에서수행결과를빠르게리턴한다고해도, JDBC 에문제가있다고하면성능문제를야기할수있다. 앞으로 JDBC 로인해발생할수있는성능문제가무엇인지살펴보고좀더나은 Performance 를내기위한방법이어떤것이있는지알아볼것이다. 2 JDBC 구조 JDBC 는크게 JDBC API, JDBC Manager, JDBC Driver API 로구성되어있다. 2-1 JDBC 역할 우선간단히 JDBC 의역할에대해서알아보자. Application JDBC Interface JDBC Driver Database [ 그림 1] JDBC 역할 Part 2 APM 335
Application 에서 Parameter 값을받아 JDBC Interface 로보내게되면적절한 JDBC Driver 를통해 SQL 을 Database 로보내주게되고결과를받아서사용자에게보여주게된다. 2-2 JDBC Interface JDBC 의핵심 Interface 는 Connection, Statement, ResultSet 인데간단히살펴보도록하자. Connection Interface 데이터베이스에접근하기위한객체를생성한다. Statement Interface SQL Query를실행하고그것에대한결과값을가져온다. ResultSet Interface 테이블에접근해서값을추출하는기능을한다. [ 그림 2] JDBC 핵심 Interface JDBC Interface 를사용하려면 java.sql, javax.sql Package 를사용해야하는데 java.sql Package 은 DBMS 에저장된정보를다루기위해사용되고, javax.sql Package 는 Datasource 로접근하기위해사용된다. 핵심인터페이스인 Connection, Statement, ResultSet 을사용하려면 java.sql Package 를 Import 해주면된다. 2-3 JDBC Interface의사용핵심 Interface 가어떤일련의과정으로사용되는지살펴보도록하자. Driver Manager 를사용할때를예로들겠다. 일단 JDBC Driver Manager 를통해서적합한 Driver 를로딩하게된다.(JDBC Driver 는 JDBC Call 을 DBMS 에전달하고 Result 를받아서 JDBC Interface 에넘겨주는역할을한다 ) Driver 가로딩되면 Connection 객체를생성한다. 그런다음획득한 Connection 객체로부터 Statement 객체를획득하고 SQL 을수행한다. Select 의경우는 Resultset 이있으므로결과를가져오기위해 ResultSet 객체를생성한다. [code 1] 336 2013 기술백서 White Paper
1 import java.io.*; 2 import java.sql.*; 3 public class DBConnection { 4 public static void main(string [] args) throws SQLException, ClassNotFoundException{ 5 Class.forName("oracle.jdbc.driver.OracleDriver"); 6 Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.123.52:1521:intermax","dh","dh"); 7 Statement stmt = con.createstatement(); 8 ResultSet rs = stmt.executequery("select * from emp"); 9 while(rs.next()){ System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)); } 10 rs.close(); 11 stmt.close(); 12 con.close(); } } 앞으로사용될 Driver 는 Oracle 을기준으로하겠다. 위의코드를간단하게살펴보자 5 번째줄을보면 Oracle 의경우 Driver 클래스명이 oracle.jdbc.driver.oracledriver 인것을알수있다. 6 번째줄은 Driver 가로딩이되면 Driver Manager 클래스의 getconnection(url,user,password) Method 를이용해서 Connection 객체를생성하는것을알수있다. Oracle 의경우 Url 은 jdbc:oracle:thin:@ip:port:sid 형식이다. 8 번째줄을보면 Select 를사용했으므로 ResultSet Interface 가사용되었다. 10~12 번째줄 을보면 close() 처리를해줬다. 이는모든 java.sql 관련된 Resource 는사용하면반드시반납 을해줘야하기때문이다. Part 2 APM 337
만약반납하지않는다면모든 Connection 을다사용하게되어서 Thread pool 이밀리는현상 이발생하고최악의경우는 WAS Crash 현상이나타날수있다. 지금까지간단히 JDBC 의구조 를알아보았다. 이제 JDBC 로인해발생할수있는성능문제에대해서살펴보도록하자. 3 JDBC 성능문제 JDBC 로인해다양한성능문제가발생할수있다어떤성능문제발생하는지살펴보도록하자. 3-1 JDBC Driver Type JDBC Driver 는 4 가지의종류가있다. Type1 Type2 Type3 Type4 JDBC-ODBC Bridge Driver Native-API Driver Net-Protocol Driver Native-Protocol Driver [ 그림 3] JDBC Driver Type 간단히각 Driver 에대해서살펴보자. Type1 인 JDBC-ODBC Bridge Driver 는 JDBC 를사용해서, ODBC 데이터소스에접근하는방식으로제한이많고비효율적이다. Type2 인 Native API Driver 는 Java + JNI 방식으로 Native Call 하는부분이추가된다. Type3 인 Net-Protocol Driver 는 Net protocol 을사용하여 Middleware 계층서버와통신하는방식이다. Type4 인 Native-Protocol Driver 는 Pure java 방식이고 TCP/IP 로 Socket 을이용하는방식이다 Type1 방식은플랫폼에종속적이고 JDBC 의요청을 ODBC 로, ODBC 에서의응답을 JDBC 로변경해주는과정때문에불필요한 Overhead 가발생하게된다. Type2 방식은 Native Code 로되어있는 Database Library 를설치해줘야한다. Type3 방식은 Application 과 DBMS 사이에 Middleware 를구현하고 JDBC Driver 는 Middleware 에요청하는방식인데 Middle Tier 가추가되기때문에 Bottleneck 현상의원인이될수있다. Type4 방식은 Socket 통신을하기때문에 Socket 통신에소비되는시간을고려해야만한다. 이와같이 4 가지의 Driver Type 이있다. 338 2013 기술백서 White Paper
3-2 DriverManager Connection 객체를생성하고제거하는작업은상당히고비용의작업이다. 달리말하자면 DriverManager 의 getconnection() Method 를이용해서 Connection 객체를 얻어내는것이고비용의작업이란이야기이다. Connection 이비록 Idle 한상태에있는다하더라도생성및제거작업을하는것보다차라리 Open 해놓은상태로있는것이훨씬유리하다. Mark Chamness 의 Performance Tuning For the JDBC API 문서에따르면 Connection 을생성하고제거하는작업이평균 0.5 초에서 2 초사이가걸린다고한다만약반복적인 Open 과 Close 가발생한다면 Application 성능저하가나타날것이다. Connection 관리를효율적으로하기위해서는 Connection Pool 을사용하면된다.. 3-3 Statement Statement 객체는주로간단한 SQL 을전송할때쓰인다. 하지만 Statement 객체는다음과 같은데이터처리의한계를가진다. 1. 반복되는똑같은질의도새로운질의로해석된다. 2. 질의조건이달라질때마다하드코딩해야한다. 이와같이 Statement 가갖는한계점은 Prepared Statement 를사용하면해결할수있다. 3-4 Transaction Isolation Level Transaction Isolation Level 은다음과같이 4 가지로나뉘게된다. ISOLATION LEVEL DESCRIPTION TRANSACTION_READ_UNCO 트랜잭션에서처리중인, 아직 commit 되지않은데이터를다른 MMITE 트랜잭션이읽는것을허용한다 TRANSACTION_READ_COMM 트랜잭션이 commit 되어확정된데이터만읽는것을허용한다 Part 2 APM 339
IT TRANSACTION_REPEATABL 선행트랜잭션이읽은데이터는트랜잭션이종료될때까지후행 E_READ 트랜잭션이갱신하거나삭제하는것을불허한다 TRANSACTION_SERIALIZABL 선행트랜잭션이읽은데이터를후행트랜잭션이갱신하거나 E 삭제하지못할뿐만아니라중간에새로운레코드를삽입하는것도막아준다 [ 그림 4] Transaction Isolation Level 트랜잭션고립화의수준을높일수록데이터의일관성은향상되지만, 확장된 Lock 을사용하게 되므로동시성이저하가된다. ( 단 Oracle 은 Lock 이아닌 Undo 메카니즘을사용한다 ) 3-5 SQL SQL 은 WAS 와 DB 성능에직결된다하드웨어의 Spec 을아무리뛰어나게구성한다고해도업무성격을잘못이해하고 SQL 을작성하거나 (Outer Join) 비효율적으로작성하게 (Subquery 남용 ) 되면성능저하및장애를유발할수있다. 또한과도한 Fetch Count 는 Out Of Memory Error 를유발하고 WAS Crash 를유발하기도한다. 4 JDBC 성능문제해결 이전에살펴보았듯이 JDBC 성능문제는여러원인과연관되어있다. 이제 JDBC 성능문제를해 결하는방법에대해서알아보도록하자. C c Connection Pool c c saddsda Application DAO [ 그림 5] Connection Pool 340 2013 기술백서 White Paper
4-1 Connection Pool 사용 Connection Pool 을사용하게되면 Connection 객체를생성하고제거하는작업을하지않아도되기때문에 Overhead 가적고응답속도가빠르게된다. 또한 Connection 의수의제한을두기때문에연결관리에효율적이다. 그리고 JNDI Look Up 을하여접근할수있으므로특정한드라이버나속성에구애받지않고참조가가능하다. Connection Pool 을사용할때주의해야할사항이있는데그것은적절한 Pool Size 에대해고려해야만한다는것이다. 이는 Pool Size 와 Performance 가 Trade Off 의관계에있기때문이다. 적절한 Pool Size 설정을위해서는실제 Connection 사용량및 OS Resource 사용량을체크하고부하 Test 를거쳐야한다. Connection Pool 의동작방식은다음과같다. 만약 WAS 에서제공하는 Connection Pool 을사용하게된다면 Minimum 과 Maximum Size 를설정할수있다. 최초 Pool 을생성시 Connection 을 Minimum Size 만큼생성하게되고추가로요청이발생하면 Maximum Size 까지생성한다. 그리고사용이없게되면다시 Minimum Size 까지작아지게된다. 만약 Connection 생성 Delay 로인한이슈가있다면 Minimum 과 Maximum Size 를같게설정하도록한다. 주의해야할점은 Connection 객체를 close() 해주지않으면다른사용자가사용할수없으므로 반드시 close() 처리를해주도록한다. 4-2 Transaction Isolation Level 사용 동시성제어가어려운이유는동시성과일관성이 Trade Off 의관계에있기때문이다. Transaction Isolation Level 에따라다음의문제점이발생할수있다. Part 2 APM 341
LEVEL READ_UNCOMMIT READ_COMMIT REPEATABLE_READ PROBLEM Dirty Read, Non-Repeatable, Phantom Read Non-Repeatable, Phantom Read Phantom Read SERIALIZABLE [ 그림 6] Transaction Isolation Level 에따른문제점 업무성격에비해 Transaction Isolation Level 을높게사용할경우불필요하게 Transaction 의 동시성을제한하게되고반대로낮게사용하게되면데이터의무결성이보장되지않을수있다. 그러므로업무성격에맞는 Transaction Isolation Level 을설정하는것이중요하다. 4-3 Prepared Statement, Callable Statement 사용 SQL 문장을반복적으로 DB 에전송할필요가있다면 Prepared Statement 객체를사용하도록 한다. Prepared Statement 장점은 두가지가있다첫째는반복적으로실행해야하는 SQL 을미리컴파일해서저장해놓음으로실 행속도를빠르게할수있다. 둘째는 Parameter 로값을넘길수있기때문에 Hard Coding 할 필요가없고쉽게인자를전달하여수행속도를향상시킬수있다. Callable Statement 는 DB 에서미리컴파일된 Stored Procedure 을실행하기위해사용한다. 앞서말한 Prepared Statement 와 Callable Statement 를사용하게되면 Statement Caching 기능을사용할수있다. 4-4 Statement Caching 사용 Prepared Statement 또는 Callable Statement 를 Application 이나 EJB 에서사용하게되면 WAS 와 DB 에상당한오버헤드가발생할수있다. JDBC 3.0 이상에서는 Statement Caching 기능을사용할수있는데, Statement Caching 이란 Prepared Statement 와 Callable 342 2013 기술백서 White Paper
Statement 를 Caching 하여재사용할수있는기능이다. 이는 DB 의 CPU 사용량을줄일수있 을뿐아니라성능향상을도모할수있다. 자료를찾다가 Weblogic 의 Statement Caching 에 관한자료가있어서옮겨적어본다. Weblogic 은 Statement Cache Algorithm 이두가지가존재하는데 LRU 방식과 Fixed 방식이 있다. LRU 방식 - 캐시가가득찰때까지 Prepared Statement 와 Callable Statement 를 Caching 한다. 모든 캐시가가득차게되면가장적게사용된 Statement 를밀어내고새로운 Statement 를저장한 다. Fixed 방식 -Statement 를캐시하고사이즈가가득차게되면더이상 Statement 를 Caching 하지않는다. Statement Cache 기능을사용하기전에각 Connection 마다의 Cache Size 를고려해야만한 다. 그이유는대부분의경우에있어서 Open Statement 하나당하나의커서를유지하기때문 이다. 만약많은 Statement 를 Caching 한다면 DB 의 Open Cursor 의제한을초과할수있다. 예를들어 Statement Cache Size 가 10(default) 이고 Connection 이 10 개그리고 2 개의 WAS 가 Clustering 된구조라면 DB 에서는캐시를위해 200 개 (10*10*2) 의 Cursor 가 Open 되게된다. Statement Cache Size 를 0 으로설정하게되면해당기능은꺼지게된다. Caching 된 Statement 는 DDL 이수행되고나면재수행에실패하게되는데, 이는 Caching 된 Statement 는명시한데이터베이스오브젝트를참조하기때문이다. 이는 Oracle 에서는 Breakable Parse Lock 과관계가있다. Cursor 가참조하는오브젝트에 DDL 이수행되면실행가능 LCO 가무효화된다. Part 2 APM 343
4-5 JDBC Driver의적절한사용 JDBC 드라이버 Type 에대한적적한이해가필요하고때에따라서는 Test 도필요하게될것이다. 그이유는 WAS 와 DB 의구성환경에따라 Type 의선택이달라질수있기때문이다. 예를들어 WAS 와 DB 가같은 Machine 에있는경우를생각해보자. Type4 의경우는 Socket 을이용한다고했는데불필요한 TCP/IP Socket 통신이발생하게된다. 만약이런시간으로인해데이터베이스에직접접근하는시간이오래걸린다고하면 Native Call 을하는 Type2 보다유리할게없다환경 ( 구성, JVM) 에따라서는 Type2 가 Type4 보다성능면에서유리할수있다. 최적의성능을위해서 DB Server 와 DB Client JDBC Driver 는동일 / 최신버전을사용하도록 한다. 4-6 SQL Tuning의최적화 SQL 을어떻게작성하느냐또는어떻게처리하느냐에따라서성능은극과극의상황을보여줄수있다. 이처럼 SQL 작성은 WAS 와 DB 성능에직결된다고볼수있는데, 개발자입장에서어떤 SQL Tuning 방법이있는지살펴보도록하자. 1. 한번에한행을처리하지않고다수행을처리하는 addbatch(), executebatch() 를사용 하도록한다. 2. 읽기전용혹은 Data Update 가일어나지않는질의는 caching 을고려한다. 3. Statement 보다는 Prepared Statement 를사용해라 4. 대량의 Data 조회시 Paging 처리를하도록한다. 5. 적절한예외처리를이용해반드시 Connection leak 을막아주도록한다. 6. 복잡한 SQL 질의문이나여러개의 DB 호출을하나로통합하는 Stored 프로시저를 사용하라. 7. 불필요한 Meta Data 호출은피한다. 8. Application 요구에맞는가장낮은수준의 Transaction 을사용한다. 9. Transaction 과부하가우려된다면 Auto commit 모드를사용하지않도록한다. 10. SELECT * FROM TABLE 과같은조회는피하고필요한칼럼만기술하도록한다. 344 2013 기술백서 White Paper
11. Result Set 을 Network 로넘어오기전에줄여서 Round Trip 을적게발생시키도록한 다. 4-7 Oracle JDBC Performance Extension Oracle JDBC Driver 는 Round Trip 을줄여성능을향상시키는다음의기능을지원한다 Row Prefetch - 접근가능성이높은블록을 Fetch 할때 Client Buffer 에저장하여불필요한 Round Trip 을줄 인다 Update Batching -Update, Delete, Insert 문을하나의 batch 로수행한다 -Update Batching 은 Prepared Statement 와함께사용시유용하다 - Oracle JDBC 의 Update Batching 방식에는 Standard Model 과 Oracle-Specific Model 이있지만두모델을함께사용할수는없다 5 결론 Framework 가개발의대부분을차지하고있는이시대 JDBC 의성능에대한고찰은시대에뒤떨어져보일수있다. 하지만 WAS 에서수행한 Application 이어떻게 DB 까지전달되어서결과를사용자에게보여주는지에대한궁금증은어느정도해소가되었으리라본다. 그역할의중심에는 JDBC 가있다. 우리는지금까지 JDBC 가어떤일을수행하고어떤구조로되어있는지또어떤문제를일으키는지에대해알아보았다. 어플리케이션성능관리가주업무인나에게 JDBC 의동작원리를이해하는것은 WAS DB 성능전문가가되기위한초석이될것이라고생각한다. JDBC 를사용할때가장큰성능개선을할수있는방법은 WAS 와 DB 사이에서의 Round Trip 을적게발생시키는것이다. Oracle Driver 는이미 Round Trip 을줄일수있는 Row Prefetch Part 2 APM 345
나 Update Batching 기능을제공하고있다. 하지만이런좋은기능이있음에도불구하고 SQL 의수행의결과가불필요하게많은 ResultSet 을가져온다면심각한성능문제를야기할수있다. 대표적으로과도한 Fetch Count 로인한 Out Of Memory Error 현상을말할수있다. 이를해결하기위해서는업무성격을잘파악하여 Network 를통해넘어오기전에 ResultSet 을줄여 Round Trip 을적게발생시켜야한다.. 앞서우리는 JDBC 의핵심 Interface 에대해서살펴보았다. 그렇다면 SQL 의 Parse 와 Execute 와 Fetch 는어느객체에서수행이될까? 답은 Java Performance Fundamental 에나오는데 Statement 객체의 executequery() Method 단계에서 Parse, Execute, 1 회의 Fetch 가수행된다고한다. 그리고 ResultSet 객체의 next() Method 에서나머지 Fetch 가수행된다. Cursor 가 Open 되는시점은 Parse 하는시점과동일하고 Close 되는시점은 Connection 객체가 Close 될때이다. 잘이해가되지않는다면 [code1] 을참조하라 지금까지 JDBC 에대해서정리를해보았다. APM(Application Performance Management) 을지원하지만큰관점에서보면 Database 역시 Application 의한부분이고대부분의성능이슈는 WAS 와 DB 상태를같이봐야명확히장애원인이밝혀지는경우가많다. Oracle 에대한내용을추가한것도그런맥락에서이다. 기초를단단히잡고싶은마음에 JDBC 를주제로잡았다. 뭐든지가장중요한것은기반을튼튼 히하는것이라고생각을한다. JDBC 지식을기반으로다음백서의주제는 Framework 가될 것같다 참고문헌 서적 김한도, [Java Performance Fundamental] Soluvill, [Enterprise Java JDBC] 문서자료 Mark Chamness, [Performance Tuning For the JDBC API] 346 2013 기술백서 White Paper
웹사이트 http://alumnus.caltech.edu/~chamness/jdbc_tuning.pdf http://etutorials.org/programming/java+performance+tuning/chapter+16.+tuning+j DBC/16.2+Tuning+JDBC/ http://docs.oracle.com/cd/e11035_01/wls100/jdbc/performance.html. Part 2 APM 347