Cluster Technology (iii) MySQL cluster depth analysis

What is MySQL Cluster

The MySQL cluster is a non-shared (shared-nothing), distributed node-architecture storage scheme designed to provide fault tolerance and high performance.

Data Update uses Read Committed isolation level (read-committedisolation) to ensure consistency of all node data using the two-phase commit mechanism (TWO-PHASEDCOMMIT) Ensure that all nodes have the same data (the update fails if any one of the write operations fails).

A non-shared peer node makes update operations on one server immediately visible on other servers. Propagation updates use a sophisticated communication mechanism that is dedicated to providing high throughput across the network.

Allocate loads with multiple MySQL servers to maximize performance and ensure high availability and redundancy by storing data in different locations.

Architecture Diagram

How to store data

Mysqlcluster Data node Group in the master-slave synchronization is synchronous replication, to ensure the consistency of node data within the group. Generally implemented through two-phase commit protocol, the general work process is as follows:

A) When Master executes the commit statement, the transaction is sent to Slave,slave to begin preparing the commit of the transaction. b) Each slave prepares a transaction and then sends an OK (or abort) message to master, indicating that the transaction is ready (or the transaction cannot be prepared). c) Master waits for all slave to send an OK or abort message

d) If master receives all slave's OK messages, it sends a submission message to all slave, telling Slave to commit the transaction;if master receives an abort message from any of the slave, it sends an abort message to all slave, telling slave to abort the transaction.
e) Each slave waits for an OK or abort message from master.

If the slave receives the submission request, they commit the transaction and send a confirmation to master that the transaction has been submitted;

If the slave receives a cancellation request, they will revoke all changes and release the resulting resource, thus aborting the transaction and sending the acknowledgment to Masterv that the transaction has been aborted.

f) When Master receives a confirmation from all slave, it reports that the transaction was committed (or aborted), and then proceeds to the next transaction.

Since synchronous replication requires 4 messages, MySQL cluster is slower to update data than a single MySQL. So MySQL cluster required to run in more than gigabit LAN, the node can use a dual network card, the node group between the use of direct connection.

Mysql cluster All of the indexed columns in main memory, and other non-indexed columns can be stored in RAM or stored on disk by establishing tablespace. If the data changes (insert,update,delete, etc.), the MySQL cluster writes the changed records to the Redo log, and then periodically passes the data to the disk through checkpoints. Because redo logs are committed asynchronously, a small number of transactions may be lost during a failure. To reduce transaction loss, the MySQL cluster implements a deferred write (default delay of two seconds, configurable) so that the checkpoint can be written without losing the last checkpoint when a failure occurs. Typically a single data node failure does not result in any data loss because synchronous data replication is used internally within the cluster.

MySQL scale-out of a cluster

1. Add a Data node group to extend the write operation and improve the storage capacity of the cluster. Support Online expansion, the first to add a new node to the Clsuter, after the start with

ALTER ONLINE TABLE table_name REORGANIZE PARTITION

command to migrate data and distribute the data evenly over the data nodes.

2. Add slave just extend the read, but not the horizontal extension of the write operation.

The average load of the entire system can be described as:

The Averageload=∑readload+∑writeload/∑capacity assumes that each server has a 10000 transaction volume per second, while the write load per second for master is 4,000 transactions and read load per second is 6000, the result is: averageload=6000+4000/10000=100%

Now, add 3 slave and increase the transaction volume per second to 40000. Because the write operation is also replicated, each write operation is executed 4 times, so that each slave write load is 4,000 transactions per second. So now the average load is:

averageload=6000+4*4000/4*10000=55%

Advantages and disadvantages of MySQL cluster

Advantages:

High availability of 99.999% Fast auto-fail switch flexible distributed architecture with no single point of failure ; High throughput and low latency , strong scalability, Support online expansion

Disadvantages:

There are many restrictions, such as: Do not support foreign keys, deployment, management, configuration is very complex, large disk space, large memory, backup and recovery is inconvenient; When restarting, the data node takes a long time to load the data into memory

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Cluster Technology (iii) MySQL cluster depth analysis