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.
numconnused-current
asadmin get server.resources.jdbc-connection-pool.application1.
numconnacquired-count
asadmin get server.resources.jdbc-connection-pool.application1.
numconnreleased-count


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 =
jmxConn.getMBeanServerConnection();
ObjectName objectName =
new ObjectName("amx:pp=/mon/server-mon[server],
type=jdbc-connection-pool-app-mon,
name=resources/testPool/application1");
javax.management.openmbean.CompositeDataSupport returnValue =
(javax.management.openmbean.CompositeDataSupport)
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



amx:pp=/mon/server-mon[server],
type=connector-connection-pool-mon,
name=resources/testPool/application1



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


amx:pp=/mon/server-mon[server],type=jdbcra-mon,name=resources/testPool


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
    timeout.

  • Statement Timeout should be lesser than the Statement leak
    timeout.

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

amx:pp=/mon/server-mon[server],type=jdbcra-mon,name=resources/testPool 

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


[#|2009-11-27T15:46:52.202+0530|FINE|glassfishv3.0|
javax.enterprise.resource.sqltrace.com.sun.gjc.util|
_ThreadID=29;_ThreadName=Thread-1;
ClassName=com.sun.gjc.util.SQLTraceLogger;
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>.
property.number-of-top-queries-to-report=15
asadmin> set server.resources.jdbc-connection-pool.<POOL_NAME>.
property.time-to-keep-queries-in-minutes=10

Monitoring information could be got using Command Line Interface as


server.resources.testPool.frequsedsqlqueries-current =
query1
query2
query3
...

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

amx:pp=/mon/server-mon[server],type=jdbcra-mon,name=resources/testPool

About

shalini_m

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today