Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MongoDB Profiler Cheat Sheet
// a list of useful queries for profiler analysis. Starting with the most basic.
// 2.4 compatible
//
// output explained:
//
{
"ts" : ISODate("2012-09-14T16:34:00.010Z"), // date it occurred
"op" : "query", // the operation type
"ns" : "game.players", // the db and collection
"query" : { "total_games" : 1000 }, // query document
"ntoreturn" : 0, // # docs returned with limit()
"ntoskip" : 0, // # of docs to skip()
"nscanned" : 959967, // number of docs scanned
"keyUpdates" : 0, // updates of secondary indexes
"numYield" : 1, // # of times yields took place
"lockStats" : { ... }, // subdoc of lock stats
"nreturned" : 0, // # docs actually returned
"responseLength" : 20, // size of doc
"millis" : 859, // how long it took
"client" : "127.0.0.1", // client asked for it
"user" : "" // the user asking for it
}
// last few entries
show profile
// sort by natural order (time in)
db.system.profile.find({}).sort({$natural:-1})
// sort by slow queries first
db.system.profile.find({}).sort({$millis:-1})Alimit(10);
// anything > 20ms
db.system.profile.find({"millis":{$gt:20}})
// single coll order by response time
db.system.profile.find({"ns":"test.foo"}).sort({"millis":-1})
// regular expression on namespace
db.system.profile.find( { "ns": /test.foo/ } ).sort({millis:-1,$ts:-1})
// anything thats moved
db.system.profile.find({"moved":true})
// large scans
db.system.profile.find({"nscanned":{$gt:10000}})
// anything doing range or full scans
db.system.profile.find({"nreturned":{$gt:1}})
//
// using the agg framework
//
// response time by operation type
db.system.profile.aggregate(
{ $group : {
_id :"$op",
count:{$sum:1},
"max response time":{$max:"$millis"},
"avg response time":{$avg:"$millis"}
}});
// slowest by namespace
db.system.profile.aggregate(
{ $group : {
_id :"$ns",
count:{$sum:1},
"max response time":{$max:"$millis"},
"avg response time":{$avg:"$millis"}
}},
{$sort: {
"max response time":-1}
});
// slowest by client
db.system.profile.aggregate(
{$group : {
_id :"$client",
count:{$sum:1},
"max response time":{$max:"$millis"},
"avg response time":{$avg:"$millis"}
}},
{$sort: {
"max response time":-1}
});
// summary moved vs non-moved
db.system.profile.aggregate(
{ $group : {
_id :"$moved",
count:{$sum:1},
"max response time":{$max:"$millis"},
"avg response time":{$avg:"$millis"}
}});
db.system.profile.aggregate([
{ $project : {
"op" : "$op",
"millis" : "$millis",
"timeAcquiringMicrosrMS" : { $divide : [ "$lockStats.timeAcquiringMicros.r", 1000 ] },
"timeAcquiringMicroswMS" : { $divide : [ "$lockStats.timeAcquiringMicros.w", 1000 ] },
"timeLockedMicrosrMS" : { $divide : [ "$lockStats.timeLockedMicros.r", 1000 ] },
"timeLockedMicroswMS" : { $divide : [ "$lockStats.timeLockedMicros.w", 1000 ] } }
},
{ $project : {
"op" : "$op",
"millis" : "$millis",
"total_time" : { $add : [ "$millis", "$timeAcquiringMicrosrMS", "$timeAcquiringMicroswMS" ] },
"timeAcquiringMicrosrMS" : "$timeAcquiringMicrosrMS",
"timeAcquiringMicroswMS" : "$timeAcquiringMicroswMS",
"timeLockedMicrosrMS" : "$timeLockedMicrosrMS",
"timeLockedMicroswMS" : "$timeLockedMicroswMS" }
},
{ $group : {
_id : "$op",
"average response time" : { $avg : "$millis" },
"average response time + acquire time": { $avg: "$total_time"},
"average acquire time reads" : { $avg : "$timeAcquiringMicrosrMS" },
"average acquire time writes" : { $avg : "$timeAcquiringMicroswMS" },
"average lock time reads" : { $avg : "$timeLockedMicrosrMS" },
"average lock time writes" : { $avg : "$timeLockedMicroswMS" } }
}
]);
@peterkuiper

This comment has been minimized.

Copy link

peterkuiper commented Sep 20, 2016

Hi Kenny,

Great work, I watched your presentation and it is helping me a lot! I noticed there's a small copy/paste error at line 33:

db.system.profile.find({}).sort({$millis:-1})Alimit(10);

A should be .

Cheers

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.