My employer developed a web application we provide on software-as-a-service terms to our customers. To allow for multiple customers with a huge mass of data to be stored in a database, we chose to let the application create a schema per tenant. So if we had 5 customers we had something along the lines of
mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | tenant_1a | | tenant_d2 | | tenant_yf | | tenant_ok | | tenant_8n | +--------------------+
Edit: Numbers and letters represent a hash around 32 chars long.
Tenant schema names are in fact not numeric but hashes of certain facts during creation. So not really predictable until creation. This prevents us from preparing query rules in advance.
what we have/do now
For now we are good with this and only run a mariadb galera cluster of three nodes behind a maxscale readconnroute balancer. But we will eventually hit a barrier, where adding nodes to this cluster won't do, because the overall data size won't fit on disk and/or the amount of tables will kill performance.
To keep the complexity of the applications database layer low, our devs would like us to handle the routing transparent from the viewpoint of the application: they want the application to just to talk to one "server" and not care about where which tenant is located physically.
To expand our application cluster to multiple mariadb galera clusters we could use maxscales schemarouter which exposes all schemas on all connected sub-clusters as if there was only one server. This fits perfectly into our devs expectations.
Now, a few months ago ProxySQL entered the scenery of database proxies and claims better performance paired with greater flexibility among other stuff.
We can route queries based on hard-coded schema names, but would refrain from doing so as this would mean to create/update them each time a tenant is created/deleted.
How could we replicate the dynamic behaviour of maxscales schemarouter with proxysql query rules, if at all?