P6Spy For Potential SQL Profiling

[nickman]

Clebert;

I took alook at the P6 API and based on that, it would be possible to collect the following per unique processed SQL statement:

1. Number of invocations
2. Raw elapsed time (and therefore High/Low/Average/Last)


Pros:
Simple architecture could be implemented purely as use of P6 and a log4j appender based profiling adaptor.
Reasobaly useful level of data collection.
Quick to implement vs. implementing full JDBC instrospection or proxy.

Cons:
Limited metrics collection. (No CPU, Wait or Block Data)
Somewhat inflexible.
Third party dependency.

//Nicholas

[clebert.suconic]

I was willing to create a Proxy driver instead of using P6.

I could use P6 as an implementation example, but not use any code from P6. (I think that's legal, right?)

Since we know how a proxy driver works, it's just a matter of writing testcases for it.

Another reason for creating the driver is a better integration between the driver and the metrics colelctor. We are planning to run this profiling code in production, and IMO if we could have this code really lite we could be really successful on capturing and associating Interceptors and Queries. I'm really excited about such idea.


Clebert Suconic

[clebert.suconic]

I'm not saying we can't use P6. It's just at this point I think a new driver is better. (Unless we can find a lite way of doing this with P6)

[wlouth_jboss]

Hi Nicholas,

JXInsight already provides the most comprehensive SQL and transaction pattern analysis on the market and can help solve many of the real world performance problems in product with its unique concurrency analysis at many levels: SQL, Transaction, Trace. The JXInsight console has many visualizations built in for SQL analysis and with its built in SQL parser it understands the execution context whereas most profilers adding SQL support just see strings with a little tag for typing.

Considering that JXInsight (formerly JDBInsight) has been around for a number of years and proven in a production environment it would be hard pressed for any new development to come close to the feature set.

Is there really a need to reinvent the another wheel that would remain under par for a significant period? I am not trying to stop people working on a pet project but surely one must question the reasoning behind any new venture when there are viable solutions today on the market that cannot be matched.

JXInsight is also a distributed tracing solution that can trace across multiple tiers.

I have recently blogged about the risks in using commercial low level Java code profilers in production applications.

http://blog.jinspired.com

We will also publish comparative benchmark figures for open source solutions pretty soon.


By the way JXInsight has an open Tracer API that allows developers to create their own trace extensions. There is also support for custom trace metric groups and UI integrations.

Regards,

William Louth
CTO, JInspired

"JEE tuning, tracing, testing and monitoring with JXInsight"
http://www.jinspired.com

[starksm64]

And why are you posting about a proprietary offering in our profiler design forum? This is for design discussions around our bundled profiling support.

[stevesouza]

check out http://www.jamonapi.com for a proxy driver that is similar to p6spy. There is a live demo available at http://www.ssouza.com/jamon

It is opensource too, and could fit into jboss in other areas. Even if you guys proceed with your own solutions, some of the ideas may prove useful.