Ask HN: How to scale a multi tenant app that uses a db per tenant
We have a multi tenant application which uses a db for each tenant, how do scale it to support 1000s of tenants. Managing dbs for each tenant seems a lot of work.
MySQL server has a limit on how many databases you can create on a single server.
Also how to make sure each tenants load stays balanced, and one tenants worse performance does not affect another.
4 comments
https://dev.mysql.com/doc/refman/5.6/en/database-count-limit...
Says MySQL has no limit on the number of databases, although InnoDB has a limit of 4 billion tables. If you run into that, I guess use a couple servers.
If there's no connection between tenants, you have lots of options. I don't remember if you can easily do replication of individual databases, but even if not, you can replicate the whole thing and then drop databases as needed to separate tenants. You could also run one mysqld process per tenant and move it to a separate machine if needed.
Says MySQL has no limit on the number of databases, although InnoDB has a limit of 4 billion tables. If you run into that, I guess use a couple servers.
If there's no connection between tenants, you have lots of options. I don't remember if you can easily do replication of individual databases, but even if not, you can replicate the whole thing and then drop databases as needed to separate tenants. You could also run one mysqld process per tenant and move it to a separate machine if needed.
Assuming you mean a DB schema per tenant (and not server/tenant), you can control quality of service by setting account limits for each user: https://dev.mysql.com/doc/refman/8.0/en/user-resources.html
You can write scripts to do bulk updates/queries as long as you can ensure that the table schema remain consistent between each individual DB.
You can write scripts to do bulk updates/queries as long as you can ensure that the table schema remain consistent between each individual DB.
One approach, assuming you are talking about relational databases: ensure that every query contains the tenant-id and the tenant-id is a key in all relevant tables. No way of balancing tenant load in this model, though, unless you can balance at the query level.
Are there use cases where the database schema may differ slightly between tenants? How would migrations/schema changes propagate thru 1000 databases? What if it fails on one database? That sounds like a nightmare to maintain.
Your goal is to scale the application and I think the way is to change the multi-tenancy to store the tenant id in each relevant table as well as indexes on that column for every table since the majority of queries will have tenant_id.
The load balancing part depends on what your app is doing, you could segment web traffic based on URL or whatever per tenant to different servers. Or all traffic goes to all servers and scope when using DB is the tenant_id, then you treat all traffic the same and the load balancer just ensures even-ish traffic across a pool of web servers.
Your goal is to scale the application and I think the way is to change the multi-tenancy to store the tenant id in each relevant table as well as indexes on that column for every table since the majority of queries will have tenant_id.
The load balancing part depends on what your app is doing, you could segment web traffic based on URL or whatever per tenant to different servers. Or all traffic goes to all servers and scope when using DB is the tenant_id, then you treat all traffic the same and the load balancer just ensures even-ish traffic across a pool of web servers.