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.

.NET thread local storage implementation kicks my butt

I got skooled today in .NET. I wrote a thread-local storage abstraction using named data slots some time ago. Avoid named data slots at all costs! Use the ThreadStaticAttribute instead. Not only is the ThreadStaticAttribute much easier to implement, but it performs better also. I had to wait till my code was in a near-prod environment before enough load could be generated to cause the race condition to occur. It took me about 2 days of debugging to find the issue. Most importantly, read the damn documentation before you attempt to write some threading code. There are some subtle issues in the .NET Base Class Library that I would have avoided if I had read the MSDN site. Very embarrassing.

From Microsoft:

The .NET Framework provides two mechanisms for using thread local storage (TLS): thread-relative static fields (that is, fields that are marked with the ThreadStaticAttribute attribute) and data slots. Thread-relative static fields provide much better performance than data slots, and enable compile-time type checking. For more information about using TLS, see Thread Local Storage: Thread-Relative Static Fields and Data Slots. (http://msdn.microsoft.com/en-us/library/system.threading.thread.allocatenameddataslot.aspx)

nmon performance monitoring tool

One of the takeaways from yesterday's Tomcat Expert Series seminar was the use of nmon (Nigel's monitor), a curses-based performance monitoring tool. I had never heard of nmon before yesterday, so I was curious why I missed this tool in my IT travels. Seems the tool has originated out of IBM, originally on IBM's AIX Unix variant. The tool has been ported to Linux. More information here. If you're deploying onto Linux or AIX, it might be worthwhile to take a look at this tool and add it to your systems. Unfortunately it does not look like there's a version for Mac OS X yet. Hey, OS X is Unix too ;-)