JPA/Hibernate "Failed to accept a connection" and "Timed out waiting for a free available connection"

[Johan Dahlberg]

I started to test our system yesterday and it worked fine while simulating a lower load. But while incresing the load, the following errors started appearing. I guess people have seen this before but I can't find any solutions to them. Most solutions is to upgrade to boneCP 0.8 but since that is default now that isn't helpful. What configuration options is relevant to this in Play and MySQL.

It started with this warning in the beginning.

2015-03-24 09:02:04,072 - [warn] o.j.n.c.s.n.AbstractNioSelector - Failed to accept a connection.

java.io.IOException: Too many open files

at sun.nio.ch.ServerSocketChannelImpl.accept0(Native Method) ~[na:1.7.0_76]

at sun.nio.ch.ServerSocketChannelImpl.accept(ServerSocketChannelImpl.java:241) ~[na:1.7.0_76]

at org.jboss.netty.channel.socket.nio.NioServerBoss.process(NioServerBoss.java:100) [io.netty.netty-3.9.3.Final.jar:na]

at org.jboss.netty.channel.socket.nio.AbstractNioSelector.run(AbstractNioSelector.java:318) [io.netty.netty-3.9.3.Final.jar:na]

at org.jboss.netty.channel.socket.nio.NioServerBoss.run(NioServerBoss.java:42) [io.netty.netty-3.9.3.Final.jar:na]

and after a minute without any requests being completed it was followed by:

2015-03-24 09:03:55,153 - [warn] o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 08001

2015-03-24 09:03:55,164 - [error] o.h.e.j.s.SqlExceptionHelper - Timed out waiting for a free available connection.

2015-03-24 09:03:55,179 - [error] play - Cannot invoke the action, eventually got an error: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection

2015-03-24 09:03:55,192 - [error] application - 

! @6lhpbbgle - Internal server error, for (POST) [/feedback] ->

play.api.Application$$anon$1: Execution exception[[PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection]]

at play.api.Application$class.handleError(Application.scala:296) ~[com.typesafe.play.play_2.11-2.3.8.jar:2.3.8]

at play.api.DefaultApplication.handleError(Application.scala:402) [com.typesafe.play.play_2.11-2.3.8.jar:2.3.8]

at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$4.apply(PlayDefaultUpstreamHandler.scala:320) [com.typesafe.play.play_2.11-2.3.8.jar:2.3.8]

at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$3$$anonfun$applyOrElse$4.apply(PlayDefaultUpstreamHandler.scala:320) [com.typesafe.play.play_2.11-2.3.8.jar:2.3.8]

at scala.Option.map(Option.scala:146) [org.scala-lang.scala-library-2.11.5.jar:na]

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Could not open connection

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763) ~[org.hibernate.hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) ~[org.hibernate.hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771) ~[org.hibernate.hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:64) ~[org.hibernate.hibernate-entitymanager-4.3.8.Final.jar:4.3.8.Final]

at play.db.jpa.JPA.withTransactionAsync(JPA.java:177) ~[com.typesafe.play.play-java-jpa_2.11-2.3.8.jar:2.3.8]

Caused by: org.hibernate.exception.JDBCConnectionException: Could not open connection

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:235) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

Caused by: java.sql.SQLException: Timed out waiting for a free available connection.

at com.jolbox.bonecp.DefaultConnectionStrategy.getConnectionInternal(DefaultConnectionStrategy.java:88) ~[com.jolbox.bonecp-0.8.0.RELEASE.jar:na]

at com.jolbox.bonecp.AbstractConnectionStrategy.getConnection(AbstractConnectionStrategy.java:90) ~[com.jolbox.bonecp-0.8.0.RELEASE.jar:na]

at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:553) ~[com.jolbox.bonecp-0.8.0.RELEASE.jar:na]

at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:131) ~[com.jolbox.bonecp-0.8.0.RELEASE.jar:na]

at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139) ~[org.hibernate.hibernate-core-4.3.8.Final.jar:4.3.8.Final]

[Johan Dahlberg]

Is there really no one who has encountered this problem before?

/Johan

[Igmar Palsenberg]

 

Is there really no one who has encountered this problem before?

Assuming you're talking about Play 2 : 

My guess would be that the connection made to SQL is done from the default Play thread pool. That would also explain why it works under a lower load, since it has enough threads to handle that.

Normally that would be the "Play default thread pool", which default to one thread per core. Since Hibernate and MySQL block like hell, it would explain why it starts to crap out on higher load : It exhausts the thread pool.

I would see if I could put the whole DB related stuff under it's own execution context. Or even better, get rid of the RDS stuff at all : It's a performance killer, and not scalable.

If you're using Play 1 : 

What's the settings in application.conf ? The default pool is limited, so you probably want to extend that.

Igmar 

[Johan Dahlberg]

Is there really no one who has encountered this problem before?

Assuming you're talking about Play 2 : 

I am talking about Play 2.

 

My guess would be that the connection made to SQL is done from the default Play thread pool. That would also explain why it works under a lower load, since it has enough threads to handle that.

Normally that would be the "Play default thread pool", which default to one thread per core. Since Hibernate and MySQL block like hell, it would explain why it starts to crap out on higher load : It exhausts the thread pool.

I would see if I could put the whole DB related stuff under it's own execution context.

How do I put the JPA/Hibernate calls in a different execution context?

 

Or even better, get rid of the RDS stuff at all : It's a performance killer, and not scalable.

There are so many relations that I really don't think there is an option to relational databases. With a few exceptions where we already use Redis.

 

Igmar 

Thanks for the answer! 

[Igmar Palsenberg]

 

My guess would be that the connection made to SQL is done from the default Play thread pool. That would also explain why it works under a lower load, since it has enough threads to handle that.

Normally that would be the "Play default thread pool", which default to one thread per core. Since Hibernate and MySQL block like hell, it would explain why it starts to crap out on higher load : It exhausts the thread pool.

I would see if I could put the whole DB related stuff under it's own execution context.

How do I put the JPA/Hibernate calls in a different execution context?

 

Or even better, get rid of the RDS stuff at all : It's a performance killer, and not scalable.

There are so many relations that I really don't think there is an option to relational databases. With a few exceptions where we already use Redis.

Can you share the relevant DB related parts of your application.conf ?

Igmar

[Igmar Palsenberg]

Thanks for the answer! 

Can you shove : 

 

play {

  akka {

    akka.loggers = ["akka.event.Logging$DefaultLogger", "akka.event.slf4j.Slf4jLogger"]

    loglevel = WARNING

    actor {

      default-dispatcher = {

        fork-join-executor {

          parallelism-factor = 2.0

          parallelism-max = 64

        }

      }

    }

  }

}

in your application.conf, and see if it makes any difference ? If it uses the default pool, it should. If it works, you still need to tune the numbers.

Igmar

[Johan Dahlberg]

# Akka

# ~~~~~

play {

  akka {

    loggers = ["akka.event.Logging$DefaultLogger", "akka.event.slf4j.Slf4jLogger"]

    #loglevel = WARNING

    loglevel = DEBUG

    actor {

      retrieveBodyParserTimeout = 5 seconds

      default-dispatcher = {

        fork-join-executor {

          parallelism-min = 2.0

          parallelism-max = 100

        }

      }

      actions-dispatcher = {

        fork-join-executor {

          parallelism-factor = 2.0

          parallelism-max = 100

        }

      }

      promises-dispatcher = {

        fork-join-executor {

          parallelism-factor = 2.0

          parallelism-max = 100

        }

      }

//      websockets-dispatcher = {

//        fork-join-executor {

//          parallelism-factor = 1.0

//          parallelism-max = 24

//        }

//      }

    }

  }

# Database configuration

# ~~~~~

# You can declare as many datasources as you want.

# By convention, the default datasource is named `default`

#

db.default.driver=com.mysql.jdbc.Driver

db.default.url="mysql://user:password@localhost/database_name"

db.default.jndiName=DefaultDS

jpa.default=defaultPersistenceUnit

[Dominik Dorn]

"Too many open files" sounds also like a issue with sockets... try to adjust your 

sysctl.conf .. google is your friend on how to configure it. 

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to play-framewor...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Dominik Dorn
http://dominikdorn.com
http://twitter.com/domdorn

Skripten, Mitschriften, Lernunterlagen, etc. findest Du auf http://www.studyguru.eu !

[Johan Dahlberg]

I have adjusted my sysctl to a level that it couldn't be that. But thanks anyway because that is probably important for the future anyway.

I guess that Igmar is right and that the problem is in the thread pool. Is it possible to create a thread pool that is used for all Database calls in a practical way and how would I do that.

How should the Akka numbers look in the conf-file for a server with high load.

/Johan

[Mark Moore]

I am getting similar errors to the op under light load but using slick.  Would your suggestions still apply? I have changed all the db related configurations I can find to no avail.  

[Igmar Palsenberg]

I have adjusted my sysctl to a level that it couldn't be that. But thanks anyway because that is probably important for the future anyway.

I guess that Igmar is right and that the problem is in the thread pool. Is it possible to create a thread pool that is used for all Database calls in a practical way and how would I do that.

How should the Akka numbers look in the conf-file for a server with high load.

It depends. DB stuff usually has a thread per connection (which is a really shitty model), so you need to have a thread available per piece of blocking DB code I guess.

In Play, you need to wrap the DB stuff in a Promise, so that you can assign it to a different context. If you don't, it will be part of the main threadpool, which is not what you want.

Igmar

[Johan Dahlberg]

Thanks, the majority of the requests goes to the same place so I guess it would be easy to change that as a start.

Thanks for the help!

/Johan