Video Transcript (English)
Now you know what query tuning is, how MongoDB’s architecture ties in, and some strategies for optimizing queries, let's apply the query tuning lifecycle in action. In this video, we'll identify a slow query and analyze it.
In this video, we will use the MongoDB Atlas Query Profiler and log data to identify and analyze slow queries in a cluster.
You’ll recall that MongoDB Atlas offers a Query Profiler that helps identify slow queries. It provides insights into query performance so that we can focus on the queries that need attention. The Query Profiler is available in M10 or higher clusters.
To access the Query Profiler, we first need to log in to our MongoDB Atlas account.
From here, we select the cluster we want to analyze.
Then, we navigate to the Query Insights tab.
The Query Insights tab is available for M10 or higher clusters and provides detailed visibility into the performance of your database queries.
From this tab, we select the option for "Query Profiler."
The Profiler gives a detailed view of the queries being executed, including metrics such as
execution time, the number of documents examined and returned, the ratio of documents examined to returned, and more.
This information is crucial for identifying which queries are slowing down your application.
First, we want to look for queries with high execution times - queries that are taking longer than what our business needs require.
Execution time is a top indicator of a query's performance because it directly impacts the user experience and system efficiency.
We also want to look for queries that we notice are executed frequently.
So, how do we identify queries that fall into these categories?
To help find our queries with high execution times, we want to make sure that Operation Execution time is selected here.
Next, we want to narrow the scope of our graph to specific time periods to focus on when performance issues occur.
For example, if problems have been reported in the last hour, we’d choose 1 hour from the list of options. We can expand our window up to 5 days from this list.
And if we wanted to troubleshoot a previous issue, we can click here to filter by a specific time range.
If our cluster has multiple nodes, we can also filter by host to see where the slow queries are happening.
We can see a few slow operations on our graph. To further isolate our problem queries, we can click and drag on the graph to zoom in on a particular time window to get a more detailed analysis. That’s better!
If we highlight a data point, we can see specifics about the database and collection involved in the queries. This gives us some quick insights into potential causes for the slow operation.
For example, we can already see that this query used a collection scan and how long it took, and that it used a sort stage.
From here, we can click on "view more details" to examine the slow operation more closely.
This takes us to a page for our slow queries with the specific query already selected.
Here, we are immediately given valuable information about the query, including Operation Execution time, Keys and Docs Examined, Docs Returned, and the Examined to Returned Ratio. Let’s start our analysis by going through each of these metrics and talk about why you may be interested in them.
The Operation Execution time refers to the total time taken for a query to execute from start to finish. This includes the time spent processing the query, accessing the necessary data, and returning the results.
Our goal for this exercise is to have the execution time less than 100 milliseconds, but the target time should be whatever your business requirements are.
Keys Examined refers to the number of index keys that MongoDB evaluates during the execution of a query. This metric is relevant when a query uses an index to locate the required documents. This query shows zero keys examined, which means no index was used. We can see the effects of no index on the next two metrics.
The first of those is Docs Examined which refers to the number of documents that MongoDB scans to fulfill a query. We can see our query examined a lot of documents. By itself, this is not a direct indicator of poor query performance. We can evaluate the performance by comparing this number to the Docs Returned value.
The next metric is Docs Returned which refers to the number of documents that a query returns as its result. This metric helps you understand the output size of a query. The output for our query is fairly small. This means we are examining a lot of docs for every returned doc which is not ideal.
Finally, we have the Examined to Returned Documents ratio metric. This ratio can indicate inefficiencies in query execution. A high ratio suggests that MongoDB is examining many documents but returning few, which can be a sign that an index is needed. The ideal ratio is 1:1.
Our Examined to Returned ratio is significantly high. This means we either have a poorly designed index, or no index at all. To dig deeper into this, let’s look at the log information for the query.
On our query details page, we see the Parsed Log Document. This document eliminates the need to download log files for the purpose of examining the slow query.
When we look closely at the log document, we see the query structure. We are matching on address dot market and amenities fields. Then, we are doing range scans on the price, minimum number of nights, and bedrooms. Finally, once we have all that data, we are sorting by price from highest to lowest.
If we move further down the log information, we can see that the query plan used a collection scan to retrieve the data.
Based on this analysis, we can assume that the first step to optimizing this query will be to add an appropriate index to the collection, so we can leverage the index instead of scanning the entire collection.
Great work! In this video, we walked through the process of identifying and analyzing slow queries using tools in MongoDB Atlas. Using the Query Profiler and log data, we pinpointed queries that needed to be tuned and gained insights into how to improve them.