3 Replies Latest reply on Aug 2, 2002 3:53 AM by Dan Cornell

    Connection Pool

    Peter Kelley Newbie

      JBoss 3.0.1

      How can I tell if my connections are being pooled for MySql and when new connections are being taken from the pool ? I'm using the standard mysql-service.xml file which seems to offer connection pooling but I'm seeing 5 second delays between database access attempts from the application (JIRA) and the query being reported in the database transaction logs.

      Are there other options for connection pooling ? I have heard Minerva reccommended but I wasn't sure if this was for jboss 2.4.4 or 3.0.

        • 1. Re: Connection Pool
          David Jencks Master

          No other pooling options for 3.x at the moment. The design is rather pluggable so you can easily write your own pool and use it with the ConnectionManager implementation.

          Are you sure you have enough connections available for all your requests? If not, the connection requests will wait in order for available connections. Please investigate further. I haven't seen delays when there is an adequate connection supply.

          • 2. Re: Connection Pool
            Peter Kelley Newbie

            We have 50 connections configured and at most 2 users on the application at present so I'm guessing that connection availability is not a problem.

            The reason I thought it might be connections is that the tuning instructions at http://www.atlassian.com/software/jira/docs/performance.jsp suggest that this may be a problem. It definitely seems database query related as there are bursts of activity in the server.log that synchronize with activity in the mysql logs. There are points where it hangs for 5 seconds before doing anything.

            Do you have any other suggestions as to how I might go about diagnosing this performance problem ?

            • 3. Re: Connection Pool
              Dan Cornell Newbie

              I'm having a similar problem. I have a routine that runs from a MDB where I run through a whole bunch of SQL queries in a row - all on different Connection objects (at least they're retrieved separately from the DataSource). It seems like after a number of INSERT statements (all on "different" connection from the pool) I exhaust all of the connections. For some reason this doesn't seem to happen when I only use the connection to do SELECT queries.

              There is only one thread running, and it has to create/close a whole bunch of JDBC connections, but this is all done serially. I was under the impression that when I called close() on my database connection from the pool that it is automatically returned to the pool and should be instantly available.

              The only thing I can think of is that I've got some sort of problem with transactions. This is run through an MDB with Bean transaction management. I make no effort in my code to direct the transactional properties (although the MySQL table type is InnoDB) The MaxSize on my pool is 75, and it consistently craps out after creating 74 objects. There is at least one (maybe two) SELECT statement on a different Connection before each INSERT statement (again, on its own Connection), so it seems as though the INSERTs are causing the troubles.

              The exception message I get is "No Managed Connections Available" I'm pretty sure that this is something in my code that I'm doing wrong, so once I correct this I'll post it to the list. I suspect that either JIRA or your application might be running into the same issue.

              I'm using:

              Dual ~500Mhz PIII, 512MB RAM
              RedHat Linux 7.2
              JBoss 3.0.0
              MySQL 4.0.2 alpha
              MM.MySQL driver 2.0.14