MongoDB Performance Audit: The Profiler, explain, and $indexStats

Here's the fastest way to learn something true about your MongoDB performance today: turn the database profiler on at level 1 for one hour of peak traffic, then read what lands in system.profile. That single hour shows you which query shapes are slow, which are scanning collections instead of walking indexes, and which are sorting in memory. It won't tell you why, or what index to build, and that's what the rest of this audit is for.

In part one of this series we walked through what bad looks like on real MongoDB workloads: COLLSCANs, compound indexes in the wrong order, unbounded array growth, working sets that outgrow RAM, and write-side stalls. This article is the hands-on audit. Every check uses tools that ship with MongoDB or come free with Atlas. Nothing to install beyond the Database Tools, nothing to pay for. Budget half a day for a first pass on a production replica set.

We'll go tool by tool: the profiler finds the slow queries, explain diagnoses them, $indexStats finds the dead weight, mongotop and mongostat give you the live workload picture, and Performance Advisor shortcuts some of it if you're on Atlas. Then we'll work one finding end to end.

01Collect

Profiler

Slow operations into system.profile, grouped by query shape.

02Diagnose

explain

executionStats on each offender: stages, keys and docs examined.

03Prune

$indexStats

Usage per index on every member. Hide before you drop.

04Pressure

mongotop, mongostat

Per-collection time and cache, queue and cursor vitals per node.

05Atlas

Advisor

Ranked index suggestions from your workload, validated with explain.

---

Tool 1: The database profiler (collect the evidence)

The profiler writes operations that exceed a threshold into a capped collection called system.profile, per database. It's off by default. Turn it on from mongosh against the database you're auditing:

// Level 1: log only operations slower than slowms
db.setProfilingLevel(1, { slowms: 100 });

// Confirm what's active
db.getProfilingStatus();

Three settings to choose on purpose:

• Level. Use level 1 in production. Level 2 records every operation and will measurably tax a busy node; keep it for short bursts on a staging box.
• slowms. The default is 100 ms. On a latency-sensitive API-backing database, drop it to 50 or even 20. A query that takes 60 ms at 500 calls a minute is a bigger problem than one taking 400 ms once an hour.
• sampleRate. On very hot databases, pass sampleRate: 0.5 alongside slowms in the same setProfilingLevel call. It records half of the qualifying operations and halves the overhead.

Let it run through at least one representative peak hour, then aggregate by query shape rather than reading entries one at a time:

db.system.profile.aggregate([
  {
    $group: {
      _id: { ns: '$ns', plan: '$planSummary', op: '$op' },
      count: { $sum: 1 },
      avgMillis: { $avg: '$millis' },
      totalMillis: { $sum: '$millis' },
      avgDocsExamined: { $avg: '$docsExamined' },
      avgReturned: { $avg: '$nreturned' },
    },
  },
  { $sort: { totalMillis: -1 } },
  { $limit: 10 },
]);

How to read it. Sort by totalMillis, not avgMillis. A moderately slow query running constantly costs more than a horror story that runs nightly. Any group whose plan is COLLSCAN goes straight onto the findings list. So does any group where avgDocsExamined is 100x or more of avgReturned: that's a query doing far more work than it returns, usually an index that matches part of the filter but not the full predicate.

Two caveats. system.profile is a capped collection (1 MB by default), so on a busy system entries rotate out within minutes. Run your aggregation while the window is fresh, or recreate the collection larger. And profiling data is per node: if your application reads from secondaries, repeat the exercise there. When you're done, run db.setProfilingLevel(0). Slow operations above slowms still land in the server log either way, which makes mongod.log a poor man's profiler with no expiry.

Tool 2: explain("executionStats") (diagnose each offender)

For every query shape the profiler flagged, run it through explain with execution stats. This actually executes the query and reports what the planner did:

db.orders
  .find({ customerId: 'c-1042', status: 'open' })
  .sort({ createdAt: -1 })
  .explain('executionStats');

Ignore most of the output. Five fields tell the story:

The SORT stage deserves emphasis because it fails loudly. Blocking sorts have a 100 MB memory limit, beyond which the query either spills to disk (allowed by default since 6.0) or errors. The fix is almost always compound-index order, and the working heuristic is ESR: Equality, Sort, Range (docs). For the query above, that means:

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

Equality fields first (customerId, status), the sort field next (createdAt), range predicates last. An index with the same three fields in a different order can be nearly useless for this query, which is why "we have an index on that field" isn't the same claim as "this query is indexed."

Also glance at queryPlanner.rejectedPlans. If it's crowded, you have several near-identical indexes competing, which is a hint for the next section.

Tool 3: $indexStats (find the indexes nobody uses)

Every index you keep costs write amplification (each insert or update touches every index on the collection), RAM and disk. The $indexStats aggregation stage reports how often each index has been used since the node last restarted:

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

Pair it with sizes so you know what each candidate costs:

db.orders.stats().indexSizes; // bytes per index
db.orders.totalIndexSize(); // total for the collection

How to read it. An index with ops: 0 after weeks of uptime is a drop candidate, but check since, and check every replica set member, because secondaries serve their own reads and keep their own counters. Before dropping, hide it:

db.orders.hideIndex('status_1_legacyFlag_1');

A hidden index is still maintained on writes but invisible to the planner. If nothing regresses after a week or two, drop it for real; if something does, unhideIndex brings it back instantly with no rebuild. It's the safest index-hygiene mechanism MongoDB ships, and almost nobody uses it. Also look for prefix-redundant pairs: a single-field index on customerId is redundant once a compound index starting with customerId, status exists, since the compound index serves every query the single-field one can.

Tool 4: mongotop and mongostat (the live workload view)

The profiler and explain are query-level. These two ship with the Database Tools and show node-level pressure in real time.

mongotop reports time spent reading and writing per collection, per interval:

mongotop 15

How to read it. You already suspect which collections are hot; mongotop confirms or embarrasses that intuition. A collection dominating write time that isn't your main ingest path often points at a fan-out you forgot: audit logs, a chatty session store, a change-stream consumer writing back.

mongostat is the vitals monitor, one row per interval, one node per row with --discover:

mongostat --discover 5

The columns that matter for this audit:

• dirty and used. WiredTiger cache pressure. used pinned above roughly 80% with dirty climbing means eviction is struggling and your working set is outgrowing RAM, pattern 4 from part one.
• qrw and arw. Queued versus active readers and writers. Sustained queues mean the node can't keep up, not that queries are individually slow.
• getmore. Cursor batch fetches. High values usually mean large scans feeding an application loop.

Tool 5: On Atlas, Performance Advisor and the Query Profiler

If you run on Atlas, two built-ins shortcut parts of the manual work. The Performance Advisor (cluster view, Performance Advisor tab) analyzes your slow queries and produces ranked index suggestions, including drop suggestions for unused and redundant indexes, with the impact scored from your own workload. The Atlas Query Profiler (under Query Insights) gives you the system.profile analysis from Tool 1 as a scatter plot over the last 24 hours, with no capped-collection archaeology.

Treat Advisor suggestions the way you'd treat a colleague's PR: usually right, occasionally naive. It suggests indexes per query shape and won't always notice that one well-ordered compound index could replace three of its proposals. Validate each suggestion with explain("executionStats") before and after, same as any hand-built index.

---

One finding, end to end

Here's how the tools fit together on a single problem (illustrative numbers). The profiler aggregation puts one shape at the top by totalMillis: a find on orders filtering by tenantId and status, sorted by createdAt, averaging 640 ms and running about 9,000 times an hour. Its planSummary is IXSCAN { createdAt: 1, tenantId: 1 }.

Four readings could explain it, and each predicts something different in explain:

The second reading wins. The existing index leads with the sort field, so MongoDB walks the whole createdAt range and filters tenants as it goes. ESR says equality first:

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

Build it in a low-traffic window and watch rs.printSecondaryReplicationInfo() while it runs. Re-run the same explain: keys examined should drop to roughly nReturned. Then check $indexStats on every member for the old createdAt_1_tenantId_1. If nothing else uses it, hide it, wait a week, and drop it.

That's a complete, correct loop. It also took a profiler window, five commands, an index build and a calendar reminder, for one finding.

Where the DIY audit stops

Run this audit; it works. Be honest about its shape, though.

01Coverage

A snapshot

One profiler hour of one week. $indexStats counters reset on restart.

02Cost

Half a day

Per pass, per cluster. Quarterly quietly becomes after-incidents.

03Judgment

Interpretation

Turning a SORT stage and a 300:1 ratio into one ESR index is expertise.

04Follow-through

Tickets

Build, hide, wait, verify, drop. Detection was never the bottleneck.

It's a snapshot. The profiler window you analyzed is one hour of one week. The next deploy, the next tenant onboarded, the next 10 GB of documents shift the picture, and $indexStats counters silently reset at every restart.

It's engineer-hours, recurring. Half a day per pass is realistic, and "we audit quarterly" quietly becomes "we audit after incidents."

Interpretation is the hard part. The tools print numbers. Deciding that a SORT stage plus a 300:1 examined-to-returned ratio equals one specific ESR-ordered index is expertise, applied per finding.

Nothing here fixes anything. Every finding becomes a ticket: build the index during a quiet window, hide the dead one, wait, verify, drop. Detection was never the bottleneck; follow-through is.

What comes next

Everything above can run continuously instead of quarterly. CloudThinker connects to MongoDB read-only and runs the same loop on a schedule: watching slow query shapes, index usage and cache pressure, and proposing specific index changes with the evidence attached. Index changes go through Auto Mode, so you decide what runs on its own and what waits for approval. That's part three of this series.

Related reading

• Part one: 5 ways fast queries turn slow at scale
• Part three: automating MongoDB performance tuning
• PostgreSQL performance tuning with native tools
• Triage MySQL slow queries with native tools

Or start with your own cluster's findings: try CloudThinker free with 100 premium credits and no card required.