[HOWTO] Optimzing your Mongo indexes and Queries within Meteor


#1

For a fast server, you need fast queries. For fast queries you need good indexes.

Checking to see if your mongo db queries run efficiently is kinda hard to do from within Meteor. It is possible, but the info on how to do it is all over the place or outdated.

So usually you go to a Mongo shell to check which indexes you have:

> db.mycollection.getIndexes()

[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "meteor.mycollection"
	}
]

And to check if your queries are running smoothly you do something like:

> db.mycollection.find({mykey:'abc'}).explain()

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "meteor.mycollection",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"mykey" : {
				"$eq" : "abc"
			}
		},
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"mykey" : {
					"$eq" : "abc"
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"ok" : 1
}

where "stage" : "COLLSCAN" should be a red flag.

It took me quite a bit of time to figure out a way to check them from within Meteor (during development, not for production use! ), and I came up with 2 functions:

export const explainQuery = async (collection, query) => {
  console.log(`explainQuery on ${collection._name}:`)
  const result = await raw.find(query).explain()
  console.log(JSON.stringify(result, null, 2) )
  console.log('///////////////////////')
}

export const getIndexes = async collection => {
  console.log(`Indexes on ${collection._name}:`)
  const raw = collection.rawCollection()
  const result = await raw.indexes()
  console.log(JSON.stringify(result, null, 2) )
  console.log('///////////////////////')
}

Now, in any server-side publication you can temporarily import these functions to check your pub:

import { explainQuery, getIndexes } from '/imports/shared/server/explainQuery'

Meteor.publish('mycollection', function(args){
 const query = { mykey: 'abc' }
 getIndexes(MyCollection)
 explainQuery(MyCollection, query)
 return MyCollection.find(query)
})

By simply inserting explainQuery and getIndexes in my pubs, I can see where and how I need to add indexes:

MyCollection.rawCollection().createIndex({ 'mykey': 1 })
    .then( () => {}, e => console.log(e.message))

And now, the database is happy:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "meteor.mycollection",
		"indexFilterSet" : false,
		 ...
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"mykey" : 1,
					"status" : 1
				},
				"indexName" : "mykey_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				...
			}
		},
		"rejectedPlans" : [ ]
	},
	"ok" : 1
}

( note the: "stage" : "IXSCAN" instead of COLLSCAN)

Don’t forget to remove them again.

Hope it helps for others.

Eleventy


#2

I use a different approach.

For every Meteor.publish with a query, I add a collection._ensureIndex function with the same query:

Meteor.publish('player', function (name) {
  return Players.find({ name: name });
});
Meteor.startup(function () {  
  Players._ensureIndex({ name: 1 });
});

What do you think about this?


#3

Yeah, that works.

Running ._ensureIndex() on every startup shouldn’t be an issue: In production, you don’t restart that often, and if the index is already there, it has no impact.

Adding a basic index like ._ensureIndex({ name: 1 }) is a no brainer, and can do wonders for perfomance. The troubles start when you have to add multiple or complex indexes. And that’s when you should start looking at .explain(), because to many or wrong indexes can be worse than no indexes.

(Note: I’m not an really expert on indexing.)


#4

I think it should be

explainQuery(MyCollection, query);

It’s useful tool for development. Thank you.