PolarDB for MySQL uses PolarTrans to improve database performance in highly concurrent online transaction processing (OLTP) scenarios. One of the core technologies of PolarTrans is Commit Timestamp Store (CTS), which replaces the traditional active transaction list to manage transactions. This topic describes the technical principles and advantages of CTS used in PolarTrans, as well as the performance test results in standard scenarios.

Prerequisites

Your cluster must be a PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.12 or later.

Background information

Currently, mainstream open-source relational databases such as MySQL and PostgreSQL adopt transaction status update and multiversion concurrency control (MVCC) mechanisms based on active transaction lists. These mechanisms degrade database performance in high-concurrency scenarios and cannot effectively use multiple vCPUs to process concurrent transactions. Additionally, these mechanisms also degrade read consistency, multi-point writes, and the extended architecture (XA) transaction management in the shared-nothing architecture.

However, when PolarTrans is enabled, it updates transaction status by obtaining the maximum commit timestamp without the need to maintain the active transaction list and copy the transaction status. PolarTrans provides a more lightweight solution for the update, obtainment, 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 and write scenarios and in write-only scenarios.

CTS logs are used to record the core data of transactions, such as transaction status updates, transaction visibility, and transaction active status.

CTS logs are stored in memory. The CTS log of a transaction is composed of a slot of a ring buffer. Each transaction is modulo-mapped to its corresponding slot based on transaction ID. Each slot includes a transaction cursor and a commit sequence number (CSN).

Technical advantages

  • Write transaction start

    If PolarTrans is not enabled, the system allocates an ID by using mutexes when a transaction is started. The system then writes the ID to the active transaction ID array rw_trx_ids and updates the active transaction ID in data structures such as the read and write transaction linked list rw_trx_list and the data set rw_trx_set to which the transaction cursor is mapped. However, if PolarTrans is enabled, PolarTrans registers each transaction to the CTS log when the transaction is started, modulo-allocates a slot to the transaction based on the transaction ID, and marks the transaction status with a special active tag. This whole process is lock-free.

  • Write transaction commitment

    If PolarTrans is not enabled, mutexes are also used in the write transaction commitment process. The system commits a write transaction by querying rw_trx_ids, removing the transaction ID in rw_trx_ids, and updating rw_trx_set and rw_trx_list. If PolarTrans is enabled, PolarTrans assigns a commit timestamp and updates the CSN field in the CTS log when a transaction is committed.

  • read view

    The MVCC mechanism of InnoDB uses a read view to control the visibility of data. The read view is obtained by using mutexes. After the read view is obtained, the system copies the active transaction ID array, the current minimum active transaction ID, and the maximum transaction ID. The system may need to query the active transaction ID array to determine data visibility. In scenarios with severe read-write conflicts, read-write transactions and read-only transactions need to update and copy the current transaction status by using mutexes, which leads to high maintenance costs and low visibility determination efficiency. However, if PolarTrans is enabled, PolarTrans obtains the maximum commit timestamp of a transaction instead of the read view to determine data visibility. In this way, you only need to compare the CSN of the read-only transaction and the value of the trx csn field in the row record.

Enable PolarTrans

After you enable the global consistency (high-performance mode) feature, PolarTrans is enabled by default. For more information about how to enable the global consistency (high-performance mode) feature, see Enable SCC.

Performance comparison

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

  • Test environment

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

  • Test tool

    Sysbench

  • Tested data volume

    88 tables with 12,000,000 rows in each table.

  • Test cases
    sysbench provides the following test cases:
    • oltp_read_write
    • oltp_insert
    • oltp_update_index
    • oltp_update_no_index
    • oltp_read_only
  • Test results

    The following 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 incurred when the database copies transaction status.