Optimize slow MongoDB queries using MongoLab

You’ve launched your application, driven some traffic to it (maybe it’s even “gone viral”!), accumulated a meaningful volume of data and now it feels like some of your queries have become sluggish. What now? Time for query profiling!  MongoLab can help.

Step 1: Turn on the profiler

To turn on the query profiler log in to your MongoLab account and navigate to your database’s home screen. From there select the “Tools” tab and the “commands” sub-tab. Then select the “profile (log slow)” command.

The default threshold for slow queries is 100ms. To change this threshold, pass a different value for “slowms”:

{
   "profile" : 1,
   "slowms" : 50
}

Step 2: Examine the ‘system.profile’ collection

The profiler deposits all profile data into a collection named ‘system.profile’. After running your application for a bit (and hopefully accessing the code paths that you believe might be executing slow queries), go into the ‘system.profile’ collection and take a look.

The resulting documents represent operations taking more than 100ms (or whatever you set the threshold to be). Each profile entry has the following form:

{
   "ts" : <timestamp>,
   "info" : <detailed-info-on-the-operation>,
   "millis" : <how-long-it-took>,
}

Step 3: Diagnose and take action

If your slow operations are queries, the most likely problem is that you are doing needless “collection scans” on large collections. This means that the database is iterating through each document in the collection to compute the query result instead of using an index.

You can identify queries doing collection scans by examining the “info” field of the profile entries. If the ‘nscanned’ (number of documents scanned) value is significantly greater than the ‘nreturned’ (number of documents returned) value, you should consider creating an index to optimize the query. You can see an example of this in the second profile entry in the screenshot above.

The next step in the diagnosis would be to look at the query itself. Is it filtering on a field that could be indexed? If that field has a decent range of values (vs. say two values, like with booleans) an index might help a great deal. Is the query performing a sort on an unindexed field? If so, indexing that field is a good idea. Finally, consider compound indexes if you are filtering and sorting by different fields in the same query. You can add indexes for a collection in MongoLab by going to the “Indexes” tab on any collection home screen. See MongoDB’s documentation on indexes here.

Of course, all of this applies to any operation with a query or sort component. This can include updates, deletes, and the use of findAndModify.

Other possible reasons why your queries are slow:

  • You not limiting a large result set (i.e. you are shipping a million records back to your app instead of using skip() and limit() to page through the results)
  • Your documents are simply enormous. Consider masking some of the fields so that only a subset of fields are returned.
  • You are executing a count() on a query that filters by one or more fields that have not been indexed
  • You are using server-side javascript execution (can be slow)

Step 4: Turn off the profiler

You don’t want to leave the profiler on if you are not using it. While not huge, there is some overhead to leaving it on (i.e. it can slow things down a tad). To turn it off simply go back to the same screen you used to turn in on in Step 1, select the “profile (turn off)” command from the menu, and run the command.

Further reading

Subscribe

Subscribe to our e-mail newsletter to receive updates.

16 Responses to Optimize slow MongoDB queries using MongoLab

  1. Brian Armstrong 2011/12/31 at 9:26 pm #

    This is really an awesome feature, thanks for adding it!

    • MongoLab 2011/12/31 at 9:31 pm #

      So glad you are finding it useful!

  2. Brian Armstrong 2011/12/31 at 2:26 pm #

    This is really an awesome feature, thanks for adding it!

    • MongoLab 2011/12/31 at 2:31 pm #

      So glad you are finding it useful!

  3. Brian Armstrong 2011/12/31 at 9:41 pm #

    By the way, in case it helps anyone – to clear out the system.profiles (after making some changes so you can re profile) you need to first stop profiling.  Then run “db.system.profile.drop()” after connecting to the mongo shell.  If you are on heroku you can get your username/password by running “heroku config”.  Then you can re-enable it and profile a new batch.

  4. Brian Armstrong 2011/12/31 at 2:41 pm #

    By the way, in case it helps anyone – to clear out the system.profiles (after making some changes so you can re profile) you need to first stop profiling.  Then run “db.system.profile.drop()” after connecting to the mongo shell.  If you are on heroku you can get your username/password by running “heroku config”.  Then you can re-enable it and profile a new batch.

Trackbacks/Pingbacks

  1. Profiling tool for MongoDB » Refactored scope - 2011/08/31

    […] increasing more important to be able to understand what the hell it is doing. I ran across this post this morning, it’s short but a good place to start in learning about profiling Posted by […]

  2. Cheatsheet: 2011 09.01 ~ 09.06 - gOODiDEA.NET - 2011/09/06

    […] Optimize slow MongoDB queries using MongoLab […]

  3. Thought this was cool: MongoDB Query Profiling on MongoLab | Lisheng Yu - 2011/09/06

    […] MongoDB Query Profiling on MongoLab: […]

  4. MongoDB Query Profiling on MongoLab | Nosql online - 2011/09/06

    […] MongoDB Query Profiling on MongoLab: […]

  5. Announcing MongoLab's Remote Connections - a MongoDB GUI admin tool for any cloud MongoDB | MongoLab - MongoDB Hosting - 2012/01/04

    […] to port our Web GUI to manage their independent instances. With features like Saved Queries, Query Profiler Enabler, Table Views with projections, and a JSON-validating document editor, our Web GUI makes short work […]

  6. Announcing MongoLab’s Remote Connections – a MongoDB GUI … | Programmer Solution - 2012/01/06

    […] to port our Web GUI to manage their independent instances. With features like Saved Queries, Query Profiler Enabler, Table Views with projections, and a JSON-validating document editor, our Web GUI makes short work […]

  7. harbaremnmn - 2012/05/02

    Gems form the internet…

    […]very few websites that happen to be detailed below, from our point of view are undoubtedly well worth checking out[…]……

  8. website - 2012/05/03

    Websites worth visiting…

    […]here are some links to sites that we link to because we think they are worth visiting[…]……

  9. Get Off Your But - 2012/05/05

    Get Off Your But…

    […]usually posts some pretty fascinating stuff like this. If you?re new to this site[…]…

  10. Remote Dex: Index Analysis Using the Profile Collection | MongoLab - MongoDB Hosting - 2012/07/16

    […] Log into your database through the mongo shell and run db.setProfilingLevel(1). If your MongoDB is hosted with us at MongoLab, you can also enable profiling through our UI. […]

Leave a Reply