2

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?

  • Personally, I prefer 1 database for all clients where possible. Makes keeping the database schemas in sync a lot simpler, and you don't have to start messing around with master tables that point to the correct customer database. But, YMMV. – GrandmasterB Sep 30 '14 at 6:11
2

This is the classic single-tenant vs multi-tenant dilemma. To service multiple clients, should you:

  1. Have a single database that includes the data for all your clients (i.e. make them all tenants of your single database), or
  2. 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.

1

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.

  • I like your idea of pre-creating DBs, I don't know how well that would fit in our model since we have no clue how many inscriptions could occur on a daily basis. Initially probably very, very few. But how would this scale with something that is "ondemand" (i.e. any amount of clients could sign up on a given day) ? Do you mean to say that the webservice would automatically/dynamically? keep a lead on the number of available DBs? While this is interesting overall, it doesn't exactly answer the primary concern... – Sebastien Daniel Sep 29 '14 at 22:38
  • As you can only create a finite amount (due to space - unless you have very elastic storage) you know the maximum number of DBs you need to create though, or even 'comfortable' load? You could pre-create and then when a configurable threshold has been reached e.g. 80% of pre-created, trigger an expansion onto a new server or instance or a request for manual intervention? – David Scholefield Sep 30 '14 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.