Becoming Fast(AF) - Mongo Query Structure and Indexes

Recently, we’ve been looking into the platform’s performance and focusing on the primary system of record, which uses MongoDB via Mongo Atlas on AWS. Coming from the relational side, I’m familiar with the common areas to look at like latch_contention and page_io_latch_contention and the common items like r/w amplification and cache hit ratios. However, MongoDB was new to me, so what better way to dive deep than by digging into our real environments and looking at real workloads.

DISCLAIMER: While these are some actual queries that were written or generated, all customer-specific information has been obfuscated, and this is not from product but our development (dev) environment. No data was hurt in this experiment.

The Baseline

When looking into the Mongo Atlas UI, one of my favorite pages is the Query Insights page, which is where I can see how good or bad some queries are performing. One that stood out was a query that was taking a long time to run and was being run quite frequently. It wasn’t using an index (eek) and was doing a lot of work to get the data back.

We will start here.

The following is the query that was being run, as you can tell it had a lot of stuff going on including a mix of $or, $and, $in, $eq, $ne, $sort, $skip, $limit, $addFields, $divide, $subtract, $isArray, $size, $multiply, $cond, and $subtract. The “whole shebang”.

NOTE: some of these are auto-generated on the fly, so don’t be concerned about why a human would write something like this.

[
  {
    "$match": {
      "$or": [
        {
          "$and": [
            {
              "reported_by_ids": {
                "$in": ["don:identity:dvrv-us-1:devo/SAMPLE:revu/SAMPLE"]
              }
            },
            {
              "rev_oid": {
                "$eq": "don:identity:dvrv-us-1:devo/SAMPLE:revo/SAMPLE"
              }
            },
            {
              "subtype": {
                "$eq": "dependent_fields_demo"
              }
            },
            {
              "dev_oid": {
                "$eq": "don:identity:dvrv-us-1:devo/SAMPLE"
              }
            },
            {
              "rev_oid": {
                "$eq": "don:identity:dvrv-us-1:devo/SAMPLE:revo/SAMPLE"
              }
            }
          ]
        },
        {
          "$and": [
            {
              "reported_by_ids": {
                "$in": ["don:identity:dvrv-us-1:devo/SAMPLE:revu/SAMPLE"]
              }
            },
            {
              "dev_oid": {
                "$eq": "don:identity:dvrv-us-1:devo/SAMPLE"
              }
            },
            {
              "rev_oid": {
                "$eq": "don:identity:dvrv-us-1:devo/SAMPLE:revo/SAMPLE"
              }
            }
          ]
        }
      ]
    }
  },
  {
    "$match": {
      "work_type": {
        "$in": ["Work_WorkTypeEnumTicket"]
      },
      "$or": [
        {
          "object_type": {
            "$eq": "ticket"
          }
        }
      ],
      "is_deleted": {
        "$ne": true
      },
      "dev_oid": "don:identity:dvrv-us-1:devo/SAMPLE"
    }
  },
  {
    "$sort": {
      "created_date": -1
    }
  },
  {
    "$skip": 1000
  },
  {
    "$limit": 501
  },
  {
    "$addFields": {
      "age_days": {
        "$divide": [
          {
            "$subtract": ["$$NOW", "$created_date"]
          },
          86400000
        ]
      },
      "rev_score": {
        "$add": [
          {
            "$multiply": [
              6,
              {
                "$cond": [
                  {
                    "$isArray": "$rev_score_metadata.rev_orgs_via_tickets"
                  },
                  {
                    "$size": "$rev_score_metadata.rev_orgs_via_tickets"
                  },
                  0
                ]
              }
            ]
          },
          {
            "$multiply": [
              3,
              {
                "$cond": [
                  {
                    "$isArray": "$rev_score_metadata.rev_orgs_via_part"
                  },
                  {
                    "$size": "$rev_score_metadata.rev_orgs_via_part"
                  },
                  0
                ]
              }
            ]
          }
        ]
      }
    }
  }
]

Result

Well, to be a little bit more descriptive, things didn’t look pretty. When we look at the Explain results, we can see the following:

Non-Cached == REALLY BAD

With a cold cache, we can see that performance was… bad, even for such a small number of documents being scanned. At scale this can get exponentially worse.

Even though this “used an index”, it didn’t use it for every required field, and still resulted in document scans. When we look at the overall execution time, it was 3.23 seconds! :X Yea, definitely not good, but the only way to go from here was up.

Cached == Better, But Still Bad

When the cache was hot (you cannot assume this), performance did look better. However, there was still much more room to improve. The overall execution time was ~335 ms which is still not good, but orders of magnitude better than before. With a small dataset size you always want as much as possible to be pulling from cache, however, at massive scale this cannot be the case and hence should not be assumed.

But Why?

When we look at the Query Performance Summary we can see it is returning 145 docs, is examining 9221 docs and is using an index only consisting of keys for dev_oid and created_date.

Because we are filtering on fields outside of this index, it has to do a full scan, which as we will later find out, is primarily impacted by the use of $skip which causes it to read every doc from the beginning.

Now, let’s make this better…

Experiment: Removing $or and unindexed fields

I know that the use of $or can sometimes be expensive as it may lead to the branching of evaluation and processing of multiple queries. So, I figured, let’s try removing it and see what happens.

Changes

...
-     "$or": [
-       {
-         "$and": [
-           {
-             "reported_by_ids": {
-               "$in": ["don:identity:dvrv-us-1:devo/SAMPLE:revu/SAMPLE"]
-             }
-           },
-          {
-            "rev_oid": {
-              "$eq": "don:identity:dvrv-us-1:devo/SAMPLE:revo/SAMPLE"
-            }
-          },
-          {
-            "subtype": {
-              "$eq": "dependent_fields_demo"
-            }
-          },
-           {
-             "dev_oid": {
-               "$eq": "don:identity:dvrv-us-1:devo/SAMPLE"
-             }
-           },
-           {
-             "rev_oid": {
-               "$eq": "don:identity:dvrv-us-1:devo/SAMPLE:revo/SAMPLE"
-             }
-           }
-         ]
-       },
-       {
...
-        }
-      ]
...
-    "$or": [
-       {
-         "object_type": {
-           "$eq": "ticket"
-         }
-       }
-     ],
...

Result

If we compare this to our baseline of ~323ms (cached), with this change, we achieved a decrease down to ~193ms, is almost a 33% reduction and not too bad. However, the variance can fluctuate and be much more drastic depending on the data and the query. More to do.

But Why?

Here we can see that it was able (or randomly preferred) to use a different index with more input fields than the default. This is likely because we removed any unindexed fields from the query, allowing it to pick a more suitable index.

But did it really make a difference removing the unindexed field? No. Suppose we kept the $or and removed the unindexed field subtype from the query. In that case, we get the same baseline performance, meaning the performance increases here are not really from removing the unindexed field but rather the removal of $or.

Experiment: Removing $skip

I have no idea why, or who is doing still doing a $skip, as everyone should be using cursors for any pagination; however, this didn’t look to be the case in some areas. Let’s try removing it and see what happens.

Changes

-  {
-    "$skip": 1000
-  },

Result: Holy $%!@!

I’ll admit I didn’t expect this to make that big of a difference; however, it did. When looking at the reasons behind this, it became apparent as $skip is very costly as it requires Mongo to walk from the beginning of the collection or index before it can get the offset and start returning the page of data.

If we compare this to our baseline of ~3.2s (non-cached) and ~323ms (cached), with this one change we achieved a decrease down to ~45ms, which is phenomenal! This is a great example of how a small change can have a big impact.

It turns out this was due to some legacy tech debt where some clients hadn’t migrated over to cursor based queries. This is now being fixed.

But Why?

If we look at all of the previous examples, we can see the docs scanned were ~9221 and the number returned were ~145. With Mongo, this ratio is very key as this is our read amplification and can be very costly without the use of indexes. However, those wouldn’t even help in this case with $skip.

So why are so many docs scanned? When $skip is used, Mongo has to start from the beginning of the collection or index and walk through all the documents until they reach the specified offset before returning the data page. This is why it is so costly and why it is not recommended to use it.

Without $skip we can see it only looked at the index keys and scanned 500 docs. Even without using the index containing all of the keys included in the filter, it was still much more efficient.

TODO: Determine why and how $skip and (non-)indexed fields work together. There are some interesting interactions here that need to be explored.

TL;DR

• Avoid $skip as much as possible
• Use the Explain functionality to analyze queries and planner queries
• Try to avoid $or where possible
• Duplication of predicates doesn’t make a huge impact

Additional Experiments

Below you’ll find some other experiments performed and their results. These are not as detailed as the previous ones, but still provide some insight into the performance improvements that can be made.