Hacker News Reader

Comment by morgo | Hacker News Reader

Hi! I'm a Product Manager for the MySQL Server. I don't specifically work on InnoDB Cluster, but am happy to answer questions.

Let me start of by describing how the clustering works:

- A cluster is 3+ nodes in a group, each with a complete set of data

- When you say 'COMMIT' the server you are connected to certifies your write-set with its peers (i.e. has anyone else tried to modify the same data). Once the majority agree, the transaction is considered successful.

- The distributing and certification of the transaction is synchronous, but the applying of the transaction is asynchronous. Thus you actually retain pretty good performance if the network latency is reasonable.

The performance question comes up a bit, so here's another post for more context:

http://mysqlhighavailability.com/an-overview-of-the-group-re...

View on HN

Thanks for the quick summary! You're saying that each node in the cluster is a complete copy of the data. So this solution will give you high availability on both writes and reads with one node in a three node cluster down. Can you read as long as one node us up but not write?

For the performance piece, I'm looking at the attached link and it seems like neither read nor write throughput goes up as you add nodes. So will this allow you to scale your read / write volume horizontally or is it just for high availability? It doesn't seem like you can just add more nodes to get more write throughput. I'm guessing you'd still need to take advantage of read only replicas to scale read volume in addition to a cluster.

Also, since each copy needs a complete set of the data it does not seem like this clustering solution addresses growing data size. Correct?

The three node minimum is to avoid split brains. You can still access the data with fewer (for recovery etc.) but the cluster is not HA.

Read throughput will go up by being able to distribute reads amongst the cluster. Write throughput shouldn't really get better; since all nodes have a copy of the data. Thus; the maximum node count is 9.

On the last question, there is data size, and there is working set size (what needs to be in memory). You can actually stretch working set size by sending certain queries (i.e. reports) to one of the nodes and keep the others for more transactional queries, but for storage on disk - yes, it is a multiple of however many nodes you have.

But also consider: Much of the pain I've had in large DBs is not being able to keep enough backups on fast media for quick restore (i.e. I'd like to have every day for the last 2+ weeks). From that pain point though, it's not a multiple - you just need to pick one node to backup.

How does this differ from the Galera Cluster / mysql-wsrep implementation?

Very similar concept, but differences are well described here: http://lefred.be/content/group-replication-vs-galera/

The Tl;dr I give most people is that the Group Replication technology (innodb cluster) uses Paxos for group communication (with majority) and Galera uses Totem Single-ring Ordering (all nodes).

Interesting, I've been using galera clustering for a couple of years and the first months of setting up a new cluster, a new environment, have always been plagued by bugs and issues.

Eventually it stabilizes but I still have binary logs disabled because of an open issue over a year old.

I'm afraid that MySQL will have the same issues now that it's new.

Is there an AWS Cloudformation configuration that will deploy this to AWS in an automated fashion?

Have you run Aphyr's Jepsen tests?

We do not have current plans for an AWS CloudFormation template.

QA has definitely been one of the harder parts of creating a distributed MySQL. I don't work closely enough on that part to answer this question. Sorry!

Is it possible to get stale reads from non-primary nodes in a single-primary setup?

In single primary the other nodes are put in a read-only mode so they can not accept writes. So stale reads can not happen.

To expand on your question a little - the MySQL router currently works better for single primary (it supports multi-primary but does not prevent stale reads). If you go third party, ProxySQL also supports Group Replication natively with multi-primary:

http://lefred.be/content/mysql-group-replication-native-supp...

I was chatting to the author about this at FOSDEM - since it is possible to keep track of nodes state by following GTIDs executed in the binary log stream. I believe this is how ProxySQL is doing it, but it's possible the final implementation differs :-)

When you read from non-primary nodes in Group Replication you get stale reads. There is no protection in place.

I have been introduced to this from my local MySQL team. Just want to confirm, how is the conflict resolution solved? e.g., two different updates on two different server at about the same time.

Certification of transactions is done on transaction commit.

If two people have modified the same data, then one of them will get a similar error to a deadlock. The other is free to proceed.

Reply on news.ycombinator.com