- Alerts and Monitoring >
- Analyze Slow Queries >
- Performance Advisor
Performance Advisor¶
On this page
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 is always enabled and does not negatively affect the performance of your Ops Manager clusters.
Note
To view the Performance Advisor, you must:
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 an Ops Manager user with one or more of the following roles:
Project Owner
Project Data Access Admin
Project Data Access Read/Write
Project Data Access Read Only
Users without the aforementioned roles cannot see the field values.
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.
Slow Query Threshold¶
The Performance Advisor recognizes a query as slow if it takes longer
to execute than the value of
slowOpThresholdMs.
By default, this value is 100
milliseconds. You can change the
threshold with either the
profile
command or the db.setProfilingLevel()
mongo
shell method.
Example
The following profile
command example sets the threshold at 200
milliseconds:
If you are running MongoDB 3.6 or later, you can customize the
percentage of slow queries in your logs used by the Performance Advisor
by specifying the sampleRate
parameter.
Example
This sets the slow query threshold to a lower value of 100 milliseconds but also sets the sample rate to 10%.
Note
By default, the value of profile
is 0
. MongoDB recommends
leaving this value unchanged since other values can negatively
impact database performance. To learn more, see the
profile command.
Understanding the Query Inefficiency Score¶
The Performance Advisor displays sample queries with recommended indexes. Each sample query includes a query inefficiency score, which is how many documents were read for every document returned by the query. A score of 1 represents a very efficient query because every document read matched the query and was returned with the query results. The Performance Advisor displays a warning icon next to any score over 1,000, which represent highly inefficient queries. All suggested indexes represent an opportunity to improve query performance.
Limitations¶
The foillowing limitations apply to indexes and log sizes.
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-utc.
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
Access Performance Advisor¶
To access the Performance Advisor:
Click Deployment.¶
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.
Click Performance Advisor.¶
Select a collection from TOP COLLECTIONS.¶
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.
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.