Optimizing Long-Running Query Execution Time

Working with Data
1

Hi Team,

I need help with the following query that is causing a CPU usage of 30%. How can we reduce the cause of this issue in the future?

  1. How can we reduce the query time from op_msg 23145 to 100?

What is the exact solution?

Explain output:

{
  "type": "command",
  "ns": "dba.student",
  "command": {
    "aggregate": "student",
    "pipeline": [
      {
        "$match": {
          "$and": [
            { "status": "Open" },
            {
              "DateTime": {
                "$gte": { "$date": "2023-08-18T13:51:37.495Z" }
              }
            },
            {
              "DateTime": {
                "$lt": { "$date": "2023-09-17T13:51:37.495Z" }
              }
            }
          ]
        }
      },
      {
        "$group": {
          "_id": "$storeNumber",
          "totalSlotCount": { "$sum": 1 },
          "minSlotDate": { "$min": "$appDateTime" }
        }
      }
    ],
    "cursor": {},
    "allowDiskUse": false,
    "$db": "dba",
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": { "t": 1692366695, "i": 6 }
      },
      "signature": {
        "hash": {
          "$binary": {
            "base64": "x6qCTzYPFUl+HPaNJ1umPcrI0os=",
            "subType": "00"
          }
        },
        "keyId": 7215676178336580000
      }
    }
  },
  "planSummary": "IXSCAN { status: 1, DateTime: 1, reservedTime: 1 }",
  "cursorid": 4735232936145454000,
  "keysExamined": 4355310,
  "docsExamined": 4355310,
  "numYields": 4474,
  "nreturned": 101,
  "queryHash": "E2C2E097",
  "planCacheKey": "6DD10207",
  "reslen": 6200,
  "protocol": "op_msg",
  "durationMillis": 23145,
  "v": "4.4.23"
}
3

Hey @hari_dba,

The high CPU usage is likely a side effect of the long-running query, rather than an inherent issue with the query itself.

The OP_MSG refers to the wire protocol used to encode the request and response, not the actual query execution time.

While reducing query time depends on various factors like database configuration, indexes, etc., we can still make some targeted improvements based on the information provided.

Based on the explain() output, it seems there may be constraints on available RAM as indicated by the high numYields value of 4.4k. Also, the current index does not appear efficient for this specific query filter.

Per the ESR rules, an index on {status: 1, DateTime: 1} may be more optimal given the query criteria.

Could you confirm if you see a similar number of numYields when re-running the query? This will help determine if it is truly a RAM space issue.

Please let us know if you need any clarification or have additional details to share.

Regards,
Kushagra

1 Like