How to make updateMany() faster

0

i have a documentDb with, let's say mycollection, ewith docs such as

{ 
  "_id" : "id string 1", 
  "field" : "Fieldvalue" 
  "obj1" : { "until" : ISODate("2022-12-19T00:00:00Z") }, 
  "obj2" : { "id" : 316524 }, 
  "obj3" : { "status" : "available" }, 
  "data" : { },
},
{ 
  "_id" : "id string 2", 
  "field" : "Fieldvalue" 
  "obj1" : { "until" : ISODate("2023-12-19T00:00:00Z"), id: 77, username: "some" }, 
  "obj2" : { "id" : 316524 }, 
  "obj3" : { "status" : "available" }, 
  "data" : { },
},
...

and respectively, i have an index

db.mycollection.createIndex(
    { "field": 1, "obj1.id": 1, "obj2.id": 1, "obj3.status": 1 },
    { name: "my_special_index" }
)

but running

db.mycollection.updateMany(
    {
        $and: [
            { field: "Fieldvalue" },
            { "obj2.id": 24569 },
            { "obj3.status": "available" },
            {
                $or: [
                    { "obj1.until": { $lt: new Date()} },
                    { "obj1.id": 77 }
                ]
            }
        ]
    },
    {
        $set: {
            "obj1": { "id": 77, "username": "some", "until": ISODate("2023-12-19T19:51:32.479Z") }
        }
    }
)

it takes 12 seconds, that is way too much, given the whole collection is ~280k, and the records that match the condition ar 62k

i have tried running explain on the find() ( unfortunately, no way to run explain on the updateMany() )

 db.mycollection.find(
    {
        $and: [
            { field: "Fieldvalue" },
            { "obj2.id": 24569 },
            { "obj3.status": "available" },
            {
                $or: [
                    { "obj1.until": { $lt: new Date()} },
                    { "obj1.id": 77 }
                ]
            }
        ]
    },
).limit(100000).explain("executionStats").executionStats;

and i get, what are think is good response

{
	"executionSuccess" : true,
	"executionTimeMillis" : "173.373",
	"planningTimeMillis" : "1.064",
	"executionStages" : {
		"stage" : "SUBSCAN",
		"nReturned" : "60163",
		"executionTimeMillisEstimate" : "168.929",
		"inputStage" : {
			"stage" : "LIMIT_SKIP",
			"nReturned" : "60163",
			"executionTimeMillisEstimate" : "151.908",
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : "60163",
				"executionTimeMillisEstimate" : "147.511",
				"indexName" : "my_special_index",
				"direction" : "forward"
			}
		}
	}
}

i tried adding another index

db.mycollection.createIndex(
    { "field": 1, "obj1.id": 1, "obj1.until": -1, "obj2.id": 1, "obj3.status": 1},
    { name: "my_special_index_with_until" }
)

but the find().explain() still prefers the first index, and updateMany() doesn't seem to run any faster

so, my question is, what can i do to improve performance, i would expect these to run very fast

Thanks!

Georgi
asked a year ago446 views
2 Answers
0

Hi

  • What is the DocDB version used here?
  • How many indexes for the collection? Update() operations will go faster with less indexes. For the collection you shared, a better index is filed (obj2.id, obj3.status, obj1.id)
  • Is there a way you may add hint to force the index using?

From the DocBD doc: https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html#functional-differences.explain

Amazon DocumentDB emulates the MongoDB 4.0 API on a purpose-built database engine that utilizes a distributed, fault-tolerant, self-healing storage system. As a result, query plans and the output of explain() may differ between Amazon DocumentDB and MongoDB. Customers who want control over their query plan can use the $hint operator to enforce selection of a preferred index.
  • From the MongoDB doc: https://www.mongodb.com/docs/manual/reference/method/db.collection.updateMany/ There seems to be a new feature in version 4.2.1 to add hint in the updateMany() function. See if this is available in the DocDB 4.0 version. Maybe not.
  • is there is a way to bulkwrite for the same result using updateMany, and is that going to be faster?
  • If you have support plan, I would suggest reach to us through a support case and our team can help investigate further.
AWS
SUPPORT ENGINEER
Kevin_Z
answered a year ago
0
  1. Try with bulkwrite
  2. DocumentDB is using write concern with majority so it may cost time to wait for the last reply
AWS
jjpe
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions