1

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.

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)
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.

  • "The only real way to get write scaling is via Sharding", hi Rick, how to do "Sharding" using Galera Cluster & MaxScale ? – tesmojones Nov 8 '16 at 3:38
  • Each Galera set of nodes would be one shard. That is, you would use Galera for HA; multiple clusters would give you sharding. – Rick James Nov 12 '16 at 0:36
  • Are you already at the point of needing sharding? It sounds like you need a full-time, experienced, on-staff, DBA for your project. If you are not at that point yet, then questions like this thread could be questions to use when interviewing. – Rick James Nov 12 '16 at 0:38
  • If you want more discussion in this forum, please provide more details on your application, including why you expect to need Clustering. – Rick James Nov 12 '16 at 0:39
0

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.

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.