Qfusion vs MGR, MGC

Wo fun qfusion using the current very mature and widely used master-slave replication data Synchronization architecture, in the premise of ensuring high performance, combined with commercial high-performance, highly available distributed storage Qcfs 0 loss of data, while the fun technology from the BIOS, hardware configuration, file system, operating system kernel, MySQL configuration parameters such as bottom-up to do a large number of overall optimization, so that the amount of transaction time within the unit to further improve.

When it comes to MySQL, the most common concerns are:

How much TPS/QPS can be achieved on the performance of the Write node. Why do we care about it because it directly affects the amount of trading in a unit of time

Read the replication delay from the library is large. Why do we care about it because it directly affects whether the query data is timely enough

Can you guarantee that the data is not lost? Why do we care about it, nonsense, believe no one wants to lose data

Based on the three issues mentioned above, looking at the MySQL industry, the most popular three distributions (also called three branches: official MySQL, Percona Server, mariadb), spawned different Open-source data synchronization technologies, The most popular data synchronization architecture in China includes the following three kinds:

Master-slave Replication architecture (no release generic architecture): A universal Data synchronization technology based on Binlog logs. Under high concurrent pressure, the data rate is the fastest (parallel replication technology), the transaction volume within the unit time is affected by the other nodes, but it is difficult to ensure the data is not lost in the master-slave switching.

MySQL GR (the official version of Mgr,mysql): Based on the Totem protocol data synchronization technology, plug-in installation, MySQL official native plug-ins. Cluster architecture, providing multiple-node writing, allowing a few nodes to crash and ensuring data is not lost, the data synchronization delay between nodes is very small, but the volume of unit time is seriously affected by the flow control

MARIADB Galera Cluster (MGC) and Percona xtradb Cluster (PXC): Data synchronization technology based on Galera Cluster Third-party plug-in library. Cluster architecture, providing multiple-node writing, allowing a few nodes to crash and ensure data is not lost, the data synchronization delay between nodes is very small, but the volume of unit time is affected by the flow control and Galera plug-in authentication efficiency seriously

As mentioned above, the current open source of the three data synchronization architecture have their own advantages, but also have their own obvious short board, there is a data synchronization architecture to ensure high performance, but also to ensure that the data are not lost.

Yes... Wo fun qfusion using the current very mature and widely used master-slave replication data Synchronization architecture, in the premise of ensuring high performance, combined with commercial high-performance, highly available distributed storage Qcfs 0 loss of data, while the fun technology from the BIOS, hardware configuration, file system, operating system kernel, MySQL configuration parameters such as bottom-up to do a large number of overall optimization, so that the amount of transaction time within the unit to further improve.

Based on our comprehensive testing and comparison, the Qfusion has a strong performance and stability advantage over open source data synchronization solutions.

Kothe, what are the advantages of qfusion compared to the current open source solution? Below we together from the performance data, the structure principle, the configuration parameter several aspects to compare, sees the truth.

First, performance comparison

In terms of performance testing, we used the Sysbench benchmarking tool to test in the same hardware environment, comparing the 5 metric values of OLTP, select, UPDATE, INSERT, delete, which is the highest performance value under different data synchronization architectures, Test results are:

Mgr compared with qfusion, OLTP dropped 32.12%, select dropped 5.44%, update dropped 24.18%, insert dropped 58.18%, delete dropped 11.44%;mgc compared to the Wo-fun qfusion, OLTP down 76.28%, select down 0.19%, update down 45.74%, insert down 90.49%, delete drop 59.65%

Detailed test data are as follows:

Description: The version number involved in the test

Wo fun qfusion: mysql 5.7.19 Master Copy

MGR: Using MySQL 5.7.19 group replication

MGC: Using mariadb 10.2.12 galera Cluster (gcs.fc_limit = 100)

Ii. Comparison of architectures

In terms of architectural complexity and the complexity of the principles, the master-slave replication technology used by Qfusion has been very mature (the earliest data synchronization technology), the maintenance cost is very low (there is a good manual document, there are quite a lot of experience to refer to), and Mgr and MGC is the subsequent data synchronization technology, In particular, MGR is the latest data synchronization technology, there are few production cases, maintenance costs are high-even MGR and MGC error logs are quite a few people can not understand.
丨 2.1. MGC

1. Structure

A database cluster architecture based on Galera Cluster Third-party plug-in library, at least 3 nodes, not more than 8 nodes recommended

Composition of Galera components

Group communication layer: the main implementation of the unified global data synchronization strategy and the sorting of all the transactions within the cluster, easy to generate Gtid

Replication layer: Mainly used to complete data synchronization, composed of Applier and slave queue. The efficiency of the replication module directly affects the writing function of the whole cluster.

2. Working principle

When the Write node has an update operation, the first broadcast sends the Writeset to the other node, after the other node certification passes, the Write node executes the COMMIT_CB (the other node executes APPLY_CB and COMMIT_CB), otherwise executes rollback_ CB (Other nodes certifacation discard the dataset), as shown in the following figure:

3. Advantages and Disadvantages

Advantages:

High Availability

Strong consistency

Easy to expand

Disadvantages:

Transactions require global validation through, and cluster performance is limited to the worst performing nodes

Lock conflicts are serious when multiple-point concurrent writes

Full-volume copy data SST, the node as donor (the node that provides synchronization files) cannot provide read and write during synchronization

High maintenance costs, restrictions and precautions more than丨 2.2. MGR

1. Structure Chart

Mgr is the MySQL Group replication shorthand, based on the official native of the GR replication plug-in implementation of a database cluster architecture, at least 3 nodes, at most not recommended more than 8 nodes, frame composition and mgc the same;

GR Component: At the bottom of MySQL, Gr adds another API layer to achieve the required functionality. Program structure, the GR API is mainly divided into three parts:

Capture the context in which the currently executing transaction is being traced.

Applier performs remote transaction transfer to local log to local database.

Recovery is responsible for the node recovery in the distributed environment, as well as related data back-tracking, failure processing and so on.

2. Working principle

MySQL group replication is a MySQL plug-in, built on the existing MySQL master-slave replication infrastructure, using binary logs, based on the logging and global transaction identifier functions of the row. Implemented based on distributed consistency algorithm (variant of Paxos protocol). In principle, similar to Galera, when a transaction is ready to be submitted, an atomic broadcast is automatically made within the group, telling the other node what has changed or what transaction has been performed. Group replication determines that the first committed transaction is a valid transaction, which is replayed throughout the group, and the committed transaction is immediately interrupted, or rolled back, and finally discarded.

3. Advantages and Disadvantages

Similar to MGC, but Mgr also has the following restrictions

Must enable the Binlog,binlog format must be row format, log slave updates must be open, Binlog checksum currently does not support

Gtid mode must be turned on

Copy related information must use table storage

The transaction write collection (Transaction write set extraction) must be open. (This is currently in conflict with SavePoint, which is why mysqldump cannot back up the GR instance)

SERIALIZABLE isolation level does not support

Concurrent execution of the DDL can result in errors in data consistency, and is not currently supported for the simultaneous execution of the same object's DDL at multiple nodes

Cascading constraint operations for foreign keys The current implementation does not fully support丨 2.3. Qfusion

1. Structure Chart

Data synchronization between different data nodes is based on master-slave replication mechanism, but thanks to the QCFS storage, the main library data is stored in QCFS, the main library host is abnormal automatically dispatch a new host to mount the main library data volume can be restored, the whole process of 10 seconds to complete

2. Working principle

When the main library transaction commits, the data changes are written to the main library's own Binlog file, and the Binlog dump thread on the main library is notified of the update, and the Binlog file of the main library is read from the IO thread of the library and stored in the relay log file from the library, Then the SQL thread reads relay log file parsing replay from the library to the data file, completes the data synchronization

3. Advantages and Disadvantages

Advantages:

Main Library asynchronous write, performance is comparable to single instance, and performance is not constrained by other nodes, there are no Mgr and MGC of many limitations

Disadvantages:

Using asynchronous replication in primary write business switching can easily lead to data loss. However, in the Qfusion platform, using the "single Master +QCFS Storage" architecture to solve this flaw perfectly, a replication cluster requires only one node as a write node, the other as read-only from the node, when the main library crash, benefiting from QCFS storage, the main library data stored in the QCFS, Master Library host exception automatically schedule a new host mount the main library data volume can be restored, the entire process of 10 seconds to complete and ensure that the data 0 lost.

Iii. Comparison of parameters

From the complexity of the configuration parameters, to successfully run the data synchronization framework, the master-slave replication architecture must be 2~3, and MGC must have nearly 10 parameters, Mgr due to more restrictions on the use of the necessary parameters are more than 10丨 3.1. MGC

The Galera component must be configured with parameters:

# Different nodes need only modify the wsrep_node_address parameter for their own IP address
Wsrep_on=on
wsrep_provider=/usr/local/mysql/lib/galera/ libgalera_smm.so
wsrep_provider_options= "gcache.size=1g;evs.send_window=256;evs.user_send_window=128"
wsrep_cluster_name=wqglr_0001
wsrep_cluster_address=gcomm:// 10.10.90.167:4567,10.10.90.174:4567,10.10.90.180:4567
Wsrep_node_name = physical
wsrep_node_address= 10.10.90.180:4567
Wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth= "Xtrabackup:xtrabackuppass"
Wsrep_ Slave_threads=8