PolarDB:Support for ultra-large transactions

Test instance specifications

The tests use the following PolarDB-X instance:

The tests run on a 4-core, 16 GB ECS instance. The ECS instance and the PolarDB-X instance are in the same network and connected by the same vSwitch.

Run the following statement to create a table:

CREATE TABLE `tb` (
    `id` bigint(20) NOT NULL,
    `c` longblob/char(255)/char(1),
    PRIMARY KEY (`id`)
);

This table has two columns: an id column (BIGINT, primary key) and a c column. The data type of the c column varies across scenarios and can be LONGBLOB, CHAR(255), or CHAR(1). The test uses three sharding configurations:

• One shard: This represents a single-database, single-table scenario. PolarDB-X optimizes the commit for a transaction on a single shard into a one-phase commit.

• Eight shards: The table is partitioned into eight shards based on the ID as the sharding key. Each data node (DN) holds two shards.

• Sixteen shards: The table is partitioned into sixteen shards based on the ID as the sharding key. Each data node (DN) holds four shards.

Scenario 1

This scenario executes a moderate number of SQL statements. Each statement carries a large amount of data, resulting in a high total write volume for the transaction.

Test procedure

In this test, each SQL statement has the following format:

INSERT INTO `tb` VALUES (id, c)

In the data table, the data type of the c column is longblob, and the size of c ranges from 256 KB to 8 MB. This means that when 2,048 rows are written per transaction, the total amount of data written ranges from 512 MB to 16 GB. The actual amount of data written is slightly larger than this value because the ID of the bigint type also occupies some space.

In a single-table scenario where the size of the c column is 1 MB, the transaction fails with the following error if the total write volume exceeds 2 GB:

ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP ...: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 

The MAX_BINLOG_CACHE_SIZE parameter on the data node (DN) limits the amount of binlog data generated during a transaction. For a distributed transaction, each shard has a corresponding branch transaction, and the binlog volume for each branch transaction must not exceed this limit. In this test scenario, if all statements in the transaction are INSERT statements, the data written to each shard must not exceed 2 GB. The total data write volume for the transaction cannot exceed (Number of shards × 2 GB). For example, with eight shards, a total write volume of 16 GB also triggers this error. However, this does not guarantee that your transaction will succeed as long as the write volume is below this threshold.

Test resultsConclusion

• The data volume carried by a single SQL statement cannot exceed the value of the MAX_ALLOWED_PACKET parameter on the CN, regardless of whether it is in a transaction.

• For each transaction, the binlog volume generated by statements on each shard cannot exceed the value of the MAX_BINLOG_CACHE_SIZE parameter on the DN. For example, in an INSERT scenario, the binlog records the inserted values, so the amount of data inserted per shard must not exceed this value.

• To support larger transactions, such as inserting more data, partition the table into more shards.

Scenario 2

This scenario executes a small number of SQL statements. Each statement carries a small amount of data, but the total data modification volume in the transaction is large.

Test procedure

In this test, the data type of the c column is CHAR(255). First, 2^25 rows of data are inserted into the table. The values in the id column are 0, 1, 2, ..., 2^26-1, and each value in the c column is a string of 255 'a' characters. Then, a transaction is started to execute the following statement:

UPDATE `tb` SET c = "bb...bbb" /* a string of 255 'b' characters */ where id < ?

Assume that the placeholder ? is replaced with a value x. This statement modifies a total of x rows. Since each row modification is approximately 256 bytes, the total data modification size is x * 2^8 bytes. In this test, x ranges from 2^20 to 2^25, and the total data modification size ranges from 512 MB to 8 GB.

For a single, non-sharded table, when x is 2^22 and the transaction modifies approximately 1 GB of data, the same error as in Scenario 1 occurs:

ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP ...: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again 

For an UPDATE statement, the binlog must record both the before and after images. This means that updating 256 bytes of data generates at least 512 bytes of binlog data. In the single-table case, when the total modification size is about 1 GB, the generated binlog exceeds 2 GB, triggering the error. Similarly, for a distributed transaction composed entirely of UPDATE statements, the amount of data modified on each shard should not exceed 1 GB. For example, in this test with an 8-shard setup, updating 8 GB of data in a single transaction also triggers this error.

Test resultsConclusion

• Similar to Scenario 1, the binlog volume generated on each shard cannot exceed the value of the MAX_BINLOG_CACHE_SIZE parameter on the DN. When a transaction consists entirely of UPDATE statements, the binlog records both the before and after images. Therefore, the amount of data modified per shard should not exceed half of this limit. In this scenario, a transaction on a single shard can modify at most 1 GB of data, while a transaction on eight shards can modify at most 4 GB.

• To support larger transactions, such as modifying more data, partition the table into more shards.

Scenario 3

This scenario executes a large number of SQL statements. Each statement carries a small amount of data, and the total transaction write volume is small.

Test procedure

In this test, the data type of the c column is CHAR(1). First, 32 rows are inserted into the table, with id values from 0 to 31 and the c column set to "a". Then, a transaction is started that executes the following block of 64 statements x times, for a total of 64x statements.

UPDATE `tb` SET c = “b” where id = 0;
UPDATE `tb` SET c = “a” where id = 0;
UPDATE `tb` SET c = “b” where id = 1;
UPDATE `tb` SET c = “a” where id = 1;
...
UPDATE `tb` SET c = “b” where id = 31;
UPDATE `tb` SET c = “a” where id = 31;

Conclusion

In this scenario, executing 1.28 million SQL statements within a single transaction does not reach a performance bottleneck. The transaction completes successfully across different shard configurations. This suggests that in most cases, the transaction performance bottleneck is more likely the total data volume being written, deleted, or modified, not the number of SQL statements.

Scenario 4

In this scenario, we test the impact of the number of shards on transaction performance.

Test procedure

In this scenario, the data type of the c column is CHAR(1). First, x rows are inserted into the table, with id values from 0 to x and the c column set to "a". Then, a transaction is started to execute the following statement, which modifies x rows and x bytes of data:

UPDATE `tb` SET c = “b” where id < x

Each transaction executes this SQL statement once, modifying x bytes of data across x rows. We test with x values that result in modifying 8 KB and 8 MB of data, respectively.

Conclusion

• When modifying 8 KB of data, the total data volume is small, and the SQL execution time is short. As a result, the transaction execution time depends heavily on the transaction commit time. In this case, the total execution time increases as the number of shards grows. Specifically, when the number of shards is between 1 and 64, the execution time increases at a sub-linear rate. This is because with fewer shards, DML execution time is still a significant factor. However, when the number of shards is between 1,024 and 2,048, the commit time dominates the total execution time, while DML execution time becomes negligible. In this range, the execution time increases almost linearly with the number of shards.

• When modifying 8 MB of data, the transaction execution time is primarily determined by the DML execution time, and the commit time is negligible. The total execution time does not increase significantly with the number of shards.

• In summary, while partitioning a table into more shards helps support larger transactions, it also increases commit time. For workloads with many small transactions across a large number of shards, the commit time can become the dominant factor in overall execution time. Therefore, you should choose an appropriate number of shards based on your specific workload to achieve optimal transaction performance.

Scenario 5

This scenario tests the impact of high computational pressure on CNs on transaction performance.

Test procedure

In this scenario, the table tb is partitioned into 16 shards. The data type of the c column is CHAR(255). First, 2^26 rows are inserted into the table, with id values ranging from 0 to 2^26-1. The value of the c column is the same as the id. Next, another table, tb2, is created with the exact same structure and data as tb. At this point, each table is approximately 16 GB in size. Finally, a temporary table named tmp with the same structure as tb is created. A transaction is then started to execute the following statement:

INSERT INTO tmp
    SELECT tb.id, tb.c
  FROM tb, tb2
  WHERE tb.c = tb2.c AND tb.id > x AND tb2.id > x

Running the EXPLAIN statement shows the following execution plan:

LogicalInsert(table="tmp", columns=RecordType(BIGINT id, CHAR c))
  Project(id="id", c="c")
    HashJoin(condition="c = c", type="inner")
      Gather(concurrent=true)
        LogicalView(tables="[000000-000015].tb_VjuI", shardCount=16, sql="SELECT `id`, `c` FROM `tb` AS `tb` WHERE (`id` > ?)")
      Gather(concurrent=true)
        LogicalView(tables="[000000-000015].tb2_IfrZ", shardCount=16, sql="SELECT `c` FROM `tb2` AS `tb2` WHERE (`id` > ?)")

The plan indicates that the CN pulls data where id > x from both tables to perform a hash join. The parameter x controls the amount of data pulled and processed by the CN. When x=0, all data from both tables is pulled for the hash join, requiring the CN to process approximately 32 GB of data. Note that the memory of a single CN is only 16 GB.

Test results

Conclusion

PolarDB-X remains stable when handling large transactions that impose high computational pressure on the CN, such as transactions with complex joins that cannot be pushed down and must be processed on the CN. As the amount of data to be processed increases, the execution time grows linearly without causing errors.

Summary

• The data volume carried by a single SQL statement cannot exceed the value of the MAX_ALLOWED_PACKET parameter on the CN, regardless of whether it is in a transaction.

• For each transaction, the binlog volume generated by statements on each shard cannot exceed the value of the MAX_BINLOG_CACHE_SIZE parameter on the DN.

  • This parameter defaults to 2 GB and has a maximum value of 4 GB due to a MySQL limitation. For example, in an insert-only transaction, where the binlog records the inserted values, the maximum total data volume you can write is approximately: Number of shards × 2 GB.

  • To support larger transactions that insert, delete, or modify more data, partition the table into more shards.

• The number of shards significantly affects transaction time for small data modifications but has a minor impact on large ones. Therefore, choose a shard count appropriate for your workload to optimize performance.

• PolarDB-X remains stable and performs reliably even when a single transaction creates high computational pressure on CNs.