Database Reliability

Database Pooling

One of the most interesting things about the DataSource object used in the JNDI example is the fact that the underlying driver can be written to perform three different levels of connection:

• A basic implementation returns a Connection object based on an attempt to communicate with the database when the getConnection() method is called.

• A connection pooling implementation returns a Connection object obtained from a pool with possible connections already made.

• A distributed transaction implementation returns a Connection object obtained from a pool that allows transactions.

Depending on the underlying database, support for distributed transactions may or may not be available. The modes available depend on the implementation of the database driver. In the case of the Resin MySQL driver, database pooling is available. The system includes a number of parameters for making full use of pooling:

• max-connections— Determines the total connections; defaults to 20.

• max-idle-time— Determines the idle time of a pooled connection before it's removed; defaults to 30 seconds.

• max-active-time— Determines the maximum connection time; defaults to 6 hours.

• max-pool-time— Determines the maximum time a connection is kept in the pool; defaults to 24 hours.

• connection-wait-time— Determines the time to wait for an idle connection; defaults to 10 minutes.

• max-overflow-connections— Determines the number of overflow connections when a connection times out; defaults to 0.

All the times used in the parameters can be represented in several different ways. Here are the values to use:

• s = seconds

• m = minutes

• h = hours

• D = days

If a value for a parameter isn't one we've listed, the default used is seconds.

Let's see how the JNDI configuration for a database driver can use pooling. Note that nothing from the server side tells you whether pooling is being used; you simply configure for its use, and, if it's available, the DataSource object and the database driver handle the details. Consider the following configuration:

 <database>     <jndi-name>jdbc/mysql</jndi-name>     <driver type="org.gjt.mm.mysql.Driver">     <url>       jdbc:mysql://localhost:3306/products     </url>     <user>spider</user>     <password>spider</password>     </driver>     <max-connections>20</max-connections>     <max-idle-time>30s</max-idle-time>     <connection-wait-time>15</connection-wait-time>   </database> 

This configuration uses the Resin MySQL driver and sets several pooling options, including the maximum number of connection, the maximum idle time for a connection object, and the time a new connection will wait for a connection object from the pool. Listing 11.3 shows a snippet of code based on Listing 11.2; it builds five threads, each with a new connection to the database.

Listing 11.3: Code with multiple connection objects.

 private class Csql implements Runnable {   Connection connection;   int time;   public Csql(Connection localConnection, int t) {     connection = localConnection;     time = t;   }   public void run() {     try {       Thread.sleep(time*1000);       Statement statement = connection.createStatement();       ResultSet rs = statement.executeQuery("SELECT title,        price FROM product");     } catch(Exception e){}     System.out.println("Done = " + time);   } } ...     try {       for (int i=0;i<5;i++) {         myConnection = ds.getConnection();         Csql s = new Csql(myConnection, i);         Thread t = new Thread(s);         t.start();       } 

As each thread is launched, it removes a thread from the thread pool and executes some SQL. Notice, though, that the connection isn't closed; this is done to show that 20 pool connections are indeed available. After the code executes four times, the following message appears in the browser:

 SQLException caught: Can't open connection with full database pool (20) 

This small program has used all the pool connection objects.

Most databases can view the processes currently executing on them. MySQL has two commands that show the current connections to the database: show status and show processlist;. At the end of the show status command is a Threads_connected variable. When you execute the previous code, the value is 21: 20 connections from the servlet and 1 from the administrator. The show processlist; command lists all the current connections, the current user, machine, database accessed, current status, and current idle time. After the servlet executes four times, the show processlist; command details 21 threads: 20 threads come from the Resin server and 1 comes from the administrator.

You can change all the pooling options to find the right combination for both the Resin server and the database server. Too many connections will bog down the database, and too few can potentially cause a connection attempt failure error.