Application acquires high number of connections on boot time

[ahmednasir91]

We've a spring boot application, using c3p0 for connection pooling in our master datasource. Whenever the servers are restarted the application acquires high numbers of connections and then release it in a minute or so.

We spent sometime tweaking the parameters but still we are facing this issue, following is the configuration of data source.

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
          destroy-method="close" autowire="byName">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl"
                  value="${datasource.database.master.endpoint}/${datasource.database.master.schema}?characterEncoding=UTF-8&amp;useUnicode=true"/>
        <property name="user" value="${datasource.database.master.username}"/>
        <property name="password" value="${datasource.database.master.password}"/>

        <property name="initialPoolSize" value="5"/>
        <property name="maxPoolSize" value="350"/>
        <property name="maxIdleTime" value="600"/>
        <property name="maxConnectionAge" value="720"/>
        <property name="maxIdleTimeExcessConnections" value="90"/>
        <property name="minPoolSize" value="5"/>
        <property name="acquireIncrement" value="10"/>
        <property name="numHelperThreads" value="10"/>
        <property name="preferredTestQuery" value="select 1"/>
        <property name="idleConnectionTestPeriod" value="300"/>
        <property name="testConnectionOnCheckin" value="false"/>
        <property name="testConnectionOnCheckout" value="true"/>
        <property name="checkoutTimeout" value="5000"/>
    </bean>

The highlighted part shows, when tomcat was restarted on 6 servers (total we have 12 servers running).

There is no effect on CPU, any recommendations on what settings/parameters we need to tweak, or how we can debug this behavior?

[swaldman]

Note that the number of Connection approximately doubles. c3p0 closes Connections asynchronously, so what might be going on is just that you recreate new Connections before old Connections have been close()ed. Still, with numHelperThreads set at 10, Connections should get close()ed pretty quickly. (You might check whether there is any sensitivity of this issue to numHelperThreads, does the duration of the spike diminish when it is high? The restart itself may be asynchronous, depending how you arrange it, so new Connections may be acquired before the old pools are even shut down. One thing you might try is shutdown-delay-restart rather than an immediate restart, to see if the issue remains.)

[ahmednasir91]

@swaldman thanks for the reply. Will try the shutdown-delay-restart and will get back with the observation.

[ahmednasir91]

One more behavior we've observed, if there a single bad query running on the server by mistake that causes some CPU load on the server. We see a very high number of connections (2000-3000 new connections) acquired by application in an instant and they drop in 2-3 mins.

What we are unable to understand is, why the application acquires too many connections in a very short period and then also drops them suddenly. Any suggestions/recommendations you have, on how best we can debug this behavior?

[swaldman]

I'm not at all sure. But if your DBMS is badly enough blocked, you might end up with your pools having asynchronously tried to destroy and acquire a lot of Connections. Those Connection destructions would not have actually happened, nor the reacquisitions, and the order in which they complete might be undefined. You might see a lot of churn as those backlogged requests get handled all at once when the DBMS frees up. Given the settings you have, I don't see anything that would cause so much churn. The DBMS would have to be held up for a pretty long time. But that's the first possibility that comes to mind.

[ahmednasir91]

@swaldman thanks for the help.