In this blog post, we take an introductory look at connection pools and how to configure them in Payara Server using best practices.

(last updated 22/08/2019)

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 Connection Pools Needed?

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

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 Connection Pools 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 Payara Server

For this practical demonstration I will be using the following:

Operating System - Ubuntu 18.04

Java Version - 1.8.0_172

App Server – Payara Server 5.181

Database -H2

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

Installation

Firstly, extract the JDK and Payara Server zip files to a directory of your choosing. For the purposes of this demo I installed Java in /opt, and Payara Server in my home directory in a folder named payara:

/home/arjan/ payara

For convenience add the Java bin directory to your PATH and set the JAVA_HOME environment variable to the directory where you untarred Java (if you haven't done so already). In my case I extracted the Oracle JDK to /opt and set the PATH as follows:

export JAVA_HOME =/ opt / jdk1 . 8.0 _172

Java's bin directory was then added via:

export PATH = $PATH : $JAVA_HOME / bin

The PATH then looks as follows:

PATH =/ usr / local / sbin: / usr / local / bin: / usr / sbin: / usr / bin: / sbin: / bin: / usr / games: / usr / local / games: / snap / bin: / opt / jdk1 . 8.0 _172 / bin

Creating the Connection Pool - Via the Web Admin Console

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

cd / home / arjan / payara / payara5 / bin ./ asadmin start - domain

(from this point on we'll assume the current directory is /home/arjan/payara/payara5/bin)

Once the server has started you can access the web admin 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.XADataSource

Driver Vendor – H2

Click Next.

For this example I'll be using an in-memory database using H2 called "test". This is done by scrolling all the way down in the previous shown screen and adding the following property:

url = jdbc:h2:mem: test

The other properties can be left at their defaults:

(Note that the properties shown here are dynamically scanned from the DataSource implementation that's being used, which in case of H2 is org.h2.jdbcx.JdbcDataSource)

Now click Finish, accepting all other 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 - Via the Command Line

You can also create a connection pool using the asadmin command line tool with the following:

./ asadmin create - jdbc - connection - pool -- datasourceclassname

org . h2 . jdbcx . JdbcDataSource -- restype javax . sql . XADataSource --

property url = "jdbc\:h2\:mem\:test" test - pool2

Note how the colons have to be escaped here. This is needed since the unescaped ones are used to delimit multiple properties and their values. For instance --property user=root:password=test:port=3306. Also note that I'll be using "test-pool2" for the pool created via the command line.

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





asadmin ping - connection - pool test - pool2

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

Creating the Connection Pool - Manually via domain.xml

Creating a connection pool offline (when the server is not running) is possible by editing a file called domain.xml, which can be found in the (payara install dir)/glassfish/domains/(domain used)/config folder. For this example that would be:

/home/arjan/payara/payara5/glassfish/domains/domain1/config/domain.xml

A connection pool can be added in this XML document under the node domain → resources, using the jdbc-connection-pool element:

< domain ...> < resources > < jdbc - connection - pool name = "test-pool3" res - type = "javax.sql.XADataSource" datasource - classname = "org.h2.jdbcx.JdbcDataSource" > < property name = "url" value = "jdbc:h2:mem:test" /> </ jdbc - connection - pool > </ resources > </ domain >

Note that both the web admin console and command line util too will create an entry like shown above in the domain.xml file.

Creating the Connection Pool - Per Application Resources

The connection pools created above are server managed, global pools. This means they are applicable for all applications deployed to Payara Server. Via Java EE annotations as well as deployment descriptors, we can create those per application as well. There's on difference though, and that's that via the Java EE alternative a "data source" ("jdbc-resource"), is created together with the connection pool.

Using an annotation:

@DataSourceDefinition ( name = "java:app/MyApp/MyDS" , className = "org.h2.jdbcx.JdbcDataSource" , url = "jdbc:h2:mem:test" ) Using an entry in web . xml , ejb - jar . xml or application . xml : < data - source > < name > java: app / MyApp / MyDS </ name > < class - name > org . h2 . jdbcx . JdbcDataSource </ class - name > < url > jdbc:h2:mem: test </ url > </ data - source >

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. As a general rule of thumb though, if you find yourself having to constantly increase the maximum value, say from 200 to 300, from 300 to 500 etc, then you may have to look at your software architecture. For instance, in a transaction processing system, saving each transaction independently to a database could perhaps be replaced with a system where multiple transactions are queued first, and then saved to a database in one go using a JDBC or JPA batch.

Setting Min/Max Sizes - Via the Web Admin 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 - pool2 . steady - pool - size = 10

To set the maximum pool size:

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

Creating Min/Max Sizes - Manually via domain.xml

Min/Max Sizes can be added to the domain.xml document under the node domain → resources, using the jdbc-connection-pool element:

< domain ...> < resources > < jdbc - connection - pool name = "test-pool3" res - type = "javax.sql.XADataSource" datasource - classname = "org.h2.jdbcx.JdbcDataSource" steady - pool - size = "10" max - pool - size = "200" > < property name = "url" value = "jdbc:h2:mem:test" /> </ jdbc - connection - pool > </ resources > </ domain >

Creating Min/Max Sizes - Per Application Resources

Using an annotation:

@DataSourceDefinition ( name = "java:app/MyApp/MyDS" , className = "org.h2.jdbcx.JdbcDataSource" , initialPoolSize = 10 , minPoolSize = 10 , maxPoolSize = 200 , url = "jdbc:h2:mem:test" )

Using an entry in web.xml, ejb-jar.xml or application.xml:

< data - source > < name > java: app / MyApp / MyDS </ name > < class - name > org . h2 . jdbcx . JdbcDataSource </ class - name > < initial - pool - size > 10 </ initial - pool - size > < max - pool - size > 200 </ max - pool - size >

< min - pool - size > 10 </ min - pool - size > < url > jdbc:h2:mem: test </ url > </ data - source >

Note that Payara Server's steady-pool-size property translates to two standard properties here, while coincidentally max-pool-size has the exact same name in the standard descriptors.

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 Web Admin 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 – org.glassfish.api.jdbc.validation.H2ConnectionValidation

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 - pool2 . is - connection - validation - required = true

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

./ asadmin set resources . jdbc - connection - pool . test - pool2 . validation - classname = org . glassfish . api . jdbc . validation . H2ConnectionValidatio

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

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

Connection Validation - Manually via domain.xml

Connection Validation can be added to the domain.xml document under the node domain → resources, using the jdbc-connection-pool element:

< domain ...> < resources > < jdbc - connection - pool name = "test-pool3" res - type = "javax.sql.XADataSource" datasource - classname = "org.h2.jdbcx.JdbcDataSource" steady - pool - size = "10" max - pool - size = "200" is - connection - validation - required = "true" connection - validation - method = "custom-validation" validation - classname = "org.glassfish.api.jdbc.validation.H2ConnectionValidation" > < property name = "url" value = "jdbc:h2:mem:test" /> </ jdbc - connection - pool > </ resources > </ domain >

Connection Validation - Per Application Resources

Using an annotation:

@DataSourceDefinition ( name = "java:app/MyApp/MyDS" , className = "org.h2.jdbcx.JdbcDataSource" , initialPoolSize = 10 , minPoolSize = 10 , maxPoolSize = 200 , url = "jdbc:h2:mem:test" , properties = { "fish.payara.is-connection-validation-required=true" , "fish.payara.connection-validation-method=custom-validation" , "fish.payara.validation-classname=org.glassfish.api.jdbc.validation.H2ConnectionValidation" }) )

Using an entry in web.xml, ejb-jar.xml or application.xml:

< data - source > < name > java: app / MyApp / MyDS </ name > < class - name > org . h2 . jdbcx . JdbcDataSource </ class - name > < url > jdbc:h2:mem: test </ url > < property > < name > fish . payara . is - connection - validation - required </ name > < value > true </ value > </ property > < property > < name > fish . payara . connection - validation - method </ name > < value > custom - validation </ value > </ property > < property > < name > fish . payara . validation - classname </ name > < value > org . glassfish . api . jdbc . validation . H2ConnectionValidation </ value > </ property > <initial-pool-size> 10 </initial-pool-size>

<max-pool-size> 200 </max-pool-size>

<min-pool-size> 10 </min-pool-size>

</ data - source >

Note that there are no standard properties corresponding to connection validation. Payara Server supports setting these as vendor specific properties. These properties are normally mainly intended as data source properties. The Java EE / common annotations spec makes no clear distinction between properties intended for the data source and those intended for the connection pool. Payara Server makes this distinction by prefixing properties intended for the pool with "fish.payara.".

See "Advanced Connection Pool Properties in Deployment Descriptors" in the Payara Platform documentation for more information.

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 Web Admin 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 value

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 - pool2 . statement - leak - timeout - in - seconds = 5 ./ asadmin set resources . jdbc - connection - pool . test - pool2 . connection - leak - timeout - in - seconds = 5

Setting Statement and Connection Leak Detection - Manually via domain.xml

Statement and Connection Leak Detection can be added to the domain.xml document under the node domain → resources, using the jdbc-connection-pool element:

< domain ...> < resources > < jdbc - connection - pool name = "test-pool3" res - type = "javax.sql.XADataSource" datasource - classname = "org.h2.jdbcx.JdbcDataSource" steady - pool - size = "10" max - pool - size = "200" is - connection - validation - required = "true" connection - validation - method = "custom-validation" validation - classname = "org.glassfish.api.jdbc.validation.H2ConnectionValidation" connection - leak - timeout - in - seconds = "5" statement - leak - timeout - in - seconds = "5" > < property name = "url" value = "jdbc:h2:mem:test" /> </ jdbc - connection - pool > </ resources > </ domain >

Setting Statement and Connection Leak Detection - Per Application Resources

Using an annotation:

@DataSourceDefinition ( name = "java:app/MyApp/MyDS" , className = "org.h2.jdbcx.JdbcDataSource" , initialPoolSize = 10 , minPoolSize = 10 , maxPoolSize = 200 , url = "jdbc:h2:mem:test" , properties = { "fish.payara.is-connection-validation-required=true" , "fish.payara.connection-validation-method=custom-validation" , "fish.payara.validation-classname=org.glassfish.api.jdbc.validation.H2ConnectionValidation" , "fish.payara.connection-leak-timeout-in-seconds=5" , "fish.payara.statement-leak-timeout-in-seconds=5" }) )

Using an entry in web.xml, ejb-jar.xml or application.xml:

< data - source > < name > java: app / MyApp / MyDS </ name > < class - name > org . h2 . jdbcx . JdbcDataSource </ class - name >

< url > jdbc:h2:mem: test </ url > < property > < name > fish . payara . is - connection - validation - required </ name > < value > true </ value > </ property > < property > < name > fish . payara . connection - validation - method </ name > < value > custom - validation </ value > </ property > < property > < name > fish . payara . validation - classname </ name > < value > org . glassfish . api . jdbc . validation . H2ConnectionValidation </ value > </ property > < property > < name > fish . payara . connection - leak - timeout - in - seconds </ name > < value > 5 </ value > </ property > < property > < name > fish . payara . statement - leak - timeout - in - seconds </ name > < value > 5 </ value > </ property >

<initial-pool-size> 10 </initial-pool-size>

<max-pool-size> 200 </max-pool-size>

<min-pool-size> 10 </min

-pool-size> </ data - source >

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 Payara Server 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.

Need Help With Payara Platform in Production?

Learn about Payara Enterprise