1

We have a multi-tenant setup where each client of ours runs a DB that is identical to every other client. We were using Mysql sofar and with CentOS 7.x ,switched to MariaDB.

If we want to distribute the load so that requests for Databases 1-500 go to Node-A, request for Databases 501-1000 go to Node-B, would that be possible through some out of the box solution like haproxy? I looked into their documentation but the "balance" options seem to be about distributing the load based on pre-defined headers/values rather than my use-case above. Would it be possible to add a custom "balance" algorithm that will send the requests to the corresponding nodes?

The other approach I was considering was to use a local key-value store that contains the information about which clients are in which Node and switching to those based on the request. I also want to have master-slave replication for these, but have already handled that through a proxy in my application.

Our application runs on PHP(Symfony Framework) with Mysql, if that matters.

How would you design a system that will have few thousand databases and be able to spread the load?

2
  • @Erik, Im trying to find out how to spread the databases between multiple database servers and access them through something like a Virtual IP. The application already knows which DB to talk to based on the client, so thats not a problem, but right now, they all talk to a single database server. If we were to grow, that means putting a group of DBs in a different server or moving the most loaded DB into another server. I dont want to write something custom for this, so was wondering what would be a better approach for this.
    – rajasaur
    Nov 2, 2015 at 18:37
  • Check out MaxScale.
    – Rick James
    Dec 4, 2015 at 21:01

2 Answers 2

0

You can use Galera for multi-master replication. If you could move individual clients to specific nodes you will not run into locking issues. It also has automatic recover of failed nodes. http://galeracluster.com/

1
  • It is true that if you can guarantee that transactions do not use the same rows, you can avoid deadlocks on commit for the most part. However, you'll still end up doing the same IO on all nodes so you end up paying more for the iops on two nodes while still being limited to the write speed of roughly one node. If the worload is very read heavy and writes happen rarely, Galera could be an adequate, easy-to-use solution. In practice Galera just doesn't scale up that well past a handful of nodes (6 nodes or so).
    – markusjm
    Feb 22, 2023 at 7:24
0

You should be able to do this with MariaDB MaxScale using the schemarouter module. The schemarouter was designed for multi-tenant clusters where a user will only use a subset of the nodes on the cluster and there are no cross-node joins. The schemarouter will automatically detect which databases are "visible" to each user account. This makes it possible to isolate a set of customers to their own nodes.

To set it up, define the servers that make up the cluster, configure a monitor for it and then add a schemarouter service that uses those servers. Finally, add a listener that points to the schemarouter service.

Here's an example maxscale.cnf you can use as a starting point:

[maxscale]
threads=auto

[node1]
type=server
address=127.0.0.1
port=3000
protocol=MariaDBBackend

[node2]
type=server
address=127.0.0.1
port=3001
protocol=MariaDBBackend

[cluster-monitor]
type=monitor
module=mariadbmon
servers=node1,node2
user=maxuser
password=maxpwd

[cluster-service]
type=service
router=schemarouter
servers=node1,node2
user=maxuser
password=maxpwd

[cluster-listener]
type=listener
service=cluster-service
port=3306

If you need to join data across multiple nodes or the amount of data exceeds the capacity of a single node, MariaDB Xpand could be an alternative solution for scaling up your database.


Using Galera for this is not a good idea if you intend to scale up writes: a write to one Galera node will cause the same IO to happen on all other Galera nodes. There's also the additional latency that you'll end facing when transactions need to be acknowledged by a quorum of the cluster.

As can be seen here, here and here, using Galera for write-scaling is a bad idea simply due to the fact that it wasn't designed to scale writes. What it's designed for is high-availability and self-healing and that it does quite well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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