PolarDB:CTS

Prerequisites

You cluster runs PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.12 or later.

Background information

Mainstream open source relational databases such as MySQL and PostgreSQL update transaction status and implement the Multi-Version Concurrency Control (MVCC) mechanism based on an active transaction list. The active transaction list-based approach can lead to performance bottlenecks in high-concurrency environments and cannot fully use multiple vCPUs to process concurrent transactions. This approach also does not adequately meet the requirements for read consistency, multi-point writes, and Extended Architecture (XA) transaction management within the shared-nothing architecture.

PolarTrans is developed to address this issue. In PolarTrans, you do not need to update the transaction status based on an active transaction list. PolarTrans determines the transaction status based on the maximum commit timestamp of the cluster instead of copying transaction status. PolarTrans provides a more lightweight solution for the update, retrieval, and query (visibility determination) of transaction status. PolarTrans also optimizes most of the transaction logic by implementing lock-free algorithms to improve the performance of databases in read-write scenarios and in write-only scenarios.

CTS logs are maintained entirely in memory to ensure efficiency and speed. Each transaction is modulo-mapped to a specific slot within a ring buffer based on the transaction ID specified by trx_id. Each slot includes a trx pointer and a commit sequence number (CSN).

Benefits

• Write transaction start

  When a write transaction starts, a traditional transaction system allocates an ID to the transaction, adds the ID to the active transaction ID array rw_trx_ids, updates the rw_trx_set dataset that maintains mappings between active transaction IDs and corresponding transactions trx, and enqueues the transaction to the read and write transaction linked listrw_trx_list. To ensure data consistency and integrity during this process, the system must use a trx sys mutex to protect the operations. In contrast, when a transaction starts, PolarTrans records information about the transaction in the CTS log, modulo-allocates a slot to the transaction based on the transaction ID specified by trx_id, and marks the transaction status with a special active tag. The whole process is lock-free.

• Write transaction commitment

  To commit a write transaction, a traditional system queries rw_trx_ids, removes the transaction ID specified by trx_id, and updates rw_trx_set and rw_trx_list. To ensure data consistency and integrity during the process, the system must use a trx sys mutex to protect the operations. To commit a write transaction, PolarTrans needs to only assign a commit timestamp and update the CSN field in the CTS log.

• Read view

  The MVCC mechanism of InnoDB controls data visibility by using read views. To create a read view, a traditional transaction system must copy the active transaction ID array and record the current minimum active transaction ID and maximum transaction ID. To ensure data consistency and integrity during the process, the system must use a trx sys mutex to protect the operations. The system may need to query the active transaction ID array to determine data visibility. In scenarios in which severe read-write conflicts occur, the system must update and copy the status of read-write transactions and read-only transactions under the protection of a mutex. This generates high maintenance costs and results in reduced efficiency in determining transaction visibility. PolarTrans uses the maximum commit timestamp of the system instead of read views to determine data visibility. This way, you need to only compare the CSN of the read-only transaction and the value of the trx csn field in the row record.

Enable PolarTrans

By default, after you enable the global consistency (high-performance mode) feature, PolarTrans is enabled. For information about how to enable the global consistency (high-performance mode) feature, see the "Enable global consistency (high-performance mode)" section of the Overview topic.

Performance comparison

The performance of a database before and after PolarTrans is enabled is tested. The performance is tested based on queries per second (QPS).

• Test environment

  A PolarDB for MySQL 8.0 cluster of Cluster Edition that is equipped with 88 cores and 710 GB memory is used in the test.

• Test tool

  Sysbench

• Tested data volume

  88 tables with 12 million rows in each table.

• Test cases

  Use the following SysBench built-in cases for testing:

  • oltp_read_write

  • oltp_insert

  • oltp_update_index

  • oltp_update_no_index

  • oltp_read_only

• Test results

  The test results show that PolarTrans significantly improves the overall performance of databases in read-write and write-only scenarios. However, in read-only scenarios, the performance of databases is not improved after PolarTrans is enabled because read-only transactions can use the read view cache to reduce the locking overhead generated when the database copies transaction status.