Navigation

Performance Advisor

Note

This feature is available only with Cloud Manager Premium, which comes with certain MongoDB subscriptions. Contact MongoDB for more information.

The Performance Advisor monitors any operation with a query predicate that MongoDB considers slow and suggests new indexes to improve query performance. For the selected host and time period, the Performance Advisor evaluates up to the 20,000 most recent slow queries found in the logs.

Recommended indexes are accompanied by sample queries, grouped by query shape, that were run against a collection that would benefit from the suggested index. The Performance Advisor does not negatively affect the performance of your Cloud Manager clusters.

Note

To view the Performance Advisor, you must:

  • Be a Cloud Premium user.
  • Run MongoDB version 3.2 or later on your cluster.

  • Manage your cluster with MongoDB Agent Automation.

    To learn more about the MongoDB Agent, see MongoDB Agent.

To view the field values in the example queries, you must be a Cloud Manager user with one or more of the following roles:

Users without the aforementioned roles cannot see the field values.

Enable or Disable Performance Advisor for a Project

Required Privileges

To enable Performance Advisor for a project, you must have the Project Owner role for the project.

Performance Advisor is enabled by default. To disable or enable Performance Advisor:

1

In the left navigation, click Settings under the Project section.

2

Toggle the button next to Performance Advisor and Profiler.

Considerations

Indexes improve read performance, but a large number of indexes can negatively impact write performance since indexes must be updated during writes. It is important to consider this tradeoff when deciding whether to create new indexes for a collection. Examine whether a query for such a collection can be modified to take advantage of existing indexes, as well as whether a query occurs often enough to justify the cost of a new index.

Access Performance Advisor

To access the Performance Advisor:

1

Click Deployment.

2

Click the replica set where the collection resides.

If the replica set resides in a sharded cluster, first click the sharded cluster containing the replica set.

3

Click Performance Advisor.

4

Select a collection from Top Collections.

5

Select the time period from the list box for which you want to view data.

The Performance Advisor displays up to 20 query shapes across all collections in the cluster and suggested indexes for those shapes. The indexes are ranked according to their Impact score, which estimates the improvement in total operational latency across the cluster that creating the index would cause. For more information on index ranking, see Index Ranking in Performance Advisor.

Note

The Performance Advisor collects existing indexes for collections with slow queries every six hours. However, the Performance Advisor generates suggested indexes based on actual queries within five minutes of query execution. It is possible to see suggestions for indexes that you have already created, but these are removed the next time that the Performance Advisor collects indexes.

Index Suggestions

The indexes suggested by the Performance Advisor are ordered by their respective Impact scores. Impact indicates the estimated performance improvement that the suggested index would bring. For more information on how the Performance Advisor ranks indexes, see Index Ranking in Performance Advisor.

The Performance Advisor displays a warning icon next to any index with an Impact score of 5% or greater, indicating indexes with the most potential to improve cluster performance.

Each index suggested by the Performance Advisor contains the following metrics. These metrics apply specifically to queries which would be improved by the index:

Metric Description
Execution Count Number of queries executed per hour which would be improved.
Average Execution Time Current average execution time in milliseconds for affected queries.
Average Query Targeting Average number of documents read per document returned by affected queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see Query Targeting.
In Memory Sort Current number of affected queries per hour that needed to be sorted in memory.

Sample Queries

For each suggested index, the Performance Advisor shows the most commonly executed query shapes which would be improved by index. For each query shape, the Performance Advisor displays the following metrics:

Metric Description
Execution Count Number of queries executed per hour which match the query shape.
Average Execution Time Average execution time in milliseconds for queries which match the query shape.
Average Query Targeting Average number of documents read for every document returned by matching queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see Query Targeting.

The Performance Advisor also shows individual sample queries executed which match the query shape, with specific metrics for that query.

Query Targeting

Each index suggestion includes an Average Query Targeting score indicating how many documents were read for every document returned for the index’s corresponding query shapes. A score of 1 represents very efficient query shapes because every document read matched the query and was returned with the query results. All suggested indexes represent an opportunity to improve query performance.

Filter Index Suggestions

By default, the Performance Advisor suggests indexes for all clusters in the deployment. To only show suggested indexes from a specific collection, use the Collection dropdown at the top of the Performance Advisor.

You can also adjust the time range the Performance Advisor takes into account when suggesting indexes by using the Time Range dropdown at the top of the Performance Advisor.

Limitations

Timestamp Format for Indexes

The Performance Advisor cannot suggest indexes for MongoDB databases configured to use the ctime timestamp format. As a workaround, set the timestamp format for such databases to either iso8601-utc or iso8601-local.

Log Size

The Performance Advisor analyzes up to the first of the following limits:

  • the most recent 20,000 slow queries OR
  • the most recent 10MB of logs