The context
we’re building a web-based system and one of the design decisions (database related) was whether to have 1 main DB manage all client data VS having 1 DB per client.
After reading a lot on the matter (especially in these forums), it appears that 1 DB per client is overall a better approach, especially on the mid to long-run and especially in our use-case.
The basic architecture
Our system is mostly a front-end system that pulls data from a RESTful webservice (where the client data is located), to access that webservice we use unique keys, each key belonging to a specific pair of CLIENT:DATABASE.
Where things get tricky
When a new client creates an account, the idea is to have the webservice auto-create a new DB, DB user and API key for that account. I’ve looked for a standard method of implementing this, but failed to find anything conclusive. Obviously, SaaS providers automate this stuff, but where I fail to understand is how they do this safely.
The only current solution I have is to create a sort of MASTER MySQL user, which has absolute total full über power over MySQL, which we would use to create these DB, DB user pairs. But this feels like such a dangerous set up that it CANT be the only solution. Any compromise to this user would be fatal. This is where I need your help…
Another side-question, is how to limit DB size with MySQL/innoDB. The only things that I have found is that MySQL can only limit TABLE SIZE. And, in more sophisticated contexts, that DBs are stored in separate directories which have a specific disk-quota (this seems to be used by some web hosting providers… but I can’t confirm).
Does anyone know of any other way to control DB size?
1
This is the classic single-tenant vs multi-tenant dilemma. To service multiple clients, should you:
- Have a single database that includes the data for all your clients (i.e. make them all tenants of your single database), or
- Have multiple databases, with each client partitioned off and the single tenant interacting with just its own database.
Most of the SaaS (and other XYZ-as-a-service) shops I know, and almost all of the Internet-scale properties (Google, Facebook, Twitter, etc.), strongly prefer the multi-tenant model. Multi-tenant is generally more efficient, for performance perhaps, but even more so for minimizing administrative overhead. There’s just one database to set up and worry about, with data for different clients separated by client_id values. But there is no magical solution. If you “put all your eggs in one basket,” then you have to work extra hard to protect that basket. And all-in-one databases can rapidly grow large if you’re successful and have many clients, requiring investment in distribution, replication, indexing, and other scalability and quality-of-service optimizations. Developers of SaaS and Internet-scale services invest enormous time and energy in their infrastructure.
Some as-a-service shops do use single-tenant configurations, with clients each having their own database instance (and often, separate virtual machines or virtual environments). One reason is the obvious “greater isolation” virtue, which is often perceived as having stronger security and privacy protections. This isn’t just a technical attribute; it’s also a question of customer preference and ease of sale.
Single- and multi-tenant are great labels, but there are a lot of middle-ground and hybrid approaches between the two poles. The single database manager running multiple “database instances” that you seem to be describing, for instance, is the architecture used by many WordPress hosts. It’s kind of a
“multi-tenant lite” or “housemate” scenario. For getting going and smaller configs, it has lot of virtues.
Have you considered pre-creating a number of databases prior to use and when a new client creates an account, record the mapping from the client account name to the pre-created DB? This way you can limit denial of service attacks that auto-create too many databases for the DBMS to manage (because the list of pre-created databases would run out) and you’d avoid the need for database create privs for day-to-day user interaction.
If you included in the mapping the database server at the same time i.e. used a ‘client name’ -> ‘server:pre-created-db-name’ mapping, you could manage sharding dynamically as well by simply moving the DB from server to server and updating the master map which could be stored either as a central lib/file somewhere, or even in a master DB.
If you populated the pre-created set of DBs each day (for example) you’d have an automatic throttle on the number at any one time.
2