Have you noticed that your app is slow? Are snapshots of your MongoDB instances trending in the wrong direction? Troubleshooting MongoDB isn’t always easy because there are often a combination of issues causing slow database response. One of the most common issues we see slowing down MongoDB stem from index issues.
If you’ve ever used physical cookbooks or large reference manuals, you know how handy it is to have a good index at the back of the book. If there’s no index that tells you what page the “chicken parmesan” recipe is on, it can take forever to flip through the book to find it. The same holds true for MongoDB indexes. The larger your cookbook or MongoDB instance, the more important it is to have a good index to help find things quickly.
Index analysis
Our jumping off point for index and query analysis is through the explain() method. We won’t go into too much detail here, but this is how we determine the number of documents that have to be scanned and retrieved.
The first area of focus in the explain output is:
“totalKeysExamined” : 0
This indicates the number of index keys being examined, which is 0.
“totalDocsExamined” : 25359,
This indicates the number of documents scanned in the collection, which is 25359.
Going back to our cookbook analogy, if you want to find “chicken parmesan” (our query), it’s way faster to look it up in the index instead of looking through the whole cookbook. In MongoDB, Indexes help queries by narrowing down the search scope when looking up a document. Poor performing queries are usually the result of not having an index at all or not setting up the appropriate indexes for your queries.
On the flip side, too many indexes also impact write performance. If every “the” and every “and” is indexed, this slows down your MongoDB instances because each time data is inserted into results, the indexes are updated. Also, while it’s better than having no index at all, it can take longer for MongoDB to find what you’re looking for and then everything slows down.
Index examples
We’ll be looking at example scenarios of different index cases using some open source restaurant test data. Note that the explain function will be used to illustrate the query execution path.
Missing index
The basic function of an index is to allow queries to avoid having to scan through the whole collection to retrieve specific information. This query shows a scan through the whole document because it’s missing an index:
db.restaurants.find({“borough” : “Bronx”}).explain(‘executionStats’)
"nReturned" : 2338,
"executionTimeMillis" : 14,
"totalKeysExamined" : 0,
"totalDocsExamined" : 25359,
"totalChildMillis" : NumberLong(13),
"shards" : [
{
"shardName" : "8ab9187a6e974a9dafa251fee94c5a67",
"executionSuccess" : true,
"executionStages" : {
"stage" : "COLLSCAN",Not having the right index
Queries also have to be formulated in such a way that they’re able to use the correct index. In a lot cases, queries need to include the predicate field or the first field of a compound/concatenated index for it to be able to use the index.
Here we have an index on “borough” : 1 “restaurant_id” : 1 (in this order). However, the query db.restaurants.find({“restaurant_id” : “30075445”}); is unable to utilize the index because it does not contain “borough” as part of the search criteria as it is the predicate in the index.
"executionSuccess" : true,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"restaurant_id" : {
"$eq" : "30075445"
}
},
"$eq" : "30075445"
}Fully managed MongoDB: Get Started Free
ObjectRocket for MongoDB gives you access to DBAs and database experts that proactively help you optimize your indexes and queries, plus we give you an easy way to scale MongoDB in the cloud. Get started with a free pilot today, which includes free consultation and up to 5GB on an MMAPv1 of storage for prototyping your app.