[2.0.3-java] Heroku dev and PostgreSQL connection pool problem

[BiGuP]

Hi there,

I've been running a non-prod play 2.0 application quite sucessfully on a Heroku dev plan for quite some time now and before going to production I wanted to do some load tests and that's when the problems started appearing.

I use Jenkins + Jmeter (Performance plugin) to do some load tests every 10 minutes. The test consists of doing 600 http requests in 20 seconds : 300 logins and also 300 times loading the page showing the user list.

It runs pretty good and fast for 3-4 hours without errors but then I start getting 95% of 500 Server Error pages. And it will error this way until I restart the application. This is caused by the connection pool not being able to acquire new connections. I tried to change BoneCP settings to limit to number of connections to 15 (less than the max connections in the plan) but nothing works, I always have the same results. It's kind of scary the application will fail 95% of the time if I put it in prod.

I wonder if anyone had the same issues ? Are my settings wrong ? Maybe play 2.0 or Ebean or BoneCP is leaking some connections ?

Details follow, any help would be appreciated.

Setup :

-------

- Heroku dev plan

- Postgresql 9.1 database part of the heroku dev plan (max: 20 connections)

- Ebean without any specific transactions or custom JDBC code

Exception :

---------

[error] c.j.b.h.AbstractConnectionHook - Failed to acquire connection Sleeping for 1000ms and trying again. Attempts left: 3. Exception: null

[error] c.j.b.PoolWatchThread - Error in trying to obtain a connection. Retrying in 1000ms

org.postgresql.util.PSQLException: FATAL: too many connections for role "vsrgmjqwtwpegv"

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) ~[postgresql-9.1-901.jdbc4.jar:na]

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) ~[postgresql-9.1-901.jdbc4.jar:na]

at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) ~[postgresql-9.1-901.jdbc4.jar:na]

at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:469) ~[postgresql-9.1-901.jdbc4.jar:na]

at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) ~[postgresql-9.1-901.jdbc4.jar:na]

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) ~[postgresql-9.1-901.jdbc4.jar:na]

Configuration:

-------------

db.default.driver=org.postgresql.Driver

db.default.ssl=true

db.default.jndiName=DefaultDS

db.default.partitionCount=1

db.default.maxConnectionsPerPartition=15

db.default.minConnectionsPerPartition=2

db.default.acquireIncrement=2

db.default.acquireRetryAttempts=5

db.default.acquireRetryDelay=1 seconds

db.default.connectionTimeout=2 second

db.default.idleMaxAge=15 minutes

db.default.idleConnectionTestPeriod=5 minutes

db.default.initSQL="SELECT 1"

db.default.logStatements=false

db.default.maxConnectionAge=1 hour

Thank you,

Laurent.

[James Ward]

I'm not sure why you are running out of connections if you are only using 1 dyno.  But you might want to make sure you have configured the Akka thread pool to be large enough to handle many requests that could be waiting on the DB.  More details:

http://www.jamesward.com/2012/06/25/optimizing-play-2-for-database-driven-apps

Hope that helps.

-James

[BiGuP]

Hi James,

Thank you for your reply, I read your article and it's was very interesting. It helped me configure Akka and improve the performance of the application on heroku, but it didn't solve the connection pool problem.

It looks like the connection pool problems comes from BoneCP and after hours of looking in the documentation and forums I came up with a configuration that works much better. It looks like the key is to disable the release helper thread. Even if this works so far, I'm not really happy with BoneCP since it has problems obviously and it looks like the support and release schedule is not very good for this library. I wish we could switch pooling libraries more easily in Play.

Here's the BoneCP configuration that works better for me on Heroku :

db.default.partitionCount=1

db.default.maxConnectionsPerPartition=20

db.default.minConnectionsPerPartition=2

db.default.acquireIncrement=1

db.default.acquireRetryAttempts=3

db.default.acquireRetryDelay=30 seconds

db.default.connectionTimeout=30 seconds

db.default.idleMaxAge=5 minutes

# Disable keeping the connection active

db.default.idleConnectionTestPeriod=0

db.default.maxConnectionAge=15 minutes

db.default.initSQL="SELECT 1"

# Disable helper threads

db.default.releaseHelperThreads=0

Regards,

Laurent.