Skip to main content

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. Collection: the platform periodically pulls slow query records from MongoDB system.profile (the profiler must already be on).
  2. Grouping: records are aggregated by query shape (a query template that ignores specific parameter values); each group shows the cumulative count, average / maximum latency, documents scanned and returned, and a representative sample.
  3. 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).

Enabling the Profiler​

When the list is empty, first enable the profiler 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.