Percona XtraDB Cluster (PXC) principle

Introduced:

PXC belongs to a set of nearly perfect MySQL high-availability cluster solution, compared to the traditional master-slave replication mode-based cluster architecture MHA and mm+keepalived, the most prominent feature is to solve the data replication delay problem, basically can achieve real-time synchronization. The relationship between the nodes is peer, the transaction is either executed on all nodes, or not, and its implementation mechanism determines that it treats consistency very strictly, which also can perfectly guarantee the data consistency of MySQL cluster.

1.PXC using ports

• 3306 Database External Service port
• 4444 SST Full-volume transmission port, refers to the data mirror image transmission, can be configured first: Xtrabackup, rsync, mysqldump
• 4567: Member Communication port
• 4568:ist Incremental Transfer port (relative to the SST increment).

Advantages of 2.PXC

• Strong consistency
• No synchronization delay (the individual feels that there is still a delay)
• Each node can read and write
• With the box pushed to all members of the group, the data page is equivalent to the physical copy, not the blog log, and then reproduce.
• The result data is synchronized.
• Supports parallel execution with better performance when the node is in the Apply data

3.PXC Execution Process

The client initiates a transaction that executes locally and initiates a commit to the transaction after the execution is completed. The write set needs to be broadcast before the user responds to the commit, and then a global transaction ID number is obtained and sent to the other node. By merging the data, it is found that there are no conflicting data, perform apply_cd and COMMIT_CB actions, or you will need to cancel the operation of this transaction. After the current server node passes validation, the commit operation is performed, and an OK is returned, and the rollback is performed if the validation does not pass. Of course, in the production of at least 3 nodes of the cluster environment, if one of the nodes did not pass validation, there is a data conflict, then the way is to take the inconsistent node kicked out of the cluster environment.

Limitations of 4.PXC

• Only the InnoDB engine is supported. Because an XA transaction is likely to occur when a commit occurs rollback
• XA transactions are not supported,
• A table delete operation without a primary key is not supported, and a SELECT ... limit also returns a different value.
• Because of optimistic concurrency control, transaction conflicts, failed nodes are exited from the cluster.
• No lock tables, all table DDL operations, be sure to use pt-osc otherwise to cause the entire cluster to lock
• The maximum transaction size is defined by Wsrep_max_ws_rows, wsrep_max_size, and the load data infile commits every 10k row, and the transaction will be split into a number of times.
• The overall performance of the cluster depends on the slowest node.

Percona XtraDB Cluster (PXC) principle