Skip to content

Instantly share code, notes, and snippets.

@marceloalmeida
Forked from rantav/README.md
Last active January 18, 2019 15:53
Show Gist options
  • Save marceloalmeida/ea66e8b0324bceeb8df26cecc24dc321 to your computer and use it in GitHub Desktop.
Save marceloalmeida/ea66e8b0324bceeb8df26cecc24dc321 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)

Find all slow queries on a specific time window (keep in mind the capped collection)

> db.system.profile.find({ts: {$gte: ISODate("2019-01-18T11:20:00.000Z"), $lt: ISODate("2019-01-18T11:59:59.999Z")}}).sort({$natural: -1})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment