Database connection pooling

Each QueueMetrics transaction requires a connection to the database in order to access call and configuration data. By using a connection pool in Tomcat, a given set of database connections is opened at startup and then recycled as needed, thus saving the cost of opening and closing a connection for every transaction. While this cost is negligible for general usage with a local database (generally in the order of 10 to 30 milliseconds), it can be a performace boost if the MySQL database is remote or your server is very busy.

The advantages of this technique can be summed up as:

  • faster database access, mostly when the database is over a network or when there are many configuration parameters needed to fire up a connection

  • easier monitoring of JDBC resource usage by third party tools

  • maximum advantage when running AGAW clients

Prerequisites

  • A working QueueMetrics instance, version 1.6.0 or newer

Before you start, find your JDBC URI in your 'web.xml' file and copy it for future reference.

Changes to the MySQL server

Make sure that the total number of allowed connections is more than the maximum you configured for your pool. The number we use here is 50, as set by the 'maxActive' parameter below, and should be OK for most MySQL servers. Keep in mind that if you need to access your MySQL server with other applications or a monitoring script, you will need more connections.

Changes to QueueMetrics

Modify the file 'web.xml' in QueueMetrics as follows.

Change the parameter 'JDBC_URL' (Where the JDBC URI was) to:

    <init-param>
        <param-name>JDBC_URL</param-name>
        <param-value>pool:jdbc/qm</param-value>
    </init-param>

By the end of the file, just before the web-app XML element closes, add:

  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/qm</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

This basically tells QueueMetrics that instead of connecting straight to the database, it must fetch a connection from a pool called 'jdbc/qm' that is managed by Tomcat at the container level.

Changes to Tomcat

First, remove the username and password from the JDBC URI - they are passed separately here.

Modify the file 'server.xml' that is usually held in '/usr/local/queuemetrics/tomcat/config', adding the following (long) section before the closing Host element:

<Context docBase="MYQMAPP"
         path="/MYQMAPP" reloadable="true">

  <Resource name="jdbc/qm"
               auth="Container"
               type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/qm">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>

    <!-- Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to 0 for no limit.
         -->
    <parameter>
      <name>maxActive</name>
      <value>50</value>
    </parameter>
    <!-- You don't want to many idle connections hanging around
         if you can avoid it, only enough to soak up a spike in
         the load -->

    <parameter>
      <name>maxIdle</name>
      <value>5</value>
    </parameter>

    <!-- Don't use autoReconnect=true, it's going away eventually
         and it's a crutch for older connection pools that couldn't
         test connections. You need to decide whether your application
         is supposed to deal with SQLExceptions (hint, it should), and
         how much of a performance penalty you're willing to pay
         to ensure 'freshness' of the connection -->

    <parameter>
      <name>validationQuery</name>
      <value>SELECT 1</value>
    </parameter>

   <!-- The most conservative approach is to test connections
        before they're given to your application. For most applications
        this is okay, the query used above is very small and takes
        no real server resources to process, other than the time used
        to traverse the network.

        If you have a high-load application you'll need to rely on
        something else. -->

    <parameter>
      <name>testOnBorrow</name>
      <value>true</value>
    </parameter>

   <!-- Otherwise, or in addition to testOnBorrow, you can test
        while connections are sitting idle -->

    <parameter>
      <name>testWhileIdle</name>
      <value>true</value>
    </parameter>

    <!-- You have to set this value, otherwise even though
         you've asked connections to be tested while idle,
         the idle evicter thread will never run -->

    <parameter>
      <name>timeBetweenEvictionRunsMillis</name>
      <value>10000</value>
    </parameter>

    <!-- Don't allow connections to hang out idle too long,
         never longer than what wait_timeout is set to on the
         server...A few minutes or even fraction of a minute
         is sometimes okay here, it depends on your application
         and how much spikey load it will see -->

    <parameter>
      <name>minEvictableIdleTimeMillis</name>
      <value>60000</value>
    </parameter>

    <!-- Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->
    <parameter>
      <name>maxWait</name>
      <value>30000</value>
    </parameter>

    <!-- MySQL dB username and password for dB connections  -->
    <parameter>
     <name>username</name>
     <value>queuemetrics</value>
    </parameter>

    <parameter>
     <name>password</name>
     <value>javadude</value>
    </parameter>

    <!-- Class name for MySQL JDBC driver -->
    <parameter>
       <name>driverClassName</name>
       <value>com.mysql.jdbc.Driver</value>
    </parameter>

    <!-- The JDBC connection url for connecting to your MySQL dB.
         The autoReconnect=true argument to the url makes sure that the
         mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
         connection.  mysqld by default closes idle connections after 8 hours.
         -->
    <parameter>
      <name>url</name>
      <value>jdbc:mysql://localhost/queuemetrics?zeroDateTimeBehavior=convertToNull&amp;jdbcCompliantTruncation=false</value>
    </parameter>
  </ResourceParams>

</Context>

In the file above, you need to change the following elements:

<Context docBase="MYQMAPP"
         path="/MYQMAPP" reloadable="true">

Change 'MYQMAPP' to the name of the webapp as deployed on your system (usually "queuemetrics").

Then change the 'url' parameter and the 'username' and 'password' elements, setting your JDBC URL.

You may also fine-tune the maximum number of allowed connections in the pool and the eviction policies (but this goes beyond the scope of this tutorial).

Now copy the connector driver, such as 'mysql-xxxx.jar', to the '/common/lib' directory of your Tomcat installation and remove it from 'WEB-INF/lib/' of your QueueMetrics instance.

Restart QueueMetrics.