8 Replies Latest reply on Aug 8, 2002 10:24 AM by Ian Hunter

    Getting DB connections

    Ian Hunter Newbie

      [Using 2.4.4 and Jetty with MySQL XA wrappers]

      I feel stupid asking this but...

      We've developed a system in JBoss, looked at performance and went OUCH!

      We profiled and found that getting connections was very expensive.

      I *had* coded it get a connection from the pool for each query and put it back afterwards, we now get a connection at start of HTTP request and pass the connection around - is this the recommended way or should pulling from pool each query have been OK?

      Many Thanks
      Ian Hunter

        • 1. Re: Getting DB connections
          Werner Ramaekers Newbie

          hey,
          the 'recommended' way is to get the connection from the pool when you need it and put it back as soon as you don't need it anymore.
          The number of concurrent connections is best kept to a minimum to be able to serve the largest number of concurrent users with the smallest RAM footprint.
          I know this is not important for MySQL but for commercial databases like Oracle and DB2 the license price is determined by the number of concurrent connections.

          Werner

          • 2. Re: Getting DB connections
            J. Wolfgang Kaltz Novice

            Yeah getting NEW connections is expensive, that is the whole point of pooling them.
            Presumably, you are not giving back the connections to the pool, so everytime you get a connection from the pool, it actually has to open a new one to the db.
            The recommended way is:
            just before you need a connection (like, the line before creating the statement), get the connection from the pool. As soon as you're done processing the answer from the db, release the connection to the pool, by calling close()

            I just checked our application (using oracle connections), getting a connection the first time costs 0.54 s, but getting a connection from the pool which has already been set up, costs only 0.04s - and the includes all the debug logging which probably accounts for most of that time ;)

            • 3. Re: Getting DB connections
              J. Wolfgang Kaltz Novice

              Oh yeah I almost forgot this important issue too:

              be sure to write a finally {} clause around all your sql stuff. If an exception occurs, release all resources, including connection, if they're set !!
              Otherwise for any old sql error, you will have stuff lying around until some system cleanup, and you don't want to rely on that if there is heavy usage.

              • 4. Re: Getting DB connections
                Ian Hunter Newbie

                Thanks for the response chaps.

                I'm a bit worried here guys because you are concurring with my view of how to get and release connections, but I *think* my code reflects recommendations. However when we set connection pool size to min 500 max 1000 and then repeatedly get/put from the pool operation times drop from the order of 17secs to 2secs under load!

                I've put the 'get' code below and also the jboss.jcml settings. Is there something obvious that I haven't seen?

                Many many thanks
                Ian

                ======================================================
                // Get connection & use it for a query...
                public Records executeQuery(Command comm)
                throws Exception {

                Records result = null;

                Connection conn = null;
                Statement stmt = null;
                ResultSet rs = null;

                Exception exception = null; // Reference to exception.

                try {

                conn = _connections.getConnection(); // Uses JNDI to a local EJB that does a connection retrieval from pool

                if (conn == null) throw new SQLException("Unable to get a connection");

                stmt = conn.createStatement();

                ///////////////////////////////////
                // Execute command here....
                rs = stmt.executeQuery(comm.getSql());
                result = new Records(rs); // Process results
                rs.close();
                ///////////////////////////////////

                // The finally clause closes the connection
                // statement

                } catch(Exception exc) {
                exception = exc;
                println("Problem with database access: " + exc.getMessage());
                } finally {

                // This follows the pattern as
                //STRONGLY recommended by the JBoss team...
                try {
                if (rs != null) rs.close();
                } catch(SQLException sqle) {
                exception = sqle;
                println("Cannot close the results set");
                } catch(NullPointerException npex) { }

                try {
                if (stmt != null) stmt.close();
                } catch(SQLException sqle) {
                exception = sqle;
                println("Cannot close the statement");
                } catch(NullPointerException npex) { }

                try {
                if (conn != null) _connections.releaseConnection(conn); // Simply does conn.close();
                } catch(SQLException sqle) {
                exception = sqle;
                println("Cannot close the connection");
                } catch(NullPointerException npex) { }

                }

                if (exception != null) throw exception;

                return(result);

                }

                =======================================================


                org.gjt.mm.mysql.Driver




                org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

                DefaultDS
                jdbc:mysql://@DB_SERVER@/teamwarrior

                [User]
                [Pword]

                true
                1000
                500

                true
                200000
                true
                1800000
                true


                ================================================

                • 5. Re: Getting DB connections
                  J. Wolfgang Kaltz Novice

                  > conn = _connections.getConnection(); // Uses
                  > Uses JNDI to a local EJB that does a connection
                  > retrieval from pool

                  This one looks suspicious to me. Are you actually creating an ejb everytime you want to get a connection from the pool ? That would be overhead. Also, it's not so clear how the resource retrieved would then be freed.
                  Maybe the comment is misleading; in any case for what it's worth here's how I get connections:
                  in a common class, accessible by all beans (a ServiceLocator class, with a static instance of itself) I have a getConnection() method with:
                  DataSource ds = (DataSource)getResource("java:/" + poolName, DataSource.class);
                  Connection connection = ds.getConnection();

                  Now from a bean which needs a connection, a call to ServiceLocator.getConnection() provides a connection within 0.04s (assuming it is already an open connection, meaning one which has already been used).

                  I'm running JBoss 2.4.4 on a not-so-mighty machine, so you should not have worse lookup times.

                  • 6. Re: Getting DB connections
                    Ian Hunter Newbie

                    Mmmm. I briefly questioned this myself.

                    We set the standard stateless bean config to a minimum way above our requirements. So I *believe* that getting a Stateless Bean should be quick and not impact the performance as much as we see - wrong?

                    When I have time I'll try the static object approach (are there threading issues with this?)

                    Many Thanks
                    Ian

                    • 7. Re: Getting DB connections
                      Andreas Schaefer Apprentice

                      Hi

                      Never, ever try to make you own DBConnection pooling. Whenever you need a new connection create one from the DataSource and close it after you are done.

                      Andy

                      • 8. Re: Getting DB connections
                        Ian Hunter Newbie

                        Hi Andy,

                        Re-inventing connection pools is not on the agenda here.

                        Cheers
                        Ian