Measuring Database Performance
Calls from an instrumented node to a database are recognized as traffic related to specific business transactions. AppDynamics monitors the performance of calls to databases in two ways:
- Overall performance of calls to individual databases.
- Call performance for specific business transactions
Captured JDBC Exit Calls
The agent treats all of the below calls (for oracle jdbc driver example) as exit calls and captures the time for them:
For a typical flow, the calls made in order will be getConnection, prepareStatement, execute, and then commit.
Response Time Calculations
The response time for a database call is measured as the round trip time it takes for JDBC call to return. The agent captures the database call as exit call from the tier, for example *.execute() method calls, and captures the time it takes this call to return. so the response time for the database call includes:
network round trip time + database connection time + SQL query time (or any other time spent in DB)
Based on the calls per min rate and response time for these calls, the 'average response time' is calculated.
Snapshots do not show any calls which take less than 10 ms. but the agent considers calls less than 10ms when calculating the average response time.
Metrics for the database calls and response times are collected at four levels:
- Business transaction metrics - the metrics for a specific business transaction for a specific database are visible on the Transaction Flow Map.
- Tier metrics - the metrics for all calls from a tier to the specified database are visible on the Tier Flow Map.
- Database metrics - the overall database access metrics across the application (all business transactions) are visible on the Application Flow Map and the Database Dashboard.
- Internal database metrics - see AppDynamics for Databases.
Auto-detecting and Manually Configuring Backends
By default AppDynamics app agents automatically detect many common databases. See the Supported Environments and Versions for lists of auto-detected databases per platform.
To monitor call performance to a database, first make sure it shows up in the Databases List and has its own Databases Dashboard. If a service is not appearing, check the configuration. See Configure Backend Detection.
Database Visibility on Flow Maps and Dashboards
For a discussion of the various KPI graphs on the flow maps, see KPI Graphs.
For a discussion of baselines, see Behavior Learning and Anomaly Detection.
Databases on Application Flow Maps
Databases detected during the specified time window show up on the Application Dashboard flow map. You can view the detected databases in the context of the entire application's transaction flow. The application flow map displays calls per minute and average response time for calls made to databases. These metrics include all calls made from a specific tier to a database across all business transactions. The tier and node flow maps display a similar metric aggregating data from calls across all business transactions by tier or node respectively.
Resolving an unexpected database on the flow map
If you intermittently see a connection from an application to a database on the flow map that you do not think was being called by that application, try the following to determine why this database appears:
- From the left navigation menu, select application -> Servers -> App Servers -> Databases. Select the Slowest Database Calls tab and drill down into the snapshots using to see the code that is calling the database. See To troubleshoot slow database and remote service calls.
- Run a diagnostic session to capture some transaction snapshots and look for calls to the database. See Diagnostic Sessions and Transaction Snapshots.
- Are any exceptions thrown when the database is seen? If so, look for error snapshots that point to the exception trace. See Tutorial for Java - Exceptions and Troubleshoot Errors.
You can view a list of the detected database servers along with key performance indicators including:
- Response time
- Total calls
- Calls per minute
- Errors per minute
The database list shows all databases that were ever detected. Stale databases can be configured to be automatically removed.
From the database list, you can select a database and click View Dashboard to see the Database Dashboard. The dashboard displays a Database Flow Map, database properties, and graphs of the key performance indicators (KPIs). The database properties indicate how the database is identified and control how it shows in the display map and how the metrics are aggregated. For a discussion of baselines and how they are used and configured, see Behavior Learning and Anomaly Detection.
Databases on Tier Flow Maps
The detected databases show up on the Tier Flow Map. You can view the detected databases in the context of the traffic on this specific tier. For example, the ECommerce Server tier is shown in the following screen shot.
Databases on Business Transaction Flow Maps
For business transactions involving calls to databases, the databases appear on the Transaction Flow Map. You can view the detected databases in the context of the traffic for this specific business transaction. The transaction flow map shows the average time spent in database calls for the business transaction. The following transaction flow map for the Fetch catalog business transaction shows that the average time per transaction spent on database calls to the MySQL database is 5 ms and that time represents 13.9% of the time for the entire business transaction.
Use AppDynamics for Databases to See More Metrics
Users of AppDynamics for Databases can link to that product by right-clicking on a database from the database list or from the database icon on any flow map. See AppDynamics for Databases.
Troubleshoot Database Performance
Slow Database Calls
AppDynamics displays a list of the slowest database calls with call details. Click Troubleshoot -> Slow Response Times -> Slowest DB & Remote Service Calls tab to view specific call details and related business transaction snapshots that can help you to troubleshoot.
The Slowest DB & Remote Service Calls tab lists up to ten calls to the database with the longest execution time over the selected time frame, by tier and for all tiers. Each call shows the following information:
- Call: SQL Query
- Avg. Time per Call (ms): the average time per call in milliseconds
- Number of Calls: the number of calls executed during the time range
- Max Time (ms): the maximum execution time in milliseconds
- View snapshots: a link to view existing transaction snapshots
Max Time determines which calls are displayed in the Slowest Database Calls list. For example for JDBC calls, Max Time must exceed 10 ms before AppDynamics tracks the call as a potential candidate for this list. App agents aggregate and report call data to the Controller every 15 minutes.
To summarize, AppDynamics defines the slowest remote services calls list as:
- Max Time greater than 10 ms
- Top ten worst
- Reported every 15 minutes
AppDynamics displays NoSQL databases as Remote Services. The slow call threshold for remotes services is 50ms. See Monitor Remote Services.
SQL Queries in Backend Calls
Normally AppDynamics reports SQL queries for database calls that have execution times exceeding 10ms up to a default maximum of 500 queries.
Configure Database Detection
You can customize the discovery and naming rules and configure the detection of additional databases. See Configure Backend Detection.