Is this Update performance ok?

Working with Data
, , ,
1

Hello,

I have 22261 documents in a collection.

Inside each of these documents, among other things, there is a subdocument:

  {
    "name": "a name of a category",
    "categoryId": ObjectId("xxxxx"),
  }

I am attempting to update the category name, by a matching categoryId by running this query:

db.products.updateMany({"categoryDetails.categoryId": ObjectId("63d224e007e09cd3c6526038")},
{$set: {"categoryDetails.name": "New category name"}})

The query takes 4.412sec to execute and returns the following:

{
    "acknowledged" : true,
    "insertedId" : null,
    "matchedCount" : 9538.0,
    "modifiedCount" : 9538.0,
    "upsertedCount" : 0.0
}

Given the parameters provided is this considered good? Where things get really bad is when I try to run this update query inside a transaction. Even as the only query inside the transaction, it takes around 20sec to execute.

Is this normal? Are 4.412sec as a standalone query and 20sec within transaction normal performance numbers? For reference, I am attaching the

db.products.find({"categoryDetails.categoryId": ObjectId("63d224e007e09cd3c6526038")}).explain()

output below:

{
    "explainVersion" : "1",
    "queryPlanner" : {
        "namespace" : "Preproduction.products",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "categoryDetails.categoryId" : {
                "$eq" : ObjectId("63d224e007e09cd3c6526038")
            }
        },
        "collation" : {
            "locale" : "it",
            "caseLevel" : false,
            "caseFirst" : "off",
            "strength" : 2.0,
            "numericOrdering" : false,
            "alternate" : "non-ignorable",
            "maxVariable" : "punct",
            "normalization" : false,
            "backwards" : false,
            "version" : "57.1"
        },
        "maxIndexedOrSolutionsReached" : false,
        "maxIndexedAndSolutionsReached" : false,
        "maxScansToExplodeReached" : false,
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "categoryDetails.categoryId" : 1.0
                },
                "indexName" : "categoryDetails.categoryId_1",
                "collation" : {
                    "locale" : "it",
                    "caseLevel" : false,
                    "caseFirst" : "off",
                    "strength" : 2.0,
                    "numericOrdering" : false,
                    "alternate" : "non-ignorable",
                    "maxVariable" : "punct",
                    "normalization" : false,
                    "backwards" : false,
                    "version" : "57.1"
                },
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "categoryDetails.categoryId" : [

                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2.0,
                "direction" : "forward",
                "indexBounds" : {
                    "categoryDetails.categoryId" : [
                        "[ObjectId('63d224e007e09cd3c6526038'), ObjectId('63d224e007e09cd3c6526038')]"
                    ]
                }
            }
        },
        "rejectedPlans" : [

        ]
    },
    "command" : {
        "find" : "products",
        "filter" : {
            "categoryDetails.categoryId" : ObjectId("63d224e007e09cd3c6526038")
        },
        "$db" : "Preproduction"
    },
    "serverParameters" : {
        "internalQueryFacetBufferSizeBytes" : 104857600.0,
        "internalQueryFacetMaxOutputDocSizeBytes" : 104857600.0,
        "internalLookupStageIntermediateDocumentMaxSizeBytes" : 16793600.0,
        "internalDocumentSourceGroupMaxMemoryBytes" : 104857600.0,
        "internalQueryMaxBlockingSortMemoryUsageBytes" : 33554432.0,
        "internalQueryProhibitBlockingMergeOnMongoS" : 0.0,
        "internalQueryMaxAddToSetBytes" : 104857600.0,
        "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600.0
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1680093107, 6),
        "signature" : {
            "keyId" : NumberLong(7183337578563633158)
        }
    },
    "operationTime" : Timestamp(1680093107, 6)
}

Thank you

2

Given that you get

the following indeed looks slow.

Considering that you only have

and only updating

I suspect that your hardware setup is insufficient for your use-case and data set.

2 Likes
3

You mentioned that “given that you get Input Stage as IXSCAN”. What do you mean by that? Could you share what information does this give you that allows you to draw further conclusions in your comment?

4

Please read

and then

https://www.google.com/search?q=mongodb+IXSCAN+vs+COLLSCAN

5

I am aware that IXSCAN is generally better than COLLSCAN since it means the query has to traverse fewer documents. I was just wondering if there was something more to consider when reading your previous comment.

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.