How MongoDB Instance works for per tenant databases? Is it better than MySQL for this Use Case?

[Sandesh Pawar]

Dear All,

Just to give you brief idea, we are using Micro-Service based per tenant architecture in AWS. We have 12-15 different Micro-Services/Databases per Tenant and we are expecting around 125 Tenants. Apart from this we also need to support Dynamic schema, hence at the start of development phase we've gone ahead with MySQL 5.7 which introduced JSON as datatype to support dynamic fields.

However we came to realize that, MySQL 5.7 Dev Instance crashes every day when we execute DDL cleanup statements(DROP Tables, Database etc.) in one go over 500 tenants. We had some performance issues with MySQL 5.7 compared to MySQL 5.6. We suspect the reason behind is changed default behavior of using file per table approach in MySQL 5.7. So we have decided to downgrade to MySQL 5.6 without JSON datatype capabilities and we are not sure when MySQL 5.7 becomes stable on AWS platform again.

With intrinsic JSON/BSON capabilities we strongly believe that MongoDB would be more suitable for our case, but there are few questions regarding above issue we faced with MySQL. We don't want to be in position to change database again and look for other option which suits all of our Use Cases. :) 

Question 1 - 

I believe MongoDB (WiredTiger Storage Engine) stores two files per collection. So if we have so many small databases(<20 GB per month) for tenants, does it impact performance for any case(I/O etc)? I would like to know how MongoDB works with multiple collections in multiple databases simultaneously. We also expect to have big tenants and I think MongoDB's secondary index would suit our performance needs.

Question 2 -

If we decide to create one big database for all Tenants(partitioned by TenantID and Date field) and use Sharding feature, can we leverage secondary indexes on other fields? Is it better than above approach?

I would really appreciate any help regarding this problem. If anyone has created multi-backend architecture for Micro-Services using MongoDB, please provide your valuable insights.

Thanks,
Sandesh

[Rhys Campbell]

Hi Sandesh,

I think a lot of the questions you have asked will be covered here...

https://www.mongodb.com/presentations/black-box-mongodb-running-mongodb-scale-parse

Rhys

[Sandesh Pawar]

Thanks Rhys for sharing Parse Use Case. It helps to answer few questions on high level. It would be great if you could share your views for below question.

MongoDB (WiredTiger Storage Engine) stores file per collection. So if we have so many small databases(<20 GB per month) for tenants, does it impact performance for any case(I/O etc)? 

Thanks,
Sandesh

[Kevin Adistambha]

Hi Sandesh,

MongoDB (WiredTiger Storage Engine) stores file per collection. So if we have so many small databases(<20 GB per month) for tenants, does it impact performance for any case(I/O etc)?

Depending on your use case, you may be able to create many small databases. However, you might want to consider:

• WiredTiger opens two files per collection at the minimum. Additional index will open an additional file. Since WiredTiger works in a per-collection basis, the number of databases have no impact toward the number of open files.

• Each connection to the database is also counted toward the open file limit.

• The Unix ulimit settings page has a detailed description on how many files you can expect to be open, and also has some recommendations on the ulimit settings.

• Overall performance also depends on the provisioned hardware. More CPU/RAM/Disk is certainly better.

• Your schema design certainly has a great impact on performance as well. For example, querying on a non-indexed collections will always be much slower compared to indexed collections, unless you have a small amount of documents ("small" is also relative in this case).

The best way to find out if MongoDB is better for your use case is to extensively test it with your intended application. Unfortunately, it is not possible to make a sweeping statement about performance. Every use case is unique, even when they look similar at a glance.

Best regards,
Kevin

​