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

Recently, Tencent Cloud has released a distributed database solution (DCDB). One of its most obvious features is that it provides higher performance than open source distributed transaction XA. Large-scale business systems have the characteristics of many users and high concurrency. In this respect, it is difficult to support the performance of centralized databases (stand-alone databases), so mainstream Internet companies often adopt distributed (architecture) databases. Physically, the use of more low-end devices, the logical need for large table horizontal split to support business.

Although distributed database can solve the performance problem, the problem of transaction consistency (Consistency) is difficult to solve on distributed database.

Distributed transaction is a big problem, and data consistency is difficult to achieve.

As we all know, the update made by a transaction is completed by multiple independent data nodes within the distributed database system (the local transaction of each node is a transaction branch of this global transaction). It is possible that some transaction branches cannot be committed successfully.

To solve this problem, although there has long been a theoretical solution in the industry-two-phase commit protocol (2PC), and extended to the solution of distributed transaction (XA). However, there are few cases of engineering implementation and large-scale application in the industry. However, Tencent Cloud distributed database DCDB has been used in internal business for many years.

(figure: two-phase commit algorithm)

At present, DCDB has been used in more than 90% of Tencent's internal trading and billing business, and Sany heavy Industry (Tree Root Interconnection), Huitong World (G7), China Literature Group (starting Point / Chuangshi Chinese Network, etc.), WeBank, Hetai Life Insurance, Weifutong and so on are all in this product.

Tencent Cloud launches distributed database XA, which supports MySQL 5.7

Tencent Cloud distributed database DCDB is a distributed database compatible with MySQL protocol, which is based on the cloud transformation of Tencent financial database (company internal code TDSQL). Currently, Tencent Cloud DCDB officially supports distributed transaction XA on MySQL 5.7 (percona branch) protocol, and has been released for developers' use in Tencent Cloud Public Cloud and Finance Cloud. Developers can apply for a DCDB instance, and after initialization, run the following sql to connect the instance for initialization:

MySQL > xa init

Query OK, 0 rows affected (0.03 sec)

Note: before initializing xa, enable strong synchronous replication. In addition, the sql will create a xa.gtid_log_t. Users must not do anything about it in subsequent use.

To better support distributed transactions, DCDB also adds the SQL command:

1) SELECT gtid (), which gets the gtid (global uniqueness identification of the transaction) of the current distributed transaction, and returns null if the transaction is not a distributed transaction

The format of gtid:

'Gateway id'-' gateway random value'- 'serial number'-'timestamp'-'partition number', such as c46535fe-b6-dd-595db6b8-25

2) SELECT gtid_state ("gtid"), get the status of "gtid". Possible results are:

A) "COMMIT", indicating that the transaction has been or will eventually be committed

B) "ABORT", indicating that the transaction will eventually be rolled back

C) Null, because the status of the transaction will be clear after an hour, there are two possibilities:

1) query after an hour, indicating that the transaction status has been cleared

2) query within an hour to identify that the transaction will eventually be rolled back

3) Operation and maintenance commands:

Xa recover: send xa recover commands to the back-end SET and summarize them

Xa lockwait: displays the wait relationship of the current distributed transaction (you can use the dot command to convert the output to a wait diagram)

Xa show: the distributed transaction running on the current gateway

Take Python as an example, you can encode the transfer business as follows:

Db = pyMySQL.connect (host=testHost, port=testPort, user=testUser, password=testPassword, database=testDatabase)

Cursor = db.cursor ()

Try:

Cursor.execute ("begin")

# subtract 1 from the balance of an account Bob

Query = "update t_user_balance SET balance = balance-1 where user='Bob' and balance > 1)

Affected = cursor.execute (query)

If affected = = 0: # insufficient balance, rollback transaction

Cursor.execute ("rollback")

Return

# add 1 to the balance of an account John

Query = "update t_user_balance SET balance = balance + 1 where user='John')

Cursor.execute (query)

# for security reasons, it is recommended to execute 'SELECT gtid ()' here to get the id value of the current transaction, so as to track the execution of the transaction later.

# commit transaction

Cursor.execute ("commit")

Except pyMySQL.err.MySQLError as e:

# when a failure occurs, roll back the transaction

Cursor.execute ("rollback")

The advantage of distributed transaction is that it will greatly reduce the difficulty of application development, because in some databases that do not support XA, the business system needs to be specially and skillfully designed, rather than using the database to solve the problem of data inconsistency in the transaction. This requires a high technical level of application developers, the more complex the business system, the more will increase the development costs and technical threshold, which is the main reason why most developers in the industry can only be prohibitive when facing distributed databases.

Key implementation solution of Tencent Cloud DCDB XA

1. Introduction to DCDB architecture

The diagram of the entire cluster architecture of Tencent Cloud DCDB is shown below. MySQL uses master / slave node configuration (also known as master / slave). A set of master / slave nodes is called SET, and a gateway (TProxy) is configured outside each SET to form a physical shard (Shard).

The DCDB backend is the MySQL (or its branch version) database. Currently, Tencent Cloud Public Cloud publishes a version that supports XA based on MySQL 5.7.17 (percona branch).

2. Gateway (TProxy) and XA

A gateway is a network module used to receive requests and establish a connection with the back-end MySQL. The gateway can work in two modes, one is called noshard, in which the gateway does not process / parse SQL statements and transparently forward requests and replies. In another mode called shard (distributed, that is, support for automatic horizontal table splitting), TProxy parses the SQL and forwards it to different data shards.

Until XA is implemented, the gateway does not allow DML statements to be sent to multiple SET in a transaction. Because when the two-phase commit (2PC) is not implemented, the transaction adopts one-phase commit, so if a SET commit in the distributed system fails or rolls back, then the distributed transaction is in an inconsistent state.

(how the gateway works)

The transaction manager (TM) required in the two-phase commit. To solve disaster recovery and simplify the architecture, Tencent Cloud DCDB implements TM in TProxy, while the gateway of DCDB is a stateless module. Through this architecture, DCDB XA can support:

1. Distributed transaction is transparent to business and compatible with stand-alone transaction syntax (start transaction/commit/rollback/savepoint).

2. Each gateway can accept and process transaction requests independently, and there is no need to coordinate node failures with other gateways without losing transactions.

3. Allow multiple statements in an explicit transaction to be sent to multiple fragments respectively.

4. The gateway does not need persistent state or disaster recovery, and can exit or join the cluster at any time through the scheduling cluster, and the performance can be expanded.

5. Support autocommit to issue order bar statements, write and access multiple shards, etc.

The DCDB gateway also allows group by and order by to be run in streaming, which makes such operations very efficient; the gateway also supports two Shard equivalent connections using shardkey (split table keys) and subqueries using shardkey.

In the future, Tencent Cloud also plans to support advanced features such as distributed JOIN, Sparksql and second-level partitioning, and is compatible with more advanced MySQL syntax.

3. Strong synchronization and XA

Since Tencent Cloud DCDB uses strong synchronous replication by default, that is, the data of master and slave nodes are identical, XA transactions also follow the logic of strong synchronization, that is, you need to wait for the slave to confirm data synchronization before giving commit to the business. Based on strong synchronization, DCDB XA can easily deal with the following two abnormal situations

1. When the master node fails, it is confirmed that the transaction data will not be lost: if the master node fails, the slave computer with the latest data and binlog is selected as the master node, which also includes the data of all transactions that have been confirmed to the user that the commit has been completed.

2. The original master node rejoins the cluster after recovery, and unacknowledged transactions flashback automatically: when the original master node resumes reconnecting to the cluster, it will run as a slave, and it may retain redundant committed transactions (at this time, transactions are not confirmed by strong synchronization, that is, the original machine does not have relevant data), then these transactions will be flashed back. Although these transactions may have been committed within the MySQL of the original primary node, due to the strong synchronization mechanism, he does not return a commit statement to the client, which means that it is still considered an outstanding transaction. Therefore, the flashback of these transactions does not break the ACID property of the database. It is worth saying that flashback flashback is based on binlog generation to do inverse operation, which is different from database rollback rollback, flashback can do DDL operation.

Strong synchronization of Tencent Cloud DCDB is a self-developed capability of Tencent financial-grade database. Its performance is much higher than that of official semi-synchronization, which is almost equal to the performance of asynchronous replication. Tencent Cloud DCDB has been used in Tencent for many years, and there has not been a single data error caused by master-slave switching or failure. Moreover, in terms of performance, it also supports the massive concurrency of all kinds of large-scale operational activities of Tencent, such as red packets and large-scale promotion of all kinds of games. The main reason is that strong synchronization adopts asynchronous submit / wait mode and does not occupy database worker threads.

4. Concurrency control and isolation level

In order to achieve the balance between data consistency and performance, the key of distributed transaction is database isolation control. The isolation level of XA can be up to serializable (full serialization), and there will be no problem of phantom reading. The serializable level can be set for all physical shards of DCDB (and the MySQL database hosted on it) by setting SET global tx_isolation='serializable'. Of course, you can also adjust the database instance performance by adjusting the isolation level. Theoretically, Read Uncommitted has the highest performance, but there may be dirty reads and phantom reads.

Isolation and separation

Dirty reading

Non-repeatable

Illusory reading

Read Uncommitted

Y

Y

Y

Read Committed

N

Y

Y

Repeatable (default)

N

N

Y

Serializable

N

N

N

(four isolation levels defined by the SQL-92 standard defined by ANSI/ISO)

5. Distributed transaction processing algorithm

As mentioned earlier, the gateway of Tencent Cloud DCDB has been able to parse SQL statements in shard mode. Tencent Cloud implements TM on the gateway to make XA most efficient. To this end, we implement the coordinator (coordinator) in the TM in the gateway, maintain the status of each XA in the gateway, record the SET written by each XA, and then commit in two phases during the submission phase. The process is as follows:

1. When the gateway executes the insert/update/delete statement of a transaction, it records which SET is modified by the statement.

2. When SET, a XA START is sent to start the transaction branch on the SET. (note: when the XA transaction starts, it is not sure which commit mode the transaction will be executed, so it always starts a transaction with xa start.)

3. Check whether the number of SET is affected by ≤ 1. If so, do one-phase commit (xa commit one phase) directly.

4. If the number of SET is affected by ≥ 2, it will be submitted in two phases instead.

1) the gateway first sends xa prepare 'gtid' to the participating SET (2 or more SET)

2) SET receives xa prepare reply ok (indicates successful confirmation)

3) after receiving the successful confirmation, write the commit log corresponding to XA, and then send xa commit 'gtid' to participate in SET.

4) if an SET returns an error, or if writing to the commit log fails, the gateway sends a xa rollback 'gtid' to the relevant SET so that the global transaction is rolled back.

The commit log of Tencent Cloud DCDB is stored in SET, and this step is completed in batches-the gateway backend thread aggregates the committed distributed transactions and then writes each SET in separate connections and transactions, and the commit log of each transaction is only written to one SET, so this overhead does not significantly increase transaction commit time or reduce TPS. Moreover, depending on the strong synchronization and disaster recovery features of Tencent Cloud DCDB, as long as the XA is successfully written to the commit log, it means that the data has been written to the slave.

Although the vast majority of XA transactions are executed normally. However, a few exceptions will still affect the stability of the entire cluster. Therefore, Tencent Cloud has designed agent (monitoring module) to continue to assist in the submission of prepared transactions on the local MySQL after a failure, that is, agent will parse the commit log and process the local transaction data that is still in prepared according to the exception. If there is no transaction commit decision on the commit log, agent will also roll back the prepared local transactions that have timed out.

Although XA has long been implemented in MySQL 5.5,5.6 and other versions, these two versions still lack performance compared with 5.7. therefore, Tencent Cloud only supports XA version 5.7.17 on the public cloud. Today, Tencent Cloud has made a large number of optimizations and related bug fixes in MySQL, percona, MariaDB and other branches (some of which have been submitted to the community for patch or open source repair). In the future, Tencent Cloud will continue to work on the development of new features and related Bug repair, so as to provide better distributed database support for many enterprises in need.

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.