Skip to content

Instantly share code, notes, and snippets.

@kgorman
Last active May 7, 2024 13:34
Show Gist options
  • Save kgorman/134896c7414fde8e090b to your computer and use it in GitHub Desktop.
Save kgorman/134896c7414fde8e090b to your computer and use it in GitHub Desktop.
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}).limit(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
Copy link

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

@kgorman
Copy link
Author

kgorman commented Aug 9, 2022

fixed! better late than never!

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