Mariadb – 100+ DBs in one instance or divide and conquer : MariaDb Ubuntu 64bits

mariadbmulti-tenantUbuntu

I have a desktop app (100+ customers and more to come) running at customer's small local server, all of them have same structure in tables (different info). I'm migrating that app to web ( almost finished ) and I need to store all those DBs into a new server : Xeon 4cores 4 ghz, 64 Gb ram, 1 Tb sss hd. What could be best approach to correctly handle the information : 1 instance for all DBs or several instances with few DBs ? each DB has 150 tables many with few 1000- records and just some with 100,000- records. Right now I have an instance with 13 DBs working pretty well, but not sure about 100+, any advice is appreciated.

Best Answer

The term describing this architecture is 'multi-tenancy'. There are many q&as on here on that topic already, so presumably something can be learned from those.

You'll have to consider e.g.:

  • All customers in the same schema or one schema for each customer?
  • How many queries/sec each customer generates and how much your server can handle. If each customer has its own DB user, then MariaDB (at least more recent versions) has features to limit how many queries/sec and simultaneous connections are allowed.
  • Problems related to volume of data:
    • Backup: Can you do it non-disruptively? mariabackup is pretty good. One backup for all customers or a separate backup for each?
    • Disaster recovery and full restore from backup, how long each takes, and whether that is acceptable to your customers.
  • How to do schema changes: Non-disruptively to users is the ideal. Newer MariaDB versions can do a lot of ALTER TABLE operations with ALGORITHM=NOCOPY|INPLACE|INSTANT.
  • Stability / high availability: If all users are on the same server, then all users will be affected if there's a problem with that server. Are you planning to do any kind of replication?
  • Security: Isolating customers in different databases or even different servers will help with security, but comes at higher maintenance costs.
Related Question