c3p0 generating many connections

Asked

Viewed 362 times

-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 configuration.xml

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

c3p0 configuration in Jetty

<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 picking up a connection, the eclipse console log is recording a pool startup, and this generates a slowness when connecting to the database, a significant slowness (Obs, the log is much bigger than this, not to get too big, I only 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 in oracle, with a person using, and performing basic operation, has 1500 INACTIVE sessoes that are not discarded

sessoes no BD

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

java jetty pooling c3p0

1 answer

3

It seems that its configuration creates a DataSource c3p0 every time it starts, but does not call the close() in that same DataSource when it restarts. Over time, it eventually launches several DataSources and the "leak" at each time reboots.

You need to set up Jetty (I’m not sure how) to call close() about the DataSource c3p0 each time the application closes and restarts.

Original response (in English):

I’m Sorry to Respond in English, but I don’t 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.

  • This is a Portuguese community, and for this reason there will be several users who do not know how to speak English, which makes their response useless | This is a Portuguese community, and for that Reason some if not Many of its users will not be Able to Speak English, which makes your Answer not Useful.

  • 1

    Sorry! i’m the Author of the c3p0 library, but i don’t Speak English. (i Wish i Did!) English is the best i can do.

  • Considering that your post isn’t very big, i’m willing to Translate it for you, if you want

  • sure, that’d be Great!

  • ......thank you!

  • @Isac Thank you so much for the translation. I added the original version, I find it interesting to keep it.

  • @LINQ Yes, I appreciate it, I was there to do it and I ended up not doing it, but I think it really looks better.

  • @Stevewaldman, Thank you for answering the question, for now I have chosen to use Tomcat to manage my connections, because I need to walk with the project, but your suggestion is probably the cause of the error and I will research how to do this in Jetty. | Thanks for Answering the Question, for the time being I opted to use Tomcat to Manage my Connections as I need to walk with the project, but your suggestion is probably the cause of the error and I will research on how to do this in Jetty. Isac and LINQ Thank you for translating

Show 3 more comments

Browser other questions tagged java jetty pooling c3p0

You are not signed in. Login or sign up in order to post.