What is the FusionReactor datasource monitoring feature? Why would I use it? Powerful stuff!

Note: This blog post is from 2008. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.

What is the datasource monitoring feature? Why would I use it? Powerful stuff!

The datasource monitoring feature, referred to in the docs as the "JDBC wrapper", is a low-overhead way to configure FusionReactor so that it captures information about each query (against a given datasource), for every request, in detail and in aggregate. It captures and reports:

• the SQL statement (including CFQUERYPARAM values)
• the execution time (time spent in the database, and time spent getting it from the database to CF)
• the number of rows returned
• the CFML template and line of code where it was executed
• and more

This query information is tracked per request and also in aggregate across all requests (in the graphs in the Enterprise Dashboard and System Metrics pages, the Longest JDBC page, and other places.)

It's really powerful information to have when you're trying to understand why and how your page, application, or server it performing.

You can see more about using the information in these feature focus pages at the FR site: What are System Metrics? and Resource Graphs, among others.

As I said in the last entry, you can learn more about setting up the FR JDBC wrapper feature (including the simple steps on how to implement it--which have been updated in the FusionReactor 3 docs) in the Tutorial (pdf) and User Guide (pdf).

What's the overhead of this datasource monitoring? What's it doing?

That's a natural and reasonable question. The folks at Intergral (makers of FusionReactor) say that it's very low overhead, and they've done considerable testing (millions of requests before each release) to confirm it.

Maybe it would help to understand how it works. It's quite simple, really. When you configure a datasource to be wrapped (per the instructions pointed to above), you're basically just implementing an alternative JDBC driver (FusionReactor's) to literally "wrap" the driver that was being used originally by your datasource.

Your code continues to use the same Datasource Name, but the change of the datasource causes CF to pass the SQL first to the FusionReactor JDBC wrapper, which takes note of the starting time, the SQL statement, etc., and then passes it the query on to the real (original) database driver. That then passes it to the DB, and gets the result, and before it's passed back to CF, it goes back through the FusionReactor wrapper, which notes the execution time, recordcount, etc.

So you see, the wrapper is really quite lightweight. It's just an observer, watching what's going to and from the database. The info is then written to FusionReactor's JDBC log (if enabled) and stored in FusionReactor's small memory space for presentation in the various interfaces that show JDBC status information. Again there's more about this in the documentation.

Some other tips on the JDBC Wrapper

To see information on what queries were executed (if the datasource was wrapped), see the bottom of the request details page (for either a running request or the request history page). Further, note the available JDBC tab in that request details page. That will show each SQL statement and the details (time, count, etc.). The bottom of that page will also show counts and averages for all the queries in that request.

You'll see at the bottom of that page that you can also configure the JDBC settings page to restrict how many queries are shown in the JDBC request details, and you can limit it to only those exceeding a certain amount of time, as well as whether to show them in chronological order or executiontime order.

I'll note as well that if you do implement the JDBC wrapper feature, you'll probably want to enable JDBC logging, which is disabled by default. This writes all the detail above to a log file, for each SQL statement, which can be great for either post-mortem analysis (after a crash) or for trend analysis (perhaps across several restarts). You can choose to limit the logging to queries slower than a set number of milliseconds (separate from the control above about what queries to show in the request detail page.)See the JDBC>JDBC Settings option in the left nav bar of the FR interface.

The wrapper configuration for each DSN itself also offers still more features that many miss, so check out the docs. The Tutorial concludes with an example of using the rowlimiter feature to put the brakes on a rogue page, while the user guide details this and all the available features of the wrapper.

Learning more about FusionReactor

For more on FusionReactor in general, see their web site or my past blog entries on it.

(And yes, before anyone else would point it out in a comment, both the CF 8 Server Monitor and SeeFusion also provide this sort of JDBC query information. In the CF 8 Monitor, it's built-in (no need to configure the DSNs at all). And in SeeFusion, there's no need to manually configure each DSN, as there's a one-button configuration option. Intergral are said to be working on something similar for a future release of FR, so some of the concepts above apply across the monitors.)

FusionReactor works with ColdFusion Standard and Enterprise in version 6, 7, and 8, as well as Railo, Open BlueDragon, and BlueDragon/J2EE, and indeed any J2EE application server or Servlet engine. (BTW, technically, the term J2EE has been obsoleted by the more version-neutral Java EE or JEE. So many things in CF docs and related tools still refer to it as J2EE simply because most CFers aren't paying that close attention to such details, so there's been little motivation to make the clarification.)

PS: Why is it called the JDBC wrapper feature, instead of the datasource monitor?

That's an interesting question. It might feel a little clumsy calling it the JDBC wrapper. I wonder sometimes if CF users may miss the feature. Hopefully the explanation above about how it works helps. But you may wonder why it's not called the "datasource monitor", which might be friendlier for CFers.

But here's the thing: as I said a moment ago, FusionReactor is not JUST a CF server monitor. It can be used by any J2EE server or servlet engine. And not all J2EE app servers would use a concept of a "datasource".

Consider a Tomcat developer who might write a servlet or JSP that calls a database. They could leverage this JDBC wrapper feature (and all of FusionReactor's features) just as readily, though instead of changing the configuration of a datasource they would modify the JDBC URL in their code or a config file. That said, there are some J2EE servers that do use JNDI datasources.

So the makers of FusionReactor (Intergral) have to walk a bit of a fine line about labeling features in a way that might apply only to CF.

It's amazing, really, that one might not otherwise have any reason to notice that the product is designed to be used by either CF or J2EE shops. Most of us think it's just a CF monitor. It's much more.

And BTW, it's licensed per server, so you can install it on as many engines as you have (that it can monitor) on a single machine. In fact, you can even configure a free Tomcat engine just to be a monitor of other FusionReactor (Enterprise) instances.

This is something that Intergral is now offering (in beta) as a new packaging of the tool as "FusionReactor Enterprise Monitor - FREM". You can get it (or learn more) at their Fusion Labs site. I'll have more on that in a later entry.

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed