MySQL/MariaDB: Scaling 10 millions database in cluster

Question

This is for SaaS app (currently in MySQL-InnoDB). Each app contains about 150 tables. The database size is not big, but the number is high.

For each database, InnoDB create directory in /var/lib/mysql. Because too many database, I choose to have individual database per app to prevent file system bottleneck (Performance degrading badly if too many files/directories exist under a directory).

I will upgrade once it reach 5000 databases.

I need to know if MariaDB cluster or MySQL cluster able to deal with 10 millions database or more, or is there any other solution?

Answer 1

10 million databases is too many to have in one cluster and will likely hit a bunch of internal limits and be unable to balance workloads. You should look into containerizing the databases so you have one small DB associated with each client.

Answer 2

150*5000 is a lot of tables.

table_open_cache may be too low. And there may be other issues. If you would like further analysis, follow the instructions here.

10M databases is implemented as 10M subdirectories in a single parent directory. The OS, not MySQL, will probably croak.