Multi-tenancy with database-per-tenant: how to handle lot of migrations efficiently

We have a multi-tenancy with database-per-tenant, so every Tenant has is own database. If we have 1.000 Tenants, we will have 1.000 databases (one per tenant) ;)

@sr57 is not an option to have one common DB, we need to stick to database-per-tenant approach.

Thank you very much for your feedback!

Regarding the approach on the linked article: it should be fine.

Just make sure you have a proper max_connections value set on your MySQL server.

The default value allows 151 connections, which should suffice for 10 workers.

References:

• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections
• https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html

@rodrigo.pedra hello Rodrigo. Thank you for your help!

Here are the answer for your questions:

** Do you already have 1000 tenants, or are you planning ahead to prepare for it? **

Yes, we have almost 1.000 tenants right now.

** How are you currently routing the tenants? Subdomain, custom domain for each tenant, subfolder? **

We are routing with subdomain.

** Do you have a central storage that store minimal data for all tenants? For example, spatie/laravel-multitenancy package requires a landlord database to hold meta-data about each tenant.**

Yes, we have a "landlord" database, but it's a custom made implementation (not using spatie/laravel-multitenancy package).

@ibarral

If you can afford any downtime I would experiment with the parallel solution.

If you prefer to try a near zero-downtime solution, follow along.

As you are routing with subdomain you could install a reverse or an application proxy, such as Traefik, that routes traffic to a different server depending of the subdomain.

• Then you deploy the new code version to a second application server.
• Traefik, or even nginx as a reverse proxy, can make the routing decision based on reading from a storage. I guess nginx can't query MySQL or any DBMS directly, but you could update a .conf file while your migration progresses.
• In your tenant table, on your landlord DB, you add a column that holds the version.
• The reverse/application proxy would redirect traffic for each subdomain based on the version on this storage. Current version to current application server, new version to newer application server.
• While migrating a single tenant you could issue a 503 response for that specific subdomain requests. This would be the "near" zero-downtime, as you said the migrations runs for about 5 seconds for each tenant.
• When finishing migrating a tenant you change the version in its landlord record, the proxy will start using the new server for this tenant.

For the 503 reponse while migrating, you could play with how Laravel handles the php artisan down command and customize it to allow traffic matching certain subdomains while responding with a 503 to subdomains being migrated. Current implementation adds a script to the public folder, so you could tweak this generated script.

For tracking when each migration starts or ends, you can listen for the Migration events. Check this docs page for reference: https://laravel.com/docs/9.x/migrations#events

Disclaimers:

• While I worked on doing something similar on the past, it was a Symfony project, not a Laravel one, and some years ago.
• So I am writing from memory this outline, but it is just an outline, if you need implementation details I probably won't remember them all.
• By then we used Traefik to handle dynamic traffic routing between servers. I guess nowadays there might be other solutions than Traefik or using nginx directly.
• We also used this approach to every new version deployment, you can search for blue/green deployment strategy for more info.

Postscript:

• My question about if you already had 1,000 tenants or were planning ahead, was precisely to advise against premature optimization.
• And also to advise you to hire an expert consultant to help you out with this in case you already had 1,000 tenants.
• With close to 1,000 tenants I assume you (as a company) already have enough revenue to afford hiring one.
• I am not offering any services, nor I am an expert on infra-structure. Although in the project I mentioned, I participated actively in implementing the solution, we had an infra-structure expert advising us at the time.
• This is the kind of the problem I usually referred as "a good problem to have" when I used to give classes, as this means you (as a company) seems to be succeeding. Congratulations!

Hope I could make it clear, English is not my native language. Good luck =)

@rodrigo.pedra thanks a lot for all your time and explanations Rodrigo! we will evaluate your recommendation to improve our app for zero-downtime on this kind of scenarios.

Thank you very much!!!

@ibarral you're welcome! Hope this can help you out =)

@carlosmora we could do that, but the code is the same for all tenants, and some changes will broke the app until the migrations are complete :(

@ShadyarBzharOthman You’ve already asked your question here. There’s no need to bump a year-old thread. Be patient.

@martinbean You're right, thanks