Eating our own dog food: improving JDBC monitoring

As you might or might not have noticed, Plumbr is a monitoring solution that detects performance problems within Java Virtual Machines. Following the recommendation to “eat your own dog food”, we have also been using Plumbr to monitor our own services.

A direct result of practicing what we preach has been improved performance of Plumbr itself – we have detected and solved hundreds of performance issues. Additionally, we have harvested valuable product feedback along the way.

This post will illustrate one of the product improvements that was a result of such feedback and that we released today. The motivation for the change was triggered by our own needs. When we launched expensive JDBC operation monitoring in June, we immediately stumbled upon a possibility to improve the offering.

The way Plumbr works is built upon monitoring incoming user transactions. When the transactions start affecting end users by completing slower than expected, Plumbr will link the poorly behaving transaction with the root cause in source code. One of the most frequent root causes happens to be a JDBC call to a relational database. For such queries, we expose the SQL query embedded in the JDBC statement and a call stack through which the query was executed:

At a first glance you might think that the offending SQL and the call stack are all you might ever need to fix the problem – you know what query is slow and where in the source code the query was executed.

However,  there is a vital piece of evidence missing.  As you can see from the above example, we are dealing with prepared statements. The way prepared statements are designed results in input parameters of the query in the WHERE and GROUP BY clauses being omitted. So in cases where only a specific combination of input parameters causes poor performance, Plumbr fell short in its promise of zooming you into the actual root cause without any hassles regarding issue reproduction.

So we decided to stand true to our promise and equipped the executed statements with actual parameter sets. Fast forward a couple of months and – we have done just that. The very same query is now equipped with actual parameter values that caused the performance problems:

The difference is immediately visible – we can quickly see that the query was slow for a particular JVM (with the ID ‘S1ng342mzn’) and in a specific time range (a week starting from October 10th). Next steps were now as easy as launching the example above in the SQL client and start improving the situation with the help of the query execution plan.

Monitoring of expensive JDBC statements with parameters is available to all Plumbr users as of today. If you are not a customer yet, I encourage you to take Plumbr for a spin during the 14-day trial to check out how easy performance monitoring and management can actually be. The feature is available for all Plumbr Agents version 15.10.06 and newer.

Considering the possibility of exposing sensitive data via JDBC statement parameters, the feature is offered on opt-in basis. By default Plumbr will still monitor just the statement, parameters for the statements will be added when you enable the feature in your account configuration.