It happens all the time: the engineers notice your application is slowing down just as a health rule fires a warning. Now you are in a race to identify and fix the issue before customers feel its impact.
One tried-and-true solution is database query optimization. But how do you know which queries to optimize? And how can you verify that the changes you’ve made have addressed the issue? In this two-part blog series, I’ll cover some methods for optimizing queries that every developer should know about. Today, I’ll focus on using Business Transactions with and without AppDynamics Database Visibility licenses.
The beauty of Business Transactions is that they allow you to examine application performance by function. This allows you to focus on optimizing the important functions of your application and not waste effort on other parts. In addition, the performance of Business Transactions is automatically recorded in dynamic baselines, so you can easily compare response times before and after optimization in order to see what impact your efforts are having.
In the example below, slowness is affecting the Checkout function of an ecommerce application. Let’s start out by investigating the scope of the problem. On the flow map you can see that average response time is slower now. The current response time is over 10 seconds where it had been around 0.5 seconds, the average response time for the last minutes is up to 4.6 seconds already.
The Transaction Performance health rule has been violated too.
You can also see in the Transaction Scorecard that the number of very slow transactions is increasing along with the average response time.
Note: You can see that there are errors, but we have investigated them before and they are not impacting the customer.
All of this is confirming that there is a performance problem here. Let’s find out what part of this transaction we need to fix. Returning to the flow map, we can immediately identify a potential area of trouble: The database call time is dominating the overall transaction time.
Drilling down further into the JDBC calls, we learn that about 40% of calls to this transaction use this particular database, and when they do the queries are taking an average of 8.1 seconds. What this tells us is that 86% of the time spent in the Checkout Business Transaction is being spent in calls to the XE-Oracle Database.
The next step is to determine which queries to optimize. Ideally, we want to focus on the most expensive calls/SQL statements. Finding those with AppDynamics is as simple opening up the snapshots window, selecting a group of non-error transactions, right-clicking to open a pop-up window and choosing “Identify the most the most expensive calls.”
AppDynamics compares all the snapshots and shows the slowest queries and methods for the group. Now you can see what the application was doing when it was waiting on the database.
AppD provides the full text of the query and a set of response times for the slow queries. It shows which queries are repeatedly causing an important function of your application to be slow.
If you have AppDynamics Database Visibility licenses and the DB Agent set up and configured with your database information, then AppD can help you learn more about your query performance from the database point of view. You can go to 1 “Databases” and then the 2 “Queries” tab to see a view of the query performance from the database.
This view is built from metrics gathered directly from the database system tables so it’s not limited to snapshot data. You can see all the calls in the database for this query, including calls from fast transactions that are not saved by the snapshots. You can also drill deeper into the query and see more details.
Finally, you can get to the execution plan by double clicking on the cached plan row in the table. Now you have all the information you need about which exact parts of this query are making it slow.
Some developers like to use the Database Queries screen queries as a starting point for query optimization. While this screen alone doesn’t give you any context about the business impact of a particular query, it does do a good job of reporting what’s impacting the database.
If this is your preferred approach, you should keep in mind that the query performance you see isn’t stored in the Metric Browser. You will need to set the time range to a period before the change was put in place and use the Actions | Export function to get a report about the old behavior of the query. By comparing that export with the performance of the query after changes have been made, you will be able to measure the impact of your optimization efforts.
Even if you don’t have the DB agents configured for your database, you can validate the effect of your efforts by simply watching the BT performance after the fix goes in. If you saw something like this after your fix, it would be a great result. The current response time declined from a range of 6-21 seconds to 0.5 seconds and the average response time dropped 3.3 seconds.
In today’s blog post we started off by analyzing a complex transaction that was slow and progressed to isolating specific sections of a single database query that were affecting the transactions. In my next post, we’ll look at how to use Service Endpoints to monitor mission-critical database queries.
Find out more about AppDynamics’ database monitoring solutions here.
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.
