9 Replies Latest reply on Mar 13, 2009 1:44 PM by Juan Garat

    ConnectionCount and InUseConnectionCount behavior

    Juan Garat Newbie

      Hello,
      I have a doubt in the behavior of the connection pool of the Jboss.
      We are using JBoss 4.0.2 with java version "1.5.0_10"

      I'm trying to understand the behavior of the ConnectionCount in relation with the InUseConnectionCount, and it seems like the JBoss create connections that he don't realy use.
      I'm being looking in the source of JBoss to understand this but I can't find much about it.

      Our datasource configuration is:

      <local-tx-datasource>
      <jndi-name>XXXXXXX</jndi-name>
      <connection-url>XXXXXXX</connection-url>
      <driver-class>XXXXXXX</driver-class>
      <user-name>XXXXXXX</user-name>
      XXXXXXX
      <min-pool-size>5</min-pool-size>
      <max-pool-size>40</max-pool-size>
      <idle-timeout-minutes>5</idle-timeout-minutes>
      <check-valid-connection-sql>some valid sql select</check-valid-connection-sql>
      </local-tx-datasource>



      For example, a common behavior of one normal day, sometimes we have 40 ConnectionCount (the max-pool configurated) where is only 5 InUseConnection.
      We have a average of a 16.88 of difference between de ConnectionCount and InUseConnectionCount


      Anyone can tell me wich is the behavior of the connection pool?? When he creates new connection that re don't realy needs?

      Thanks

        • 1. Re: ConnectionCount and InUseConnectionCount behavior
          Peter Johnson Master

          The ConnectionCount is the total number of connections that have been established since JBoss AS started. Consider this scenario. An app accesses the database so 5 connections (min-pool-size) are created. Then the app gets busy and it needs more connections, let's say a total of 20 connections. At this point both InUseConnectionCount and ConnectionCount are 20. The the app is no longer busy, so after 5 minutes (idle-timeout-minutes) the number of connections in use (InUseConnectionCount) goes back down to 5. At this point ConnectionCount is still 20. Later the app gets busy again and let's assume that 25 connections are required. Thus 20 new connections were created. At this point ConnectionCount is 40 and InUseConnectionCount is 25. Then the app is no longer busy and those connections are no longer needed, so the InUseConnectionCount goes back down to 5. But ConnectionCount is still at 40.

          Hope this helps.

          • 2. Re: ConnectionCount and InUseConnectionCount behavior
            Peter Johnson Master

            Oh, and is the above scenario plays out several times (in use connections increasing and decreasing) you could end up with a ConnectionCount of 50 or 100 or 200...

            • 3. Re: ConnectionCount and InUseConnectionCount behavior
              Juan Garat Newbie

               

              "PeterJ" wrote:
              The ConnectionCount is the total number of connections that have been established since JBoss AS started. Consider this scenario. An app accesses the database so 5 connections (min-pool-size) are created. Then the app gets busy and it needs more connections, let's say a total of 20 connections. At this point both InUseConnectionCount and ConnectionCount are 20. The the app is no longer busy, so after 5 minutes (idle-timeout-minutes) the number of connections in use (InUseConnectionCount) goes back down to 5. At this point ConnectionCount is still 20. Later the app gets busy again and let's assume that 25 connections are required. Thus 20 new connections were created. At this point ConnectionCount is 40 and InUseConnectionCount is 25. Then the app is no longer busy and those connections are no longer needed, so the InUseConnectionCount goes back down to 5. But ConnectionCount is still at 40.

              Hope this helps.


              I think you are confused with the ConnectionCreatedCount parameter.
              The ConnectionCount is the number of connection established to the database, but not necessarily in use. Every idle-timeout-millis time the connections that was idle for more than that time, es be destroyed, so ConnectionCount decrease.

              I have a graphic showing the value of this two parameters in time, but i don't know how to put it here to show you what I meen to say.

              • 4. Re: ConnectionCount and InUseConnectionCount behavior
                Peter Johnson Master

                You are correct. I didn't look at all of the properties of the mbean before I replied, and instead replied based on my memory which is apparently deteriorating faster than I care to admit! ;-)

                In your graph, what is the highest that InUseConnectionCount goes?

                You could always post the raw data for your graph and I could replot it. Hopefully you have it as a CSV file or something like that.

                • 5. Re: ConnectionCount and InUseConnectionCount behavior
                  Juan Garat Newbie

                  Right now, the propertie MaxConnectionsInUseCount is 34.

                  I have to flush the pool to initilize all the pool properties in 0 and give you correct data.

                  Thanks

                  • 6. Re: ConnectionCount and InUseConnectionCount behavior
                    Vicky Kak Master

                     

                    "jguser" wrote:

                    We have a average of a 16.88 of difference between de ConnectionCount and InUseConnectionCount

                    What does 16.88 mean here ?

                    "jguser" wrote:

                    Anyone can tell me wich is the behavior of the connection pool?? When he creates new connection that re don't realy needs?

                    Thanks

                    When IdleRemover is called, the PoolFiller thread will clean the idle connection which are below the min-pool-size and also recreate them.
                    So during the IdleRemover kicking the connections would be created which might not really used.
                    You can have a look at the <strict-min-pool> parameter for the Jboss5 here
                    http://www.jboss.org/community/docs/DOC-9349

                    If you can provide the clearer details I can assist you more ;)

                    • 7. Re: ConnectionCount and InUseConnectionCount behavior
                      Juan Garat Newbie

                       

                      "PeterJ" wrote:

                      You could always post the raw data for your graph and I could replot it. Hopefully you have it as a CSV file or something like that.


                      I have some data in csv format. The data was taked with a process every minute.
                      If you graph the data you can see what I see.

                      "HOUR";"CONNECTIONCOUNT";"INUSECONNECTIONCOUNT";"MAXCONNECTIONSINUSECOUNT"
                      "16:25";5;1;5
                      "16:26";9;2;9
                      "16:27";13;3;13
                      "16:28";13;1;13
                      "16:29";13;5;13
                      "16:30";14;9;13
                      "16:31";14;7;13
                      "16:32";25;7;24
                      "16:33";25;5;24
                      "16:34";25;8;24
                      "16:35";25;6;24
                      "16:36";25;7;24
                      "16:37";24;2;24
                      "16:38";24;5;24
                      "16:39";24;2;24
                      "16:40";24;1;24
                      "16:41";24;3;24
                      "16:42";14;4;24
                      "16:43";14;6;24
                      "16:44";12;3;24
                      "16:45";14;1;24
                      "16:46";14;3;24
                      "16:47";14;4;24
                      "16:48";18;2;24
                      "16:49";18;5;24
                      "16:50";19;5;24
                      "16:51";19;5;24
                      "16:52";19;3;24
                      "16:53";19;15;24
                      "16:54";23;0;24
                      "16:55";23;1;24
                      "16:56";23;1;24
                      "16:57";23;0;24
                      "16:58";23;2;24
                      "16:59";9;4;24
                      "17:0";16;1;24
                      "17:1";16;2;24
                      "17:2";16;1;24
                      "17:3";16;5;24
                      "17:4";16;1;24
                      "17:5";16;1;24
                      "17:6";16;1;24
                      "17:7";16;5;24
                      "17:8";18;8;24
                      "17:9";18;1;24
                      "17:10";18;3;24
                      "17:11";18;2;24
                      "17:12";18;2;24
                      "17:13";18;3;24
                      "17:14";12;6;24
                      "17:15";17;3;24
                      "17:16";17;1;24
                      "17:17";17;4;24
                      "17:18";17;4;24
                      "17:19";17;4;24
                      "17:20";22;7;24
                      "17:21";22;0;24
                      "17:22";22;3;24
                      "17:23";22;7;24
                      "17:24";22;12;24
                      "17:26";22;8;24
                      "17:27";19;7;24
                      "17:28";19;1;24
                      "17:29";19;0;24
                      "17:30";19;2;24
                      "17:31";19;7;24
                      "17:32";18;8;24
                      "17:33";18;3;24
                      "17:34";18;4;24
                      "17:35";18;4;24
                      "17:36";18;4;24
                      "17:37";17;2;24
                      "17:38";17;3;24
                      "17:39";17;3;24
                      "17:40";17;6;24
                      "17:41";17;2;24
                      "17:42";16;2;24
                      "17:43";16;2;24
                      "17:44";16;4;24
                      "17:45";16;0;24
                      "17:46";16;0;24
                      "17:47";14;4;24
                      "17:48";14;3;24
                      "17:49";13;7;24
                      "17:50";13;1;24
                      "17:51";13;4;24
                      "17:52";13;4;24
                      "17:53";20;1;24
                      "17:54";20;1;24
                      "17:55";20;2;24
                      "17:56";20;1;24
                      "17:57";20;0;24
                      "17:58";20;1;24
                      "17:59";10;3;24
                      "18:0";14;1;24
                      "18:1";14;2;24
                      "18:2";14;3;24
                      "18:3";14;2;24
                      "18:4";14;3;24
                      "18:5";14;1;24
                      "18:6";14;1;24
                      "18:7";12;4;24
                      "18:8";12;6;24
                      "18:9";12;1;24
                      "18:10";12;1;24
                      "18:11";12;2;24
                      "18:12";12;1;24
                      "18:13";12;2;24
                      "18:14";11;0;24
                      "18:15";11;1;24
                      "18:16";11;2;24
                      "18:17";9;0;24
                      "18:18";9;1;24
                      "18:19";9;3;24
                      "18:20";9;1;24
                      "18:21";9;2;24
                      "18:22";9;0;24
                      "18:23";9;3;24
                      "18:24";12;2;24
                      "18:25";15;0;24
                      "18:26";15;3;24
                      "18:27";15;2;24
                      "18:28";15;0;24
                      "18:29";15;0;24
                      "18:30";15;2;24
                      "18:31";15;0;24
                      "18:32";9;4;24
                      "18:33";9;0;24
                      "18:34";9;2;24
                      "18:35";9;1;24
                      "18:36";9;1;24
                      "18:37";9;1;24
                      "18:38";9;2;24
                      "18:39";9;1;24
                      "18:40";9;1;24
                      "18:41";9;2;24
                      "18:42";9;0;24
                      "18:43";9;0;24
                      "18:44";8;1;24
                      "18:45";8;2;24
                      "18:46";9;2;24
                      "18:47";9;3;24
                      "18:48";9;1;24
                      "18:49";9;2;24
                      "18:50";9;1;24
                      "18:51";9;1;24
                      "18:52";9;2;24
                      "18:53";9;2;24
                      "18:54";9;3;24
                      "18:55";9;1;24
                      "18:56";9;0;24
                      "18:57";8;0;24
                      "18:58";8;0;24
                      "18:59";7;0;24
                      "19:0";9;0;24
                      "19:1";9;0;24
                      "19:2";12;6;24
                      "19:3";12;0;24
                      "19:4";12;0;24
                      "19:5";16;1;24
                      "19:6";16;2;24
                      "19:7";16;2;24
                      "19:8";16;3;24
                      "19:9";16;0;24
                      "19:10";16;2;24
                      "19:11";16;0;24
                      "19:12";6;0;24
                      "19:13";6;0;24
                      "19:14";6;0;24
                      "19:15";6;1;24
                      "19:16";6;1;24
                      "19:17";7;0;24
                      "19:18";10;2;24
                      "19:19";10;1;24
                      "19:20";10;2;24
                      "19:21";10;0;24
                      "19:22";10;0;24
                      "19:23";10;1;24
                      "19:24";8;1;24
                      "19:25";8;0;24
                      "19:26";8;0;24
                      "19:27";6;0;24
                      "19:28";6;1;24
                      "19:29";6;0;24
                      "19:30";6;0;24
                      "19:31";6;1;24
                      "19:32";5;0;24
                      "19:33";5;0;24
                      "19:34";5;0;24
                      "19:35";5;0;24
                      "19:36";5;2;24
                      "19:37";8;2;24
                      "19:38";8;4;24
                      "19:39";8;3;24
                      "19:40";10;4;24
                      "19:41";19;2;24
                      "19:42";19;5;24
                      "19:43";19;2;24
                      "19:44";19;0;24
                      "19:45";19;0;24
                      "19:46";19;0;24
                      "19:47";6;2;24
                      "19:48";6;1;24
                      "19:49";6;1;24
                      "19:50";6;1;24
                      "19:51";6;1;24
                      "19:52";9;4;24
                      "19:53";9;4;24
                      "19:54";17;0;24
                      "19:55";17;0;24
                      "19:56";17;1;24
                      "19:57";17;2;24
                      "19:58";17;0;24
                      "19:59";5;0;24
                      "20:0";9;0;24
                      "20:1";9;0;24
                      "20:2";9;0;24
                      "20:3";9;0;24
                      "20:4";9;1;24
                      "20:5";9;0;24
                      "20:6";9;2;24
                      "20:7";6;0;24
                      "20:8";6;0;24
                      "20:9";6;0;24
                      "20:10";6;0;24
                      "20:11";6;0;24
                      "20:12";5;0;24
                      "20:13";5;0;24
                      "20:14";5;0;24
                      "20:15";5;3;24
                      "20:16";6;2;24
                      "20:17";16;1;24
                      "20:18";16;0;24
                      "20:19";16;0;24
                      "20:20";16;1;24
                      "20:21";16;0;24
                      "20:22";16;1;24
                      "20:23";16;0;24
                      "20:24";5;0;24
                      "20:25";5;0;24
                      "20:26";5;0;24
                      "20:27";5;0;24
                      "20:28";5;2;24
                      "20:29";5;0;24
                      "20:30";5;0;24

                      Maybe is just an error of take the data avery minute, and in the midle, he realy use the connectioncount he said in the next measure

                      Thanks

                      • 8. Re: ConnectionCount and InUseConnectionCount behavior
                        Peter Johnson Master

                        Sorry for the late reply. Looking at the data, I don't see anything wrong. As you pointed out, with taking a snapshot every minute your could have missed the time when the ConnectionCount == InUseConnectionCount.

                        Your database might also keep track of connection counts in one way or another, or perhaps track requests and responses. If you watch those you should see that, for example, sometime between 19:53 and 19:54 the in use count actually went up to 17 connections. And then because those connections were no longer used (based on he InUseConnectionCount of 0 and 1 from 19:54 to 19:58), the timeout limit hit (default of 5 minutes) and the unused connections were closed and you were back to the MinConnections (5).

                        • 9. Re: ConnectionCount and InUseConnectionCount behavior
                          Juan Garat Newbie

                          Peter, thanks for the reply.