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".
Before use, confirm the following:
- 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
- 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. It does not automatically create indexes or write business data. If the monitoring account lacks permission, logs will showPermission denied; enable it manually as described below. - 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. - Filter: Only records with documents examined ≥ 100000 are retained. Queries that are slow but scan few documents are not recorded.
- 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.
- Store: Results are written only to the Ops Platform's own
ops-mongoand retained for 7 days. - Index recommendations: Based on query fields and existing indexes, the system recommends compound indexes that can be created. After the user clicks one-click creation, the backend calls
createIndexto create the index.
Slow query collection depends on execution duration, documents examined, occurrence count, and collection interval:
| 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 |
Even if MongoDB profiler records a long-running query, it may not appear in the Ops Platform diagnostic results because the scan volume or occurrence count of the same fingerprint is insufficient. During testing, you can use a batch task to trigger queries that meet the conditions above.
After triggering, wait for the next collection interval. In test environments, you can lower the two thresholds above to make reproduction easier.
Troubleshooting: Page Has No Data
Check in order:
- Whether profiling is enabled: Run
db.getProfilingStatus()on the target database. With the default configuration,wasshould be1andslowmsshould be100. Ifwasremains0, the Ops Platform failed to enable profiling, usually because the monitoring account lacks permission. - Whether
system.profilehas records:If the result is empty, check whether the query meets the scan threshold and check the profiler configuration.db.system.profile.find({ docsExamined: { $gte: 100000 } }).sort({ ts: -1 }).limit(5) system.profilehas 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 adds database overhead. In production, it is recommended to control collection with the
slowmsthreshold and avoid level 2 full collection. system.profileis a capped collection, and records may be overwritten quickly under high QPS. The following operations delete existing profiling records. If you need to increase its size, confirm the impact before running them:db.setProfilingLevel(0)db.system.profile.drop()db.createCollection("system.profile", { capped: true, size: 100000000 }) // 100MBdb.setProfilingLevel(1, { slowms: 100 })- Evaluate write impact before one-click index creation, and run it during off-peak hours when necessary.