Video Transcript (English)

Let’s say you’re troubleshooting an app that’s underperforming. Pages load slowly, processes are lagging, and users are frustrated.

Is the problem in the app code, with the network, in the database, or could slow queries be the cause?

In this video, we’ll use MongoDB’s profiling tools to test this hypothesis and identify the database issues causing the slowdown.

But how do we find slow queries? Profiling is a process that helps find inefficiencies, such as long execution times or excessive document scans, before they escalate into major disruptions.

With profiling, you gain insights into query behavior, allowing you to refine indexes, restructure queries, or adjust schema design. These improvements enable your app to perform reliably under heavy workloads.

Profiling is important in all stages of the development lifecycle and is especially useful during Development and Testing to catch problems early on, or for troubleshooting a slow application.

To ensure continuous, reliable application performance as traffic and data volumes scale, ongoing profiling must also happen in production.

To profile queries in MongoDB, we have two main tools: The Atlas Query Profiler analyzes query performance in MongoDB Atlas, and the Database Profiler is designed for self-managed instances.

Profiling in MongoDB can be set to 3 levels. We can choose to profile all operations, but this will impact database performance. This is fine for development but should be avoided in production. Or we can profile only slow operations that exceed MongoDB's default threshold of 100 milliseconds. And finally, we can disable profiling altogether.

Let's look at the Atlas Query Profiler first. It is a graphical tool available for M10 or higher tier clusters that enables you to track and visualize slow-running queries.

To access it, we first need to log in to our MongoDB Atlas account.

From here, we select the Query Insights tab and then select the Query Profiler button for the cluster we want to analyze.

The Profiler gives a detailed view of executed queries within a namespace, which is the database and collection. It includes metrics such as execution time, the number of documents examined and returned, the ratio of documents examined to returned, and more.

Let's use the query profiler to find queries with execution times that are taking longer than what our business needs require.

To start, let's make sure that Operation Execution time is selected.

If we highlight a data point, we see specifics about the database and collection involved in the queries. This gives us quick insights into potential causes for the slow operation.

The example highlights the application that executed the query, its execution time, and the operation type.

From here, we can click on "view more details" to examine the slow operation more closely.

This takes us to a page for slow queries with the specific query already selected.

Here, we have information about the query, including Operation Execution time, Index Keys Examined, and the Examined to Returned Ratio.

First, the Operation Execution Time measures the total time a query takes to execute, including data processing and result retrieval. The goal is to reduce execution time below our business requirements of 100 milliseconds.

Next, Keys Examined refers to the number of index keys MongoDB evaluates during a query. A value of zero indicates no index was used, which can lead to inefficient performance.

And finally, the Examined to Returned Documents Ratio measures query efficiency. We want to strive for a 1:1 ratio. A high ratio means MongoDB is scanning many documents but returning few, often indicating the need for an index.

Our Examined to Returned ratio is high, which makes sense since no index was used. This ratio can also be high if you have a poorly designed index.

We won't cover that here, but you can check out our skills on Query optimization and indexing design for more details.

If we're not using Atlas, we can use the Database Profiler to get this information.

Let’s take a look.

To enable the Database Profiler, use this command in the MongoDB shell, mongosh.

Level one represents the profiling level and only profiles slow operations over 100 milliseconds by default.

We can disable profiling by setting this to zero. Setting this to level 2 captures all operations but comes with resource implications, so use it sparingly.

Once profiling is enabled, the profiler stores data in the system.profile collection. Using mongosh commands, we can query this collection to review profiling data.

To retrieve profiling data for our slow queries, we can use this command to find all queries that took longer than 100 milliseconds and pretty print the JSON output for readability.

From the output, we should focus on millis, which represents the Query execution time, keysExamined to see if an Index was used for the query, docsExamined for the Number of documents scanned during execution, nreturned to see how many documents were returned from the query, and planSummary for an overview of how the query was executed.

This query took over half a second to execute.

It's scanning thousands of documents to return a small number of documents and no index keys were examined. We can further verify that there is no index since we see COLLSCAN in the planSummary which tells us that the database had to scan the entire collection to return the documents.

From this information, we know that adding an index should improve our query performance.

With our hypothesis validated, we can move on towards optimization.

As a final step in our process, we should schedule routine profiling checks post-deployment to ensure our optimizations continue to perform under evolving workloads.

Great job! In this video, we learned why profiling is essential for identifying slow queries that can cause app performance issues, and explored tools to address them. First, we used the Atlas Query Profiler to analyze query performance in the cloud, leveraging metrics like execution time and document scan efficiency. Then, we demonstrated how to use the Database Profiler to configure profiling, query its data, and identify opportunities to optimize queries for better performance.