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 arrayrw_trx_ids
and updates the active transaction ID in data structures such as the read and write transaction linked listrw_trx_list
and the data setrw_trx_set
to which thetransaction cursor
is mapped. However, if PolarTrans is enabled, PolarTrans registers each transaction to the CTS log when the transaction is started, modulo-allocates aslot
to the transaction based on thetransaction 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 queryingrw_trx_ids
, removing thetransaction ID
in rw_trx_ids, and updatingrw_trx_set
andrw_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. Theread view
is obtained by usingmutexes
. 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 usingmutexes
, 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 theread view
to determine data visibility. In this way, you only need to compare the CSN of the read-only transaction and the value of thetrx 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 casessysbench 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.