I have an application that uses MySQL fail-over via a connection url:
jdbc:mysql://172.17.0.4:3306,172.17.0.3:3306/db_name?autoReconnect=true&failOverReadOnly=false
Now when primary db is down then it should move to secondary connection and application flow should work as expected.
Now the problem happens when moving to secondary db. It takes too long to move/execute the queries, causing the flow to take much longer than expected.
I have checked already with db and there's no slow query issues. I guess it's something to do with fail-over and with checking the states. So, any idea what might be causing this issue or how to resolve this delay?
I am also using c3p0 to manage the connections pools. Already tried with initialTimeout and maxReconnects but no luck so far.
DataSource
<bean id="productionDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="description" value="integration_ds"/>
<!-- configuration pool via c3p0-->
<property name="acquireIncrement" value="${datasource.acquireIncrement}"/>
<property name="idleConnectionTestPeriod" value="${datasource.idleConnectionTestPeriod}"/>
<!-- seconds -->
<property name="maxPoolSize" value="${datasource.maxPoolSize}"/>
<property name="maxStatements" value="${datasource.maxStatements}"/>
<property name="maxStatementsPerConnection" value="${datasource.maxStatementsPerConnection}"/>
<property name="minPoolSize" value="${datasource.minPoolSize}"/>
<property name="initialPoolSize" value="${datasource.initialPoolSize}"/>
<property name="maxIdleTime" value="${datasource.maxIdleTime}"/>
<property name="acquireRetryAttempts" value="${datasource.acquireRetryAttempts}"/>
<property name="acquireRetryDelay" value="${datasource.acquireRetryDelay}"/>
<property name="breakAfterAcquireFailure" value="${datasource.breakAfterAcquireFailure}"/>
<property name="debugUnreturnedConnectionStackTraces" value="true"/>
</bean>
Properties
datasource.acquireIncrement=1
datasource.idleConnectionTestPeriod=1000
datasource.maxPoolSize=10
datasource.maxStatements=600
datasource.minPoolSize=5
datasource.initialPoolSize=5
datasource.maxIdleTime=7200
#datasource.acquireRetryAttempts=5
datasource.acquireRetryAttempts=1
#datasource.acquireRetryDelay=5000
#datasource.acquireRetryDelay=1000
datasource.acquireRetryDelay=100
datasource.breakAfterAcquireFailure=false
datasource.maxStatementsPerConnection=3
datasource.checkoutTimeout=100
DAO
private static final String findByAppIdHql = "select app from AppImpl app where app.appId = ?";
final Query query = sf.getCurrentSession().createQuery(findByAppIdHql).setString(0, appId);
query.setCacheable(true);
query.setCacheRegion("app_query_cache");
query.setCacheMode(CacheMode.NORMAL);
List<App> apps = query.list();