3

last night Play suddently could no longer connect to database (PostGreSQL on the same EC2 instance).

23:57:31.996 [BoneCP-pool-watch-thread] ERROR com.jolbox.bonecp.hooks.AbstractConnectionHook {1}

-Failed to acquire connection Sleeping for 5000ms and trying again. Attempts left: 10. Exception: null

Sometimes, the error thrown is different :

01:21:05.920 [BoneCP-pool-watch-thread] ERROR com.jolbox.bonecp.PoolWatchThread {1} -Error in trying to obtain a connection. Retrying in 5000ms

org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

It retried every 5 seconds without success. I restarted Play and it reconnected well.

This happens twice this week.

I thought about auto restart. Do you know a tool for that ? Or any other idea ?

EDIT I updated BoneCP by updating dependencies in Build.scala : 

  "com.google.guava" % "guava" % "14.0-rc1",
  "org.slf4j" % "slf4j-api" % "1.7.5",
  "com.jolbox" % "bonecp" % "0.8.0-rc1",  
  "org.reflections" % "reflections" % "0.9.8"

But now I regularly get this error telling it can't close connection. So it reaches the max number of connections again.

-Error closing Connection org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction. at org.postgresql.jdbc2.AbstractJdbc2Connection.setReadOnly(AbstractJdbc2Connection.java:617) ~[postgresql.postgresql-9.1-901-1.jdbc4.jar:na]

Julien D
  • 1,259
  • 9
  • 22
  • It happened again, so I added details. – Julien D Sep 13 '13 at 08:08
  • After updating boneCP, I get a different error : see my edit – Julien D Sep 17 '13 at 11:27
  • First check for any connection leaks in the application. Also try setting the value of setCloseConnectionWatch. Setting this property have negative impact on performance. Test thoroughly in developent environment and remove this property before promoting it to production http://jolbox.com/bonecp/downloads/site/apidocs/index.html – Abhijith Nagarajan Sep 23 '13 at 11:59
  • Please go through below link. It seems the same issue http://stackoverflow.com/questions/15397840/cannot-change-transaction-read-only-property-in-the-middle-of-a-transaction – Abhijith Nagarajan Sep 23 '13 at 12:01

1 Answers1

1

This error indicates that there are too many open connections. This could be caused by connection pooling issues, or by connection leaks in your application. To troubleshoot:

  1. Check the pg_stat_activity view to see where the connections are coming from. Are they your app? Somewhere else?

  2. If they are from your app, pay close attention to when these are starting up, Are you failing to release the connection properly so the pooler can re-use the connection?

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • Thanks. I use Play Framework with Ebean: I dont open or close connections. As I dont have time to debug Play/BoneCP, I'm gonna upgrade it to see if it has been fixed in latest versions. – Julien D Nov 16 '13 at 10:48