c3p0 generating many connections

-1

Code that gets the connection

InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/oracledb");
            Connection connection = ds.getConnection();
            connection.setAutoCommit(false);
            return connection;

Web.xml Settings

<resource-ref>
    <res-ref-name>jdbc/oracledb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Jetty c3p0 configuration

<Configure id="wac" class="org.eclipse.jetty.webapp.WebAppContext">
<New id="myds" class="org.eclipse.jetty.plus.jndi.Resource">
    <Arg></Arg>
    <Arg>jdbc/oracledb</Arg>
    <Arg>

         <New class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
            <Set name="driverClass">oracle.jdbc.OracleDriver</Set> 
            <Set name="jdbcUrl">jdbc:oracle:thin:@SRVODA-SCAN.CAIRUNET.AD.BR:1521/WINTP</Set> 
            <Set name="user">x</Set> 
            <Set name="password">x</Set> 
            <Set name="maxPoolSize">20</Set>
            <Set name="minPoolSize">5</Set>
            <Set name="acquireIncrement">5</Set>
            <Set name="maxIdleTime">45</Set>
            <Set name="maxIdleTimeExcessConnections">30</Set>
            <Set name="preferredTestQuery">select sysdate from dual</Set>

        </New> 

    </Arg>
</New>

Closes the connection

public static boolean closeConnection(Connection connection) {

    if(connection != null) {
        try {
            if(!connection.isClosed()) {
                connection.close();
                return true;
            }else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new IllegalArgumentException(e.getMessage());
        }
    }else {
        return false;
    }
}

Versions:

<dependency>
        <groupId>com.mchange</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.5.2</version>
    </dependency>

    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>mchange-commons-java</artifactId>
        <version>0.2.7</version>
    </dependency>

Jetty is the 8x eclipse version

Now the problem, every time when you pick up a connection, the eclipse console log is registering a pool initialization, and this causes a slow connection to the database, a significant slowness (obs, the log is much larger than this, not to get too big, I just took a piece of the log)

INFORMAÇÕES: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2ukwbk9rlq3x1yrxl5ji|591f989e, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.OracleDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2ukwbk9rlq3x1yrxl5ji|591f989e, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:@SRVODA-SCAN.CAIRUNET.AD.BR:1521/WINTP, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 45, maxIdleTimeExcessConnections -> 30, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> select sysdate from dual, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
out 24, 2017 8:13:21 AM com.mchange.v2.resourcepool.BasicResourcePool 
ADVERTÊNCIA: Bad pool size config, start 3 < min 5. Using 5 as start.
out 24, 2017 8:13:22 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
INFORMAÇÕES: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2ukwbk9rlq3x1yrxl5ji|591f989e, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.OracleDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2ukwbk9rlq3x1yrxl5ji|591f989e, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:@SRVODA-SCAN.CAIRUNET.AD.BR:1521/WINTP, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 45, maxIdleTimeExcessConnections -> 30, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> select sysdate from dual, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
out 24, 2017 8:13:22 AM com.mchange.v2.resourcepool.BasicResourcePool 
ADVERTÊNCIA: Bad pool size config, start 3 < min 5. Using 5 as start.
out 24, 2017 8:13:22 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
INFORMAÇÕES: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2ukwbk9rlq3x1yrxl5ji|591f989e, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.OracleDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2ukwbk9rlq3x1yrxl5ji|591f989e, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:@SRVODA-SCAN.CAIRUNET.AD.BR:1521/WINTP, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 45, maxIdleTimeExcessConnections -> 30, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> select sysdate from dual, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
out 24, 2017 8:13:22 AM com.mchange.v2.resourcepool.BasicResourcePool 
ADVERTÊNCIA: Bad pool size config, start 3 < min 5. Using 5 as start.

La oracle, with a person using, and performing basic operation, has 1500 INACTIVE sessions that are not discarded

What can I be doing wrong here? Any help is welcome.

    
asked by anonymous 24.10.2017 / 14:31

1 answer

3

It seems like your configuration creates a DataSource c3p0 every time it starts, but it does not call close() on that same DataSource when it restarts. Over time, it will launch several% s of% s and "leak" them every time it reboots.

You need to configure Jetty (I'm not sure how to) to call DataSource on close() c3p0 each time the application quits and restarts.

Original answer (in English):

  

I'm sorry to answer in English, but I do not know Portuguese!

     

It looks like your configuration creates a c3p0 DataSource each time it starts, but does not call close () on the DataSource on hot redeploy. Over time, you start up lots of DataSources, and then "leak" them with each restart. You need to configure Jetty (I'm not sure how) to call close () on the c3p0 DataSource each time the application shuts down and restarts.

    
25.10.2017 / 10:26