Monday Feb 28, 2011

Application based Connection Pool monitoring

A brand new feature in the latest GlassFish 3.1 is monitoring a connection pool based on the application name. Earlier, monitoring statistics could be got at the connection pool level like 

asadmin get server.resources.jdbc-connection-pool.<POOL_NAME>.\*

With this new feature, it is possible to

  1. Get the number of connections in use by an application for the specified connection pool.

  2. Get the number of connections acquired from the connection pool by an application

  3. Get the number of connections released to the connection pool by an application

When monitoring is set to HIGH, using the command line interface, the above information could be got as follows.

asadmin get server.resources.jdbc-connection-pool.application1.
asadmin get server.resources.jdbc-connection-pool.application1.
asadmin get server.resources.jdbc-connection-pool.application1.

Using AMX (Application Server Management Extenstions), the following program could be used to get the monitoring statistics : numconnfree, numconnacquired and numconnreleased.

public int getValue(String statName) throws Exception {
final String urlStr = "service:jmx:rmi:///jndi/rmi://" +
HOST_NAME + ":" + JMX_PORT + "/jmxrmi";
final JMXServiceURL url = new JMXServiceURL(urlStr);
final JMXConnector jmxConn = JMXConnectorFactory.connect(url);
final MBeanServerConnection connection =
ObjectName objectName =
new ObjectName("amx:pp=/mon/server-mon[server],
name=resources/testPool/application1"); returnValue =
connection.getAttribute(objectName, statName);
return new Integer(returnValue.get("count").toString());

The above method gets the value of a monitoring statistic for a particular jdbc connection pool (testPool in this case) for application1. To retrieve attribute values for a connector connection pool, use the ObjectName given below instead


Statement cache monitoring

My earlier blog on Statement Caching described how PreparedStatement, CallableStatement, Statement objects are cached to provide performance improvement.

GlassFish maintains a statement cache to cache the statements that are repeatedly executed by applications. It is considered a cache hit when one of the statement objects from the cache is returned and a cache miss when the statement object is newly created as it was missing in the cache.

Now, the number of statement cache hits and misses can be monitored using the monitoring infrastructure of GlassFish. When statement-cache-size is set to a positive non zero number and monitoring is set to HIGH, the statement cache hit/miss statistics can be got using the command line interface.

A sample of the output is shown below.

server.resources.testPool.numstatementcachehit-count = 3
server.resources.testPool.numstatementcachemiss-count = 1

To get the monitoring statistics using AMX programmatically, use the following objectName


Statement leak detection and reclaim

Statement leak detection is a new feature to detect any statements that have not been closed by the applications for a specified time period.

Why is this important?

Applications might run out of cursors if the statement objects are not
closed and hence detecting them as leaks can help developers and
administrators to make sure that the statement objects are indeed closed after use.

How to know if there is a statement leak?

The stack trace of the caller that creates the statement is logged in the server.log when a statement leak is detected.

Enabling Statement leak detection

Statement leak timeout can be enabled by setting a connection pool configuration attribute statement-leak-timeout-in-seconds. Default value of this attribute is zero, indicating that the statement leak detection is turned off. Leak detection is enabled when set to a positive non-zero value. After this timeout, the statement object is considered to be leaked.

Enabling Statement leak reclaim

Statement leak reclaim can be enabled by setting a connection pool configuration attribute statement-leak-reclaim. By default, this attribute is set to false and the statement leak
reclaim is disabled. For statement reclaim to work, statement leak
timeout need to be enabled. Once a statement leak is detected, leaked
statement is reclaimed. During a statement reclaim, the leaked
statement(s) is closed.

Points to note

  • Connection leak timeout should be greater than the Statement leak

  • Statement Timeout should be lesser than the Statement leak

Monitoring Statement leaks
The total number of statement leaks for the jdbc connection pool at the sample time can be got using the command line interface. When monitoring is set to HIGH, this monitoring statistic is of the form

server.resources.testPool.numpotentialstatementleak-count = 6

The corresponding amx objectName for collecting this monitoring statistic programmatically is


Statement leak detection is done for all new connections got in an
application for an active pool. Similarly, the monitoring statistics are collected for these new connections from which statements are created.

Tracing SQL queries & monitoring

In GlassFish v3, you could trace the SQL statements executed by an application using a specific jdbc connection pool. This helps administrators filter server.log for SQL statement analyses. In GlassFish 3.1, the frequently used SQL queries could be monitored.

Short description about SQL tracing

Attribute sql-trace-listeners can be set to a comma separated list of listener implementations for a JDBC connection pool.This turns on the SQL tracing feature.

The public interface org.glassfish.api.jdbc.SQLTraceListener can be implemented by administrators/developers to provide a means of recording the org.glassfish.api.jdbc.SQLTraceRecord objects.

GlassFish provides an inbuilt SQL tracing logger to log the SQL operations in the form of SQLTraceRecord in server.log file. "javax.enterprise.resource.sqltrace" is the module name using which SQL operations are logged. SQL traces are logged as FINE messages along with the module name so they can be easily filtered.

A sample SQL trace record from server.log looks like

MethodName=sqlTrace;|ThreadID=77 | ThreadName=p: thread-pool-1; w: 6 |
TimeStamp=1259317012202 |
ClassName=com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40 |
MethodName=executeUpdate |
arg[0]=insert into table1(colName) values(100) |
arg[1]=columnNames | |#]

Above trace shows that an executeUpdate(String sql, String columnNames) is being done.

New feature : Displaying frequently used queries

The top 'n' most frequently used sql queries by applications can be got for a specific time window. There are 2 properties

  • number-of-top-queries-to-report representing the 'n' top queries to be reported. Default value of this property is 10.

  • time-to-keep-queries-in-minutes representing the time window for which queries will be maintained in the cache. Default value is 5 minutes.

that could be set on a jdbc connection pool on which SQL tracing has been turned on. Please note that the monitoring statistics are collected only for standard JDBC calls. This feature helps administrators to a greater extent. The above properties could be set using Command Line Interface as

asadmin> set server.resources.jdbc-connection-pool.<POOL_NAME>.
asadmin> set server.resources.jdbc-connection-pool.<POOL_NAME>.

Monitoring information could be got using Command Line Interface as

server.resources.testPool.frequsedsqlqueries-current =

Using AMX, the following objectName could be used to get the monitoring statistic "frequsedsqlqueries" programmatically.


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.

Thursday Nov 06, 2008

JDBC support in V3 Prelude

The latest application server of Sun – GlassFish V3 Prelude includes JDBC support. The JDBC Connection pooling infrastructure in V3 prelude is same as that of GlassFish 9.1 application server.

Some of the cool features that could be useful to you are :

  • Connection Leak Tracing

    •  enable the connection-leak-timeout to a certain value along with connection-leak-reclaim to true to avoid the potential connection leaks in your application

  • Validation calls optimization

    • Set the validate atmost once period to a value within which the validation will be carried out atmost once.

  • Associate with thread

    • To reuse connections in a multi threaded application to avoid the overhead of getting a connection from the pool.

and many more...

Things that have changed:

Setting the pool properties/attributes using asadmin set command would be like:

asadmin set --value=10 server.resources.jdbc-connection-pool.DerbyPool.steady-pool-size

asadmin set --value=newUser

Note that the first asadmin set does not need a server restart whereas the second one mandates restart.

There are many more attractive things coming up in the next release of V3 for JDBC. One of them is advanced pool monitoring. The monitoring framework has undergone a major change in v3 prelude.

Try the latest V3 prelude and its connection pooling features. Any issues? Mail .

Monday Oct 13, 2008

JDBC Pool Monitoring using Management Rules

After my basic blog blog about creating and writing mbeans and self management rules, here is a post on how to monitor a certain attribute of a jdbc connection pool using Self Management Rule.
I would take for example, the connection validation failures. The Monitoring framework of glassfish provides statistics on various attributes including an attribute specially for the number of connection validation failures. Here are the steps :

1. First of all, enable the monitoring for JDBC Connection Pool to HIGH from the admin console or from CLI.

2. Create JDBC Connection Pool/JDBC Resource for this exercise : conValPool and jdbc/conval respectively.

3. We would be monitoring the attribute for connection validation failure on this pool conValPool.

4. The objectName for this conValPool for monitoring would be "com.sun.appserv:name=conValPool,type=jdbc–connection–pool,
I got this objectName from listing all mbeans and querying the names for conValPool. Note that this mbean is specifically for monitoring this pool since the category is monitor.

5. When i open jconsole and login to it, i see that com.sun.appserv  section, there is a head called conValPool. Under this, we see a server element if we browse down the tree. On the right pane, we can see all the attributes and i would take a note of the attribute with name numconnfailedvalidation–count.
I would use this attribute in my custom mbean event.

6. Create an mbean called ValidationMonitor for getting the value of numconnfailedvalidation–count at any point of time. It would have code that gets the value as follows

MBeanServer server = ManagementFactory.getPlatformMBeanServer();
numConnFailVal = (Long) server.getAttribute(new
    category=monitor,server=server”) , "numconnfailedvalidation–count";)

I use the getAttribute method on the MBeanServer with the above mentioned ObjectName and the attribute name. The numConnFailVal is the attribute that will be returned whenever this mbean is invoked.

7. Create an mbean called ValidationAction for the action part of the management rule. This action would be triggered every time some threshold is crossed as part of the rule that we are going to create shortly. The action as of now is going to be a simple System.out.println  to the server.log as follows

public class ValidationAction implements NotificationListener,
    com.sun.example.action.ValidationActionMBean {
    public synchronized void handleNotification(Notification notification, Object handback) {
         try {
System.out.println("Validation  Failure Threshold Notification: " +
          } catch(Exception ex) {

Note that the action mbean implements the NotificationListener interface.

8. Coming to the part of creating a management rule to hook up this action and event, we log into the admin console and deploy the custom mbeans created above.
Then under the Management Rules link under Configuration, create a new management rule called ValFailRule. Specify the event type as monitor and other values as follows

observed MBean value : user:impl–class–name=com.sun.example.mbeans.ValidationMonitor,

observedAttribute value : numConnFailVal
Monitor type to Counter
Granularity period to 1000 milliseconds so that every second the rule would check for the numConnFailVal value.
Number type to long since the return value from our mbean is of type long.
Init Threshold value to 2 so after 2 failed validations, the rule is going to write something to the
server log.
Offset to 1
Select the validationaction from the list under Action section for the action mbean name.

9. The management rule is successfully setup and once you have a test running that would change the validation failure values, the rule would play its role. Whenever the numConnFailVal value goes above 2, the following can be seen in the server.log

Validation  Failure Threshold[

43f6a09f5ae1be9f0e51;|Logging selfmanagement event :

SEVERE messages are because the log level was set to SEVERE while the rule was created.

This is just an example of how a JDBC pool monitoring attribute can be observed via mbeans and action taken above a threshold using a management rule. Any other meaningful action could be taken instead of the above.

Hope this is useful. :)

Wednesday Jun 04, 2008

Troubleshooting locale problems with updatetool (GlassFish v3)

When launching updatetool of GlassFish v3 on ubuntu, sometimes there are locale problems like the one below :

ValueError: unknown locale: en_IN 

 Troubleshooting this :

#export LC_ALL=C

and then launch updatetool.




« July 2016