7 February 2014

An Introduction to Connection Pools in Glassfish

Introduction

In this blog post I will be taking an introductory look at connection pools. To begin with I will look at answering some basic questions:

What is a connection pool?
Why are they needed?
How do they work?

I will then take a look at how to configure them in Glassfish and take a look at some best practice settings.

What is a connection pool?

So, firstly, what is a connection pool?

A connection pool is a store of database connections that can be used and (most importantly) re-used to connect to a database.

Why are they needed?

Database connections are expensive to create and also to maintain. The reasons for this are many but include:


  • Establishing a network connection to the database server
  • Parsing the connection string information
  • Performing user authentication
  • Initialising the database connection in the database
  • Establishing transactional contexts


Now, if you have a web application with a single user you can simply create a database connection at the start of the user session and then close it at the end. However, this is a highly unlikely scenario!

Now, imagine a more realistic scenario where your web application will be accessed by hundreds or thousands of users. If each user's session creates a database connection, firstly your users will experience a delay whilst that connection is set up and secondly the overall performance of your system will deteriorate.

So, in answer to the question why are they needed - they improve both the performance and scalability of your system.

How do they work?

Rather than creating a new connection each time one is needed a pool of connections is created when your application server is started. These connections can then be used and re-used. When a new connection is required the pool is searched for an available connection. If one is available it is returned to the requester. If one is not available then the request is either queued or a new connection is established depending on how many connections are already in the pool and how the pool is configured. Once the connection is finished with, rather than closing it the connection is returned to the connection pool for use by the next requester.

OK, that's the theory out of the way. So, how do they work in practice?

Connection Pools in Glassfish

For this practical demonstration I will be using the following:

Operating System - Ubuntu 12
Java Version - 1.7.0_05
App Server - Glassfish 4.0
Database - MySQL 5.5
JDBC Driver - MySQL connector 5.1.25 (Available from http://dev.mysql.com/downloads/connector/j/)

If you are using different versions of any of the above then the results may differ.

Installation

Firstly, extract the JDK and Glassfish zip files to a directory of your choosing. For the purposes of this demo I installed them in my home directory:

/home/andy

For convenience add the Java and Glassfish bin directories to your PATH and set the JAVA_HOME environment variable to the directory where you unzipped Java.

On my machine they are set to the following:

PATH=/usr/lib/lightdm/lightdm:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/home/andy/jdk1.7.0_05/bin:/home/andy/glassfish4/bin
JAVA_HOME=/home/andy/jdk1.7.0_05

Finally we need to install MySQL

On Ubuntu we can use the Advanced Packaging Tool with the following command:

sudo apt-get install mysql-server

You will be asked to set a password for the root user. Remember this as we will be using it later.

Next up extract the MySQL JDBC driver and copy the jar file (in my case mysql-connector-java-5.1.25-bin.jar) to the following directory:

glassfish4/glassfish/domains/domain1/lib/ext

Creating the connection pool - From the console

Firstly, start up Glassfish. This can be done from a terminal window with the following command:

asadmin start-domain

Once the server has started you can access the console at http://localhost:4848

In the left hand panel go to Resources - JDBC-JDBC Connection Pools

Click New and enter the following values:


  • Pool Name - test-pool
  • Resource Type - javax.sql.DataSource
  • Driver Vendor - MySql


Click Next and then click Finish on the next screen, accepting the default values.

Testing the connection

Click on the connection pool name (test-pool).
Click the Ping button at the top of the screen.

You should see a message stating Ping Succeeded.

Creating the connection pool - From the command line

You can also create a connection pool using the asadmin command line tool with the following (substituting your password for the test one) :

asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource --property user=root:password=test:DatabaseName=test:ServerName=localhost:port=3306 test-pool

To test the connection from the command line run the following command:

asadmin ping-connection-pool test-pool

OK, so now we have created our connection pool I'm going to look at a few best practices regarding how to configure it.

Connection Pool Sizing

Connection pools should be sized to cater for the maximum number of concurrent connections.

The maximum size should be set in accordance with the maximum number of client requests your system can process. If your application receives 100 requests and each of those requires a database connection then if your connection pool is anything less than 100 some of those requests will have to wait for a connection to either be created or become available.

The minimum size of the connection pool ensures that a number of connections to the database are always established - this means that if you have a pool with a minimum size of 10 and you receive 10 requests then all can retrieve a database connection without waiting for the pool to create a new connection.

There is always a trade off with setting these values as the minimum value requires that those connections are maintained regardless of system load and the maximum value could potentially require a large number of concurrent database connections.

These values will be different for everyone. There are no magic numbers so it's a case of understanding your application, what your expected load (both steady and worst case) will be, monitoring to see if this changes and setting values accordingly.

Setting min/max sizes - via the console

Click on the connection pool name and under Pool Settings you will find Initial and Minimum Pool Size and Maximum Pool Size. Set these to your required sizes.

Setting min/max sizes - via the command line

To set the initial & minimum pool size:

asadmin set resources.jdbc-connection-pool.test-pool.steady-pool-size=10

To set the maximum pool size:

asadmin set resources.jdbc-connection-pool.test-pool.max-pool-size=200

Connection Validation

Connection validation ensures that connections aren't assigned to your application after the connection has already gone stale.

Connection validation is always a trade-off between how sure you want to be that a connection is valid and the performance impact from validation. There will be a negative performance impact by having to return an invalid connection by your application and borrow a new one, so finding the right balance is key.

Before using a connection from the pool a simple query is sent to test the connection. If their is an issue with the connection it is removed from the pool and another one used. The issue here is that if you have an issue such as the database being down and you have a large number of connections then each of those connections will be tested and removed.

In order to avoid this you can set connection validation so that if a connection fails all connections are closed.

Connection Validation - via the console

Click on the name of the pool
Select the advanced tab
Scroll down to Connection Validation and select the following settings:


  • Connection Validation required
  • Validation method - custom-validation
  • Validation class name - MySQLConnectionValidation


From the same screen you can also set whether to close all connections on failure.

Connection Validation - via the command line

To turn on connection validation :

asadmin set resources.jdbc-connection-pool.test-pool.connection-validation-method=custom-validation

asadmin set resources.jdbc-connection-pool.test-pool.validation-classname=org.glassfish.api.jdbc.validation.MySQLConnectionValidation

asadmin set resources.jdbc-connection-pool.test-pool.is-connection-validation-required=true

You can also set whether to close all connections on failure with the following command:

asadmin set resources.jdbc-connection-pool.test-pool.fail-all-connections=true

Statement and Connection Leak Detection

Statement and Connection Leak Detection allows you to set time-outs so that if Statements or Connections haven't been closed by an application they can be logged and/or closed.

In testing I would recommend setting it so that leaks are simply logged but not closed. However, in production I would recommend that leaks are closed. If you have tested thoroughly enough then there shouldn't be any but if there are you don't want to leave them open. Monitoring software should be configured to alert on detected leaks and then further investigation can take place and fixes can be put in place.

By default these values are set to 0 meaning detection is turned off.

Setting Statement and Connection Leak Detection - via the console

Click on the name of the pool
Select the advanced tab
Scroll down to Connection Settings
Set the Connection Leak Timeout and Statement Leak Timeout values

Setting Statement and Connection Leak Detection - via the command line

You can set the time-out values with the following commands:

asadmin set resources.jdbc-connection-pool.test-pool.statement-leak-timeout-in-seconds=5
asadmin set resources.jdbc-connection-pool.test-pool.connection-leak-timeout-in-seconds=5

Once these values are set if connection or statement leaks are detected you will see messages similar to the ones below in the application log.

WARNING: A potential connection leak detected for connection pool test-pool. The stack trace of the thread is provided below :
WARNING: A potential statement leak detected for connection pool test-pool. The stack trace of the thread is provided below :

At this point you can go back to your development team and get them to investigate the root cause, or smack them round the head, depending on your management style.  ;o)

Conclusion

Well, that's it for this blog. We've looked at a brief overview of Connection Pools, how to create and configure them in Glassfish along with a few best practice settings to consider.

As with all server configuration settings you should always take a close look at your application's needs before making changes. You should always performance test and load test your application to ascertain the best settings, particularly before making changes in production. One size does not fit all! Once you have decided upon the optimal settings you should then monitor and re-evaluate regularly to ensure you are always running with the best settings.




1 comment :

  1. Andy,

    Thank you for providing a nice introduction to Connection Pools in Glassfish! I'm fairly new to Java EE and have been developing some trial applications that I'm running under Glassfish. So far things are working well, but I've come across one issue with connection pools that I'm not sure of the best way to solve. I'm wondering if you've also came across the issue before and, if so, you have any ideas on how best to handle it...

    I'm running:

    - CentOS 5.2 (plus I have access to 6.x versions as well)
    - Glassfish 3.1.2.2
    - MySQL 5.5

    The core issue is how connection pools handle databases that are not always online. We have an app that will not load in Glassfish unless the database server is already up and available. Unfortunately, I don't currently have control over the availability of the database server. It will be up when I need it over 95% of the time, but during certain times it will be quiesced for backups, etc.

    This leads to some minor things (like many "RAR5038:Unexpected exception while creating resource for pool ExamplePool" / "RAR5058: Error while Resizing pool ExamplePool" messages due to "Communications link failure") in the Glassfish logs.

    More importantly, if it happens while my instance of Glassfish is being restarted any applications that depend on those connection pools fail to start (with the following exceptions: "RAR5117 : Failed to obtain/create connection from connection pool [ ExamplePool ]", "RAR5114 : Error allocating connection [...]", "Exception while invoking class org.glassfish.persistence.jpa.JPADeployer prepare method" and "Exception while preparing the app").

    Ideally, the database would always be online during the times the application in Glassfish is always loaded. However, is there a better/more graceful approach that you are aware of to handling this situation when the database is not online? I've tried changing various connection pool settings (validation, etc.), but to no avail so far...

    Thank you in advance for any help you might have time to provide!

    - Dwayne

    ReplyDelete