Wednesday Dec 23, 2009

Ping Connection Pool

While a JDBC/Connector Connection pool is created, there was no way of identifying the erroneous values for some of the attributes. With the introduction of a new attribute "ping" in GlassFish V3, its possible to do so. 

An incorrect value of a database connectivity property is another case where pooling infrastructure does not warn the user when pool is created or reconfigured.

For example, if a user tries to set a wrong value for isolation level during creation like :

asadmin create–jdbc–connection–pool ........ ––isolationlevel xyz poolName

The erroneous isolation level is identified only at runtime when the pool is initialized. A get connection from an application following this would throw a WARNING message. This can be identified at an earlier stage by setting a flag like

asadmin create–jdbc–connection–pool ........ ––isolationlevel xyz --ping=true poolName

The default value of this flag is false.


Ping CLI attribute


Ping admin console


The Ping button in admin console as well as the admin CLI command asadmin ping-connection-pool are still supported in GlassFish V3.

[1] Ping CLI Command

Tuesday Dec 22, 2009

Disable Connection Pooling

Connection pooling allows you to reuse connections from a pool
instead of creating a new connection object everytime. If you desire to
turn off the connection pooling in GlassFish V3, there is a new
attribute that could be used.

By default, connection pooling is enabled in both jdbc as well as
connector connection pools. To disable this, set the "pooling" attribute to false.

In the earlier releases of GlassFish, there was a system property called "com.sun.enterprise.Connectors.SwitchoffACCConnectionPooling"
that could be set to true to turn off connection pooling in application
clients. This property is still supported in the new GlassFish V3.

How to set the flag

In administration CLI, use the following :

asadmin set server.resources.jdbc-connection-pool.

In admin GUI http://localhost:4848, see the Pooling checkbox.

Pooling in Admin Console

Effects observed

  • Associate with thread functionality is invalid

  • Flush connection pool cannot be done

Attributes useful only in pooled environment

There are a few attributes that are useful only in pooled environment hence a WARNING message is seen in the server.log when they are used.

  • Connection validation

  • Validate Atmost Once (Related to Connection Validation)

  • Match Connections

  • Max Connection Usage

  • Idle Timeout

Thursday Dec 17, 2009

Statement Caching in GlassFish V3

In general, Statement Caching is a functionality of a jdbc driver. There do exist a few jdbc drivers that do not support caching of PreparedStatement, CallableStatement and Statement objects.

A new feature in GlassFish V3, Statement caching improves performance by searching for a match in the cache and returning the object instead of preparing the statement object every time. This is useful for applications that repeatedly execute the above type of statements. Pooling infrastructure in GlassFish V3 does what is depicted in the figure below.

Statement Cache Process

GlassFish V3 uses a Least Recently Used (LRU) strategy for evicting statement objects from the cache. Every time a new statement is parsed and created, there is an overhead and to overcome this, set statementcachesize attribute of jdbc connection pool to a non–zero value.


Create jdbc connection pool

Statement cache Size in CLI


Statement cache Size in GUI

Effects of statement caching on pool

When Statement caching is set to a non-negative value, and Flush connection pool is executed, connections in the statement cache are recreated.

Wednesday Dec 16, 2009

JDBC in GlassFish V3

GlassFish V3, the first application server in the world that supports JavaEE6  is released !!!! For the JDBC users, there are some great features introduced in the newest version of GlassFish.

New features

Flush Connection Pool

To reinitialize aged/old connections in a connection pool. There is no need to reconfigure the pool to kill/destroy live connections.


The existing Ping button in admin console and asadmin ping-connection-pool reveal the unsupported values of configured attributes of a connection pool only at the time of usage (or runtime). A pool configured with "ping" attribute identifies erroneous values at the time of creation of the pool.

java.sql.Driver based Pooling Support

Mainly for applications that use java.sql.Driver  implementations, to configure non-compliant jdbc drivers.

Disable Pooling

Disable connection pooling by just setting a flag "pooling" to false. The existing system property com.sun.enterprise.connectors.SwitchoffACCConnectionPooling was useful only for application clients. This feature is for the non appclient pools.

Statement Caching

Cache Statement, PreparedStatement, CallableStatement objects executed repeatedly by applications to improve performance. Some JDBC drivers do not support caching and this feature comes in handy.

Custom Validation

Specify your own implementation for performing a connection validation. A custom implementation could be made available to the application server and used to perform validation when connection validation is turned ON. Validation routines could be performance oriented or database specific.

Init SQL

Execute a SQL query during connection creation. Mainly to set request/session specific properties.

Introspection of JDBC Drivers

A really useful feature that introspects and lists datasource/driver classnames based on the database vendor and resource type in the administration console. User does not need to remember the classnames anymore for a strange uncommon JDBC driver used with GlassFish.

Tracing SQL Statements

Trace SQL statements executed by your application using a jdbc connection pool. Administrators can filter the server.log for easier SQL statement analyses.

What has changed in GlassFish V3

  • Connection validation method has been defaulted to "table" as the auto–commit and meta–data values are cached by most of the JDBC drivers

  • GlassFish V3 pooling infrastructure will provide wrapped objects for Statement, Prepared/Callable Statement, ResultSet and DatabaseMetaData by default

    • wrap–jdbc–objects defaulted to true


  • Monitoring support is provided in GlassFish V3 for JDBC using the new probe provider framework.

  • Smart admin GUI for JDBC leading to easier user administration

    • ex: listing of connection validation table names by introspection

    • ex: listing of jdbc classnames for a particular vendor

More on the above topics (new features) are coming soon. Watch out!

Flush Connection Pool

Re-initialize connections established in the pool without reconfiguration? – Flush Connection Pool is the solution.

This is a new feature in the latest GlassFish V3 that works to recreate the connections in the jdbc connection pool and brings the pool back to the steady pool size. Administrator's work is simplified by just executing the command (CLI) or pressing the Flush button in the GUI.


  • Live connections are killed/destroyed

  • Existing transactions are lost & hence should be retired

  • Pool brought to steady pool size after flush


max-pool-size set to 5
steady-pool-size set to 5 (5 connections are initialized in the pool when it is created)

We would verify how Flush works based on physical connection IDs

An application that uses the jdbc resource of this connection pool gets 4 connections and closes them everytime in a loop. In this process, the logical connections are closed and returned back to the pool. The physical connection handles [1] for these 4 connections would be the same.

When a Flush is executed after this, the 5 connections that are in the pool are killed and recreated. When a 5th connection is got from the pool, the physical connection handle would be different.


Flush connection pool can be invoked in multiple ways :

Admin Console Flush

Some of the WARNING messages you might encounter when database server is not up or pool is not initialized and Flush is invoked :


[1] Obtain physical connection from a Wrapped connection

Tuesday Dec 15, 2009

Custom Validation in GlassFish V3

Connection Validation mechanism in GlassFish has been extended to provide a new type of validation mechanism – Custom Validation. Users can provide their own implementation to validate a connection during each application request.

During times when database server is restarted or a network failure, connections could become stale in the connection pool. When such connections are used by the applications, exceptions are thrown that connection got is invalid. There are types of connection validation mechanisms already existent in GlassFish : autocommit, metadata and table.

The connection-validation-method attribute can be set to custom-validation. This validation mechanism could could aid users to implement faster validation routines. If connection validation fails, pooling infrastructure will return the next valid connection or create a new connection to return to the application.

GlassFish provides a public interface org.glassfish.api.jdbc.ConnectionValidation for the users to implement and provide value to an attribute validation-classname.

Setting Custom Validation

Turn on Validation, set validation type to custom-validation and set an appropriate implementation class with the entire package name to validation-classname.

Turn on validation

Set to custom-validation

Set validation classname

In Admin console (http://localhost:4848) Connection validation settings can be made in the advanced tab.

Custom Validation mechanisms in GF

There are some default validation mechanisms provided by GlassFish for the users to use for certain databases. These are listed in the admin console against the validation classname field.

  • org.glassfish.api.jdbc.validation.DerbyConnectionValidation

  • org.glassfish.api.jdbc.validation.MySQLConnectionValidation

  • org.glassfish.api.jdbc.validation.PostgresConnectionValidation

  • org.glassfish.api.jdbc.validation.OracleConnectionValidation

There is also a generic validation implementation provided for jdbc 4.0 compliant drivers

  • org.glassfish.api.jdbc.validation.JDBC40ConnectionValidation


[1] Connection Validation in GlassFish

Tuesday Feb 10, 2009

JDBC Pool Manager - Max Pool Size Tuning

Max pool size is the maximum number of connections to the database at any point of time. This value can be configured as a jdbc connection pool attribute "max–pool–size". The default value of this attribute is 32. 

Some databases have license restrictions on how many connections it can allow. This attribute is configured based on such parameters.  This parameter can also be configured from the Performance Advisor page in administration console of GlassFish. Some important points to note here :

  • If one or more jdbc connection pools are selected in the JDBC Pool Manager, there is an option to specify a "Max Connections" value for each pool according to the requirement. 

  • The Default Max Connections set to a certain value overrides the settings of the max–pool–size of the selected jdbc connection pools.

On a clustered environment, when new instances are added to the cluster, the max–pool–size on each instance may need a reconfiguration. This should also be based on the instance weights. A particular instance may have a higher instance weight, say, to provide faster service to privileged customers.

  • Create a node–agent called na1

  • Create a cluster "mycluster" with 2 instances "instance1" (weight 40) and "instance2" (weight 60)

  • Start the cluster "mycluster"

  • Create a new JDBC Connection pool "mypool" with default settings

  • Create a new JDBC resource referring the above connection pool for target "mycluster" : "jdbc/myresource"

  • Create an application that refers this jdbc resource on "mycluster" target

Clustered environment

  • In the performance advisor link of admin console, specify the default max connections for "mypool" as 100. Enable this rule on "mycluster" by choosing "mycluster" for target. Jdbc Rule Manager

Setting target as mycluster

After these steps, the "mycluster" should be restarted to see the max pool size reconfiguration in each of the instances "instance1" and "instance2".

You would see, for "instance1", the max pool size is recalculated to 40

instance1 log from admin console

and "instance2" to 60 based on their instance weights.

instance2 log from admin console

  • Add another new instance "instance3" to "mycluster" with weight of 100.

  • Restart "mycluster".

Once the cluster is restarted with the new instances addition, the max pool size of instance1 becomes 20, instance2 becomes 30 and instance3 becomes 50. This is based on the instance weights and the default max connections value we specified for the jdbc connection pool. instance3 gets more number of max connections to the database since its instance weight is higher.

instance1 log from admin console after instance addition
instance2 log from admin console after instance addition
instance3 log from admin console after instance addition

This is a very useful rule to configure the maximum pool size of jdbc connection pools on cluster startup. Manual intervention is prevented and this easy–to–use feature can be configured to avoid errors in complex cluster scenarios.

JDBC Pool Manager - Steady Pool Size Tuning

Sun has recently announced the "GlassFish Enterprise Manager". I would talk about a particular feature : "Performance Advisor". One of the management rules of Performance Advisor is the JDBC Pool Manager. Jdbc pool manager tunes the steady pool size and maximum pool sizes of jdbc connection pools based on the load. This blog would briefly talk about steady pool size tuning.


Consider a cricket website. On a non–match day, there will be a minimum number of requests to this website. On a match–day, even if it is a working day, people tend to use such websites to check the runs and number of wickets. The number of requests will pour in as a result and for maximum resource utilization, the steady pool size needs to go up. If done manually, the administrator will have to configure this steady pool size.

 After  a while, number of requests are bound to come down. As a result, the steady pool size would have to be brought down. This constant recomputing of steady pool size based on the load is automated by the JDBC Pool Manager.

How is it Configured?

Assume there is a jdbc connection pool : cricket–info–pool. jdbc/cric is the resource that references this pool. The cricket–info–pool is set to a default steady pool size of 8. An application that uses the jdbc resource to get information from a database is set up. Client access this application to retrieve information. JDBC Pool Manager rule can set up from admin GUI.

  • The Default Max Connections is the maximum number of connections that can be provided from the pool at any point of time.

  • The management rule tunes the steady pool size in Sampling Frequency number of seconds.

  • The sample size denoted by Number of Samples is used to calculate the moving average value for number of connections.

Marking cricket-info-pool under PoolNames will configure the rule for this connection pool.  Click on Save after entering all the details.The domain needs to be restarted now, as the rule is configured for the target "server".

On domain restart, you can observe from the server.log that the steady pool size is recalculated to a certain value. After a while, assuming there are 500 client requests for a period of 10 minutes.

The steady pool size is recalculated every 60 seconds (default sampling frequency) to a new value suitable for the current number of requests.




« February 2017