DBAtlas · Database operations atlas

Extract the slowest queries and query shapes from profiler data

MongoDBPerformance

Command

// Recent slow operations (in the db where profiling is on; see set-profiling):
db.system.profile.find({ millis: { $gte: 100 } }, { ts: 1, ns: 1, op: 1, millis: 1, planSummary: 1, keysExamined: 1, docsExamined: 1, nreturned: 1, appName: 1 }).sort({ ts: -1 }).limit(20)
// Total time per query shape (those consuming the most time):
db.system.profile.aggregate([
  { $group: { _id: { ns: "$ns", plan: "$planSummary", shape: "$queryHash" }, n: { $sum: 1 }, totalMs: { $sum: "$millis" }, maxMs: { $max: "$millis" }, avgDocs: { $avg: "$docsExamined" } } },
  { $sort: { totalMs: -1 } },
  { $limit: 10 }
])
// Queries not using an index (COLLSCAN):
db.system.profile.find({ planSummary: "COLLSCAN" }, { ns: 1, millis: 1, command: 1 }).sort({ millis: -1 }).limit(10)

What it does

From records in the system.profile collection, lists recent slow operations, total time per query shape (queryHash) and COLLSCAN queries that use no index.

When to use it

On a slowness complaint, to find which query consumes server time and to determine index needs.

Caution

system.profile is a 1 MB capped collection by default and is per database; on a busy system records roll over within minutes, so analyze while profiling is on or right after. Profiling level 2 or slowms 0 writes every operation and adds significant overhead. Records are kept only on the member where they ran and are not replicated.

Open in DBAtlas →

Related