1. Knowledge Base
  2. Studio 3T Tutorials
  3. MongoDB Performance Tuning with Visual Explain

MongoDB Performance Tuning with Visual Explain

When querying against large MongoDB databases, it’s ideal to equip yourself with a GUI that breaks down how queries are executed, so that you can take measures to optimize MongoDB performance.

Studio 3T does this with Visual Explain, a performance tuning feature which shows the steps MongoDB took to execute a query, with accompanying runtime statistics for each stage.

Knowing when to add an index

To illustrate how useful Visual Explain can be, let’s take a look at the titles collection, which is part of the Pubs database first referenced in How to Create MongoDB Joins with SQL.

Download the titles collection here.

It contains 10,000 documents, each containing 12 fields: _id, title_id, title, type, TypeList, pub_id, price, advance, royalty, ytd_sales, notes, and pubdate.

To test how expensive a query can be, let’s run a simple query in Visual Query Builder.

Let’s sort the collection by price in descending order:

Sort the titles collection by price in descending order

        "price" : -1.0

Now, let’s click on the Explain tab.

By default, Studio 3T will show the query planner mode or brief mode. It shows the diagram of stages without runtime statistics:

Visual Explain shows the diagram in query planner mode by default

To show runtime statistics, click on the Run full explain button to trigger full mode:

Show runtime statistics with full mode

What information can we gather from this?

  1. It took MongoDB 20 milliseconds to run the collection scanVisual Explain shows that the collection scan took 20ms
    A collection scan means that the query looked at each document to get the results.
  2.  10,000 documents were passed at each stage10K documents were passed at each stage

    Hover over the number to see exactly how many documents were passed from one stage to another.

  3. It took MongoDB 21 milliseconds to run the Sort key generator stage and another 30 milliseconds to run the Sort stageShows milliseconds added by the sort stages
  4.  The whole query took 71 milliseconds to runShows the total time it took to run the query

Now, how can we optimize this query?

If you often find yourself often sorting price in descending order, then it would be best to create an index on the field, price.

Running the same query all the time? Optimize it by adding an index

Learn how to create an index in Studio 3T.

Now that the index is set up, let’s run the same query again (and this time, we expect it to be faster).

Query was faster with an index scan
Indeed, MongoDB runs the same query much faster, at 30ms (versus 71ms).

Instead of using a collection scan which took 20ms, MongoDB chose an index scan which only took 10ms. The database detected that an appropriate index exists for the query, so it scanned a limited number of documents, instead of needlessly scanning each one.

By choosing the index scan, the database was also able to skip the sort stages, and instead went straight to fetching the documents, resulting in 41ms saved.

Spotting uneven shard loads

MongoDB uses sharding to horizontally scale data, which it tries to evenly distribute across shards – but this is not always the case.

Visual Explain can come in handy in spotting imbalanced loads when you’re querying a sharded database.

Take for example the snapshot below of crimes, a collection of about 2,486,000 documents, hosted on a database with two shards.

To test, we run an empty query and discover that:

  • Shard 1 handled 1.6 million documents
  • Shard 2 handled 893K documents

A look at an uneven distribution of shard load

Now we know that the database isn’t distributing the workload evenly as expected. Shard 1 is handling almost twice the load.

We can then move on to the next steps of investigating the sharded cluster balancer or chunk size to fix a bigger issue, which Visual Explain helped us uncover.

Optimizing queries by applying relevant filters first

When it comes to querying, the best practice is to apply operators that do the most filtering first. 

Visual Explain is an ideal way to check if this is being done.

Using the same crimes collection from above, we’d like to know how many cases of theft occurred within Chicago’s public transport system, Chicago Transit Authority (CTA).

We’d also like to sort these cases in descending order by date, and we’d only like to see the four relevant fields _id, location.description, primary_type (of crime), and date.

Here’s the full query ran in Visual Query Builder:

        "location.description" : /.*cta.*/i, 
        "primary_type" : "Theft"
        "_id" : 1.0,  
        "location.description" : 1.0, 
        "primary_type" : 1.0, 
        "date" : 1.0
        "date" : -1.0

Now let’s click on the Explain tab.

What we see is exactly what we want to happen: the database is applying the filter operator first.

From the original 2.5M documents, only 13K meet the filter criteria and are then passed on to the later stages.

The filter stage should be applied in the early stages of a query

If Visual Explain shows you that this isn’t the case, then the next step would be to debug your query.

A way to debug

Say, for example, that you wanted to know how many customers have priority support and live in Kansas, from your pool of 1,000 customers.

You already have a hunch that this can’t be a big number, but Visual Explain shows that 485 documents – 485 customers – meet the criteria:

Visual Explain can be useful in determining where queries could have gone wrong

This is clearly way too a large a number that could only point to an error in your query logic. (Do you spot the $ne operator? 🙂 )

While a simple example, it still illustrates the usefulness of having a quick and visual way of double-checking the soundness of your query.

Interested in learning more about MongoDB performance tuning? Check out these related tutorials:

Updated on January 10, 2019

Was this article helpful?

Related Articles