DataSource 예제2-커넥션 풀

이번에는 DataSource 를 통해 커넥션 풀을 사용해보자.

@Test
void dataSourceConnectionPool() throws SQLException, InterruptedException {
    //커넥션 풀링
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setJdbcUrl(URL);
    dataSource.setUsername(USERNAME);
    dataSource.setPassword(PASSWORD);
    dataSource.setMaximumPoolSize(10);
    dataSource.setPoolName("MyPool");

    userDataSource(dataSource);
    Thread.sleep(1000);

}

private void userDataSource(DataSource dataSource) throws SQLException {
    Connection con1 = dataSource.getConnection();
    Connection con2 = dataSource.getConnection();
    log.info("connection={}, class={}",con1, con1.getClass());
    log.info("connection={}, class={}",con2, con2.getClass());
}

• HikariCP 커넥션 풀을 사용한다. 
• HikariDataSource 는 DataSource 인터페이스를 구현하고 있다.
• 커넥션 풀 최대 사이즈를 10으로 지정하고, 풀의 이름을 MyPool 이라고 지정했다.
• 커넥션 풀에서 커넥션을 생성하는 작업은 애플리케이션 실행 속도에 영향을 주지 않기 위해
  별도의 쓰레드에서 작동한다.
•  별도의 쓰레드에서 동작하기 때문에 테스트가 먼저 종료되어 버린다.
•  예제처럼 Thread.sleep 을 통해 대기 시간을 주어야 쓰레드 풀에 커넥션이 생성되는 로그를 확인할 수 있다.

 

실행 로그

00:58:52.602 [main] DEBUG com.zaxxer.hikari.HikariConfig - MyPool - configuration:
00:58:52.618 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension................................false
00:58:52.618 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit................................true
00:58:52.618 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog................................none
00:58:52.618 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql................................none
00:58:52.618 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery................................none
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout................................30000
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource................................none
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName................................none
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI................................none
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties................................{password=<masked>}
00:58:52.619 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................................none
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - exceptionOverrideClassName................................none
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties................................{}
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry................................none
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout................................600000
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout................................1
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries................................false
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl................................jdbc:h2:tcp://localhost/~/dbtest
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - keepaliveTime................................0
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold................................0
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime................................1800000
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize................................10
00:58:52.620 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry................................none
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory................................none
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle................................10
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - password................................<masked>
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName................................"MyPool"
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly................................false
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans................................false
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor................................none
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema................................none
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory................................internal
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation................................default
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - username................................"sa"
00:58:52.621 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout................................5000
00:58:52.621 [main] INFO com.zaxxer.hikari.HikariDataSource - MyPool - Starting...
00:58:52.653 [main] DEBUG com.zaxxer.hikari.util.DriverDataSource - Loaded driver with class name org.h2.Driver for jdbcUrl=jdbc:h2:tcp://localhost/~/dbtest
00:58:52.837 [main] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn0: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:52.841 [main] INFO com.zaxxer.hikari.HikariDataSource - MyPool - Start completed.
00:58:52.860 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn1: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:52.860 [main] INFO hello.jdbc.connection.ConnectionTest - connection=HikariProxyConnection@375466577 wrapping conn0: url=jdbc:h2:tcp://localhost/~/dbtest user=SA, class=class com.zaxxer.hikari.pool.HikariProxyConnection
00:58:52.861 [main] INFO hello.jdbc.connection.ConnectionTest - connection=HikariProxyConnection@1423983012 wrapping conn1: url=jdbc:h2:tcp://localhost/~/dbtest user=SA, class=class com.zaxxer.hikari.pool.HikariProxyConnection
00:58:52.947 [MyPool housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Pool stats (total=2, active=2, idle=0, waiting=0)
00:58:52.960 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn2: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:52.971 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn3: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:52.987 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn4: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:52.997 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn5: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:53.003 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn6: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:53.016 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn7: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:53.022 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn8: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:53.034 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - Added connection conn9: url=jdbc:h2:tcp://localhost/~/dbtest user=SA
00:58:53.034 [MyPool connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - MyPool - After adding stats (total=10, active=2, idle=8, waiting=0)

HikariConfig

• HikariCP 관련 설정을 확인할 수 있다. 풀의 이름( MyPool )과 최대 풀 수( 10 )을 확인할 수 있다.

 

MyPool connection adder

• 별도의 쓰레드 사용해서 커넥션 풀에 커넥션을 채우고 있는 것을 확인할 수 있다. 
• 이 쓰레드는 커넥션 풀에 커넥션을 최대 풀 수( 10 )까지 채운다.
• 그렇다면 왜 별도의 쓰레드를 사용해서 커넥션 풀에 커넥션을 채우는 것일까?
• 커넥션 풀에 커넥션을 채우는 것은 상대적으로 오래 걸리는 일이다. 
• 애플리케이션을 실행할 때 커넥션 풀을 채울 때 까지 마냥 대기하고 있다면 애플리케이션 실행 시간이 늦어진다. 
• 따라서 이렇게 별도의 쓰레드를 사용해서 커넥션 풀을 채워야 애플리케이션 실행 시간에 영향을 주지 않는다.

 

커넥션 풀에서 커넥션 획득

• 커넥션 풀에서 커넥션을 획득하고 그 결과를 출력했다. 여기서는 커넥션 풀에서 커넥션을 2개 획득하고
  반환하지는 않았다.
• 따라서 풀에 있는 10개의 커넥션 중에 2개를 가지고 있는 상태이다.
• 그래서 마지막 로그를 보면 사용중인 커넥션 active=2 , 풀에서 대기 상태인 커넥션 idle=8 을 확인할 수 있다.
  MyPool - After adding stats (total=10, active=2, idle=8, waiting=0)

 

참고
 HikariCP 커넥션 풀에 대한 더 자세한 내용은 다음 공식 사이트를 참고
https://github.com/brettwooldridge/HikariCP

 

Connection pool에 있는 수 보다 많은 connection을 요청하면??

@Test
void dataSourceConnectionPool() throws SQLException, InterruptedException {
    //커넥션 풀링
    HikariDataSource dataSource = new HikariDataSource();
    dataSource.setJdbcUrl(URL);
    dataSource.setUsername(USERNAME);
    dataSource.setPassword(PASSWORD);
    dataSource.setMaximumPoolSize(10);
    dataSource.setPoolName("MyPool");

    userDataSource(dataSource);
    Thread.sleep(1000);

}

private void userDataSource(DataSource dataSource) throws SQLException {
    Connection con1 = dataSource.getConnection();
    Connection con2 = dataSource.getConnection();
    Connection con3 = dataSource.getConnection();
    Connection con4 = dataSource.getConnection();
    Connection con5 = dataSource.getConnection();
    Connection con6 = dataSource.getConnection();
    Connection con7 = dataSource.getConnection();
    Connection con8 = dataSource.getConnection();
    Connection con9 = dataSource.getConnection();
    Connection con10 = dataSource.getConnection();
    Connection con11 = dataSource.getConnection();
    log.info("connection={}, class={}",con1 , con1.getClass());
    log.info("connection={}, class={}",con2 , con2.getClass());
    log.info("connection={}, class={}",con3 , con3.getClass());
    log.info("connection={}, class={}",con4 , con4.getClass());
    log.info("connection={}, class={}",con5 , con5.getClass());
    log.info("connection={}, class={}",con6 , con6.getClass());
    log.info("connection={}, class={}",con7 , con7.getClass());
    log.info("connection={}, class={}",con8 , con8.getClass());
    log.info("connection={}, class={}",con9 , con9.getClass());
    log.info("connection={}, class={}",con10, con10.getClass());
    log.info("connection={}, class={}",con11, con11.getClass());
}

Pool 이 확보될 때까지 block되어 기다림. 3000ms 기다린 후 예외 발생. 조절하는 설정 있음.