Better Index Recommendations with MongoDB’s Performance Advisor

We’re happy to announce several enhancements to the Performance Advisor, available in MongoDB Atlas, MongoDB Cloud Manager, and MongoDB Ops Manager.

The Performance Advisor is a unique tool that automatically scans operations that are running on your MongoDB instance and intelligently identifies the best indexes you can create to improve performance. In this update, we made three key improvements:

  1. A new ranking algorithm and additional performance statistics make it easier to understand the relative importance of each index recommendation.
  2. A streamlined user interface makes it easy to scan index recommendations and review additional information as needed.
  3. Index recommendations that are unlikely to impact performance significantly were removed so you don’t create frivolous indexes.

Let’s take a look at each of these in more detail.

Making Index Recommendations Easier to Understand

We introduced a new way of ranking index recommendations in the Performance Advisor so that the most important indexes are surfaced first. Each index recommendation now includes an “impact” score in the top right corner that predicts the percentage reduction in total operation latency if the index is created. If you have several index recommendations, they are ranked by impact score in decreasing order.

Index Suggestions

In the example above, the impact score for the index recommendation {kitty: 1, date: 1} is 10.2%, which means that if the index were to be created, you should expect to see an average reduction in operation latency for the cluster by 10.2%, assuming all else is equal.

In addition, we now highlight several performance statistics per index recommendation, including:

  • 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

We recommend that you just use the impact score for understanding the relative importance of each index recommendation, but these additional metrics provide more color as to why the impact was as high (or low) as we suggested.

Improving the Performance Advisor User Experience

We also made it easier for you to scan and review multiple index recommendations by cleaning up the visual layout of the Performance Advisor. First, you will now be able to see index recommendations across all collections in your cluster by default. This gives you a more holistic way to review opportunities to improve performance across your deployment. The option to view recommendations by collection is still available through a dropdown menu in the top left corner.

We also moved to a card-based layout so you can easily expand and collapse individual sections for each index recommendation that may help you make an informed decision about whether or not to create that index.

Index Recommender

In this example, you can see a fully expanded index recommendation. By default, we show you the key information and collapse the two lower sections. However, you can expand these to see existing indexes in the collection, as well as sample queries that would be improved by the index along with some performance statistics.

Removing Insignificant Index Recommendations

Finally, we’ve removed index recommendations for queries that just don’t run very often by introducing a minimum threshold for queries impacted by the recommended index. There is a real cost to building and keeping indexes on your collections, so we want to make sure that only the ones that will make a material impact to your cluster performance are surfaced in the Performance Advisor. Having too many indexes may actually hurt your cluster’s performance more than help it.

We hope these enhancements will improve your experience with how you use MongoDB’s products. 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. You can deploy a new cluster in just a few minutes. (You’ll need a dedicated cluster (M10 cluster tier or higher) in order to see the Performance Advisor.) Once you’re all set up, don’t panic if you don’t see any index recommendations - this just means that you don’t have any slow-running queries that are materially impacting your database performance.

If you’re interested in a deeper dive on how we built the Performance Advisor, subscribe to the MongoDB Engineering Journal to be notified of our upcoming post on how it all works ‘behind the scenes’!