Skip to main content

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​

  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. It does not automatically 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. After the user clicks one-click creation, the backend calls createIndex to create the index.

Slow query collection depends on execution duration, documents examined, occurrence count, and collection interval:

ConditionDefaultAdjustable Environment Variable
Execution duration> 100 msENV_GATEWAY_PROFILE_SLOW_MS
Documents examined≥ 100000ENV_GATEWAY_DOCS_EXAMINED
Same fingerprint count≥ 50 times (within the last 120 minutes)—
Collection intervalOnce every 10 minutesENV_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:

  1. Whether profiling is enabled: Run db.getProfilingStatus() on the target database. With the default configuration, was should be 1 and slowms should be 100. If was remains 0, the Ops Platform failed to enable profiling, usually because the monitoring account lacks permission.
  2. Whether system.profile has records:
    db.system.profile.find({ docsExamined: { $gte: 100000 } }).sort({ ts: -1 }).limit(5)
    If the result is empty, check whether the query meets the scan threshold and check the profiler configuration.
  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 adds database overhead. In production, it is recommended to control collection with the slowms threshold and avoid level 2 full collection.
  • system.profile is 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 }) // 100MB
    db.setProfilingLevel(1, { slowms: 100 })
  • Evaluate write impact before one-click index creation, and run it during off-peak hours when necessary.