Search This Blog

Thursday 23 August 2012

Pooled Data-sources and Spring -2

In the previous post we saw how Spring integrated with DBCP. There is another data-source c3p0 which can also be integrated via Spring.
The XML configuration for the data-source would be
<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <!-- Connection properties -->
        <property name="driverClass" value="org.gjt.mm.mysql.Driver" />
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/information_schema" />
        <property name="user" value="root" />
        <property name="password" value="root" />

        <!-- Pool properties -->
        <!-- 
            Determines how many connections at a time c3p0 will try to acquire when 
            the pool is exhausted default - 3
         -->
        <property name="acquireIncrement" value="3" />
        <!-- 
            Default: 30
            Defines how many times c3p0 will try to acquire a new Connection from the 
            database before giving up. If this value is less than or equal to zero, 
            c3p0 will keep trying to fetch a Connection indefinitely.
         -->
        <property name="acquireRetryAttempts" value="3" />
        <property name="acquireRetryDelay" value="4500" /> <!-- milliseconds.
        time c3p0 will wait between acquire attempts -->
        <!-- autoCommitOnClose Default: false
         -->
        <property name="autoCommitOnClose" value="false" />
         <!-- 
             Unreasonable values of initialPoolSize will be ignored, and minPoolSize 
             will be used instead. 
             minPoolSize <= maxPoolSize.
          -->        
        <property name="initialPoolSize" value="3" />
        <!-- Default: 15 Maximum number of Connections a pool will maintain at any given time. -->
        <property name="maxPoolSize" value="200" /> 
        <!--
        Default: 3 
        Minimum number of Connections a pool will maintain at any given time.
        -->        
        <property name="minPoolSize" value="10" /> 
        <!-- 
            Default: 0    
            Seconds a Connection can remain pooled but unused before being discarded. 
            Zero means idle connections never expire
         -->        
        <property name="maxIdleTime" value="3600" /> <!-- 1 hour -->
        
        <!-- 
        Default: 0
        Seconds, effectively a time to live. A Connection older than maxConnectionAge 
        will be destroyed and purged from the pool. This differs from maxIdleTime 
        in that it refers to absolute age. Even a Connection which has not been much 
        idle will be purged from the pool if it exceeds maxConnectionAge. 
        Zero means no maximum absolute age is enforced. 
         -->        
        <property name="maxConnectionAge" value="7200" /> <!-- 2hour -->
        <!-- 
            maxIdleTimeExcessConnections is about minimizing the number of 
        Connections held by c3p0 pools when the pool is not under load. By default, 
        c3p0 pools grow under load, but only shrink if Connections fail a Connection 
        test or are expired away via the parameters described above. Some users want 
        their pools to quickly release unnecessary Connections after a spike in usage 
        that forces a large pool size. You can achieve this by setting 
        maxIdleTimeExcessConnections to a value much shorter than maxIdleTime, forcing 
        Connections beyond your set minimum size to be released if they sit idle for 
        more than a short period of time.         
         -->
        <property name="maxIdleTimeExcessConnections" value="600" /> <!-- 10 minutes -->
        
        
        <!-- 
            The point of Connection pooling is to bear the cost of acquiring a Connection 
        only once, and then to reuse the Connection many, many times. Most databases 
        support Connections that remain open for hours at a time. There's no need to churn 
        through all your Connections every few seconds or minutes. Setting maxConnectionAge 
        or maxIdleTime to 1800 (30 minutes) is quite aggressive. For most databases, 
        several hours may be more appropriate. You can ensure the reliability of your 
        Connections by testing them, rather than by tossing them. 
        (see Configuring Connection Testing.) The only one of these parameters that 
        should generally be set to a few minutes or less is maxIdleTimeExcessConnections.         
         -->
        
            
        <!-- <property name="maxStatements" value="50" /> Use only if statement 
            caching is used -->
        <property name="preferredTestQuery" value="select 1 from DUAL" />
        <!--
            Most convenient way to speed up Connection testing is to define 
        the parameter automaticTestTable
            If provided, c3p0 will create an empty table of the specified name, 
        and use queries against that table to test the Connection. If 
        automaticTestTable is provided, c3p0 will generate its own test query, 
        therefore any preferredTestQuery set will be ignored. You should not work 
        with the named table after c3p0 creates it; it should be strictly for 
        c3p0's use in testing your Connection
        <property name="automaticTestTable" value ="connectionTestTable"/>
        -->
        
        <!-- 
            The most reliable time to test Connections is on check-out. But this 
        is also the most costly choice from a client-performance perspective. Most 
        applications should work quite reliably using a combination of 
        idleConnectionTestPeriod and testConnectionsOnCheckIn. Both the idle test 
        and the check-in test are performed asynchronously, which leads to 
        better performance, both perceived and actual.
        -->
         
        <!-- 
        idleConnectionTestPeriod Default: 0
            If this is a number greater than 0, c3p0 will test all idle, 
        pooled but unchecked-out connections, every this number of seconds. 
         -->
        <!-- Test the idle connections every 3 minutes (180)-->
        <property name="idleConnectionTestPeriod" value="180" />
        <!-- 
        testConnectionOnCheckin Default: false
            If true, an operation will be performed asynchronously at every 
        connection check-in to verify that the connection is valid. Use 
        in combination with idleConnectionTestPeriod for quite reliable, 
        always asynchronous Connection testing
         -->
        <property name="testConnectionOnCheckin" value="true" />
        
        <!-- 
        testConnectionOnCheckout Default: false
        Use only if necessary. Expensive. If true, an operation will be performed 
        at every connection checkout to verify that the connection is valid.
         -->
        <property name="testConnectionOnCheckout" value="false" />
        
        <!-- <property name="loginTimeout" value="30" /> -->
        
        <property name="connectionCustomizerClassName" value="com.test.ConnectionPoolLogger"/>
    </bean>
Just like DBCP most of the configurations are configurable. I picked up the above comments from the c3p0 documentation site.  Also to monitor the connection behavior, I added a connection pool monitor The class is capable of hooking into life cycle operations of the connection pool providing us details of the connection pool state.
public class ConnectionPoolLogger implements ConnectionCustomizer{

    private static final Logger logger = Logger
            .getLogger(ConnectionPoolLogger.class);
    private int activeConnections = 0;
    private int acquiredConnections = 0;

    public void onAcquire(Connection c, String pdsIdt) {
        logger.info("onAcquire: Connection acquired from database : " + c
                + " [" + pdsIdt + "]");
        acquiredConnections++;
        logger.info("onAcquire: Total Open Connections in Pool : " + acquiredConnections);
    }

    public void onDestroy(Connection c, String pdsIdt) {
        logger.info("onDestroy: Connection closed with database : " + c + " ["
                + pdsIdt + "]");
        acquiredConnections--;
        logger.info("onDestroy: Total Open Connections in Pool : " + acquiredConnections);

    }

    public void onCheckOut(Connection c, String pdsIdt) {
        logger.info("onCheckOut: Connection from pool provide to application : "
                + c + " [" + pdsIdt + "]");
        activeConnections++;
        logger.info("onCheckOut: Total Active Connections in Pool : " + activeConnections);
    }

    public void onCheckIn(Connection c, String pdsIdt) {
        logger.info("onCheckIn: Connection returned to pool from application : "
                + c + " [" + pdsIdt + "]");
        activeConnections--;
        logger.info("onCheckIn: Total Active Connections in Pool : " + activeConnections);

    }
}
I tested the bean using the below code:
public static void main(String[] args) throws SQLException {
    final BeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("spring-c3p0.xml"));
    DataSource dataSource = (DataSource) beanFactory.getBean("c3p0DataSource");
    System.out.println("The dataSource is created as " + dataSource);
}
The output indicates that the data-source was configured successfully.
0    [main] INFO  com.mchange.v2.log.MLog  - MLog clients using log4j logging.
609  [main] INFO  com.mchange.v2.c3p0.C3P0Registry  - Initializing c3p0-0.9.1.2 
[built 21-May-2007 15:04:56; debug? true; trace: 10]
672  [main] DEBUG com.mchange.v2.c3p0.management.DynamicPooledDataSourceManagerM
Bean  - MBean: com.mchange.v2.c3p0:type=PooledDataSource[1hge13e8l1o2l0jiffxtg8|
184ec44] registered.
1031 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - incremented p
ending_acquires: 1
1031 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - incremented p
ending_acquires: 2
...
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - incremented p
ending_acquires: 9
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - incremented p
ending_acquires: 10
1047 [main] DEBUG com.mchange.v2.resourcepool.BasicResourcePool  - com.mchange.v
2.resourcepool.BasicResourcePool@16dadf9 config: [start -> 3; min -> 10; max -> 
200; inc -> 3; num_acq_attempts -> 3; acq_attempt_delay -> 4500; check_idle_reso
urces_delay -> 180000; mox_resource_age -> 7200000; max_idle_time -> 3600000; ex
cess_max_idle_time -> 600000; destroy_unreturned_resc_time -> 0; expiration_enfo
rcement_delay -> 150000; break_on_acquisition_failure -> false; debug_store_chec
kout_exceptions -> false]
1047 [main] INFO  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource  - Initi
alizing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncremen
t -> 3, acquireRetryAttempts -> 3, acquireRetryDelay -> 4500, autoCommitOnClose 
-> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkou
tTimeout -> 0, connectionCustomizerClassName -> com.test.ConnectionPoolLogger, c
onnectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, da
taSourceName -> 1hge13e8l1o2l0jiffxtg8|184ec44, debugUnreturnedConnectionStackTr
aces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, fact
oryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityTo
ken -> 1hge13e8l1o2l0jiffxtg8|184ec44, idleConnectionTestPeriod -> 180, initialP
oolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/information_schema, maxAdministr
ativeTaskTime -> 0, maxConnectionAge -> 7200, maxIdleTime -> 3600, maxIdleTimeEx
cessConnections -> 600, maxPoolSize -> 200, maxStatements -> 0, maxStatementsPer
Connection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingChe
ckoutDefaultUser -> 0, preferredTestQuery -> select 1 from DUAL, properties -> {
user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> tr
ue, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTra
ditionalReflectiveProxies -> false ]
The dataSource is created as com.mchange.v2.c3p0.ComboPooledDataSource [ acquire
Increment -> 3, acquireRetryAttempts -> 3, acquireRetryDelay -> 4500, autoCommit
OnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false,
 checkoutTimeout -> 0, connectionCustomizerClassName -> com.test.ConnectionPoolL
ogger, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTe
ster, dataSourceName -> 1hge13e8l1o2l0jiffxtg8|184ec44, debugUnreturnedConnectio
nStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driv
er, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, id
entityToken -> 1hge13e8l1o2l0jiffxtg8|184ec44, idleConnectionTestPeriod -> 180, 
initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/information_schema, maxA
dministrativeTaskTime -> 0, maxConnectionAge -> 7200, maxIdleTime -> 3600, maxId
leTimeExcessConnections -> 600, maxPoolSize -> 200, maxStatements -> 0, maxState
mentsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwa
itingCheckoutDefaultUser -> 0, preferredTestQuery -> select 1 from DUAL, propert
ies -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheck
in -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0,
 usesTraditionalReflectiveProxies -> false ]

4 comments: