To blog |

Expensive JDBC Operation Monitoring now available

June 8, 2015 by Ivo Mägi Filed under: I/O

We are happy to announce that the JDBC Monitoring is now publicly available for all Plumbr users. After five weeks of private beta we gathered enough feedback and evidence that the detection is ready for general availability.

During the weeks we monitored billions of transactions and detected thousands of performance bottlenecks in hundreds of different JVMs deployed by 300+ companies. The insights gathered helped us to fine-tune the solution which as of today is available for all Plumbr users.

The launched solution is helping operations and engineering to be on the same page by having all the necessary information at your fingertips – with Plumbr monitoring for the expensive JDBC operations you no longer need to do impact analysis using one tool, find the offending query from the database monitoring tools and then manually find the source in your Java code composing and executing the operation.

In Plumbr, all this information is monitored and aggregated for you as seen from the following screenshot where you see a situation where Plumbr:

  • detected an expensive JDBC operation blocking a thread for close to 9 seconds;
  • confirmed that this was a recurring issue (where in total of 127 times the very same operations stalled for 23 minutes and 31 seconds;
  • outlined that the wait occurred during the very same SQL query being executed:

monitor slow sql

Equipped with this information, you can quickly triage the issue based on the severity of the problem. The next step is finding the actual root case, where we again equip you with the necessary information:

find slow sql from java

From the above you can see that all the 127 expensive JDBC operations were trying to execute the very same query against the very same datasource. Also, you can immediately drill down to the single line in source code executing the query – in this particular case the culprit was a prepared statement executed by the  eu.plumbr.portal.incident.history.JpaProblemHistoryDao.findAccountProblems() method on line 74.

In addition to fine-tuning of Plumbr slow query incident reports, the program also revealed interesting facts about the impact that slow queries have on the SLA of business critical applications. Plumbr detected slow queries in 75% of the applications that participated in our private beta program. A third of these applications regularly stalled user transactions for 10 seconds and more because of inefficient queries.

As of now, Plumbr JDBC Monitoring is officially supported for all major database vendors (with the notable exception being IBM DB2). So if your JVM is connecting to Oracle, MySQL, Postgres or MS SQL Server – go ahead and grab the free Plumbr trial to find out if your app is behaving nicely in regards to expensive JDBC operations.

We do not intend to stop here – as next step we already see the need for

  • Improving the usability to adding possibility to include the prepared statement parameters in the root cause. The possibility was removed from the original design due to privacy-related concerns, but we are working to make it possible to enable the parameters logging via opt-in configuration for applications for which it would not pose a security concern.
  • Increasing the number of officially supported data sources. In addition to currently supported vendors we are working to add support for IBM DB2 and SQLite databases already.
  • Expanding the detection from single expensive operations to detect situations where multiple queries triggered during a single user transactions will result in poor user experience. Typical example for such situation would be an ORM-introduced N+1 problem.

We expect to have results with the aforementioned list already during the next couple of months. In the longer horizon is the ability to expand the detection to other types of databases as well – the world in 2015 is no longer consisting just relational data storages accessed via JDBC and we expect to support all major noSQL vendors as well. Work in this regard is still in its infancy, so clear release dates cannot yet be scheduled.

If you read the post that far I can only recommend you to go ahead and grab the free trial for a test run. In addition to lock contention incidents, memory leaks and GC pauses, it is time to say goodbye to expensive JDBC operations.