How to configure the connection pool in Play Framework 1.3 through application.conf and c3p0.properties?

Question

I'm configured the Play 1.3 application to use four databases (on three different servers).

There was a problem of leakage connections, this problem has been partially solved, I wrote about this -

Do I need to annotate JPA actions with @Transactional in Play Framework-1.x to prevent the connection leak?

Now I configure c3p0 connection pool and would like to know how to do it properly.

I created c3p0.properties file inside conf directory.

c3p0.properties:

...
c3p0.acquireIncrement=5
c3p0.maxIdleTime=60
c3p0.maxIdleTimeExcessConnections=10
c3p0.maxPoolSize=200
c3p0.minPoolSize=20
c3p0.numHelperThreads=6
c3p0.unreturnedConnectionTimeout=30
...

In the file application.conf I commented the connection pool settings:

...
# db.hardwareLayer.pool.timeout=10000
# db.hardwareLayer.pool.maxSize=500
# db.hardwareLayer.pool.minSize=100

# db.applicationLayer.pool.timeout=10000
# db.applicationLayer.pool.maxSize=500
# db.applicationLayer.pool.minSize=100

...

etc

Now I read my configurations(just for testing):

ComboPooledDataSource local = (ComboPooledDataSource) DB.datasource;

Logger.info("MaxConnectionAge: " + 
    local.getMaxConnectionAge());
Logger.info("MaxPoolSize: " + 
    local.getMaxPoolSize());
Logger.info("NumConnectionsAllUsers: " + 
    local.getNumConnectionsAllUsers());
Logger.info("NumConnectionsDefaultUsers: " + 
    local.getNumConnectionsDefaultUser());
Logger.info("NumBusyConnectionsAllUsers: " + 
    local.getNumBusyConnectionsAllUsers());
Logger.info("NumBusyConnectionsDefaultUser: " + 
    local.getNumBusyConnectionsDefaultUser());
Logger.info("LastCheckinFailureDefaultUser: " + 
    local.getLastCheckinFailureDefaultUser());
Logger.info("NumFailedCheckinsDefaultUser: " + 
    local.getNumFailedCheckinsDefaultUser());
Logger.info("NumFailedCheckoutsDefaultUser: " + 
    local.getNumFailedCheckoutsDefaultUser());
Logger.info("NumIdleConnectionsAllUser: " + 
    local.getNumIdleConnectionsAllUsers());
Logger.info("NumIdleConnectionsDefaultUser: " + 
    local.getNumIdleConnectionsDefaultUser());
Logger.info("UnreturnedConnectionTimeout: " + 
    local.getUnreturnedConnectionTimeout());
Logger.info("NumUnclosedOrphanedConnectionsAllUsers: " + 
    local.getNumUnclosedOrphanedConnectionsAllUsers());
Logger.info("NumUnclosedOrphanedConnectionsDefaultUsers: " + 
local.getNumUnclosedOrphanedConnectionsDefaultUser());

This gives:

20:10:04,432 INFO  ~ MaxConnectionAge: 0
20:10:04,432 INFO  ~ MaxPoolSize: 30
20:10:04,432 INFO  ~ NumConnectionsAllUsers: 1
20:10:04,432 INFO  ~ NumConnectionsDefaultUsers: 1
20:10:04,432 INFO  ~ NumBusyConnectionsAllUsers: 1
20:10:04,432 INFO  ~ NumBusyConnectionsDefaultUser: 1
20:10:04,432 INFO  ~ LastCheckinFailureDefaultUser: null
20:10:04,432 INFO  ~ NumFailedCheckinsDefaultUser: 0
20:10:04,432 INFO  ~ NumFailedCheckoutsDefaultUser: 0
20:10:04,432 INFO  ~ NumIdleConnectionsAllUser: 0
20:10:04,432 INFO  ~ NumIdleConnectionsDefaultUser: 0
20:10:04,432 INFO  ~ UnreturnedConnectionTimeout: 30
20:10:04,432 INFO  ~ NumUnclosedOrphanedConnectionsAllUsers: 0
20:10:04,432 INFO  ~ NumUnclosedOrphanedConnectionsDefaultUsers: 0

Almost all settings is default except unreturnedConnectionTimeout, which I set up in the file c3p0.properties.

The default settings are defined in the source code of the framework -

Relevant part of the DBConfig.java:

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass(p.getProperty(propsPrefix+".driver"));
ds.setJdbcUrl(p.getProperty(propsPrefix + ".url"));
ds.setUser(p.getProperty(propsPrefix + ".user"));
ds.setPassword(p.getProperty(propsPrefix + ".pass"));
ds.setAcquireRetryAttempts(10);
ds.setCheckoutTimeout(Integer.parseInt(p.getProperty(propsPrefix + ".pool.timeout", "5000")));
ds.setBreakAfterAcquireFailure(false);
ds.setMaxPoolSize(Integer.parseInt(p.getProperty(propsPrefix + ".pool.maxSize", "30")));
ds.setMinPoolSize(Integer.parseInt(p.getProperty(propsPrefix + ".pool.minSize", "1")));
ds.setMaxIdleTimeExcessConnections(Integer.parseInt(p.getProperty(propsPrefix + ".pool.maxIdleTimeExcessConnections", "0")));
ds.setIdleConnectionTestPeriod(10);
ds.setTestConnectionOnCheckin(true);

Then I check the connection pool:

SELECT 
    dbExecConnections.session_id, 
    dbExecSessions.status, 
    /*client_net_address,*/ 
    program_name, 
    host_process_id, 
    login_name

FROM 
    sys.dm_exec_connections  dbExecConnections
    JOIN sys.dm_exec_sessions dbExecSessions
        ON dbExecConnections.session_id = dbExecSessions.session_id

CROSS APPLY sys.dm_exec_sql_text(dbExecConnections.most_recent_sql_handle) AS dest

enter image description here

Then I make a small DDoS attacks on my application. Failures alternates with normal works of the applications and after some time the system is stabilizes (due to parameter unreturnedConnectionTimeout):

20:29:32,317 ERROR ~ An attempt by a client to checkout a Connection has timed o
ut.
20:29:32,363 ERROR ~
@6mebbn9f8
Internal Server Error (500) for request POST /api/devices/kladr/levelthree

Oops: PersistenceException
An unexpected error occured caused by exception PersistenceException: org.hibern
ate.exception.GenericJDBCException: Could not open connection

play.exceptions.UnexpectedException: Unexpected Error
        at play.Invoker$Invocation.onException(Invoker.java:244)
        at play.Invoker$Invocation.run(Invoker.java:306)
        ... 14 more
20:29:39,520 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD
5_0_, kladr0_.SOCR as SOCR6_0_, kladr0_.Status as Status7_0_, kladr0_.UNO as UNO
8_0_ from KLADR kladr0_ where kladr0_.CODE like '%63023_00____%'
20:29:40,227 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD
5_0_, kladr0_.SOCR as SOCR6_0_, kladr0_.Status as Status7_0_, kladr0_.UNO as UNO
8_0_ from KLADR kladr0_ where kladr0_.CODE like '%63024_00____%'
20:29:45,900 WARN  ~ SQL Error: 0, SQLState: null
20:29:45,900 ERROR ~ An attempt by a client to checkout a Connection has timed o
ut.
20:29:45,932 ERROR ~
@6mebbn9fa
Internal Server Error (500) for request POST /api/devices/kladr/levelthree

Oops: PersistenceException
An unexpected error occured caused by exception PersistenceException: org.hibern
ate.exception.GenericJDBCException: Could not open connection
play.exceptions.UnexpectedException: Unexpected Error
        at play.Invoker$Invocation.onException(Invoker.java:244)
        ... 14 more
20:29:46,236 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD
5_0_, kladr0_.SOCR as SOCR6_0_, kladr0_.Status as Status7_0_, kladr0_.UNO as UNO
8_0_ from KLADR kladr0_ where kladr0_.CODE like '%63026_00____%'
20:29:52,873 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD
5_0_, kladr0_.SOCR as SOCR6_0_, kladr0_.Status as Status7_0_, kladr0_.UNO as UNO
8_0_ from KLADR kladr0_ where kladr0_.CODE like '%63027_00____%'
20:29:53,491 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD
5_0_, kladr0_.SOCR as SOCR6_0_, kladr0_.Status as Status7_0_, kladr0_.UNO as UNO
8_0_ from KLADR kladr0_ where kladr0_.CODE like '%63028_00____%'
20:29:54,090 DEBUG ~ select kladr0_.CODE as CODE1_0_, kladr0_.GNIMB as GNIMB2_0_
, kladr0_.[INDEX] as INDEX3_0_, kladr0_.NAME as NAME4_0_, kladr0_.OCATD as OCATD

All this is good.

But how to collect all the necessary configurations in one file? If I uncomment pool settings in application.conf:

20:32:44,908 INFO  ~ MaxConnectionAge: 0
20:32:44,908 INFO  ~ MaxPoolSize: 500
20:32:44,909 INFO  ~ NumConnectionsAllUsers: 100
20:32:44,909 INFO  ~ NumConnectionsDefaultUsers: 100
20:32:44,910 INFO  ~ NumBusyConnectionsAllUsers: 15
20:32:44,910 INFO  ~ NumBusyConnectionsDefaultUser: 15
20:32:44,910 INFO  ~ LastCheckinFailureDefaultUser: null
20:32:44,910 INFO  ~ NumFailedCheckinsDefaultUser: 0
20:32:44,910 INFO  ~ NumFailedCheckoutsDefaultUser: 0
20:32:44,910 INFO  ~ NumIdleConnectionsAllUser: 85
20:32:44,910 INFO  ~ NumIdleConnectionsDefaultUser: 85
20:32:44,910 INFO  ~ UnreturnedConnectionTimeout: 30
20:32:44,910 INFO  ~ NumUnclosedOrphanedConnectionsAllUsers: 0
20:32:44,910 INFO  ~ NumUnclosedOrphanedConnectionsDefaultUsers: 0

Some settings are read from application.conf, some from c3p0.properties.

simmerman simmerman · Accepted Answer · 2015-06-14T23:50:57

Play 1.x only supports configuring a subset of the c3p0 options via application.conf. If you want non defaults for any others then you are going to have to maintain a c3p0.properties file.

Due to c3p0's Precedence of Configuration Settings and the fact that Play programmatically sets the properties that can be configured with application.conf - you can't use the c3p0.properties file to set the Play supported options.

So - for the configuration you are attempting to implement you will need to maintain datasource configuration settings in both files (or fork Play to get what you want).

How to configure the connection pool in Play Framework 1.3 through application.conf and c3p0.properties?

1 Answers