Il database profiler e il log delle query lente

MongoDB can run thousands of queries per second, but a handful of slow queries can drag down an entire application. The database profiler and the slow query log are your primary tools for finding these expensive operations. They record query execution details — duration, documents examined, index usage — so you can identify and fix bottlenecks before users feel them.

The profiler has three levels: Level 0 — off, nothing is recorded. Level 1 — records operations that take longer than the slowms threshold (default 100 ms). This is the recommended production setting. Level 2 — records every operation regardless of duration. Level 2 is useful during debugging but creates too much write overhead for sustained production use.

Profiled operations are written to the system.profile capped collection in each database. Each document in this collection represents one operation and contains: op (operation type), ns (namespace), command (the query or update), millis (duration), keysExamined, docsExamined, nreturned, and the execStats tree.

The most diagnostic fields in a profile entry are: millis — total elapsed time. docsExamined — how many documents MongoDB read to satisfy the query. keysExamined — index entries scanned. nreturned — how many documents were returned. A healthy query has docsExamined / nreturned close to 1; a ratio of 1000:1 suggests a missing or inefficient index.

slowms is the cutoff in milliseconds for level 1 profiling. Only operations taking longer than this value are recorded. The default is 100 ms; you can lower it to 20–50 ms to catch more operations during an investigation, then raise it back to 100 ms (or higher) in production to reduce overhead. The setting is per-database and is not persisted across restarts unless set in mongod.conf.

Even with the profiler off, MongoDB writes slow operations to its log file. Each slow query log entry includes the operation type, namespace, duration, query shape, and plan summary. Log lines with COLLSCAN in the planSummary field are guaranteed to be missing an index. Log messages begin with Slow query and appear at log verbosity level 0.

MongoDB Atlas includes the Performance Advisor, which automatically analyzes your slow query logs and recommends indexes. It groups similar queries by their query shape (filter structure without values), shows the average execution time, and generates the exact createIndex command you need. It is the fastest way to identify missing indexes in production without manually parsing logs.

You can filter system.profile by operation type, namespace, or any field. Common analysis patterns: find all collection scans, find all slow aggregations, and find all operations on a specific collection. Sort by millis descending to see the worst offenders first.

Each profile entry is a write to the capped system.profile collection, which adds a small but measurable overhead. Level 1 (slow op only) is safe for most production workloads. Level 2 (all ops) can increase latency by 5–20% on busy clusters and should only be run for short debugging sessions. Always return to level 0 or 1 after a profiling session.

db.currentOp() shows all operations currently executing on the server — not just slow ones that already finished. Use it to catch long-running queries in real time, identify locks, and kill runaway operations with db.killOp(opid). Combine it with the profiler for a complete picture of past and present slow operations.

A practical profiling workflow: 1) Set profiling level 1 with a 50 ms threshold. 2) Let the system run for 15–30 minutes under real traffic. 3) Query system.profile sorted by millis descending. 4) For each slow query, run explain('executionStats') on the same query. 5) Create the missing index. 6) Return profiler to normal threshold. Repeat until all critical queries hit indexes.

In this lesson you learned: profiling level 1 records slow operations above the slowms threshold into system.profile, high docsExamined/nreturned ratios and COLLSCAN in planSummary identify missing indexes, and db.currentOp() lets you catch and kill long-running queries in real time. Next up we explore the ESR principle for designing optimal compound indexes.