How big is the dataset? How big is the disk? How much RAM? How many queries per second? I am fishing for whether there is actually a problem you are trying to solve.
Meanwhile...
Having one "Primary" and two "read-only" "Replicas" is possible with Galera. It may simplify establishing the connections. Or it may make it more complex. It may also lead to the Primary having a different load than the Replicas.
Most of today's hardware (whether in the Cloud or private) has very few parameters:
- SSD is much better than HDD, especially if I/O bound
- The cloud may be able to provide higher bandwidth, but this is unlikely to be the limiting component.
- RAM comes in many sizes. If it is practical to have twice as much as the dataset size, then most operations will need only a little I/O.
- If the dataset is growing rapidly, the Cloud probably has a very easy way to add RAM. Doing it yourself is a hassle. Galera makes this "easy" by letting you take a node out of the cluster, add ram (or replace the machine) and put it back in. If private, you are left with old hardware; if Cloud they repurpose it for someone else.
With 3 "read-write" nodes, each has similar load. Access (read or write) needs to be redirected to some node; a "proxy" or something is useful here.
High I/O or high CPU often means "inefficient queries" -- this should be investigated before 'throwing hardware at the problem'.
For a very high number of read-only connections, you can hang regular Replicas off each Cluster node. This can be scaled "infinitely".
