max connections in application not in sync with db max c

I have been working on an application that has average load of around 200 connections at high traffic time. And sometimes the application was throwing the below exception:

 org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection

So as we debugged the issue we had a strange behavior by another application running on the same machine. The other application was also throwing the same exception at the same time. Since both of the applications were accessing the same database which was Postgres 9.1.
Both the applications were using apache dbcp for database pools and the maximum number of connections was set 150 in both the applications. So at the highest load times the applications would need 300 connections to the database. As we looked into the Postgres config file  for max_connections, it was set to 250.
Once we made the max_connections to 310, the application was behaving fine and there were no more hibernate errors.
The other option was to use a pool on top of postgres, like pgpool. You can have a look here http://www.pgpool.net/mediawiki/index.php/Main_Page

Update

 We have now switched to pgbouncer.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.