Mongodb Slow query group(profiler)

Rexcily_Rijo_R1 · 2024-05-24T12:08:37.307Z

Hi Guys,

I have MongoDB database running on 4.0 version. I have some issues with load spike and while i troubleshoot i founded out that there were some queries running on this at the time of load spike.
While i went through the profiler(system.profile) i could see many queries and i could not get any consolidated queries.

While i surf through the internet i found that grouping the profiler with queryHash will get me the desired result i want, but this is available only on the latest version and this queryHash feature is not available on 4.0.

So is there any possibilities that i could get through this like the query, count, avg Execution time,doc eamined,doc returned.

Rexcily_Rijo_R1 · 2024-05-27T04:32:26.536Z

Hi turivishal,

I need to know if there is something like queryHash To help identify slow queries with the same query shape, each query shape is associated with a queryHash which is introduced in v4.2.

Is there anything like this prior to this version to find out the query shape in MongoDB

turivishal · 2024-05-27T08:07:47.798Z

You have to do it manually from system.profile collection for below 4.2 versions, something like the below aggregation query in system.profile collection.

 {
    $group: {
      _id: { query: "$query", ns: "$ns" },
      count: { $sum: 1 },
      avgExecTime: { $avg: "$millis" },
      totalDocsExamined: { $sum: "$docsExamined" },
      totalDocsReturned: { $sum: "$nreturned" }
    }
  },
  {
    $sort: { count: -1 }
  }