Find slow queries in mongo DB

README.md

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)

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

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

please add .pretty() lol

Thanks! @rantav

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

good start but caution!

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

else you can watch your disk space grow in gigabytes

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"}
}});