6 Replies Latest reply on Dec 22, 2006 6:39 PM by Anant K

    Sybase database pool not claiming used connections

    Vinicius Carvalho Apprentice

      Hello there! We have a JBoss 4.0.1sp1 + Sybase 12 + JConnect 5.5 with a 50 connections pool. We use a monitor (currentAvailableConnections from web-console) to check the number of available connections, what can be seen is that the pool is "shrinking", the graph looks like a stair, it starts with 45-50 available connections, then after a time (15min or so) it reduces to 40-45... and goes on. Even during lunch time where the application is almost not accessed the pool does not seem to claim its used connections back.
      What we are doing is forcing a flush on the MBean in order to free the resources. We also use a mysql and that database pool does return to original state after not being used.
      Also, checking the sybase it shows that it has 50 connections and 90% of them are on WAITING_COMMAND state.
      I wonder what might be the problem. any idea?
      Could be the idle_timeout for the pool? The driver?

      Best regards

        • 1. Re: Sybase database pool not claiming used connections
          Weston M. Price Master

          Ok, a few things:

          1) Enable tracing for the JBossManagedConnectionPool in your log4j.xml configuration file. An example of how to do this already exists in the file, you simply have to uncomment the XML.

          2) Post your *-ds.xml file.

          3) Check the logs for statements such as "CLOSING A CONNECTION FOR YOU..." this indicates that resources are not being closed appropriately in your application code.

          4) Generate a simple test case apart from your application that simply acquires a connection, does something trivial and closes the connection. This will go a long way to separating what might be application code versus the behavior of the pool itself.

          • 2. Re: Sybase database pool not claiming used connections
            Weston M. Price Master

            Also, as another path of testing try using the JTDS driver

            http://jtds.sourceforge.net/index.html

            • 3. Re: Sybase database pool not claiming used connections
              Vinicius Carvalho Apprentice

              Hello Weston thanks for your time and advices. Some points:

              Enabling the Tracing would overwhelm us, in a test machine for a simple scenario we had over 1.000 lines of output. I can't even imagine in production (thousands of users per day). Could we go without this?

              We don't have any warnings from JBoss stating that it closed the connection for us, but if you would not mind to explain to me, how does JBoss check this? I mean if we do have a connection leak, in a class that is hold in memory and not collected by the GC, would JBoss still claim that connection and close it?

              Regarding the JTDS, that's our next shot, do you know if it has a better performance than jconnect (which according to Sybase is the best around ;) )

              As you can see there's no connection idle timeout (our mistake) we are fixing it. But what's the behavior of this property, would jboss close the physical connection between it and the DB after the number of minutes informed? If so, why during low access periods (late evenings for instance) I still get 50 connections available, wouldn't the expected number be something like 10 (the minimum)?

              Sorry for all the questions, hope you could aid me on this.

              Best Regards

              datasources>
               <local-tx-datasource>
               <jndi-name>SybaseDS</jndi-name>
               <connection-url>jdbc:sybase:Tds:0.0.0.0:00/xxxxxxxxxxxxxxxxxxxxx</connection-url>
               <driver-class>xxxxxxxxxxxxxxxxxxx</driver-class>
               <user-name>xxxxx</user-name>
               <password>xxxxxxx</password>
              
              <!-- Walter -->
               <min-pool-size>10</min-pool-size>
               <max-pool-size>50</max-pool-size>
               <!--idle-timeout-minutes>6000</idle-timeout-minutes -->
               <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
               <metadata>
               <type-mapping>mySQL</type-mapping>
               </metadata>
              
               </local-tx-datasource>
              </datasources>
              


              PS: Got this from production right now, really do not know why we have mySQL as the type-mapping for a sybase ds... :P

              • 4. Re: Sybase database pool not claiming used connections
                Weston M. Price Master

                 


                Enabling the Tracing would overwhelm us, in a test machine for a simple scenario we had over 1.000 lines of output. I can't even imagine in production (thousands of users per day). Could we go without this?


                The trace is used only to evaluate what the issue might be, this is not recommended for production. I assume that you have a dev/test/qa environment that should be *fairly* close to what you run in production. You would want to do your tracing in one of these areas.


                We don't have any warnings from JBoss stating that it closed the connection for us, but if you would not mind to explain to me, how does JBoss check this? I mean if we do have a connection leak, in a class that is hold in memory and not collected by the GC, would JBoss still claim that connection and close it?


                The CacheConnectionManager is the component that handles the automatic connection close. This is actually JCA spec mandated behavior. Use of this in JBoss depends on a few things

                1) The debug flag needs to be set in the CachedConnectionManager. This is on by default, so typically you don't need to do anything to enable this.

                2) You are accessing a DataSource from an EJB
                By default all container configuration in standardjboss.xml and the EJB3 equivalent (ejb3-interceptors-aop.xml) are set to use the CacheConnectionInterceptor. Again, this is on by default and typically does not required explicit changes to enable.

                3)You are accessing the DataSource from a Servlet/JSP
                For Servlets and JSP this behavior does have to be explicity enabled. Information on how to do this can be found here

                http://wiki.jboss.org/wiki/Wiki.jsp?page=CanJBossTellMeWhenIDontCloseAConnection

                Note, this has nothing to do with the GC but rather the connection management system of JCA. If you use connections and do not appropriately close these connections (along with it's sub objects PreparedStatements, ResultSet etc) you will get leaks. As a result, the ManagedConnectionPool will run out of connections and you will not be able to aquire any further resources.


                Regarding the JTDS, that's our next shot, do you know if it has a better performance than jconnect (which according to Sybase is the best around ;) )


                Of course :-). I always recommend testing with multiple drivers for RDMS systems that have this option. Sybase/MSSQL Server are two that do. JTDS has been known to perform better and handle certain transactional sistuations much better than the close source counterpart.


                As you can see there's no connection idle timeout (our mistake) we are fixing it. But what's the behavior of this property, would jboss close the physical connection between it and the DB after the number of minutes informed? If so, why during low access periods (late evenings for instance) I still get 50 connections available, wouldn't the expected number be something like 10 (the minimum)?


                If an idle timeout is not specified, a default of 30 minutes is used so, whether you specify one or not, this behavior is always on. The behavior is that after a period of time the IdleRemover thread in JBoss evaluates the connection pool to determine if any connections have been idle for the period of time. If so, it automatically closes the connection and, depending upon your minumum setting, brings the pool back to the minimum connection allotment if necessary.

                Based upon what you are seeing, yes, the connection pool *should* be reduced to the minimum number of connections during periods of low activity. This is why a standalone test case would be useful.








                • 5. Re: Sybase database pool not claiming used connections
                  Vinicius Carvalho Apprentice

                  Weston, many thanks for the great support and help. We are testing the application here, first we will conduct a mass functional test of the entire app with tracing enabled hoping we could find a leak, then we are gonna move to jTDS.
                  Thanks a lot

                  Best Regards

                  • 6. Re: Sybase database pool not claiming used connections
                    Anant K Newbie

                    We have a similar problem. Were you able to solve this?

                    Thanks.