How to find slow queries in MongoDB

Four tools, four questions

• What is running now? The $currentOp aggregation stage.
• What has been slow recently? The database profiler, which writes slow operations to system.profile.
• What was slow when the profiler was off? The diagnostic log, which records slow operations at every profiling level.
• Why is this query slow? explain("executionStats").

The examples use mongosh against MongoDB 8.0. Most of them work on 5.0 and later; steps that need 8.0 say so. Run them on a mongod (a replica set member or standalone): the profiler cannot be enabled on mongos, so in a sharded cluster you profile each shard.

1. What is running now

db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true } },
  { $match: { active: true, secs_running: { $gte: 5 } } },
  { $project: { _id: 0, opid: 1, op: 1, ns: 1, secs_running: 1,
                planSummary: 1, appName: 1, client: 1, waitingForLock: 1 } },
  { $sort: { secs_running: -1 } }
])

$currentOp must be the first stage and must run on the admin database. allUsers: true needs the inprog privilege when access control is on. A planSummary of COLLSCAN on a large collection is the usual suspect; waitingForLock: true means the operation is waiting for something else, not working.

2. Turn on the profiler

The profiler is off by default (level 0). Level 1 records operations slower than slowms (default 100 ms). Level 2 records every operation and is rarely what you want on a busy server.

use shop
db.getProfilingStatus()

// record operations slower than 100 ms on this database
db.setProfilingLevel(1, { slowms: 100 })

// on a busy server, keep only a fraction of them
db.setProfilingLevel(1, { slowms: 50, sampleRate: 0.2 })

// or record only what matches a filter (slowms and sampleRate are then ignored)
db.setProfilingLevel(1, { filter: { op: "query", millis: { $gt: 200 } } })

• Per database, per node. The setting applies to the current database on the node you are connected to.
• Options you leave out keep their old value. After setting sampleRate: 0.2, a later db.setProfilingLevel(1, { slowms: 0 }) still samples 20%. Pass sampleRate: 1 to go back.
• Not persistent. It reverts on restart unless you set operationProfiling.mode in the configuration file.
• Small by default. system.profile is a capped collection of 1 MB, so old entries roll off quickly. Read it soon after the problem.
• 8.0 change. From MongoDB 8.0 the threshold is compared with workingMillis, which leaves out time spent waiting for locks and flow control. millis still shows the full duration.

3. Read the slowest operations

db.system.profile.find(
  { ns: "shop.orders" },
  { ts: 1, op: 1, millis: 1, planSummary: 1,
    keysExamined: 1, docsExamined: 1, nreturned: 1, appName: 1 }
).sort({ millis: -1 }).limit(20)

Three fields tell most of the story:

• planSummary: COLLSCAN means every document was read; IXSCAN { … } names the index used.
• docsExamined against nreturned: reading 60,000 documents to return 15 points to a missing or wrong index.
• keysExamined far above nreturned: an index is used, but its key order does not match the filter well.

Aggregations appear with op: "command", so do not filter commands out if your application uses pipelines.

4. Group by query shape

One slow call is an anecdote; a query shape that runs thousands of times is a workload. MongoDB 8.0 adds queryShapeHash to profiler entries, which is the same for queries that differ only in their values.

db.system.profile.aggregate([
  { $match: { ns: "shop.orders", queryShapeHash: { $exists: true } } },
  { $group: {
      _id: "$queryShapeHash",
      calls: { $sum: 1 },
      totalMs: { $sum: "$millis" },
      maxMs: { $max: "$millis" },
      docsExamined: { $sum: "$docsExamined" },
      returned: { $sum: "$nreturned" },
      plan: { $last: "$planSummary" },
      example: { $last: "$command.filter" }
  } },
  { $sort: { totalMs: -1 } },
  { $limit: 10 }
])

On versions before 8.0, group by queryHash instead. It hashes the plan cache shape, which is close enough for this purpose. 8.0 also copies it into planCacheShapeHash, and queryHash is deprecated there.

On MongoDB Atlas (M10 and above), the $queryStats stage keeps similar totals per query shape without the profiler. MongoDB documents it as unsupported and its output may change, so treat it as a view, not something to build on.

5. Slow queries without the profiler

Even at level 0, operations slower than slowms are written to the diagnostic log as structured JSON with the message Slow query. That is often the only record of last night's incident. The getLog command returns the most recent entries held in memory.

db.adminCommand({ getLog: "global" }).log
  .map(line => JSON.parse(line))
  .filter(e => e.msg === "Slow query" && e.attr.ns === "shop.orders")
  .map(e => ({ t: e.t.$date, ms: e.attr.durationMillis,
               plan: e.attr.planSummary, docs: e.attr.docsExamined }))

For anything older, search the log file itself for "msg":"Slow query". Each line carries much the same fields as a profiler entry, such as planSummary, docsExamined and durationMillis.

6. Why a query is slow

Take the filter and sort from the profiler entry and run it through explain.

db.orders.find({
  status: "PENDING",
  createdAt: { $gt: new Date(Date.now() - 3600 * 1000) }
}).sort({ createdAt: -1 }).explain("executionStats")

• queryPlanner.winningPlan: read the stage tree from the inside out. With the slot-based engine the tree sits under winningPlan.queryPlan.
• COLLSCAN anywhere in the tree on a large collection.
• A SORT stage: the sort happens in memory because no index supplies the order.
• totalDocsExamined and totalKeysExamined against nReturned. In a good plan all three are close.

7. Fix it and confirm

For a filter on equality fields plus a range or sort, put the equality fields first, then the sort, then the range. Here status is matched exactly and createdAt is both sorted and ranged.

db.orders.createIndex({ status: 1, createdAt: -1 })

Run the same explain again. In this example the plan changed from SORT over COLLSCAN with 60,000 documents examined to FETCH over IXSCAN with 15 keys and 15 documents examined for 15 returned.

db.orders.aggregate([
  { $indexStats: {} },
  { $project: { name: 1, ops: "$accesses.ops", since: "$accesses.since" } }
])

Counts reset on restart and are per node, so an index that shows zero on one member may serve reads on another. Review unused indexes before dropping them; every index slows writes.

The same steps in Quorum

In Quorum you run these commands in Query Studio against your MongoDB connection, with the target and environment printed beside Run so you can see which server a profiler change goes to before you send it.

• Query Studio with completion from the live connection, streaming results and personal data masked in the grid.
• Compare results by key to check a before-and-after run of the same query.
• Shared query library and runbooks, so the profiler steps above are saved once and reused by the whole team.
• Scheduled queries, if you want the shape report on a timer.