4

I have a collection with more than 1 million user, I'm trying to update users balance on some event.

while I'm trying to update e.g. 299 row it takes up to 15739.901ms

no high load on the sever, it's just mongo running. I'm storing the database on an SSD Samsung evo 860 but MongoDB installed on an HDD.

Here's my function:

async usersUpdate(usersToUpdate){
const updates = [];
   return new Promise(async (resolve, reject) => {
       users.forEach(user=>{

            updates.push(
               { "updateOne": {
                    "filter": { "userID": user.userID, 'balance':user.userBalance },
                    "update": { "$set": { "user.$.userBalance": user.newBalance } , "$addToSet":{'orders.$.orderID':user.OrderID} }
                }

               });

       }


       console.log('total updates' , updates.length);
       if (updates.length > 0){
           const DbConnection = await getConnection();
           const usersTable = DbConnection.collection('usersCollection');
           transactionsTable.bulkWrite(updates, {"ordered": false, writeConcern : { w : 0 } }, function(err, result) {
               // do something with result
               if (err) return reject(err);
               return resolve(result)

           });
       }else{
           return resolve('Nothing to update');
       }
   });
}

both userID and userBalance are indexed, and I set writeconcern equals to false.

I don't know what's the wrong with code and why it's super slow.

What's the problem and how could I speed up the progress a bit?

Mongodb config file: 

storage:
  dbPath: "/ssd/mongodb"
  journal:
    enabled: false

Explain result:

{ queryPlanner: 
   { plannerVersion: 1,
     namespace: 'usersDB.usersCollection',
     indexFilterSet: false,
     parsedQuery: 
      { '$and': 
         [ { userID: 
              { '$eq': 'Kfasg3ffasg' } },
           { 'user.userBalance': { '$eq': 10 } } ] },
     winningPlan: 
      { stage: 'FETCH',
        filter: { 'user.userBalance': { '$eq': 10 } },
        inputStage: 
         { stage: 'IXSCAN',
           keyPattern: { userID: 1 },
           indexName: 'userID_1',
           isMultiKey: false,
           multiKeyPaths: { userID: [] },
           isUnique: true,
           isSparse: false,
           isPartial: false,
           indexVersion: 2,
           direction: 'forward',
           indexBounds: 
            { userID: 
               [ '["Kfasg3ffasg", "Kfasg3ffasg"]' ] } } },
     rejectedPlans: [] },
  executionStats: 
   { executionSuccess: true,
     nReturned: 1,
     executionTimeMillis: 24,
     totalKeysExamined: 1,
     totalDocsExamined: 1,
     executionStages: 
      { stage: 'FETCH',
        filter: { 'user.userBalance': { '$eq': 10 } },
        nReturned: 1,
        executionTimeMillisEstimate: 0,
        works: 2,
        advanced: 1,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        invalidates: 0,
        docsExamined: 1,
        alreadyHasObj: 0,
        inputStage: 
         { stage: 'IXSCAN',
           nReturned: 1,
           executionTimeMillisEstimate: 0,
           works: 2,
           advanced: 1,
           needTime: 0,
           needYield: 0,
           saveState: 0,
           restoreState: 0,
           isEOF: 1,
           invalidates: 0,
           keyPattern: { userID: 1 },
           indexName: 'userID_1',
           isMultiKey: false,
           multiKeyPaths: { userID: [] },
           isUnique: true,
           isSparse: false,
           isPartial: false,
           indexVersion: 2,
           direction: 'forward',
           indexBounds: 
            { userID: 
               [ '["Kfasg3ffasg", "Kfasg3ffasg"]' ] },
           keysExamined: 1,
           seeks: 1,
           dupsTested: 0,
           dupsDropped: 0,
           seenInvalidated: 0 } },
     allPlansExecution: [] },
  serverInfo: 
   {  }
Jennifer
  • 603
  • 1
  • 7
  • 17
  • Could you share what it looks like when you `explain` a single update? – klhr Jul 23 '19 at 04:50
  • as Mongodb docs, updateOne() is not compatible with db.collection.explain(). https://docs.mongodb.com/manual/reference/method/db.collection.updateOne/ can you tell me how to do so using bulkwrite – Jennifer Jul 24 '19 at 18:56
  • Sorry! Run explain on the `find` portion of the update (so with a find rather than with an `updateOne`). It's generally a good idea to first check what it's doing to find the document befor eanything else – klhr Jul 24 '19 at 19:12
  • I just made an edit with the result, I see it's well indexed but I don't know where the problem is, maybe of the orders array? `orders.$.orderID` it's an indexed array as well and I ran find on it and exact same result – Jennifer Jul 24 '19 at 20:25
  • Hmmm, that explain looks normal to me. Any chance there are updates in there without a userID or with the same userID twice? – klhr Jul 24 '19 at 22:43
  • nothing at all, I don't think there are any locks right here – Jennifer Jul 25 '19 at 16:53

0 Answers0