Setting the file. One moment.
Subchapter 63.5
references/performance.mdMarkdown5 KBView on GitHub
Two modes: reactive (user has a slow query — diagnose and fix) and proactive (general performance review). Both produce concrete index commands and query rewrites you execute against the user’s cluster.
Before suggesting query rewrites that use specific aggregation operators, verify support by calling and searching the content.
Scripts
Wa Reviewweb_fetch(url="https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html")// find()
db.collection.find({ field: "value" }).explain("executionStats")
// aggregate()
db.runCommand({
explain: { aggregate: "collection", pipeline: [...], cursor: {} },
verbosity: "executionStats"
})| Field | Look for |
|---|---|
queryPlanner.winningPlan.stage | COLLSCAN = no index (bad), IXSCAN = index used, SORT = in-memory sort |
executionStats.totalDocsExamined (8.0+) | Should be close to nReturned; large gap = inefficient |
executionStats.totalKeysExamined | Large vs nReturned = index not selective enough |
executionStats.executionTimeMillis | Baseline for improvement |
queryPlanner.winningPlan.inputStage.indexName | Which index was chosen |
COLLSCAN -> create a missing index (ESR rule — equality first, sort, then range):
// Query: db.orders.find({ userId, status })
db.orders.createIndex({ userId: 1, status: 1 })
// Query with sort: include sort field in the same direction
db.products.createIndex({ category: 1, price: -1 })
// Full ESR: equality -> sort -> range
db.orders.createIndex({ userId: 1, createdAt: -1, price: 1 })Aggregation pipeline not using an index -> put $match first:
// BAD — $project before $match destroys indexed field paths
[{ $project: { total: ... } }, { $match: { total: { $gt: 100 } } }]
// GOOD — $match first on indexed fields, compute derived fields after
[{ $match: { price: { $gt: 10 } } }, { $project: { total: ... } }]IXSCAN with high docs-examined / returned ratio -> add selective fields to the compound index.
Long-running queries (> 30 minutes) -> kill them:
db.adminCommand({ aggregate: 1, pipeline: [
{ $currentOp: {} },
{ $match: { $or: [{ secs_running: { $gt: 1800 } }, { WaitState: { $exists: true } }] } }
], cursor: {} })Long queries block MVCC garbage collection -> storage growth -> CPU/memory pressure. Recommend application-level query timeouts.
Re-run explain(). Stage should change from COLLSCAN to IXSCAN, executionTimeMillis decrease, totalDocsExamined close to nReturned. Report before/after.
Profiler log group: /aws/docdb/<cluster-id>/profiler.
filter ns="<db>.<coll>" | sort millis desc | limit 10
filter planSummary="COLLSCAN" | sort millis desc | limit 20db.getCollectionNames().forEach(c => db[c].getIndexes().forEach(printjson))
db.collection.aggregate([{ $indexStats: {} }]) // unused since last restartLook for:
{a:1} and {a:1, b:1} on the same collection. The single-field is covered by the compound; drop it.| # | Anti-pattern | Fix |
|---|---|---|
| 1 | COLLSCAN on large collections | Add index on filter fields; apply ESR rule |
| 2 | Unbounded arrays in documents | Move to a separate collection with a parent-id index |
| 3 | $match after $project | Put $match first on indexed fields |
| 4 | find() without projection | Project only needed fields to reduce data transfer |
| 5 | Redundant indexes | Drop prefix indexes covered by compounds |
| 6 | High-cardinality SORT without index | Include sort field in the index (matching direction) |
| 7 | Frequent $lookup on hot path | Denormalize at write time; add indexes on join keys |
Connection anti-pattern (very common): creating MongoClient per request skips connection pooling. Create once at module scope; Lambda: outside the handler.
Organize findings by severity:
For each finding, give the exact fix command.
| Metric | Meaning |
|---|---|
CPUUtilization | High = COLLSCANs, complex aggregations, connection spikes |
DatabaseConnections | Current connection count |
DatabaseConnectionsLimit | Max allowed — alert when approaching |
LongestRunningGCProcess | > 1800s = long query blocking GC |
AvailableMVCCIds | Low = risk of read-only mode |
BufferCacheHitRatio | Low = queries hitting disk; scale up or add indexes |