4 Replies Latest reply on Jan 5, 2015 11:28 PM by jaikiran pai

    EAP 6 datasource pool connections do not idle - why?

    Ryan Evans Newbie

      I have JBoss EAP 6.3 setup with a single datasource pointing to an instance of Oracle database (v 11gR2). The data source has the default pool settings (no "<pool>" element specified) - I believe this means a minimum of 0 and a maximum of 20 connections. I also have a "<idle-timeout-minutes>" value of 5. The application running on this instance of JBoss has some threads (about 8 or so) that start upon server startup. These threads execute at about 15-30 second intervals, and execute a query against the datasource each time. The queries are quick, usually taking only 1 - 2 ms, and access to the datasource is synchronized (meaning no thread is executing a query at the same time as another). The code uses Spring transaction management and JdbcTemplate to perform these queries.

       

      Here's my question: After starting JBoss, I take a look at the open connections using a profiler tool (YourKit). The profiler reports about 14-15 actual connections remaining open in the pool. No matter how long I wait, NONE of those connections are ever closed (even though there are many more than the min open). IF, however, I "flush" the datasource using the Admin Console, then all connections are closed. Shortly thereafter, about 2 or 3 connections are opened again and kept open. This number (2-3) is sustained and does not increase, even though the same threads are continuing to execute queries at the same rate. Since the activity of my application can be sustained by 2 or 3 connections, WHY does JBoss keep so many open after startup? Why do the remaining 11-12 connections never idle and close???

       

      Any help would be appreciated. Thanks!

        • 1. Re: EAP 6 datasource pool connections do not idle - why?
          Wolf-Dieter Fink Master

          Not sure why the connections are opend, maybe if you use JPA or other persistence which check something at startup and open the connections.

           

          The other thing is the idle-time ...

          There is a change in one former version to keep the poo "hot", that mean all opened connections are used in a round-robin strategie, so lets assume you use a connection each minute and the idle time is 5 minutes it is possible that 6 connections are keept open.

          This behaviour might become configurable in the future.

           

          Does that make sense to you?

          1 of 1 people found this helpful
          • 2. Re: EAP 6 datasource pool connections do not idle - why?
            jaikiran pai Master

            From what you say it appears that every few seconds some threads use a connection from the pool to do some DB activity. Now I can't say (based on the limited info I know about your app) why the 14 connections were created in first place. But I'll take it that there was a genuine reason why those 14 connections were created. Now, when your application every few seconds asks for a connection, the pool can use some algorithm to decide which of the 14 connections to hand out to the application. It might so happen that the pool will return a different connection each time, which effectively means that over a period of 5 minutes, each of those 14 connections get used at least once, thus they never idle for 5 minutes straight.

             

            Now, I don't know what the flush() operation is meant to do. Probably, clear all existing connections? Anyway, now when the application (continues) to ask for a connection every few seconds, the pool probably finds no existing connection and creates a new one and hands it out. It perhaps does it 2-3 times (and that depends on demand for connections from the application) and hence the 2-3 connections in the pool. It then just keep reusing those existing connections since I guess those are enough for what your application does.

            • 3. Re: EAP 6 datasource pool connections do not idle - why?
              Ryan Evans Newbie

              This seems like a bad design - or is it just me? In the case of my application (frequent access to the DB, multiple times a minute), no connection will EVER idle...

              • 4. Re: EAP 6 datasource pool connections do not idle - why?
                jaikiran pai Master

                I don't know if in WildFly, there are ways to configure a different strategy for the managed connection pool. But the default strategy (ironjacamar/ManagedConnectionPoolFactory.java at 1.2 · ironjacamar/ironjacamar · GitHub) seems to be this one ironjacamar/SemaphoreArrayListManagedConnectionPool.java at 1.2 · ironjacamar/ironjacamar · GitHub  which does this for connection selection ironjacamar/SemaphoreArrayListManagedConnectionPool.java at 1.2 · ironjacamar/ironjacamar · GitHub (i.e. round robin fashion) and I think that explains the behaviour you are seeing.

                 

                Ryan Evans wrote:

                 

                This seems like a bad design - or is it just me? In the case of my application (frequent access to the DB, multiple times a minute), no connection will EVER idle...


                I don't think it's a bad design in general. Why let the connections idle if they have already been opened and are in the pool? I do understand that in your case perhaps 14 connections aren't needed for the repeated activity that the application is doing, but at some point they were needed or requested and are now in the pool. So I guess for cases like these, flush might be the right thing to do after all.