A poorly optimized query is a query that is either not using an index, or using an index that is not specific enough. You may have been alerted to the existence of poorly optimized queries by the Query Targeting alert.
Finding Queries in Real-Time Performance Panel
If the poorly optimized queries are executing consistently, or if you know they are being executed at the moment, then the Real-Time Performance Panel (RTPP) (1) is the place to go. To locate this panel you can start from the Clusters dashboard and click the Metrics button. Then click on the Real Time tab. Next, you should look at the Slow Operations table on the bottom right. This table shows all queries that take more than the current defined slowms threshold(2) to execute, and it shows them as they are executing. Poorly optimized queries typically show up here - when you see a suspiciously slow query, click on the row in the table to view the specifics of the operation.
Finding Slow Queries from the MongoDB Logs
By default, MongoDB also logs all queries that take more than the current defined slowms threshold (2) to execute. The mongod logs can be found via the following steps:
Navigate to the relevant host.
Select More .
Then select Download Logs .
You can use third-party tools like Lnav or Mtools (3) to filter and parse your logs in order to easily identify slow queries.
Finding Slow Queries via the Atlas Query Profiler
The Atlas Query Profiler is a visual representation of operations captured in the mongodb.log files that complete over the slowms threshold (4).
An operation that is logged on the Atlas Query Profiler is not necessarily an inefficient operation since this monitoring tool simply displays what's been logged, regardless of whether it is efficient or problematic.
To confirm this, you first need to determine if the current slowms threshold is higher than 100ms (5).
You can do this by either:
Downloading the mongod logs and filtering it by the slowms word to find how the slow threshold varies through the time.
Look at all operations via the Atlas Query Profiler, then sort by Operation Execution Time, add the Min column and sort by that column in ascending order to get the min registered slowms at the top.
Run db.getProfilingStatus() via MongoDB shell.
If you confirm the slowms was higher than 100ms at the moment the queries where logued in the Atlas Query Profiler, then you can assume the query was potentially unperformant and you can access the details to identify the exact query and affected namespace as well as the query execution stats.
What to do after identifying slow queries?
Once you have identified your slow queries, you can then work towards the analysis and optimization of your queries performance. Below you will find many resources to better understand how to do this:
1. The Real-Time Performance Panel identifies relevant database operations, evaluates query execution times, and shows the ratio of documents scanned to documents returned during query execution.
2. Atlas manages the threshold for slow operations for each mongod host based on average operation execution time on that host.
3. Please note that suggested Third Party tools are not supported by MongoDB.
4. You can opt out of the Atlas-managed slow operation threshold and instead use a fixed slow query threshold of 100 milliseconds via the Atlas Administration API. See Disable Managed Slow Operation Threshold.
5. Execution time above 100ms are considered slow queries by default.