Our system currently stores all customer (merchant) accounts in one "flat" MySQL (5.6) DB namespace. We would like to scale out better - we are considering breaking our data up based on the merchant account ID. So instead of having:
use database single; table sales ( `account_id` ... )
Break up merchants into separate namespaces:
use database <account_id>; table sales ( ... // stores data for a single account )
Above has several benefits:
- Accounts can be placed on different servers and scale out much better. We can decide how many accounts to co-locate, and migrate as needed.
- Individual account backup/restore times would be greatly reduced.
- Ability to upgrade one account's database at a time (more granularity).
- One bad user won't affect all accounts. etc.
Concern: Today, all of our DB access is via stored procedures - both reads and mutations. We would like to keep it that way as it isolates the DB schema from the app code. If we were to break up the accounts into separate db-namespaces the dot-notation used inside of the stored procedures cannot be parameterized, as far as we can tell. We would have to duplicate the stored procedures (~150 count) for each of the account-namespaces. Consequently if we need to make a change to one SP, we need to apply it to all of the namespaces' stored procedures. In fact, any DB schema change needs to be applied (via automation) to all the DB namespaces. The administrative overhead is slightly scary, even after automating the deployment of changes.
Any alternative/better solutions for scaling out?