6 Replies Latest reply on Aug 5, 2010 1:22 AM by Ruchir Choudhry

    Configuration of Datasource: min-pool-size and max-pool-size

    Emiliano Ricci Aparicio Newbie

      ¿Which are the recommended values for min-pool-size and max-pool-size to configure an application used normally by 30 users?

        • 1. Re: Configuration of Datasource: min-pool-size and max-pool-size
          Peter Johnson Master

          What percentage of the requests made by a user require a database connection?

          How many database connections does a typical request require?

          Typically, how many simultaneous requests are made?

           

          Once you answer those questions, then you will know how many connections to provide. For example, if only 50% of the requests require a single connection, and usually there are only 5 requests being simultaneously processed, then 3 connections is sufficient.

           

          Another way to do this is to test it. Run with max connectiosn set to a large number of connections,say 100, and min connections set low, say 2 connections. Then monitor the number of active connections over the course of a day. That should tell you how many you need.

           

          You should also monitor the number of queued connection requests. Let's say you used the first example and set max connections to 3. Yet you notive that quite often you have as many as 4 queued connection reqeusts, then you should increase the max connections to 7.

           

          Another thing to consider is that you can always set max connection to a high number, say 100, and set min connections to your typical usage, say 7 to keep with the prior example. You will only ever have more than 7 connections if you actually need them.

          1 of 1 people found this helpful
          • 2. Re: Configuration of Datasource: min-pool-size and max-pool-size
            Emiliano Ricci Aparicio Newbie

            If the application needs 50 users all time connected to the database, we can setup min-pool-size: 50 y max-pool-size: 65.  Are the values normal to this situation? Can these values to affect the JBoss's perfomance? Can these values to affect the database's perfomance?

            • 3. Re: Configuration of Datasource: min-pool-size and max-pool-size
              Peter Johnson Master

              I suspect that you will probably never need 50 connections. I would set min connections to a lower amount and monitor how many you actually use.

               

              The settings can effect performance because it takes a while to establish a connection, so having idle connections available is better than waiting for connections to be established or waiting for in-use connections to be available. On the other hand, having too many idle connections does use up some Java heap space and some memory space within the database (example: a database memory work area dedicated to the connection), but if you have the spare memory available, it usually is not that big of an issue.

               

              I have used several hundred connections with several different databases (MySQL, PostgreSQL, Oracle), and that did not seem to be a problem; the database had no trouble keeping up with the requests. Of course, some database tuning also helped. Having said that, 50 connections is probably too small of a number to worry about, so the settings you proposed are probably just fine.

              • 4. Re: Configuration of Datasource: min-pool-size and max-pool-size
                Peter Johnson Master

                >>50 users all time connected to the database

                Connected to the database, or connected to the application? There is a huge difference! The 50 users will always be connected to the application, but only when a request is in progress will a database connection be needed. As soon as the request is done, that connection is no longer needed. As an example, if your 50 users all go to lunch at the same time, then during the lunch hours there will be 50 idle connections - none will be in use.

                • 5. Re: Configuration of Datasource: min-pool-size and max-pool-size
                  Emiliano Ricci Aparicio Newbie

                  50 user connected to the application but if in one moment all the users want connect to the database, they can do it.

                   

                  Thanks you for your answers

                  • 6. Re: Configuration of Datasource: min-pool-size and max-pool-size
                    Ruchir Choudhry Newbie

                    Hello Emili,


                    Max and Min connection size  can become counter productive if not set properly, as when the server starts it reserves the connection(Handle) to the  DB in active state.Its also consumes resources as more thread is allocated to the server and hence more objects is on the stack and hence it will use more RAM/CPU. You need to also check the max connections allowed in the DB. Please check the timeout for these connections too.


                    Its a good practice to start with a lowere number, like 10 min and 60 max, see how the system behaves.


                    You have also mentioned that you have 50 concurrent users: In response to that, the algorithm in the J2EE container dose the hashing and rehashing of the connections, some times it reuses the connections too, its also dependent on  DB connection timeout both from J2EE container and from the DB.


                    Thus you have to check all these before coming to a right configuration.

                     

                    Let me know if you are not cleare with anything


                    Thank You,

                    Ruchir