hibernate - error during frequent queries

Question

I use server Tomcat + HttpServlet + hibernate. I test the server performance, for this, I created an application that simulates the operation 500 clients, each client with a periodicity repeats HTTP requests to the server.

when requests connection = 900 (+-100) i start getting hibernate errors

24.04.2013 12:27:20 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 08001
24.04.2013 12:27:23 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy24.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1711)
    at org.hibernate.loader.Loader.doQuery(Loader.java:828)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    at org.hibernate.loader.Loader.doList(Loader.java:2438)
    at org.hibernate.loader.Loader.doList(Loader.java:2424)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2254)
    at org.hibernate.loader.Loader.list(Loader.java:2249)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1248)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    at actions.player.LoginAction.perform(LoginAction.java:20)
    at action.Action.performAction(Action.java:66)
    at servlets.RequestHandlerServlet.service(RequestHandlerServlet.java:34)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException:
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:136)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:123)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:28)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:20)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:22)
    at org.postgresql.Driver.makeConnection(Driver.java:391)
    at org.postgresql.Driver.connect(Driver.java:265)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:193)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:281)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
    ... 41 more
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(Unknown Source)
    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at org.postgresql.core.PGStream.<init>(PGStream.java:60)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:74)
    ... 54 more
org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82)

please help, any ideas ...

Answer 1

The specific problem is:

Caused by: java.net.ConnectException: Connection refused: connect

This shows that when PgJDBC attempted to open a TCP/IP socket to the PostgreSQL server the connection was rejected.

This doesn't happen when the PostgreSQL server is overloaded; it'll instead accept the connection for long enough to reply with an error mentioning "too many connections" or "max_connections". When I tested with psql against Pg 9.3-HEAD I got:

FATAL:  sorry, too many clients already

Similarly, if authentication is a problem, PostgreSQL will accept the connection, reply with an error message, and close the connection, like:

FATAL:  no pg_hba.conf entry for host "[local]", user "craig", database "postgres"

or:

FATAL:  password authentication failed for user "craig"

What you are seeing is a lower level issue in your OS or networking stack. It's happening before PostgreSQL gets a chance to accept the connection. Since you have not mentioned your operating system, whether the Pg server is local or remote to the app server, or pretty much anything else it's hard to help further with that. I'd start by:

• Checking the PostgreSQL logs for errors about accept() or anything else unusual-looking
• Investigating any TCP/IP connection limits and max open file limits in the operating system
• Making sure I wasn't using any kind of dodgy 3rd party software firewall or "Internet security" suite

Furthermore, you should investigate using a connection pooler - either an in-app one, or an external pooler like PgPool-II, allowing you to reduce max_connections to an efficient level. PostgreSQL will perform better with fewer connections; the ideal number depends on workload and hardware. See PostgreSQL wiki: Number of database connections.