Optimizing Your MongoDB Deployment with Performance Advisor
We are happy to announce additional enhancements to MongoDB’s Performance Advisor, now available in
MongoDB Atlas
,
MongoDB Cloud Manager
, and
MongoDB Ops Manager
.
MongoDB’s Performance Advisor
automatically analyzes logs for slow-running queries and provides index suggestions to improve query performance.
In this latest update, we’ve made some key updates, including:
A new ranking algorithm and additional performance statistics (e.g., average documents scanned, average documents returned, and average object size) make it easier to understand the relative importance of each index recommendation.
Support for additional query types including regexes, negation operators (e.g., $ne, $nin, $not), $count, $distinct, and $match to ensure we cover with optimized index suggestions.
Index recommendations are now more deterministic so they are less impacted by time and provide more consistent query performance benefits.
Before diving further into MongoDB’s Performance Advisor, let’s look at tools MongoDB provides out of the box to simplify database monitoring.
Background
Deploying your MongoDB cluster and getting your database running is a critical first step, but another important aspect of managing your database is ensuring that your database is performant and running efficiently. To make this easier for you, MongoDB offers several out-of-the-box
monitoring tools
, such as the Query Profiler, Performance Advisor, Real-Time Performance Panel, and Metrics Charts, to name a few.
Suppose you notice that your database queries are running slower. The first place you might go is to the
metrics charts
to look at the “Opcounters” metrics to see whether you have more operations running. You might also look at the “Operation Execution Time” to see if your queries are taking longer to run.
The “Query Targeting” metric shows the ratio of the number of documents scanned over the number of documents returned. This datapoint is a great measure of the overall efficiency of a query — the higher the ratio, the less efficient the query. These and other metrics can help you identify performance issues with your overall cluster, which you can then use as context to dive a level deeper and perform more targeted diagnostics of individual
slow-running queries
.
MongoDB’s Performance Advisor takes this functionality a step further by automatically scanning your slowest queries and recommending indexes where appropriate to improve query performance.
Getting started with Performance Advisor
The Performance Advisor is a unique tool that automatically monitors MongoDB logs for slow-running queries and suggests indexes to improve query performance.
Performance Advisor also helps improve both your read and write performance by intelligently recommending indexes to create and/or drop (Figure 1). These suggestions are ranked by the determined impact on your cluster. Performance Advisor is available on M10 and above clusters in MongoDB Atlas as well as in Cloud Manager and Ops Manager.
Figure 1:
Performance Advisor can recommend indexes to create or drop.
Performance Advisor will suggest which indexes to create, what queries will be affected by the index, and the expected improvements to query performance. All of these user interactions are available in the user interface directly within Performance Advisor, and indexes can be easily created with just a few clicks.
Figure 2 shows additional Performance Advisor statistics about the performance improvements this index would provide. The performance statistics that are highlighted for each index recommendation include:
Execution Count:
The number of queries per hour that would be covered by the recommended index
Avg Execution Time:
The average execution time of queries that would be covered by the recommended index
Avg Query Targeting:
The inefficiency of queries that would be covered by the recommended index, measured by the number of documents or index keys scanned in order to return one document
In Memory Sort:
The number of in-memory sorts performed per hour for queries that would be covered by the recommended index
Avg Docs Scanned:
The average number of documents that were scanned by slow queries with this query shape
Avg Docs Returned:
The average number of documents that were returned by slow queries with this query shape
Avg Object Size:
The average object size of all objects in the impacted collection
If you have multiple index recommendations, they are ranked by their relative impact to query performance so that the most beneficial index suggestion is displayed at the top. This means that the most impactful index is displayed at the top and would be the most beneficial to query performance.
Figure 2:
Detailed performance statistics.
Creating optimal indexes ensures that queries are not scanning more documents than they return. However, creating too many indexes can slow down write performance, as each write operation needs to check each index before writing.
Performance Advisor provides suggestions on which indexes to drop based on whether they are unused or redundant (Figure 3). Users also have the option to “hide” indexes as a way to evaluate the impact of dropping an index without actually dropping the index.
Figure 3:
Performance Advisor shows which indexes are unused or redundant.
The Performance Advisor in MongoDB provides a simple and cost-efficient way to ensure you’re getting the best performance out of your MongoDB database. If you’d like to see the Performance Advisor in action, the easiest way to get started is to sign up for
MongoDB Atlas
, our cloud database service. Performance Advisor is available on MongoDB Atlas on M10 cluster tiers and higher.
Learn more from the following resources:
Monitor and Improve Slow Queries
Monitor Your Database Deployments
November 22, 2022