How to optimize countDocuments()

Working with Data
,
1

Hello! We are having some performance issues with our countDocuments() query as it is taking > 17 seconds (our client-side timeout is 17s) for most queries to complete execution during our load testing. This is only happening during load testing and the query returns in ~300ms when the system is not under load. This query, in particular, is the only one that has performance issues as I can compare it to the others from the Profiler within Atlas. The collection itself has 680k documents and we are using Mongoose within our node.js app.

I am wondering if there are any best practices for optimizing the countDocuments() operation?

Models.Pass.countDocuments({ show: showId }).maxTimeMS(17000)

Here is the the log document from the Profiler:

{
 "command": {
   "aggregate": "passes",
   "pipeline": [
     {
       "$match": {
         "show": {
           "$oid": "5d8021bd1c4eef00086d4fb6"
         }
       }
     },
     {
       "$group": {
         "_id": 1,
         "n": {
           "$sum": 1
         }
       }
     }
   ],
   "cursor": {},
   "lsid": {
     "id": {
       "$uuid": "c54a4dc4-d595-fe6d-16b9-8517594f2da1"
     }
   },
   "$clusterTime": {
     "clusterTime": {
       "$timestamp": {
         "t": 1584299634,
         "i": 255
       }
     },
     "signature": {
       "hash": {
         "$binary": "PHh4eHh4eD4=",
         "$type": 0
       },
       "keyId": 6778896889304580000
     }
   },
   "$db": "main"
 },
 "planSummary": [
   {
     "COUNT_SCAN": {
       "show": 1
     }
   }
 ],
 "numYields": 1836,
 "queryHash": "97FA1A2E",
 "planCacheKey": "D86294E6",
 "ok": 0,
 "errMsg": "Error in $cursor stage :: caused by :: operation was interrupted because a client disconnected",
 "errName": "ClientDisconnect",
 "errCode": 279,
 "reslen": 311,
 "locks": {
   "ReplicationStateTransition": {
     "acquireCount": {
       "w": 1838
     }
   },
   "Global": {
     "acquireCount": {
       "r": 1838
     }
   },
   "Database": {
     "acquireCount": {
       "r": 1837
     }
   },
   "Collection": {
     "acquireCount": {
       "r": 1837
     }
   },
   "Mutex": {
     "acquireCount": {
       "r": 2
     }
   }
 },
 "protocol": "op_msg",
 "millis": 17594
}
2 Likes
2

I see that there is an index on the query filter field show. The query planner does show a COUNT_SCAN stage when the index is used. I ran a similar query (including a filter) with over a million small documents. As such you cannot run explain on the countDocuments method in mongo shell. So, I ran an aggregation with $match and $count stages (and I believe its operation is same as countDocuments)., so that I can see the query plan output. This timed approximately 500ms (running on MongoDB 4.2 on an older PC).

When an index is not there, the query plan shows a COLLSCAN and the time is about 1000ms.

I think the performance might be something to do with the ‘load’ only. Does this operation has different semantics than other read methods like find (I don’t know)?

[ EDIT ADD ] The following query gave better time of about 330ms, also returning the count (and better time):

db.collection.find( { some_field: some_criteria } ).count()

Note there was an index on the some_filed.