Skip to content

Instantly share code, notes, and snippets.

@rantav
Created August 23, 2012 06:13
Show Gist options
  • Save rantav/3433277 to your computer and use it in GitHub Desktop.
Save rantav/3433277 to your computer and use it in GitHub Desktop.
Find slow queries in mongo DB

A few show tricks to find slow queries in mongodb

Enable profiling

First, you have to enable profiling

> db.setProfilingLevel(1)

Now let it run for a while. It collects the slow queries ( > 100ms) into a capped collections, so queries go in and if it's full, old queries go out, so don't be surprised that it's a moving target...

Histogram of the slowest collections (collections with the slowest queries) - number of queries per collection

This presents a histogram of slow collections

> db.system.profile.group({key: {ns: true}, initial: {count: 0}, reduce: function(obj,prev){ prev.count++;}})

Histogram of the slowest collections (collections with the slowest queries) - number of millies spent in each collection

> db.system.profile.group({key: {ns: true}, initial: {millis: 0}, reduce: function(obj, prev){ prev.millis += obj.millis;}})

Find the most recent slow query

> db.system.profile.find().sort({$natural: -1}).limit(1)

Find the single slowest query in the capped system.profile collection right now

> db.system.profile.find().sort({millis: -1}).limit(1)
@poxaV
Copy link

poxaV commented Apr 15, 2016

It would be much easier to cut & paste if the "> " was removed from the example mongo shell commandline.

@vn971
Copy link

vn971 commented Mar 23, 2017

@poxaV, yeah, agree. Made my fork to remove these letters though, no problem. It's git..

@congthang1
Copy link

please add .pretty() lol

@benjamingr
Copy link

benjamingr commented Jan 13, 2020

Thanks! @rantav

Also apparently after talking to Mongo Atlas they said db.setProfilingLevel(1) can pretty much decimate our cluster 🤷‍♂️

@MarkEmerson
Copy link

good start but caution!

ensure you set profiling back to : db.setProfilingLevel( 0, 100 )

else you can watch your disk space grow in gigabytes

@caltuntas
Copy link

db.collection.group() has been deprecated since Mongo 3.4 so db.system.profile.group queries won't work if you have newer version.
You have to use aggregate operations, for instance

db.system.profile.aggregate(
{ $group : {
_id :"$op",
count:{$sum:1},
"max response time":{$max:"$millis"},
"avg response time":{$avg:"$millis"}
}});

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment