No managed connections available within configured blocking timeout (JBoss 7 and Postgres)

Question

Periodically i get error :

ERROR JDBCExceptionReporter --> javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/myDB 08:12:05,928 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/mySoftware].[jsp]] (ajp--xx.255.0.yyy-8109-21) Servlet.service() for servlet jsp threw exception: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms]) at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377) and etc.

.

So, i have next datasource config. on JBoss AS :

<datasource jta="true" jndi-name="java:jboss/datasources/myDB" pool-name="ssbs-pssbs" enabled="true" use-ccm="true">
                    <connection-url>jdbc:postgresql://xx.255.0.yyy/myDatabase</connection-url>
                    <driver-class>org.postgresql.Driver</driver-class>
                    <driver>postgresql-jdbc4</driver>
                    <pool>
                        <min-pool-size>30</min-pool-size>
                        <max-pool-size>150</max-pool-size>
                        <prefill>true</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                        <user-name>tick</user-name>
                        <password>tack</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>30000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>

and in my Postgres server i allow max_connection on 500. Why i get this exception ?

Answer 1

Your primary issue is probably a connection leak, but maybe not. In other words database transactions are leaving the pool and not being returned. In this case there are a few specific things to start with because the problem is probably a code problem and not a database problem.

The first thing to check is the current status of everything in pg_stat_activity. This includes the most recent query and the transaction state. In a connection leak you will probably find a large number of IDLE connections with similar queries. The queries can help you track down the connection leak. Also in a connection leak, when the problem starts, it will continue until you restart things.

In the event where the issue is in fact too few connections available, then you will see lots of ACTIVE connections. In that case increase the number of connections in your pool. Also in this case, the problem will occur intermittently and then appear to go on its own all by itself.