Scaling for MariaDB / Percona / Galera Scaling

Question

I ventured into the world of NoSQL database and as great as they are the lack of true ACID compliance has been a nightmare resulting in a lot of bad data so I am looking into relational databases.

I am very new into this, but have been looking into MariaDB and Percona clusters with Galera. I see a basic setup is 3 nodes, all which can be read/written to, and all contain identical data in each node.

My question is what happens if I need to scale horizontally:

• slow write/reads
• disk i/o issues
• low memory

QUESTIONS

• How can I scale to more machines?
• What is the best practice?

I have not been able to find clear documentation on this.

Answer 1

It's funny you asked this question

I answered a similar question back in June : 9 node percona xtradb cluster

As I stated in my answer, PXC does not write scale.

If you need more read slaves, I would suggest the following

• Use a 3-node PXC (and no more)
• Give each PXC enough disk and RAM
• Use 10Gig E among all PXC nodes (if you are going bare metal)
• Use a VIP or CNAME to confine all writes to one PXC
• Set up standard MySQL Replication from each PXC node
• Load balance reads based on slaves with least replication lag (preferably none)

Answer 2

Rolando may be right, or I may be right; it depends on the situation...

• A single Galera cluster (PXC or MariaDB or hand-rolled) can have more than 3 nodes, but probably should not have a lot more than that.
• Writes somewhat scale with Galera. Especially if you have complex transactions that are heavy on reads. In that case, the transactions are spread across the nodes, hence the reads are spread around.
• Slaves hanging off each node can provide arbitrary read scaling.
• In WAN setups, writes are faster in certain situations. But perhaps you are talking only about LAN?
• "low memory" and "disk I/O issues" are pretty much the same with or without Galera. Maybe you want to address them separately?
• Slow reads are usually caused by too many connections, poor indexing, poor SELECT formulation, etc. Again, this is generally independent of Galera.
• Adding a node to a Galera cluster is "trivial". (Well not quite, but much of the work is automated.)

The only real way to get write scaling is via Sharding. There is no "automatic" sharding (aside from Spider and Clustrix), and I am suspicious about how "automatic" they can be.

Answer 3

You can scale writes with CockroachDB or TiDB. Both of those are horizontally-scalable, shared-nothing, strongly consistent (CP/ACID), SQL databases. Where you pay is latency: they scale by having many transactions in-flight at once, but they still need to ensure that every transaction is replicated to at least try of all nodes that it affects before it can commit. CockroachDB supports the PostgreSQL wire protocol, and TiDB the MySQL wire protocol, so you can use them in your existing applications.