A brief introduction to mysql cluster (picture)

1. What isMySQLCluster

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

Data update uses the read-committed isolation level to ensure the consistency of data on all nodes, and uses the two-phase commit mechanism (two-phasedcommit) to ensure that all nodes have the same data (if any If the write operation fails, the update fails).

Shared-nothing peer nodes make update operations on one server immediately visible on other servers. Propagating updates uses a complex communication mechanism designed to provide high throughput across the network.

Distribute the load through multiple MySQL servers to maximize program performance and ensure high availability and redundancy by storing data in different locations.

## 2. Architecture diagram

#3. How to store data
##1. The master-slave synchronization in the Mysqlcluster data node group uses synchronous replication to ensure the consistency of node data in the group. Generally implemented through a two-phase commit protocol, the general working process is as follows:

a) When the Master executes the commit statement, the transaction is sent to the slave, and the slave begins to prepare for the submission of the transaction.

b) Each slave must prepare a transaction, and then send an OK (or ABORT) message to the master, indicating that the transaction is ready (or the transaction cannot be prepared).

c) Master waits for all Slaves to send OK or ABORT messages

If the Master receives OK messages from all Slaves, it will send Slave sends a commit message to tell Slave to submit the transaction;

If the Master receives an ABORT message from any Slave, it sends an ABORT message to all Slave to tell the Slave to abort the transaction.

e) Each Slave waits for an OK or ABORT message from the Master.

If the Slave receives a commit request, they will submit the transaction and send a confirmation that the transaction has been submitted to the Master;

If the Slave receives Upon receiving a cancellation request, they will undo all changes and release the occupied resources, thus aborting the transaction, and then send confirmation to Masterv that the transaction has been aborted.

f) When the Master receives confirmation from all Slaves, it will report that the transaction has been committed (or aborted), and then continue with the next transaction.

Since synchronous replication requires a total of 4 message transfers, the data update speed of mysql cluster is slower than that of stand-alone mysql. Therefore, mysql cluster is required to run in a LAN of Gigabit or above. The nodes can use dual network cards and the node groups must be directly connected.

Question: Will expanding the cluster and adding data node groups cause the data update speed to decrease?

Answer: No, the data update speed will be faster. Because the data is processed separately, the data stored in each node group is different,

can also reduce locking.

2.Mysqlcluster stores all index columns in main memory, and other non-index columns can be stored in memory or stored on disk by creating a table space. If the data changes (insert, update, delete, etc.), the mysql cluster writes the changed records to the redo log, and then regularly writes the data to the disk through checkpoints. Because redo logs are committed asynchronously, a small number of transactions may be lost during a failure. In order to reduce transaction loss, MySQL Cluster implements delayed writes (default delay of two seconds, configurable), so that checkpoint writes can be completed when a failure occurs without losing the last checkpoint. Generally, the failure of a single data node will not cause any data loss because synchronous data replication is used within the cluster.

4.MySQL Horizontal expansion of the cluster

#1. Add a data node group to expand the write Operation to improve the storage capacity of the cluster. Online expansion is supported. First, add new nodes to clsuter. After starting, use the

ALTER ONLINE TABLE table_name REORGANIZE PARTITION

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

2. Adding Slave only expands reading, but cannot achieve horizontal expansion of write operations.

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

AverageLoad=∑readload+ ∑writeload / ∑capacity

Assume that each server has 10,000 transactions per second, and the Master's write load per second is 4,000 transactions, and the read load per second The load is 6000, and the result is:

AverageLoad=6000+4000/10000=100%

Now, add 3 slaves, and the transaction volume per second increases to 40,000. Because write operations are also replicated, each write operation is executed 4 times, so the write load of each slave is 4000 transactions per second. Then the current load average is:

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

五、MySQL Advantages and Disadvantages of Cluster

Advantages:

a) 99.999% high availability

b) Fast automatic failover

c) Flexible distributed architecture, no single point of failure

d )High throughput and low latency

e) Strong scalability, supports online expansion

Disadvantages:

a) There are many limitations, such as: foreign keys are not supported

b) Deployment, management, and configuration are complex

c) It takes up a lot of disk space and memory

d) Backup and recovery are inconvenient

e) When restarting, the data node will Loading into memory takes a long time

The above is the detailed content of A brief introduction to mysql cluster (picture). For more information, please follow other related articles on the PHP Chinese website!