countDocuments is extremly slow

Hello,

I have a mongodb PSA Cluster with 2 shards :

  • Centos 7.9
  • 16GB RAM
  • 4CPU
  • mongod 6.0.1

This is how looks my main collection :
STORAGE SIZE: 248.29GB
TOTAL DOCUMENTS: 3564898
INDEXES TOTAL SIZE: 4.74GB

When I do a simple documentCounts({}) without any filter (so no index required) it takes about 90 minutes.
The cpu usage of the shards does not exceed 10%

Is it a normal behavior and can I make it faster ?

Thanks for your help

You do not specify a query or a projection so all documents need to be fetched. So yes it is normal with 248G of data with only 16G of RAM.

Try the query {_id:{$gt:0}} and projection {_id:1}.

Can you give me the query you want me to test ? It gives me 0

[mongos] db> db.dataset.countDocuments({_id:{$gt:0}})
0

Thanks

Try {_id:{$ne:null}} which should use the index named _id_.

1 Like

How have suggested @steevej you can add on your query a condition on the _id field, coz MongoDB create a unique index on the _id field during the creation of a collection.

So the possible way to improve the performance are:

or for example:

> db.people.aggregate({$match:{_id:{$exists:true}}},{$group:{_id:null,count:{$sum:1}}})
{ "_id" : null, "count" : 1000000 }

Best regards

Here is the result when it uses an index :

[mongos] db> function a() {
… print(Date())
… print(db.dataset.countDocuments({_id:{$ne:null}}))
… print(Date())
… }
[Function: a]
[mongos] db> a()
Sat Jan 07 2023 15:59:43 GMT+0000 (Coordinated Universal Time)
3571253
Sat Jan 07 2023 17:01:44 GMT+0000 (Coordinated Universal Time)

Is 1 hour normal ?

I have a standalone mongo (4.4.1), with same kind of documents, and this is the result :

function a() {
… print(Date())
… print(db.dataset.countDocuments({_id:{$ne:null}}))
… print(Date())
… }
a()
Sat Jan 07 2023 19:06:59 GMT+0100 (CET)
3415406
Sat Jan 07 2023 19:07:01 GMT+0100 (CET)

Maybe a change in mongo 6.0 …

Hi @ABot,
i think yes coz (for the first time), the function countDocument(), perform behind an aggregate’ s operation, checking the condition for all documents in the collection.

Best Regards

To do

try

query = {_id:{$ne:null}}
projection = { _id : 1 }
db.dataset.find( query , projection ).count()

Here are some tests we gave me, thank you but it the same :

[mongos] db> function a() {
… print(Date())
… print(db.dataset.aggregate({$match:{_id:{$exists:true}}},{$group:{_id:null,count:{$sum:1}}}))
… print(Date())
… }
[Function: a]
[mongos] db> a()
Sat Jan 07 2023 18:12:09 GMT+0000 (Coordinated Universal Time)
[ { _id: null, count: 3622425 } ]
Sat Jan 07 2023 20:06:04 GMT+0000 (Coordinated Universal Time)

[mongos] db> function a() {
… print(Date())
… query = {_id:{$ne:null}}
… projection = { _id : 1 }
… print(db.dataset.find( query , projection ).count())
… print(Date())
… }
[Function: a]
[mongos] db> a()
Sat Jan 07 2023 23:15:22 GMT+0000 (Coordinated Universal Time)
3634207
Sun Jan 08 2023 01:34:34 GMT+0000 (Coordinated Universal Time)

If nothing works to speed up your use-case, then it simply means you do not have appropriate hardware resources for it.

Perhaps, your use-case does not need a precise count and may be satisfied with an estimation.

I have noticed that the count keeps changing and by a fair amount. So it looks like a lot is going on this low in RAM machine compared to data size.

As my last comment on this thread, let me recommend some reading starting with:

Hello Steeve,

I need the exact count, and sometimes with a filter. But I have issue with or without filter even with basic indexes.

Yes the amount of document increase minute by minute

The weird thing is that everything is working well on my previous setup with a single mongod server:

  • v 4.4.1
  • 64Gb RAM (but mongod WTcache limited to 1Gb because the rest of the memory is used for other tasks)
  • 16 Cpu

On my new setup it’s a sharded cluster but with more memory allocated:

  • 6Gb WTcache per instance
  • v 6.0.1
  • 4 Cpu but they are almost not used when I do the count query

Maybe it’s a disk speed problem ? I will do a speed test later

Even if I do the query in a loop it’s always long, like nothing goes in the cache.
I tried to restart the instances, and perform the count query, to force the good index to be set in memory but no success.
I have 18 indexes on this collection, maybe it’s too much (Index total size is less than 5GB so it should fit in memory).

In my mind is does not seems to be a hardware issue since the CPU is at least not moving, and the mem should be enough for the indexes.
But thank you Steeve, I will check the doc you sent to see if I missed something

When you write

Do you mean that each machines is 16GB with 4CPU and that you have a total of 7 machines, that 2 PS per shard, 2 PS for config server and 1 machine for mongos.

or

Do you mean that you are running 6 data bearing instances and mongos on a single 16GB machine?

Indicates that your bottle neck is disk I/O.