MongoDB Connector - Performance Impact - Slow $cmd Queries

[kevcodez]

Hi there,

since setting up the MongoDB Connector with CDC, we're seeing a lot of very slow queries on the op-log and wondering if there's anything we can do to reduce the times. We fear that this has a negative effect on cluster performance given the extremely slow queries.

Within just 24 hours, we're seeing the connector queries take 2h40 minutes in total.

Looking into some specific queries, they are taking up to two minutes.

Our database is about 65GB with collections ranging from 200k to 35mil entries. We set up multiple Connections in Airbyte to sync a few tables. The query times issued by the sync range from <100ms to >2 minutes per query.

Any tips on how to reduce the impact of the CDC sync? We used to have an incremental sync on an updatedAt field in our collections, which is no longer available in recent versions of the MongoDB connector and I can only do a full-sync or CDC-sync, but no an incremental sync with a custom field.

Replication oplog window is ~1 day. The CDC syncs in Airbyte run every 15-30 minutes.

Sample parsed log document for one of the slow queries (1m44s)

{
  "type": "command",
  "isFromUserConnection": true,
  "ns": "tresorone.$cmd",
  "collectionType": "normal",
  "command": {
    "getMore": 3651004660323920000,
    "collection": "$cmd.aggregate",
    "$db": "tresorone",
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1721476838,
          "i": 1
        }
      },
      "signature": {
        "hash": {
          "$binary": {
            "base64": "5fr2veeuHom6HbCsHadZwJAf8q4=",
            "subType": "00"
          }
        },
        "keyId": 7329602582454731000
      }
    },
    "lsid": {
      "id": {
        "$binary": {
          "base64": "nuyIQXtqRkm7BbvMNRNw7A==",
          "subType": "04"
        }
      }
    }
  },
  "originatingCommand": {
    "aggregate": 1,
    "pipeline": [
      {
        "$changeStream": {
          "fullDocument": "updateLookup",
          "fullDocumentBeforeChange": "whenAvailable",
          "resumeAfter": {
            "_data": "82669BA00C000000132B042C0100296E5A10046DBC8619923E4894A4250B642CF41FAF463C6F7065726174696F6E54797065003C7570646174650046646F63756D656E744B657900463C5F6964003C36363761383332643635393564303062666236373535333900000004"
          }
        }
      },
      {
        "$replaceRoot": {
          "newRoot": {
            "namespace": {
              "$concat": [
                "$ns.db",
                ".",
                "$ns.coll"
              ]
            },
            "event": "$$ROOT"
          }
        }
      },
      {
        "$match": {
          "$and": [
            {
              "$and": [
                {
                  "event.ns.db": {
                    "$regularExpression": {
                      "pattern": "tresorone",
                      "options": "i"
                    }
                  }
                },
                {
                  "namespace": {
                    "$regularExpression": {
                      "pattern": "tresorone\\.portfolios",
                      "options": "i"
                    }
                  }
                }
              ]
            },
            {
              "event.operationType": {
                "$in": [
                  "insert",
                  "update",
                  "replace",
                  "delete"
                ]
              }
            }
          ]
        }
      },
      {
        "$replaceRoot": {
          "newRoot": "$event"
        }
      }
    ],
    "cursor": {},
    "$db": "tresorone",
    "lsid": {
      "id": {
        "$binary": {
          "base64": "nuyIQXtqRkm7BbvMNRNw7A==",
          "subType": "04"
        }
      }
    }
  },
  "planSummary": "COLLSCAN",
  "cursorid": 3651004660323920000,
  "keysExamined": 0,
  "docsExamined": 1338056,
  "nBatches": 1,
  "numYields": 1867,
  "nreturned": 465,
  "queryHash": "E2888557",
  "planCacheKey": "C3F14217",
  "queryFramework": "classic",
  "reslen": 474919,
  "locks": {
    "ReplicationStateTransition": {
      "acquireCount": {
        "w": 4
      }
    },
    "Global": {
      "acquireCount": {
        "r": 1224096
      }
    },
    "Database": {
      "acquireCount": {
        "r": 4
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 4
      }
    }
  },
  "readConcern": {
    "level": "majority"
  },
  "writeConcern": {
    "w": "majority",
    "wtimeout": 0,
    "provenance": "implicitDefault"
  },
  "storage": {
    "data": {
      "bytesRead": 2583507588,
      "timeReadingMicros": 916005
    }
  },
  "cpuNanos": 78394449096,
  "remote": "35.198.81.94:58168",
  "protocol": "op_msg",
  "durationMillis": 86461,
  "v": "7.3.3",
  "isTruncated": false
}