playframeworkconnection-poolingc3p0playframework-1.x

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

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.

Solution

  • 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).