Tuesday, September 06, 2011

Tracing and profiling SQL in Grails using log4jdbc

I spent some time today tracing and profiling SQL in one of the Grails applications that I support.  I was looking around for proxy JDBC driver and happened on log4jdbc.  It's similar to p6spy, but it seems to be actively developed and supported.  Downloaded the driver, dropped it in my lib directory, and changed the logging and datasource configurations a bit in Grails and I was up and running.  Very handy.  I made copious use of the SQL timings profiling today.  There are many other options for tracing and profile with this tool.  Here are my changes to Config.groovy for enabling SQL timings to all SQL statements:

Config.groovy change to enable logging of SQL information from log4jdbc:

log4j = {
info 'org.codehaus.groovy.grails.web.servlet',

...
'grails.app',
'jdbc.sqltiming'
}

 

DataSource.groovy changes to enable log4jdbc:

development {       
dataSource {
driverClassName = "net.sf.log4jdbc.DriverSpy"
url = "jdbc:log4jdbc:mysql://localhost/mydb-DEV?useUnicode=true&characterEncoding=UTF-8&useCursorFetch=true&autoReconnect=true"
}   
}

 

Can't say enough good things about this tool.  Really helped me zero in on some queries that were performing poorly with large data sets.

2 comments: