listDatabases slow with nameOnly=false when many collections exist

Ops and Admin
1

Hi.

On my 4.4.2 deployment, I’ve created one database with 20000 collections. When I issue show dbs or db.adminCommand( { listDatabases: 1}) in mongo shell for several minutes, mongod will always log the slowQuery message:

{"t":{"$date":"2021-05-18T16:51:38.567+08:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn839","msg":"Slow query","attr":{"type":"command","ns":"admin.$cmd","appName":"MongoDB Shell","comma
nd":{"listDatabases":1.0,"nameOnly":false,"lsid":{"id":{"$uuid":"eed01c17-7afa-4207-9144-b7aec97721fa"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1621326575,"i":1}},"signature":{"hash":{"$b
inary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"admin"},"numYields":0,"reslen":405,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":5}},"ReplicationStateTr
ansition":{"acquireCount":{"w":5}},"Global":{"acquireCount":{"r":5}},"Database":{"acquireCount":{"r":4}},"Collection":{"acquireCount":{"r":20014}},"Mutex":{"acquireCount":{"r":4}},"oplog":{"acquire
Count":{"r":1}}},"storage":{},"protocol":"op_msg","durationMillis":715}}

As the number of collections get bigger, the durationMillis gets larger.

Another thing I’ve noticed that if nameOnly: true is given, it’ll be fast and no slow query.

The following metrics are those that are noticeable. We can see the latency of commands goes up.

image
image2370×1640 325 KB

  1. Is the case expected ? Can we make show dbs fast even if there’re many collections ?
  2. I’ve noticed db.stats() also has slow query log, but show tables hasn’t. Any possible requests include any CRUD will have slow query log under this situation ?
2

Hi @Lewis_Chan,

20k collections is a lot to handle for a single replica set (RS). Usually MongoDB doesn’t recommend more than 10k per RS. I’m not surprised to see the performance go down.

https://www.mongodb.com/article/schema-design-anti-pattern-massive-number-collections/

One collection corresponds to at least 2 files: 1 for the data, 1 for the _id index, and probably more index files if you are doing this correctly and probably more data files if you have a lot of data in this collection (as the files are split at some point).
The OS has to keep all these files open and 20k times 3/4/5 per collection is just too much.

So the real question here is: why do you have so many collections? Can you find a strategy to reduce that number?

2 Likes
3

Actually some customers/applications just use mongo like that. 10000 is really small number, comparing to mysql, although it has shared tablespace to alleviate overheads.

image
image1296×189 17.8 KB

I’m curious whether mongo team can guarantee all your customers don’t create collections as they like ? When customers do that no matter what kind of business logic is, is there always a way to reconstruct the original schema ? If there’s no way, maybe mongodb is not suitable for that application ?

4

In the screenshot you shared, you could replace MySQL by MongoDB and it would still work. Both systems rely on the same OS constraints and obey the same rules.

Sharding could be a solution here though if this large number of collections is the only way. 10k collections maximum per replica set is a generally accepted good practice, but you could totally use a 10 shards cluster and scale easily to 10*10k collections.

If you are building a multi-tenant application, I would recommend checking out this presentation. Multi-tenant application are usually where this problem of large number of connections occurs.

Also, remember that the document design is a crucial step to be successful with MongoDB.

Data that is accessed together should be stored together

2 Likes
5

Has this issue now been solved ?

I mean it’s a little bit cheap to say anything > 10k collections is nok ?!

If listDatabases is executed via MongoShell with nameOnly: true then it’s as expected fast - the question is what is happening beside listDatabases in the above command

and

why can’t this be speed up ?

7

Although WiredTiger does not put a hard limit on the maximum number of collections, frequently other limits come into play, such as hardware limitations as @MaBeuLux88 alluded to. Sharding is one method to alleviate this concern, but certain schema design patterns can also limit this.

MongoDB is constantly improving, and the story of a large number of collections has gotten better in newer versions. The development team understands this limitation can be frustrating and there are use cases that necessitates a large number of collections. SERVER-21629 is an ongoing effort to make this better in the future.

Best regards
Kevin

1 Like
8

Hi Kevin

Thanks for the reply - if I run MongoDB Operator (non sharding ! ← because of listDatabases) just as a replicaset, I recently created 500k databases each with 5 collection → 5M files.

It runs ok - but the problem is now, if you “shutdown” then there seems a problem with the MongoDB Operator/Replicaset - the 3rd instance shutdowns correctly, the 2nd and first seem to fail to shutdown properly which causes a “repair” then these instances are “powered on”.

So in theory it is much much better - but the stuff “around” has problems.

9

There were earlier issues with shutdown in the past, such as SERVER-44595 and SERVER-44094, but they were both fixed.

If you’re seeing a shutdown-related issue in the latest supported versions (4.2.23, 4.4.17, 5.0.13, or 6.0.2), could you open a new thread and provide more details such as MongoDB version, relevant logs, and any errors you’re seeing? Any detail that can help reproduce it will be great.

Best to be in a new thread though, since this thread is all about listDatabases :slight_smile:

Best regards
Kevin

2 Likes