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

Here is 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 will show you exactly which query shapes are slow, which are scanning collections instead of walking indexes, and which are sorting in memory. What it won't tell you is 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/mongostat give you the live workload picture, and Performance Advisor shortcuts some of it if you're on Atlas.

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 decisions worth making deliberately:

• Level. Use level 1 in production. Level 2 records every operation and will measurably tax a busy node; reserve 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/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 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 — do your aggregation while the window is fresh, or bump the collection size. And profiling data is per-node: if your application reads from secondaries, repeat the exercise there. When you're done, db.setProfilingLevel(0) — but note that 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, all under executionStats:

The SORT stage deserves emphasis because it fails loudly: blocking sorts have a memory cap (roughly 100 MB in modern versions), beyond which the query either spills to disk or errors. The fix is almost always compound-index order, and the working heuristic for MongoDB index strategy 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" is not the same claim as "this query is indexed."

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

Tool 3: $indexStats — find the indexes nobody uses

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

db.orders.aggregate([
  { $indexStats: {} },
  { $project: { name: 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 — check since, and check every replica set member, because secondaries serve their own reads and keep their own counters — is a drop candidate. Before dropping, hide it first:

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. This is the single 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 you 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-feed 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, waste source number four from part one), qrw/arw (queued vs. active readers and writers — sustained queues mean the node can't keep up, not that queries are individually slow), and 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, no capped-collection archaeology required.

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.

Where the DIY audit stops

Run this audit; it works. But be honest about its shape:

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. Tony, CloudThinker's database agent, connects to MongoDB read-only and does this same loop on a schedule — watching slow query shapes, index usage, and cache pressure, and proposing specific index changes with the evidence attached, gated behind your approval. That's part three of this series.

Or start with your own cluster's findings: Try CloudThinker free — 100 premium credits, no card required.