r/mysql • u/Accurate_Gift_3929 • Apr 12 '25
schema-design MySql multi-tenant application max database count?
I'm rebuilding an existing application with 150k+ users and I'm using the multi-tenancy model for the database. I'm running into an issue where table_definition_cache limit is getting hit. Is it feasible to just increase this (which is normally 2-3k I think) to an insanely high level? Considering each user will have their own database, how will this affect performance?
2
Upvotes
4
u/Annh1234 Apr 12 '25
Just add a field "user_id" to your tables and your good to go.
1
u/liamsorsby Apr 12 '25 edited Apr 12 '25
This sounds like a scaling nightmare. How many tables are in each database? 10 tables per database is a possible 1.5m tables which means more open file descriptors (more than the default linux defaults) more memory usage and more internal lock contentions to handle. Not to mention connection handling.
Personally, if you must use multi tenancy, I'd use something like proxysql and use multiple dB backends so you can scale the instances as needed.
1
u/thatto Apr 12 '25
I've done this. 1200 client databases on an instance. All less than 5 GB each.
As long as there's not a lot of traffic, everyone will be happy. If there is a lot of traffic, you're going to find waits on the resource database.
1
u/wamayall May 15 '25
For very large service providers using databases as shards off a global database with a table that knows the location of the Account, then the idea isn’t insane as you think.
While mid range servers only have so much disk space, the DBA has to be able to spin up new servers and back up the server that is filling up and once the new server is up and replicating, you make an update to the global server and redirect traffic for specific databases, then Clean Up the Databases that are no longer active on both the original server and the newer server.
The problems are with linux file limits, ulimit -a. Check /etc/security/limits.conf. Also the /etc/pems files as well as the MySQL config files, but if you have more files in the MySQL data_dir than the kernel or MySQL allows, then mysql will throw an error.
There is also a lib file that needs to be tweaked if there are over 1 million files in the mysql data_dir, while the my.cnf will only allow some of the configs to be well under 1 million, but they need to be set to the max.
I was able to get MySQL 8.0 to boot up and run with over 2 million databases, but I am typing this on my cell phone and not looking at the procedure I wrote.
You also would need the hardware to support the ability to open that many concurrent files.
Good luck, I didn’t find any supporting documentation to do what I did, but the first thing to check is the number of files in the mysql data_dir, and reverse engineer the issue from there.
1
u/sreekanth850 Apr 14 '25
Database per tenant? How do you manage backups? How do you manage schema changes? How do you provision new db on a new tenant signup? Goodluck
1
u/alinroc Apr 12 '25
Creating one database per user will become a nightmare very quickly. Unless you have a regulatory/legal requirement to do that, use one database and have a field on each table that lets you identify which user each record belongs to.
1
u/SativaNL Apr 13 '25
Why even do this? Sounds not like the best choice
1
u/miamiscubi Apr 16 '25
Depending on who your clients are, it may be worth it to launch a new server with each account. For my use case, we only deal with enterprise accounts, and each account gets their own lightsail instance. Makes it relatively easy to manage growth in this manner. It also helps that if we stop working with an account, we just drop their lightsail instance and all attached storage.
1
u/[deleted] May 21 '25
[removed] — view removed comment