/amazon-documentdb

DocumentDB — Performance Tuner

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.

Operator verification: Before suggesting query rewrites that use specific aggregation operators, verify support by calling web_fetch(url="https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html") and searching the content.

What to ask upfront

• Slow query vs general review?
• If slow query: the query itself, collection name, approximate collection size
• Cluster id, region, connection string

Reactive mode: fix a slow query

Step 1: Run explain("executionStats")

// find()
db.collection.find({ field: "value" }).explain("executionStats")

// aggregate()
db.runCommand({
  explain: { aggregate: "collection", pipeline: [...], cursor: {} },
  verbosity: "executionStats"
})

Step 2: Interpret output

Step 3: Apply the fix

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.

Step 4: Verify

Re-run explain(). Stage should change from COLLSCAN to IXSCAN, executionTimeMillis decrease, totalDocsExamined close to nReturned. Report before/after.

Proactive mode: performance review

Step 1: Query CloudWatch Logs Insights

Profiler log group: /aws/docdb/<cluster-id>/profiler.

filter ns="<db>.<coll>" | sort millis desc | limit 10
filter planSummary="COLLSCAN" | sort millis desc | limit 20

Step 2: Review indexes

db.getCollectionNames().forEach(c => db[c].getIndexes().forEach(printjson))
db.collection.aggregate([{ $indexStats: {} }])   // unused since last restart

Look for:

• Redundant indexes — {a:1} and {a:1, b:1} on the same collection. The single-field is covered by the compound; drop it.
• Compound indexes with > 3 fields — most filtering uses the first 1–3 fields; extras add write overhead.
• Multikey indexes on large arrays — each element is a separate index entry; storage bloat.

Step 3: Check anti-patterns

Connection anti-pattern (very common): creating MongoClient per request skips connection pooling. Create once at module scope; Lambda: outside the handler.

Step 4: Produce a report

Organize findings by severity:

• Critical — COLLSCAN on large collections, long-running queries blocking GC
• Warning — redundant indexes, high-cardinality sorts without index
• Improvement — missing projections, pipeline ordering

For each finding, give the exact fix command.

CloudWatch metrics to monitor