[유용한 Database Connection Pool 관련 java 코드 5-연재] database connection의 상태를 확인하는 기능

지금까지 올리 글에서 Database Connection, Pool, Manager에 대해서 설명을 하였습니다.
이번 글은 connection pool의 상태를 monitor하는 기능에 대해서 설명하겠습니다.

현재 연결된 database와 database connection의 상황 그리고 free인 connection의 수와 실행되고 있는 SQL의
상황을 볼 수 있는 방법이 있습니다.

실행중인 SQL을 확인하는 기능은 web application개발에 많은 도움을 줍니다.
현재 어떤 database에서 deadlock이 발생하여 어떤 SQL을 실행할때 시스템에 문제가 발생하는지를
빨리 알 수 있습니다.
실제로 필자는 이와 같은 실행중인 SQL monitor 기능을 사용하여 시스템의
문제점을 빨리 파악 할 수 있었습니다.

프로젝트를 하면서 이런 적이 있었습니다.
이미 개발된 application을 upgrade하는 일을 한적이 있습니다.
JSP page 숫자만해도 1500이었고, java까지 합하면 1600정도였습니다.
이루 말할 수 없이 많았죠, 각 page의 기능이 뭔지 파악하기도 쉽지 않았습니다.
그런데 upgrade라는 것이 기존의 application에서 문제가 되는 부분이 있는데
그것을 파악할 수가 없다는 거였습니다.
우선 1600의 소스코드에서 database 연결 부분을 WDL(web development library)로 교체를 하였습니다.
물론 모두 수작업으로 교체한것은 아닙니다. 코드변환프로그램을 개발해서 일부는 교체를 하고 일부는
수작업으로 교체를 하였습니다.

Connection oConn = ConnectionManager.getConnection("databasename");
->
WCDBConnection oDBConn = WCDBConnectionManager.getInstance().getConnection("databasename");
oDBConn.setSql(sSql);
Connection oConn = oDBConn.getConnection();

이런식으로 교체를 했습니다. 그리고 실행되는 SQL을 realtime으로 모니터 했습니다.
문제점이 무엇인지 바로 파악이 되었습니다.
WCDBConnectionManager를 개발한 가장 큰 이유도 이와 같이 application에서 실행하는 SQL을 모니터 하는
기능이었습니다. SQL을 Monitor하는 기능이 정상적으로 되기 위해서는
Database Connection 만 Monitor해서 되는 것은 아니고
SQL 실행하는 곳에서도 현재의 connection으로 실행하는 SQL이 무엇인지를 지정해야 합니다.
위의 코드에서 보듯이 database connection을 얻고 실행할 SQL을 setSql method를
호출하여 지정을 하였습니다. 이제 Connection에서 실행하는 SQL이 지정이 되었고
실행하는 SQL을 monitor하는 프로그램을 소개하겠습니다.

아래의 소스코드는 WCDBConnectionManager의 getConnectionState를 호출하여
connection의 상태를 얻어옵니다.
하나의 connection의 상태는 WCProperties에 저장됩니다.
(WCProperties는 Properties와 거의 동일한 기능이라고 보시면 됩니다.)
WCProperties에 저장된 connection pool의 정보를 출력하고,
WCProperties에 저장된 connections에서 Vector값을 얻어면 현재
설정된 Database Connection에 대한 정보가 여기 저장됩니다.
이를 다시 출력하면 현재 특정 database 에 연결된 Connection들에 대한 실행중인 SQL을 확인할 수 있습니다.

-- connection의 상태를 monitor하는 database_connection_state_monitor.jsp 시작
<%@ page language="java" import="wdl.*,wdl.board.*,wdl.board.*,java.util.*,java.sql.*,java.lang.*,
java.io.*,java.io.File " contentType="text/html; charset=EUC-KR"%><%
WCPage oPage = new WCPage();
if (oPage.initCtrl(pageContext) > 0)
{
  return;
}
oPage.printCtrl();
%>
<head>
<link rel="stylesheet" href="/wdl/css/std.css" type="text/css">
<link rel="stylesheet" href="/wdl/doc/style/std.css" type="text/css">
</head>
<%
Vector vState = WCDBConnectionManager.getInstance().getConnectionState();
StringBuffer sbBuf = new StringBuffer();
int nSize = WCVector.size(vState);
for (int i=0;i<nSize;i++)
{
  WCProperties rRcd = WCVector.getPropertiesAt(vState,i);
  if (rRcd == null)
  {
   continue;
  }
  String sDsn = rRcd.getStrValue("dsn");
  String sUrl = rRcd.getStrValue("url");
  String sDrivers = rRcd.getStrValue("drivers");
  int nConnectionCount = rRcd.getIntValue("connectionCount");
  int nFreeConnectionCount = rRcd.getIntValue("freeConnectionCount");
  int nMaxConnectionCount = rRcd.getIntValue("maxConnectionCount");
  // oPage.debugPrint(rRcd);

  String sBgColor = "";
  if (i%2 == 0)
  {
   sBgColor = "#fafa99";
  }
  sbBuf.append("<table border=0 cellpadding=0 cellspacing=0 style=\"padding:7,5,7,5;\" bgcolor=\""+sBgColor+"\">\n");
  sbBuf.append("<tr>\n");
  sbBuf.append("<td width=591 align=left>");
  sbBuf.append("dsn="+sDsn+",url="+sUrl+",drivers="+sDrivers+"<br>\n");
  sbBuf.append("connectionCount="+nConnectionCount+"<br>\n");
  sbBuf.append("freeConnectionCount="+nFreeConnectionCount+"<br>\n");
  sbBuf.append("maxConnectionCount="+nMaxConnectionCount+"<br>\n");
  sbBuf.append("</td>\n");
  sbBuf.append("</tr>\n");
  sbBuf.append("</table>\n");

  Vector vConn = rRcd.getVecAt("connections");
  if (vConn != null)
  {
   int nConn = WCVector.size(vConn);
   for (int j=0;j<nConn;j++)
   {
    WCProperties rConn = WCVector.getPropertiesAt(vConn,j);
    if (rConn == null)
    {
     continue;
    }
    String sID = rConn.getStrValue("id");
    String sState = rConn.getStrValue("state");
    String sSql = rConn.getStrValue("sql");
    sbBuf.append("<table border=0 cellpadding=0 cellspacing=0 style=\"padding:7,5,7,5;\" bgcolor=\""+sBgColor+"\">\n");
    sbBuf.append("<tr>\n");
    sbBuf.append("<td width=591 align=left><div style=\"padding:0,0,10,30;\">");
    sbBuf.append("id="+sID+"<br>\n");
    sbBuf.append("state="+sState+"<br>\n");
    sbBuf.append("sql="+sSql+"<br>\n");
    sbBuf.append("</div></td>\n");
    sbBuf.append("</tr>\n");
    sbBuf.append("</table>\n");
    // oPage.debugPrint(rConn);
   }
  }
}
oPage.printOut(sbBuf.toString());
%>
-- connection의 상태를 monitor하는 database_connection_state_monitor.jsp 끝

아래의 소스는 이전 글에서 설명드린 WCDBConnectionManager.java 일부분을 추출한 것입니다.
database state에 대한 정보를 모아서 Vector로 저장하여 반환하는 code입니다.
getPools()를 호출하면 connection pool을 저장하고 있는 Hashtable을 반환합니다.
Hashtable의 element는 WCDBConnectionPool이 되고
여기에 database 관련 정보가 저장되어 있습니다
WCDBConnectionPool.getConnections()를 호출하면 해당 database을 연결하고 있는
WCDBConnection object들을 저장하고 있는 Vector를 반환하고
Vector의 element는 WCDBConnection입니다.
WCDBConnection object에 저장되어 있는 현재 Connection의 상태 즉 free인지 running인지를
결정합니다. 현재 실행중이거나 혹은 과거에 실행했던 SQL을 "sql" key값에 저장을 합니다.

아래의 소스가 완벽히 돌기 위해서는 WCDBConnection,WCDBConnectionManager,WCStmt,WCProperties
,WCVector,WCLog,WCSystem 등의 source코드가 필요합니다.
이러한 소스코드도 모두 차차 글을 올리며 설명하겠습니다.
미리 보시려면 Web Development Library(www.webdevlib.net)에서 download 받아 사용하실 수 있습니다.

-- WCDBConnectionManager 에서 connection의 상태를 return하는 함수 시작
public class WCDBConnectionManager
{
  ...
  public synchronized Vector getConnectionState()
  {
   try
   {
    Vector vRet = new Vector();
    Hashtable htPools = getPools();
    if (htPools == null)
    {
     WCLog.getInstance().printLog("at WCDBConnectionManager.getConnectionState1 htPools is null");
     return null;
    }
    Enumeration enumPools = htPools.elements();
    if (enumPools == null)
    {
     WCLog.getInstance().printLog("at WCDBConnectionManager.getConnectionState1 enumPools is null");
     return null;
    }
    while (enumPools.hasMoreElements())
    {
     WCDBConnectionPool oPool = (WCDBConnectionPool) enumPools.nextElement();
     if (oPool != null)
     {
      WCProperties rRcd = new WCProperties();
      String sDriverName = oPool.getDriverName();
      String sUrl = oPool.getUrl();
      String sUser = oPool.getUser();
      String sPassword = oPool.getPassword();
      String sDsn = oPool.getName();
      rRcd.setValue("dsn",sDsn);
      rRcd.setValue("url",sUrl);
      rRcd.setValue("drivers",sDriverName);
      rRcd.setValue("connectionCount",oPool.getConnectionCount());
      rRcd.setValue("freeConnectionCount",oPool.getFreeConnectionCount());
      rRcd.setValue("maxConnectionCount",oPool.getMaxConn());
      Vector vConnections = new Vector();
      Enumeration enumConnections = oPool.getConnections().elements();
      while (enumConnections.hasMoreElements())
      {
       WCDBConnection con = (WCDBConnection) enumConnections.nextElement();
       try
       {
        if (con != null)
        {
         String sState = "free";
         if (con.isFree() == false)
         {
          sState = "running";
         }
         WCProperties rConn = new WCProperties();
         rConn.setValue("id",con.m_nID);
         rConn.setValue("state",sState);
         rConn.setValue("sql",con.getSql());
         vConnections.addElement(rConn);
        }
       }
       catch (Exception ex)
       {
        WCLog.getInstance().printLog("exception at WCDBConnectionManager.getConnectionState1 "+ex.toString());
       }
      }
      rRcd.setValue("connections",vConnections);
      vRet.addElement(rRcd);
     }
    }
    return vRet;
   }
   catch (Exception ex)
   {
    WCLog.getInstance().printLog("exception at WCDBConnectionManager.getConnectionState2 "+ex.toString());
   }
   return null;
  }
  ...
}
-- WCDBConnectionManager 에서 connection의 상태를 return하는 함수 시작

첨부파일 : database_connection_state_monitor.zip

database_connection_state_monitor.jsp
WCDBConnection.java
WCDBConnectionManager.java
WCDBConnectionPool.java

출처 : 고급 웹 UI 개발 라이브러리 Web Development Library 소스공개 : http://www.webdevlib.net