Search code examples
linuxmongodbmulti-tenant

MongoDB indexing IdAccount field in order to make searches by account efficient

Currently I store data on my MongoDB like so:

  • 1 database per customer
  • each customer has about 40 collections
  • a total of 50 customers (there are 40*50=2000 collections)
  • database size is 3 GB for all 50 customers

My MongoDB service crashes with the error

"Too many open files"

and MongoDB "Too Many Open Files"? Raise the limit shows how to solve the problem but I am still getting crashes.

The error "Too many open files" means that MongoDB process has too many files in use and the OS is complaining. This is how I know MongoDB is using a lot of files:

  1. Obtain the processes id of MongoDB with service mongodb status. The process id will show in the info

  2. Then to get the files used by MongoDB I use this command lsof -a -p <ProcessId>

  3. When I run that command I see 1010 files being used by that processes!

The more customer databases I create the larger that number becomes! So I guess my solution is combining all the databases into one. If I do that I will have to add the column AccountId to all my collections. If I make that change what index should I assign to AccountId so that my searches can be efficient? For example I will like to get all PurchaseOrders where IdAccount=34 fast. Performing this change is something you guys recommend? Should I place all 50 Databases into one?

PS: On a different Linux computer I created a MongoDB database with only 1 database and 40 collections. I filled the 40 collections with 6 GB of data (double of what I have now). MongoDB used 200 files even though this database is twice as big!

Solution

  • The same day after posting this question I combined all the databases into one. Moreover, I added the follwoing indexes:

    db.CollectionA.createIndex({Id_Account:1})
db.CollectionB.createIndex({Id_Account:1})
// etc...

    To prove that my queries continued to be as efficient as before I do:

    db.getCollection('CollectionA').find({"Id_Account":28}).explain("executionStats")

    That query gives the execution stats. It tells you how many documents it searched for and how many matched. Using NO index will result in scanning the whole collection every time I did find({"Id_Account":28})

    Mongo has not crashed so far and it never locks more than 300 files. From know on I will always use the same database instead of having multiple databases.