1

We are looking to shard our application, so that each customer has their own database. The data contents of all databases are different, but the structure is the same for all customers.

Imagine there are 2 million customers. How do we update 2 million databases to:

  1. Create, modify or eliminate columns.
  2. Create, modify or eliminate tables.

How do we do this across millions of databases across dozens of instances? We know how to do it manually, but how can this process be automated? So that we just modify one instance, and that propagates to all customers. Remember each customer has completely different data in each database.

Thanks!

4
  • 1
    Do you really have that many customers? Are the databases ever hosted on site of the customer's server? Do customers ever have direct access to the database (not via the app)?
    – J.D.
    Aug 8 at 23:29
  • 1
    We wrote our own script interpreter, with commands for sql, ini files, registry and file system support. We write a script for each update and distribute it. Aug 9 at 1:12
  • Millions of databases ? Seriously ? Although of course, this all depends on what you call a "database", and what database engine you are talking about. Most database engines nowadays offer "multi-tenancy", where a single physical database is divided into a number of "logical databases" (in Oracle those are called "pluggable databases"), but those still need hefty resources. Another option is to have a different "schema" per user: each schema contains the tables used by that user. Still that would be millions of schemas and millions of database user accounts to manage. Aug 9 at 7:51
  • 1
    Some databases offer what is typically called "row-level security", where the data of multiple users is all in the same table, but is isolated: a user can only read and write his/her data (= rows that belong to that user). That is probably the lightest and most scalable option. Consider large systems like Amazon or AirBnB: they obviously do not have one database per customer: that would be unthinkably complex to manage and not scalable. Aug 9 at 7:54

2 Answers 2

1

At a past job I managed a site that had a schema per customer. There were 8 MySQL instances with about 1,500 schemas per instance. Each customer's schema had an identical set of tables, so when an ALTER TABLE was needed, we had to run it ~12,000 times.

There was also one special schema that had metadata about the customers' schemas, which server they lived on, etc.

We used a custom-developed script implemented in PHP (though any language would do just as well), which queried the catalog of customers, looped over all the customer schemas named in that catalog, and invoked the needed ALTER TABLE against the table in the respective schema.

I could safely run the script in several windows concurrently as needed. As the script started each alter, it would first update a record in the respective schema's schema_version table. If that update had already occurred, then the script concludes another instance of the script is already executing the alter in another session, so it skips that customer and goes on to try the next one in the loop.

On some occasions I had 60+ concurrent windows running the script, to get through them all as quickly as possible.

One risk of trying to use greater parallelism is that if the alter involves a table-copy of a large table, the concurrent alters could run the server out of disk space. So it's not a good idea to increase the number of windows.

I don't know of any off-the-shelf tools for this. Ours was developed in-house. It's likely that your site will have its own proprietary way of enumerating the customer databases anyway.

If you have an order of magnitude more customers per server than I had, you should make sure you're using MySQL 8.0. In my case, we ran into difficulties with so many tables per server, because the number of open files in InnoDB was a bottleneck. They reimplemented the InnoDB data dictionary in MySQL 8.0, and after I gave them feedback about my use case, they specifically tested the data dictionary scalability, up to 1 million tables per server (it can probably handle more, but that's how far they tested it).

I have no idea if MariaDB can handle the same scale of data dictionary. I don't use MariaDB, and you shouldn't assume it's compatible with MySQL.

2
  • Doesn't 8.0 keep a 'recovery' copy of the schema in a file? I don't think MariaDB has the equivalent of the Data Dictionary yet.
    – Rick James
    Aug 9 at 0:41
  • @RickJames I've read that the persistent metadata is in "invisible" tables in the mysql schema. The cached copy is in RAM. dev.mysql.com/doc/refman/8.0/en/… Aug 9 at 2:51
0

MariaDB (and MySQL) implements a Database as a directory. In that directory is at least one file for each table.

Your design asks the OS to handle dozens of millions of files. It may not be able to! Even if it can, it may be slow.

As for adding a column for each user; let's see...

  • 2 million CONNCETS (if in separate VMs)
  • 2 million ALTERs
  • Each alter might take 20ms (including schema updates, etc, etc), even on SSD drives.

That's several hours.

How to do it? You could write a SELECT against information_schema.tables to build all the ALTER statements. Then you could either use a Stored procedure to execute them or you could get a snow shovel to hand the 2 million pairs of USE + ALTERs to the command line "mysql" tool.

That SELECT might, itself, take minutes to run.

Do it in parallel? It might help some; maybe get it down to 3 hours. How? Even more complexity in the code.

Could it be that a customer uses SELECT * FROM ...? Guess what happens to their programs when * now represents one more column? Explain that to 2 million customers! How big will your customer support staff be??

CREATE/DROP table -- probably even slower.

What kind of data will these customers have? We can discuss 'better' ways to design the schema.

6
  • Technically they would get automatic parallelization equal to the number of instances / servers they provision, so it can be much quicker and a lot less frightening at scale, especially in the cloud. But intuition tells me OP isn't ready to pony up the cost it would take to do so efficiently, nor do I think they actually have such a customer base presently to warrant such a thing. Even if they did, I agree with you, I'd rather architect the schema more efficiently itself.
    – J.D.
    Aug 9 at 0:12
  • 1
    Are you saying 1 customer per MariaDB instance? That adds a couple of orders of magnitude of overhead -- now there are 2 million "connects" to do!
    – Rick James
    Aug 9 at 0:14
  • 1
    @J.D. - And does a cloud necessarily have 2M VMs available?
    – Rick James
    Aug 9 at 0:15
  • @J.D. - You probably noticed I was being a big snarky with him.
    – Rick James
    Aug 9 at 0:16
  • 1
    Most cloud providers offer services to automate management such that the number of servers you provision doesn't matter. But I think we can agree there's a reasonable balance of putting a few hundred to a few thousand databases on a given instance and accepting the fact that schema changes might take a few minutes to complete (with the realization any particular database will undergo locking at a fraction of that time - making it moot that the overall runtime is longer). 2M DBs / 1,000 DBs per server = ~2,000 servers. A lot, but not impossible to do. I'm sure someone out there is doing it lol.
    – J.D.
    Aug 9 at 0:28

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.