Making sure our database queries are fast

As Woovi scales, we have more and more charges and transactions. A charge represents a payment request, and a transaction represents a movement in our merchant bank accounts. These two collections in our MongoDB database are very big and still growing very fast.

To make sure, we don't push new code that causes a COLLSCAN (reading the whole table), we always need to make sure our queries are using the proper index.

Understand MongoDB Explain Output

We first need to understand how MongoDB plan their queries.
For this we are going to use the command db.collection.explain(), you can read more about it in the MongoDB docs

It can have 5 different operations:

• COLLSCAN for a collection scan
• IXSCAN for scanning index keys
• FETCH for retrieving documents
• GROUP for grouping documents
• SHARD_MERGE for merging results from shards
• SHARDING_FILTER for filtering out orphan documents from shards

COLLSCAN is the worst case, as it needs to scan the whole collection to find an item, aka, O(n), very slow for big collections like ours.
IXSCAN is the best operation, as it uses the index a balanced B-tree, in the worst case O(log(n))
FETCH is when they need to retrieve more documents, used in pagination
SHARD_MERGE is when you have a shared database and need to merge result of some shards
SHARDING_FILTER is when you need to filter data in shards.

Automating IXSCAN index testing

We use an In-Memory MongoDB in our tests to make sure our code is doing the right queries and aggregations in the database. This avoids many false positives and false negatives that happen when mocking the database.

As we are using a real MongoDB, we can call .explain() method when performing a find or aggregate and MongoDB will return the query planner showing the winning plan of how the database will execute the query.

The test to check if our query is using an index, called IXSCAN in MongoDB, it is very simple:

const getStageFromExplanation = (explanation: MongoExplanation) =>
  explanation.queryPlanner.winningPlan.inputStage.stage;

it('should test the ixscan from myquery', async () => {
  // force model index creation 
  await Model.syncIndexes();

  // create your test query
  const modelCursor = Model.find({
    name: 'Edu',
    age: '18',
  });

  // ask to explain
  modelCursor.explain();

  // wait for explanation
  const modelExplained = await modelCursor;

  //check if the query explanation uses index - IXSCAN
expect(getStageFromExplanation(modelExplained)).toEqual('IXSCAN');
});

We perform a find, ask for explanation using .explain method and check for IXSCAN in the winning plan of the query planner.

To sum up

As you scale, your database is getting more critical, so you can't push new code to production that will use a COLLSCAN, or do slow queries.
At Woovi we automated this performance check to make sure we are using proper index before pushing new code to production.
We also monitor performance regression using Kibana and Elastic APM both in staging and production environment.

References

• Explain Results
• MongoDB Explain

---

Woovi
Woovi is a Startup that enables shoppers to pay as they like. To make this possible, Woovi provides instant payment solutions for merchants to accept orders.

If you want to work with us, we are hiring!

---

Photo by Shiro hatori on Unsplash