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

Comments:

Nice feature. Would be even more useful if there was a way to also get how long that query took? I am also sometimes looking for slow queries, like log only the ones that take longer than 1 second, for example. Very useful for performance work.

Posted by Sreeram Duvur on February 28, 2011 at 08:50 PM IST #

Post a Comment:
  • HTML Syntax: NOT allowed
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