如何支持超大事务

测试所用实例规格

使用以下PolarDB-X实例进行测试：

创建一个规格为4C16G的ECS连接实例进行测试，该ECS与实例位于同一网段下，由同一个虚拟交换机连接。

执行以下命令，创建如下表：

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

该表只有两列，一列为id（bigint类型，主键），一列为c（在不同的场景下，分别为longblob/char(255)/char(1)类型）。针对分片数，主要考虑以下三种设定：

• 1分片，即单库单表的情形，PolarDB-X对于单分片上的事务提交会优化为一阶段提交；

• 8分片，以ID为拆分键将上述实验表拆分成8个分片，每个存储节点（DN）2 个分片；

• 16分片，以ID为拆分键将上述实验表拆分成16个分片，每个存储节点（DN）4 个分片；

场景一

在该场景下，执行SQL语句数量中等，每条语句携带数据量较大，事务写入数据量较大。

测试过程

测试中，每一条SQL语句形如：

INSERT INTO `tb` VALUES (id, c)

数据表中c列的数据类型为longblob，c的大小从256 KB到8 MB不等。即每个事务写入2048条数据时，数据写入总量从512 MB到16 GB不等。实际的写入量比这个值稍大，因为bigint类型的ID也占用一定的空间。

在单表的情况下，当c的大小为1 MB，事务总写入量略大于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 

受DN的参数MAX_BINLOG_CACHE_SIZE的限制，事务执行过程中，引起的binlog写入量不应超过该值。对于分布式事务，每个分片对应一个分支事务，每个分支事务引起的binlog写入量不应超过该值。以本测试场景为例，如果事务的每条语句都是INSERT语句，那么每个分片的数据写入量不应超过2 GB，事务总的数据写入量不能超过：分片数量×2 GB，例如8分片的情况下，写入总量在16 GB时也会触发这个报错。但是，上述讨论并不意味着只要您的事务数据写入量小于：分片数量×2 GB，就一定能执行成功。

测试结果测试结论

• 无论是否开启事务，每条SQL语句携带的数据量受CN的参数MAX_ALLOWED_PACKET的限制，不能超过该值。

• 对于每个事务，在每个分片上执行的语句所引起的binlog写入量受DN的参数MAX_BINLOG_CACHE_SIZE的影响，不能超过该值。例如，在INSERT场景下，binlog需要记录插入的值，则事务对每个分片插入的数据量不应超过该值。

• 如果想支持更大的事务，比如在一个事务中插入更多的数据，请把数据表划分到更多的分片上。

场景二

在该场景下，执行SQL语句数量较少，每条语句携带数据量较小，事务修改数据量较大。

测试过程

在本测试中，数据表中c列的数据类型为char(255)。首先在数据表中导入2^25条数据，数据的id为0, 1, 2, ..., 2^26-1，每条数据的c列值为 "aa...aa"（255个a的字符串）。然后开启事务，执行以下语句：

UPDATE `tb` SET c = “bb...bbb” (255个b的字符串) where id < ?

假设语句中的?为x，则会修改共x条数据，每条数据修改约256字节的数据，数据总修改量为x * 2^8字节。本测试中x取值从2^20到2^25不等，数据修改总量从512 MB到8 GB不等。

在单表的情况下，当x为2^22，事务修改的数据量约为1 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 

对于UPDATE语句，binlog需要记录修改前和修改后的值，即每条数据修改了256字节的数据，binlog需记录至少512字节的数据。在单表情况下，事务修改量约为1 GB时，引起binlog写入量超过了2 GB，就触发了这个报错。同理，对于分布式事务，如果事务执行的都是UPDATE语句，那么每个分片上的数据修改量不应超过1 GB。例如，在本测试中，8分片的场景下，在一个事务中UPDATE的数据为8 GB时，也会触发此报错。

测试结果测试结论

• 与场景一类似，对于每个事务，在每个分片上所引起的binlog写入量受DN的参数MAX_BINLOG_CACHE_SIZE的影响，不能超过该值。例如，在事务语句全为UPDATE的场景下，binlog需要记录修改前的值和修改后的值，则事务在每个分片上修改的数据量不应超过该值的一半。在本场景下，单分片的事务修改的数据量最多不应超过1 GB，8分片的事务修改的数据量最多不应超过4 GB。

• 如果想支持更大的事务，比如在一个事务中修改更多的数据，请把数据表划分到更多的分片上。

场景三

在该场景下，执行SQL语句数量较多，每条语句携带数据量较小，事务写入数据量较小。

测试过程

在本测试中，数据表中c列的数据类型为char(1)。首先往数据表插入32条数据，数据的id为0～31，c为 "a"。然后开启事务，反复执行以下语句x次，即每个事务会执行64x条SQL：

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;

测试结果

测试结论

本场景下，一个事务内执行128万条SQL仍未达到瓶颈，不同分片设定下都能正常执行。因此，在多数情况下，首先触发事务瓶颈的更可能是事务数据写入、删除、修改量。

场景四

在该场景下，测试分片数对事务的性能影响。

测试过程

在本场景中，数据表中c列的数据类型为char(1)。首先往数据表插入x条数据，数据的id为0～x，c为 "a"。然后开启事务，执行以下语句，即每个事务会修改x条记录，修改x字节数据：

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

每个事务执行一次该SQL，数据修改量为x字节，数据修改条数为x。考虑x=8 KB和8 MB的情况。

测试结果

测试结论

• 当数据修改量在8 KB时，由于事务修改的数据量较小，SQL语句的执行时间较短，事务的执行时间受事务提交时间的影响较大。在该场景下，事务执行时间随分片数增多而增大。具体而言，在分片数为1～64时，当分片数增长为原来的n倍时，事务执行的时间比原来的n倍少得多。这是因为分片数较少时，虽然事务提交时间有一定的影响，但事务内DML语句的执行也占用了较多的时间。而当分片数在1024～2048的情况下，事务执行时间则基本全部由事务提交时间构成。相比之下，DML语句的执行时间可以忽略不计。因此，此时事务执行时间与分片数近似成倍数增加的关系。

• 当数据修改量在8 MB时，事务执行时间则不再随分片数增多而明显增大，这时事务执行时间主要由DML的执行时间组成，事务提交时间可以忽略不计。

• 总而言之，根据前文的结论，当要支持更大的事务时，建议将表划分到更多的分片上。但分片数越多，事务提交的时间也会越长。在多分片小事务的场景下，事务执行时间甚至主要由事务提交时间组成。因此，您可以参考本文在不同场景下的结论，根据具体的业务场景，选择合适的分片数，以获取更好的事务性能。

场景五

该场景下测试CN的计算压力较大的时候，对事务的影响。

测试过程

在本场景中，将数据表tb划分到16个分片进行实验。数据表中，c列的数据类型为char(255)。首先往数据表插入2^26条数据，数据的id为0～2^26-1，c和id相同。接着，创建另一个表tb2，表结构和数据与tb完全一致。此时，每个表的大小约为16 GB。最后，创建一个临时表tmp，其表结构和tb也相同，然后开启事务，执行下面的语句：

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

通过explain语句可以看到这条SQL的执行计划：

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` > ?)")

即把两个表id>x的部分拉到CN做hash join。通过x来控制拉取的数据量，即CN需要计算的数据量。当x=0，会拉取所有的数据到CN做hash join，此时CN总共需要处理约32 GB的数据，并且单个CN节点的内存只有16 GB。

测试结果

测试结论

单个事务导致CN端的计算压力较大、计算量较多时（比如事务内有复杂的join且这些join无法下推，必须要在CN处理的场景），PolarDB-X对大事务的支持仍然稳定。具体表现为，在需要处理的数据量呈倍数增长时，事务执行不报错，且执行时间也呈线性增长。

总结

• 无论是否开启事务，每条SQL语句自身携带的数据量受CN的参数MAX_ALLOWED_PACKET的限制，不能超过该值。

• 对于每个事务，在每个分片上执行的语句所引起的binlog写入量受DN的参数MAX_BINLOG_CACHE_SIZE的影响，不能超过该值。

  • 该参数值默认为2 GB，受MySQL的限制，最大值为4 GB，以INSERT为例，如果一个事务中全是INSERT语句，由于binlog需要记录插入的值，数据写入总量最大不超过：分片数×4 GB。

  • 如果想支持更大的事务，比如在一个事务中插入/删除/修改更多的数据，请把数据表划分到更多的分片上。

• 在事务修改数据量较少的情况下，分片数对事务执行时间的影响较大；在事务修改数据量较多的情况下，分片数对事务执行时间的影响则不大。因此，您可以参考本文在不同场景下的结论，根据具体的业务场景，选择合适的分片数，以获取更好的事务性能。

• 单个事务导致CN端的计算压力较大、计算量较多时，PolarDB-X对大事务的支持仍然稳定。