MongoDB Slow Query Analysis

Automatically identifies slow queries based on MongoDB profiler and provides index recommendations. The entry is the Slow Query tab under "Resource Monitoring → MongoDB".

Prerequisites

• The MongoDB instance to diagnose must be added and enabled on the Data Sources page, with the slow query diagnostics purpose selected.
• Multiple diagnostic-enabled instances, such as shards in a sharded cluster, are automatically aggregated for analysis. No extra configuration is required.

How It Works

1. Enable profiler: When the Ops Platform detects that profiling is not enabled on the target database, it automatically runs db.runCommand({profile:1, slowms:100}) to enable it. This is the only configuration write that the Ops Platform performs on the monitored database; it does not create indexes or write business data. If the monitoring account lacks permission, logs will show Permission denied; enable it manually as described below.
2. Collect: Every 10 minutes, records are pulled from MongoDB system.profile. Only records from the last 120 minutes are analyzed, with up to 2000 records per run.
3. Filter: Only records with documents examined ≥ 100000 are retained. Queries that are slow but scan few documents are not recorded.
4. Group: Records are aggregated by query shape (a query template that ignores concrete parameter values). A group is written only after the same fingerprint appears ≥ 50 times. Each group shows total count, average/maximum duration, documents examined and returned, and representative examples.
5. Store: Results are written only to the Ops Platform's own ops-mongo and retained for 7 days.
6. Index recommendations: Based on query fields and existing indexes, the system recommends compound indexes that can be created. One-click creation is supported (the backend calls createIndex; this is the only operation that writes to the monitored database and requires explicit user action).

Cannot capture slow queries? Check these four conditions first

"Slow" itself is not the recording condition. The condition is high scan volume + high execution frequency, and all four conditions must be met:

Condition	Default	Adjustable Environment Variable
Execution duration	> 100 ms	ENV_GATEWAY_PROFILE_SLOW_MS
Documents examined	≥ 100000	ENV_GATEWAY_DOCS_EXAMINED
Same fingerprint count	≥ 50 times (within the last 120 minutes)	—
Collection interval	Once every 10 minutes	ENV_GATEWAY_CAPTURE_INTERVAL_MS

A single 20-second slow query will not be recorded. Its scan volume may be below 100,000 documents, and the occurrence count may be below 50. For reproduction, prepare a table with more than 300,000 rows and use a batch task to trigger more than 50 full-scan queries.

After triggering, wait for the next collection interval. In test environments, lower the first two thresholds above to make reproduction easier.

Troubleshooting: Page Has No Data

Check in order:

1. Whether profiling is enabled: Run db.getProfilingStatus() on the target database. was should be 1, and slowms should be 100. If it remains 0, the Ops Platform usually failed to enable it because the monitoring account lacks permission.
2. Whether system.profile has records:

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

   Empty output means the constructed data did not reach the scan threshold. This is not an Ops Platform issue.
3. system.profile has records but the page is still empty: Usually the same fingerprint appears fewer than 50 times, or the 10-minute collection interval has not been reached yet.

Manually Enable Profiler (Only When Account Permissions Are Insufficient)

Normally the Ops Platform enables profiler automatically. If logs show Permission denied, enable it manually on each monitored database (slowms is the slow-query threshold; 100–500 ms is recommended for 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 })

Points to Note

• profiler has about 1–3% overhead. In production, collect only queries above the slowms threshold. Do not use level=2 (full collection).
• system.profile is a capped collection (1 MB by default) and rotates quickly under high QPS. Increase its size if longer retention is required:

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

• Evaluate write impact before one-click index creation, and run it during off-peak hours when necessary.