MongoDB Slow Query Analysis

Automatically identifies slow queries via the MongoDB profiler and gives index optimization suggestions. Find it under the Slow Query tab of Resource Monitoring → MongoDB.

Prerequisites

• The MongoDB instance to diagnose must be added and enabled on the Data Source page, with the Slow Query Diagnostics purpose selected.
• Multiple instances with diagnostics enabled (such as several shards of a sharded cluster) are automatically aggregated for analysis, with no extra configuration.

How It Works

1. Enabling the profiler: when the platform detects that profiling is off on the target database, it automatically runs db.runCommand({profile:1, slowms:100}) to turn it on. This is the only configuration write the platform makes to a monitored database — it never creates indexes or writes business data. If the monitoring account lacks the privilege, the log shows Permission denied and you have to enable it manually (see below).
2. Collection: system.profile is polled every 10 minutes, considering only records from the last 120 minutes, up to 2000 records per run.
3. Filtering: only records with at least 100000 documents examined are kept. Note that a query that is slow but scans little will not be recorded.
4. Grouping: records are aggregated by query shape (a query template that ignores specific parameter values), and a shape is only stored once it has appeared at least 50 times. Each group shows the cumulative count, average / maximum latency, documents scanned and returned, and a representative sample.
5. Storage: results are written only to the platform's own ops-mongo and kept for 7 days.
6. Index suggestions: combining the query field combinations with existing indexes, the platform suggests compound indexes to create, with one-click creation support (calling createIndex in the background — the only operation that writes to a monitored database, and it requires you to click it).

Not capturing any slow queries? Check these four conditions

Being "slow" is not by itself the recording criterion; the real bar is "scans a lot AND runs often", and all four conditions must hold at the same time:

Condition	Default	Tunable via
Execution time	> 100 ms	ENV_GATEWAY_PROFILE_SLOW_MS
Documents examined	≥ 100000	ENV_GATEWAY_DOCS_EXAMINED
Occurrences of the same shape	≥ 50 (within the last 120 minutes)	—
Collection interval	every 10 minutes	ENV_GATEWAY_CAPTURE_INTERVAL_MS

A single 20-second query will not be captured — it probably does not examine 100000 documents, and it certainly does not occur 50 times. To reproduce, the usual approach is to build a table with 300000+ rows and then drive 50+ full-scan style queries against it with a batch job.

After triggering, wait for the next collection cycle. To make reproduction easier, lower the two tunable thresholds above.

Troubleshooting: the page stays empty

Check in this order:

1. Is profiling on? Run db.getProfilingStatus() on the target database — was should be 1 and slowms should be 100. If it is still 0, the platform could not turn it on, most likely because the monitoring account lacks the privilege.
2. Is there anything in system.profile?

   db.system.profile.find({ docsExamined: { $gte: 100000 } }).sort({ ts: -1 }).limit(5)

   An empty result means the generated load did not reach the documents-examined threshold — not a platform problem.
3. Records exist but the page is still empty: the same shape probably has not reached 50 occurrences yet, or the 10-minute collection cycle has not elapsed.

Enabling the Profiler Manually (only when the account lacks privileges)

Normally the platform enables it for you. If the log shows Permission denied, enable it manually on each monitored database (slowms is the slow query threshold; 100–500ms is recommended in production):

use mdwsrows;      db.setProfilingLevel(1, { slowms: 100 })
use mdservicedata; db.setProfilingLevel(1, { slowms: 100 })
use mdworksheet;   db.setProfilingLevel(1, { slowms: 100 })
use mdworkflow;    db.setProfilingLevel(1, { slowms: 100 })

Notes

• The profiler adds roughly 1–3% overhead; in production, only collect above the slowms threshold and do not use level=2 (full collection).
• system.profile is a capped collection (1MB by default) that rolls over quickly under high QPS; to retain more, resize it:

  db.setProfilingLevel(0)
  db.system.profile.drop()
  db.createCollection("system.profile", { capped: true, size: 100000000 })  // 100MB

• Before one-click index creation, assess the impact on writes and run it during off-peak hours if necessary.