The AppD Approach: Database Monitoring with Service Endpoints

April 16 2018

How to use Service Endpoints to ensure the performance of mission-critical database queries.

I keep running into cases where the 80/20 rule (also known as the Pareto Principle) helps me prioritize my efforts in APM. I find that 80% of my problems are easy to fix, and once they are fixed, they stay that way. The other 20% require constant attention. It’s even tougher when one of the 20% is mission critical. Some examples might be a database query that touches multiple tables and can be de-optimized by changes in schema, indexing or even table row counts. Or, outside of database monitoring, the issue may lie in a key sequence of code that is regularly touched by developers, and it keeps breaking as they perfect their understanding of the problem. If you are running into these kinds of scenarios, it might make sense to continuously watch the performance of your queries (or code) and have a handy way to compare what your queries were doing in the past to what’s happening in the present moment. You might also want to create some health rules so AppD will tell you right away if performance has degraded. In this blog post, I’ll discuss how to do that using a Service Endpoint.

The first step is to get a list of snapshots from the last time the problem cropped up. For database monitoring, the “Slow Response Times” screen is good place to start.

To get there:

1.     Expand the “Troubleshoot” item in the left side navigation bar

2.     Select “Slow Response Times” in the left side navigation bar

3.     Select the “Slowest DB & Remote Query Times” tab

4.     Filter on the JDBC type calls

5.     Select a query from the list and click “View Snapshots”

Select a snapshot that has full call graph data like this one.

Find the snapshot segment that makes the query call, and the find the method in the call graph that made the query, right click on it, and select “Configure Instrumentation for this Class/Method”.

In the next dialog choose “Service Endpoint”. Provide a name for this endpoint in the Service Endpoint Definition dialog.

If you were lucky enough to have a single class and method in your Data Access Object (DAO) class for each query then you are done. In this case, we have a generic query class that is capable of making any type of query.

AppD can handle that by splitting the Service Endpoints. Back in the Service Endpoint Definition dialog change to the “Transaction Splitting” tab. Enable splitting with the checkbox. Then use the object instance and a getter chain to do the splitting. Enter a getter string like this to access the first part of the query string and clean it up for presentation.

After that, you will be able to track the performance of that query in the Service Endpoints area of the UI, and you can create a health rule too.

These steps will give you a new entry in the Service Endpoints list like this. The Service Endpoint will let you count all the calls into this query and see its Average Response Time and Error Count in real time and in the past [starting from this time forward].

It will also create a new group of metrics in the metric browser that you can use as a data source for a health rule. For example, you may want to create a health rule that fires the next time the average response time of the method tied to your query goes over 100ms (or some other value that is well over the expected response time).

The next time the query causes trouble, AppD will tell you proactively that the query time is slow. The first-level responder will see the health-rule violations in the event list right along with the reports that one or more Business Transactions are slow and impacting your customers. If this information about the query time proves to be really decisive in finding the problem’s root cause, you might even create a policy that sends the health rule violations directly to the team responsible for the query. That will shortcut any root-cause discussion before it even starts.

As I’ve explained in this blog post, Service Endpoints can make make it much easier to monitor mission-critical database queries. As detailed in my previous post, “How to Identify Problematic Database Queries with Business Transactions,” AppDynamics also helps you prioritize the inevitable issues that pop up during database monitoring so you can focus on optimizing code that is most critical to your application. Together Business Transactions and Service Endpoints are two great tools for ensuring the performance of applications.

Find out more about AppDynamics’ database monitoring solutions.

John Aronson is part of AppDynamics Global Services team, which is dedicated to helping enterprises realize the value of business and application performance monitoring. AppDynamics’ Global Services’ consultants, architects, and project managers are experts in unlocking the cross-stack intelligence needed to improve business outcomes and increase organizational efficiency.

John Aronson
John Aronson is a Senior Consultant at AppDynamics who advises customers to help them practice APM effectively. John previously worked at OpTier Inc and has been working in the APM space since 2002 as a consultant and developer.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form