10 Replies Latest reply on May 31, 2012 2:12 PM by balajisr

    JBoss 5.1 JDBC Connection Pool in Linux

    nnanda

      Hi All,

       

      We are using Linux for our testing environment and JBoss 5.1 GA as our app server and Oracle 10g as our DB. We are noticing one peculiar behavior. If the application deployed within JBoss is not in use for say 3-4 hours, all the JDBC connections become unusable and further request result in "Connection Closed" exception. Restarting of the JBoss fixes this issue. But its weird. Our oracle-ds.xml looks like below:

       

      <?xml version="1.0" encoding="UTF-8"?>
      
      <datasources>
        <local-tx-datasource>
          <jndi-name>AppDS</jndi-name>
          <use-java-context>false</use-java-context>     
          <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>   
          <connection-url>jdbc:oracle:thin:@xyz.corp:1528:sid</connection-url>
          <user-name>user</user-name>
          <password>password</password>
          <min-pool-size>15</min-pool-size>
          <max-pool-size>20</max-pool-size>
          <idle-timeout-minutes>0</idle-timeout-minutes>
        </local-tx-datasource>
      </datasources>
      

       

      Please help me to understand if we are missing any configuration to handle this issue or some configuration is needed at OS level.

       

      Thanks,

      Niranjan

        • 1. JBoss 5.1 JDBC Connection Pool in Linux
          nickarls

          Are there any firewalls between JBoss and the DB?

          • 2. JBoss 5.1 JDBC Connection Pool in Linux
            nnanda

            Hi Nicklas,

             

            No. We do not have a firewall between app server and DB servers. But, the DB servers are all AIX boxes while app servers (JBoss) are all Enterprise Linux 5.

             

            I also have noticed a pattern of 3 to 4 hours of idleness after which the connections become stale/unusable and resule in mentioned "Closed Connection" exception.

             

            Please let me know if you need more information.

             

            Thanks,

            Niranjan

            • 3. JBoss 5.1 JDBC Connection Pool in Linux
              nickarls

              Tried a

               

              <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

               

              In your datasource?

              • 4. JBoss 5.1 JDBC Connection Pool in Linux
                nnanda

                I have not tried it. But with this, what will I get? I guess I may get better logging why the connections are going down? Could you please help me to understand the advantage of such a config?

                • 5. JBoss 5.1 JDBC Connection Pool in Linux
                  nickarls

                  I'm under the impression that the implementation does a NOOP ping operation against the DB before handing off the connection from the connection pool (and making a fresh connection if the old one has gone sour)

                  • 6. JBoss 5.1 JDBC Connection Pool in Linux
                    nnanda

                    Okay. Let me add this configuration to the pool and observe it for another day. I am also going to changing the value for IDLE timeout from Zero to 3.

                    • 7. JBoss 5.1 JDBC Connection Pool in Linux
                      rouvas

                      I presume that you are aware of  http://community.jboss.org/wiki/SetUpAOracleDatasource ?

                      • 8. Re: JBoss 5.1 JDBC Connection Pool in Linux
                        peterj

                        It sounds like Oracle is configured to close unused connections after a certain period of time. Thus later when an app asks for a connection, the connection is closed. The valid-connection-checker-class-name and check-valid-connection-sql (which takes an SQL statement that it uses to verify the connection) aare used to first check the connection before handing it to a requesting app. If the check fails, JBoss AS closes the connection and restablishes it. Note that this check is done every time an app asks for a connection.

                         

                        Setting the idle-timeout-minutes should cause JBoss AS to close the connection long before Oracle times the connection out, so that should will work for the 5 optional connections (the ones between min and max connections) but it will not work for the 15 min connections - those never get released.

                        • 9. Re: JBoss 5.1 JDBC Connection Pool in Linux
                          peterj
                          but it will not work for the 15 min connections - those never get released.

                          This is wrong. If any connection is idle for the idle-timeout-minutes, then the app server will close that connection. If the connection count goes below min-pool-size, then the app server will reestablish the connection. For example, if the app server is completely idle, then every idle-time-minutes it will close and recreate the min-pool-size connections. If you set idle-timeout-minutes to 3, then every three minute the 15 connections will be closed and recreated.

                           

                          I recommend that you think about the timeout and min pool size to come up with something reasonable. For example, if the app is only busy during the day but you leave the app server running overnight, then I would set a very low min pool size (e.g. 2) and set a resonable long timeout size (e.g. 30). Then during the day the app server will most likely maintain the 20 max pool size connections because it will be busy, and then at night it will drop back to 1 connection which it will reestablish every 30 minutes. Then you will not need any of the connection checkers.

                          • 10. Re: JBoss 5.1 JDBC Connection Pool in Linux
                            balajisr

                            Hi All,

                             

                            I have configured background-validation-millis to 60000 and added the  <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql> as well.

                             

                            But still in JBoss 5.1 which is running on Linux pointing to AIX Oracle DB, if the server remains idle for some time i am getting the exception.

                             

                            Can any one confirm that org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker is same as that of above mentioned configurations ?

                             

                            ie. configuring OracleValidConnectionChecker is equivalent to configuring background-validation-millis.

                             

                            Thanks

                            Balaji