Shulou(Shulou.com)06/01 Report--

Introduction to PXC:

Galera products are designed to improve high availability cluster solutions for mysql in the form of galera cluster. Galera cluster is a mysql cluster that integrates galera plug-ins. Galera replication is a mysql data synchronization scheme provided by codership, which has high availability, easy to expand, and can achieve synchronous data replication, read and write among multiple mysql nodes, which can ensure the high availability of database services and strong data consistency.

PXC is an almost perfect mysql high availability cluster solution. Compared with the traditional cluster architecture based on master-slave replication mode, MHA and MM+keepalived,galera cluster solve the long-criticized problem of data replication delay and basically achieve real-time synchronization. And the relationship between nodes and nodes is equal. Galera cluster itself is also a multi-host architecture. Galera cluster is most concerned about data consistency. When dealing with the behavior of things, it is either executed on all nodes or not executed at all. Its implementation mechanism determines that its behavior towards consistency is very strict, which can perfectly ensure the data consistency of the MySQL cluster.

There are two wrappers for galera cluster, although the names are different, but they are all the same in essence, using galera cluster. One of the founders of MySQL implemented MAriaDB cluster; on his new MariaDB, and the other is percona xtradb cluster, or PXC, implemented by percona, a famous provider of MySQL services and tools.

To build a PXC architecture, at least three mysql instances are needed to form a cluster. The three instances are not master-slave mode, but each master, so the three are peer-to-peer, regardless of subordinate relationship. This is called multi-master architecture. When the client writes and reads data, the connection to either instance is the same. The data read is the same. After writing to any instance, the cluster will synchronize the newly written data to other instances. This architecture does not share any data and is a highly redundant architecture.

--: galera cluster has 7 functions, as follows:

①: multi-host architecture: a true multi-point read and write cluster, where the data read and written at any time is up to date

②: synchronous replication: data synchronization between different nodes in a cluster without delay, and data will not be lost after the database is down.

③: concurrent replication: slave nodes support parallel execution when apply data, with better performance

④: failover: because multiple writes are supported, it is easy to fail over in the event of a database failure

⑤: hot plug: during service, if the database is down, as long as the monitoring program discovers it fast enough, the unserviceable time will be very small. During the node failure, the impact of the node itself on the cluster is very small.

⑥: automatic node cloning: when adding new nodes or downtime maintenance, incremental data or basic data does not need to be provided by manual backup. Galera cluster will automatically pull online node data, and the cluster will eventually become consistent.

⑦: transparent to applications: cluster maintenance, transparent to applications, almost imperceptible

-- PXC principle:

The following four port numbers are most commonly used by PXC:

3306-the port number of the database external service.

4444-Port that requests SST (SST refers to the transfer of a backup full file in the database. )

4567-A port number for communication between group members

4568-used to transmit IST (an increment relative to SST)

The operation flow of PXC:

First, the client initiates a transaction, which is executed locally, and after the execution is completed, it initiates the commit operation of the transaction. Before committing, you need to broadcast the resulting replication write set, and then get a global transaction ID number and transmit it to another node. After merging the data, it is found that there is no conflicting data, and the apply_cd and commit_cb actions are performed, otherwise the operation of this transaction needs to be cancelled. After the current server node passes the verification, it commits and returns OK, and if the verification fails, it performs a rollback. Of course, there must be a cluster environment with at least 3 nodes in production. If one of the nodes fails verification and there is a data conflict, then the method adopted at this time is that inconsistent nodes are kicked out of the cluster environment, and it will execute the shutdown command and shut down automatically.

Advantages of PXC:

①: achieve high availability and strong data consistency of mysql database cluster architecture.

②: a real multi-node read and write cluster scheme has been completed.

③: improves the delay of master-slave replication in the traditional sense and basically achieves real-time synchronization.

④: newly added nodes can be deployed automatically without providing manual backup, so it is easy to maintain.

⑤: database failover is easy because it is multi-node writes.

Disadvantages of PXC:

①: the new node is expensive and needs to copy the complete data. The transmission cost of SST is too high.

②: any update transaction requires global validation before it is executed on each node library. Cluster performance is limited by the worst-performing nodes, which is often referred to as the short board effect.

③: because of the need to ensure the consistency of data, the problem of lock conflicts is more serious when writing in parallel with multiple nodes.

④: there is a write expansion problem, and something happens on all nodes.

⑤: only tables of the innodb storage engine are supported.

⑥: there are no table-level locks. Executing DDL statements will lock the entire cluster, and it will not be kill (Osc operation is recommended, that is, online DDL)

⑦: all tables must contain primary keys, otherwise an error will be reported when manipulating the data.

Attention points for PXC building:

First of all, the number of nodes in the cluster should be regulated, and the number of nodes in the whole cluster should be controlled within the range of at least 3 and at most 8. At least 3 nodes are used to prevent brain fissure, because this phenomenon occurs only under two nodes. The sign of brain fissure is that any command is entered and the return result is unkown command. Nodes in the cluster will switch due to the addition or failure of new nodes, synchronization failure and so on.

-- the stage of node state change:

Open: the node starts successfully and attempts to connect to the cluster.

Primary: the state that occurs when a node is already in the cluster and donor is selected for data synchronization when a new node joins.

Joiner: the state in which the node is waiting to receive synchronization files.

Joined: the node completes the work of data synchronization and tries to keep pace with the cluster.

Synced: the status of the normal service provided by the node, indicating that the synchronization has been completed and is consistent with the progress of the cluster.

Doner: the state in which a node provides full data to a newly joined node.

Note: doner nodes are data contributors. If a new node joins the cluster and requires SST transmission of a large amount of data, it may drag down the performance of the entire cluster. So in a production environment, if the amount of data is small, you can also use SST for full transmission, but if the amount of data is large, this method is not recommended. Consider establishing a master-slave relationship before joining the cluster.

PXC has two modes of data transmission: one is called SST full transmission, the other is called IST incremental transmission.

There are three methods of SST transmission: xtrabackup, mysqldump and rsync. One method of incremental transmission is xtrabackup. However, when the amount of data in the production environment is not large, SST can be used to transmit all data, but only the xtrabackup method can be implemented.

Another particularly important module in PXC is GCache. Its core function is that each node caches the latest write sets. If a new node joins in, the increment of the new data can be passed to the new node without the need for SST. This allows nodes to join the cluster more quickly. The parameters involved are as follows:

Gcache.size: represents the size used to cache write set incremental information. Its default size is 128MB, which is set by the wsrep_provider_options parameter. It is recommended to adjust to the 2GB-4GB range, so that there is enough space to cache more incremental information.

Gcache.mem_size: represents the size of the memory cache in gcache. Moderate scaling can improve the performance of the entire cluster.

Gcache.page_size: it can be understood that if there is not enough memory (insufficient gcache), the write set is written directly to the disk file.

-: the working mode of PXC:

The working mode of galera is that one node writes a transaction and it broadcasts to other nodes, and this so-called other node also includes itself. In other words, I will receive the transaction I sent, but after receiving and generating GTID, I will simply ignore it and will not go to apply again.

Concurrency control mechanism of galera:

Concurrency control is mainly completed in the interface galera_pre_commit, which is one of the most important interfaces of galera, in which the most important replication and verification logic is implemented. Currently, the concurrency control included in this interface is as follows:

①: data replication:

In the current version of galera, the data of the write set is sent by broadcasting the data asynchronously in asio. This transmission is serial and a critical area, because it needs to be sliced logically before each transmission, and after each transmission is completed, it needs to wait for a value of GTID, so in order to ensure data consistency, this sending operation needs serial.

②: write set verification:

It is required that all GTID entering the processing area must be sequential, because the GTID is generated sequentially, so on the basis of sequence, there must be only one transaction that can be processed at the same time.

The operations managed by this level of concurrency control are mainly verification operations, so verification is serial.

③: write set apply

④: transaction commit

The concurrency control mechanism at this level, which defaults to 3 and is also recommended, is serial commit, which ensures that the binlog generated by all nodes is exactly the same in both the master and slave libraries.

3. Galera API:

-galera_init:

The purpose of this interface is to initialize a galera node, which is the first wsrep interface called by a PXC node, initializing when the server is started, initializing all required parameters and environment variables. (for example, cluster name, instance address, need this API to copy binlog, etc.)

-galera_connect:

This interface is the second interface to be called. The purpose of this interface is to add the current node to the cluster. Before joining the cluster, the function wsrep_view_handler_cb will be called to determine whether the data of the newly joined node is synchronized with the cluster.

-galera_recv:

The function of this interface is to block the data sent by other nodes and this node in this function, and call the copy apply function to perform the copy operation. (this interface can actually exist in parallel. It corresponds to the parameter wsrep_slave_threads that has as many threads as there are calls to galera_recv)

-galera_pre_commit:

This interface is one of the most important interfaces of galera. Its function includes two parts: first, broadcast the currently specified transaction write set to the entire cluster node, and then verify it. If the verification is successful, it will hand over the processing power to the upper layer and continue to do the commit operation of the database transaction; this interface is called when the database transaction is committed, and when this interface is called, the local transaction must have been executed.

-galera_replay_trx:

The function and use of this API is that during the verification process, due to the conflict of database locks, the current operation is galera_abort_pre_com_mit by other threads, resulting in the current thread being forcibly aborted. However, since the write set has been copied to other nodes, the transaction on this node must be completed. Through this interface, apply the write set of the transaction once, so it is called replay.

-galera_append_key:

This interface is called galera verification. The object being verified is actually the write set, and the content of the write set is actually accomplished through this interface.

-galera_append_data:

This interface is the binlog content generated by the current transaction, that is, after the verification is passed, the key executes on the slave node using data to achieve data synchronization.

-galera_post_commit:

This interface is used to actually commit transactions. This interface includes four functions: update the value of the status parameter wsrep_last_committed to indicate that the current transaction has actually been committed; update the value of the parameter wsrep_local_commits to indicate that another transaction has been successfully committed locally; check whether the current verification write set buffer can perform purge operations

-galera_to_execute_start:

This interface is specifically used to handle the execution of DDL statements

-galera_to_execute_end:

This interface is actually the same as the galera_post_commit function, which appears in pairs and is set up to handle different statements, mainly to get out of the critical section of commit so that other transactions can continue to commit.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.