Docs Menu

Monitor and Improve Slow Queries

On this page

  • Common Reasons for Slow Queries
  • Index Considerations
  • Access Performance Advisor
  • Index Suggestions
  • Create Suggested Indexes

Only available on M10+ clusters and serverless instances

The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance. The threshold for slow queries varies based on the average time of operations on your cluster to provide recommendations pertinent to your workload.

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 Atlas clusters.

If a query is slow, common reasons include:

  • The query is unsupported by your current indexes.
  • Some documents in your collection have large array fields that are costly to search and index.
  • One query retrieves information from multiple collections with $lookup.
Note

By default, Atlas dynamically adjusts your slow query threshold based on the execution time of operations across your cluster. To opt out of this feature and instead use a fixed slow query threshold of 100 milliseconds, you must use the Atlas Administration API. See Disable Managed Slow Operation Threshold. For M0, M2, M5 clusters and serverless instances, Atlas disables the Atlas-managed slow query operation threshold by default and you can't enable it.

Indexes improve read performance, but a large number of indexes can negatively impact write performance since indexes must be updated during writes. If your collection already has several indexes, consider this tradeoff of read and write performance when deciding whether to create new indexes. 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.

To access the Performance Advisor:

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 Review Index Ranking.

Note

To view field values in a sample query in the Performance Advisor, you must be a user with at least one of the following access levels:

Users without the aforementioned access cannot see the field values.

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 Review Index Ranking.

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.

To learn how to create indexes suggested by the Performance Advisor, see Create Suggested Indexes.

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.

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.

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.

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.

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. To learn more about timestamp formats, see mongod --timeStampFormat.

The Performance Advisor analyzes up to 200,000 of your cluster's most recent log lines.

If a cluster experiences an activity spike and generates an extremely large quantity of log messages, Atlas may stop collecting and storing new logs for a period of time.

Note

Log analysis rate limits apply only to the Performance Advisor UI and the Access Tracking UI. Downloadable log files are always complete.

Performance Advisor doesn't provide performance suggestions for time-series collections.

The Performance Advisor includes a user feedback button for Index Suggestions. Atlas hides this button for serverless instances.

You can create indexes suggested by the Performance Advisor directly within the Performance Advisor itself. When you create indexes, keep the ratio of reads to writes on the target collection in mind. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. To learn more about indexing strategies, see Indexing Strategies.

  • You cannot create indexes through the Performance Advisor if Data Explorer is disabled for your project. You can still view Performance Advisor recommendations, but you must create those indexes from mongosh.
  • You can only create one index at a time through the Performance Advisor. If you want to create more simultaneously, you can do so using Data Explorer, a driver, or the shell
  • Atlas always creates indexes for entire database deployments. If you create an index while viewing the Performance Advisor for a single shard in a sharded cluster, Atlas creates that index for the entire sharded cluster.

To create a suggested index:

1

The Performance Advisor opens the Create Index dialog and prepopulates the Fields based on the index you selected.

2
{ <option1>: <value1>, ... }
Example

The following options document specifies the unique option and the name for the index:

{ unique: true, name: "myUniqueIndex" }
3

Use collation to specify language-specific rules for string comparison, such as rules for lettercase and accent marks. The collation document contains a locale field which indicates the ICU Locale code, and may contain other fields to define collation behavior.

Example

The following collation option document specifies a locale value of fr for a French language collation:

{ "locale": "fr" }

To review the list of locales that MongoDB collation supports, see the list of languages and locales. To learn more about collation options, including which are enabled by default for each locale, see Collation in the MongoDB manual.

4
Important

Rolling index builds succeed only when they meet certain conditions. To ensure your index build succeeds, avoid the following design patterns that commonly trigger a restart loop:

Note

Data Explorer does not support building indexes with a rolling build for M0 free clusters and M2/M5 shared clusters. You can't build indexes with a rolling build for serverless instances.

Building indexes in a rolling fashion reduces the performance impact of building indexes on replica sets and sharded clusters.

To maintain cluster availability:

  • Atlas removes one node from the cluster at a time starting with a secondary.
  • More than one node can go down at a time, but Atlas always keeps a majority of the nodes online.

To learn more about rebuilding indexes, see Build Indexes on Replica Sets.

Note

The following index options are incompatible with building indexes in a rolling fashion:

If you specify any of these options in the Options pane, Atlas rejects your configuration with an error message.

5
6
Give Feedback
MongoDB logo
© 2021 MongoDB, Inc.

About

  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.