Having a lots of apps accessing the MongoDB cluster, slow queries are hard to track. especially when it's an aggregation pipeline.
We're using MongoDB Atlas, where we have a profiler which shows the executed queries which ran beyond a threshold, say 5000ms, and also shows the corresponding collection. We can look at the slow queries but it doesn't give any hint which app has called for it. It requires to do a "find" for the relevant piece of query - throughout the applications in the whole solution. Very time consuming!
At the moment I'm using MongoDB C# Driver.
Is there any way to insert/add any "metadata" in MongoDB queries, like "name" or "calling-application", etc., and get it back from the slow queries section, or from the query logs?
Below is a sample db log (credentials redacted, for reference purpose only)
[conn280813] command <database>.<collection> command: aggregate
{ aggregate: "<collection>", pipeline: [ { $match: { <collection>.
<property2>: "XXXXXXXXXXXX", <collection>.<property3>:
"XXXXXXXXXXXXXXXXX", <collection>.<property4>: "XXXXXXX",
<collection>.<property5>: "XX", <collection>.<property6>: { $gte: 1
}, <collection>.<property7>: { $gte: new Date(1636730882184) } } },
{ $group: { _id: "$<collection>.<property>", count: { $sum:
"$<collection>.<property>" }, <aggregate-field>: { $first:
"$<collection>.<property>" } } }, { $sort: { count: -1 } }, {
$lookup: { from: "<collection>", let: { <out-field>: "$<property>"
}, pipeline: [ { $match: { $expr: { $eq: [ "$_id", "$$<property>" ]
} } }, { $graphLookup: { from: "<collection>", startWith: "$_id",
connectFromField: "<property>", connectToField: "_id", as: "
<object>", depthField: "level" } }, { $unwind: "$<object>" }, {
$unwind: "$<object>.<property>" }, { $match: { <object>.<property>:
<value> } }, { $group: { _id: "$<object>.<property>", <property>: {
$push: "$<object>.<property>" }, protectionLevel: { $push:
"$<object>.<property>" } } }, { $project: { _id: 0, <property>:
"$_id", <property2>: { $in: [ true, "$<property>" ] }, <property>:
{ $in: [ 1, "$<property>" ] } } } ], as: "<out-property>" } }, {
$unwind: { path: "$<property>" } }, { $match: { <object>.
<property>: false } }, { $limit: 50 }, { $lookup: { from: "
<collection>", localField: "_id", foreignField: "_id", as: "<out-
object>" } }, { $unwind: { path: "$<property>" } }, { $project: {
<property>: "$_id", count: 1, <property2>: 1, <property3>:
"$<object>.<property>" } }, { $unwind: { path: "$<property>" } }, {
$match: { <object>.<property>: "<filter-value>", <object>.
<property>: <value>, <object>.<property>: { $gt: 0 } } }, {
$project: { <collection>.<property>: { $arrayElemAt: [ { $map: {
input: { $filter: { input: "$<object>.<property>", as: "version",
cond: { $eq: [ "$<object>.<property>", "$$<object>.<property>" ] }
} }, as: "v", in: "$$<property>" } }, 0 ] }, <out-field>:
"$<property>", <out-field>: "$_id", <out-field>: "$<property>",
_id: 0 } }, { $lookup: { from: "<collection>", localField: "
<collection>.<property>", foreignField: "_id", as: "<collection>" }
}, { $project: { <out-field>: { $arrayElemAt: [ "$<collection>.
<property>", 0 ] }, <out-field>: 1, <out-field>: 1, <out-field>: 1
} }, { $limit: 5 } ], allowDiskUse: false, cursor: {}, $db: "<db>",
lsid: { id: UUID("XXXXXXXX-XXXXX-XXXX") }, $clusterTime: {
clusterTime: Timestamp(1644506882, 1), signature: { hash:
BinData(0, XXXXXXXXXXXXXXXXXXXXXXXXXX), keyId: XXXXXXXXXXXXX } } }
planSummary: IXSCAN { <object>.<property>: 1, <object>.<property>:
1, <object>.<property>: 1 } keysExamined:12476 docsExamined:12475
hasSortStage:1 cursorExhausted:1 numYields:99 nreturned:5
reslen:1138 locks:{ Global: { acquireCount: { r: 193 } }, Database:
{ acquireCount: { r: 193 } }, Collection: { acquireCount: { r: 192
} } } storage:{ data: { bytesRead: 10641559, timeReadingMicros:
59687 } } protocol:op_msg 263ms
Thanks in advance.
Use $comment for this target. You can specify this option in various ways, for aggregation it can be set via AggregateOptions