文档

如何优化Batch Insert

更新时间:

本文介绍了优化Batch Insert语句的方法。

Batch Insert语句是常见的数据库写入数据的方式,PolarDB-X兼容MySQL协议和语法,Batch Insert语法为:

INSERT [IGNORE] [INTO] table_name(column_name, ...) VALUES (value1, ...), (value2, ...), ...;

影响Batch Insert性能的主要因素包括:

  1. batch size

  2. 并行度

  3. 分片数目

  4. 列数目

  5. GSI的数目

  6. sequence数目

对于分片数目、列数目、GSI数目、sequence数目等内需因素,根据实际需求进行设置,并且常常会和读性能相互影响,例如GSI数目较多情况下,写入性能肯定会下降,但是对读性能有提升。本文不详细讨论这些因素的影响,主要聚焦于batch size和并行度的合理设置。

测试环境

本文档的测试环境见下表:

环境

参数

PolarDB-X版本

polarx-kernel_5.4.11-16279028_xcluster-20210802

节点规格

16核64 GB

节点个数

4

测试的表用例:

CREATE TABLE `sbtest1` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    `c` char(120) NOT NULL DEFAULT '',
    `pad` char(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `k_1` (`k`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Batch特性:BATCH_INSERT_POLICY=SPLIT

PolarDB-X针对数据批量写入,为保障更好的并发性,对Batch Insert进行了优化,当单个Batch Insert语句大小超过256 KB时,PolarDB-X会将Batch Insert语句动态拆分成多个小Batch,多个小Batch之间串行执行,这个特性称为SPLIT。

通过BATCH_INSERT_POLICY=SPLIT的机制,在保障最佳性能的同时,减少PolarDB-X并行执行Batch Insert的代价,尽可能规避分布式下多节点的负载不均衡。

相关参数说明:

  1. BATCH_INSERT_POLICY:可选SPLIT或NONE,默认值为SPLIT,代表默认启用动态拆分Batch。

  2. MAX_BATCH_INSERT_SQL_LENGTH:默认值256,单位KB。代表触发动态拆分Batch的SQL长度阈值为256 KB。

  3. BATCH_INSERT_CHUNK_SIZE_DEFAULT:默认值200。代表触发动态拆分Batch时,每个拆分之后的小Batch的批次大小。

关闭BATCH_INSERT_POLICY=SPLIT机制,可通过如下hint语句/*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/ 。 此参数的目标是关闭BATCH_INSERT_POLICY策略,这样才可以保证batch size在PolarDB-X执行时不做自动拆分,可用于验证batch size为2000、5000、10000下的性能,从测试的结果来看batch size超过1000以后提升并不明显。

单表的性能基准

在分布式场景下单表只会在一个主机上,其性能可以作为一个基础的性能基线,用于评测分区表的水平扩展的能力,分区表会将数据均匀分布到多台主机上。

测试方法为对PolarDB-X中的单表进行Batch Insert操作,单表的数据只会存在一个数据存储节点中,PolarDB-X会根据表定义将数据写入到对应的数据存储节点上。

场景一:batch size

参数配置:

  • 并行度:16

  • 列:4

  • gsi:无

  • sequence:无

测试项

batch size

1

10

100

500

1000

2000

5000

10000

PolarDB-X【单表】

性能(行每秒)

5397

45653

153216

211976

210644

215103

221919

220529

场景二:并行度

参数配置:

  • batch size:1000

  • 列:4

  • gsi:无

  • sequence:无

测试项

thread

1

2

4

8

16

32

64

128

PolarDB-X【单表】

性能(行每秒)

22625

41326

76052

127646

210644

223431

190138

160858

测试总结

对于单表的测试,推荐batch size为1000,并行度为16~32时整体性能比较好。在测试batch size为2000、5000、10000时,需要添加hint参数来关闭SPLIT特性,从测试的结果来看batch size超过1000以后提升并不明显。示例:

/*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/

分区表的性能基准

Batch size和并行度都会影响Batch Insert的性能,下面对这两个因素分开进行测试分析。

场景一:batch Size

在数据分片的情况下,由于包含拆分函数,Batch Insert语句会经过拆分函数分离values,下推到物理存储上的batch size会改变,示意图如下图所示

456789

所以数据分片下,PolarDB-X的Batch Insert语句可以更大一些,或者尽量将同一个物理表的数据放到一个batch Insert语句中,保证拆分函数分离values后下推到单个数据分片上的batch size较合适,以提升存储节点的性能。

  • 子场景一(BATCH_INSERT_POLICY=SPLIT)

    参数配置:

    • BATCH_INSERT_POLICY:开启

    • 并行度:32

    • 分片数:32

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    batch size

    1

    10

    100

    500

    1000

    2000

    5000

    10000

    PolarDB-X【单表】

    性能(行每秒)

    12804

    80987

    229995

    401215

    431579

    410120

    395398

    389176

    说明

    batch size >= 2000时,会触发BATCH_INSERT_POLICY策略。

  • 子场景二 (BATCH_INSERT_POLICY=NONE)

    参数配置:

    • BATCH_INSERT_POLICY:关闭

    • 并行度:32

    • 分片数:32

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    batch size

    1000

    2000

    5000

    10000

    20000

    30000

    50000

    PolarDB-X【分片数:32】

    性能(行每秒)

    431579

    463112

    490350

    526751

    549990

    595026

    685500

    总结:

    1. BATCH_INSERT_POLICY=SPLIT,batch size为1000,整体性能为每秒43w行,相当于单表的两倍;

    2. BATCH_INSERT_POLICY=NODE,本测试的values是随机分布,拆分函数是Hash,所以分布到每个分片上的数据基本均匀,理论情况下分片数×1000的batch size下性能会比较好,在最大batch size为50000时,整体性能为每秒68万行。

场景二:并行度

判断并行度是否合适的标准是将PolarDB-X数据节点的CPU使用率压满或将IOPS打满,以达到较好性能,因为Batch Insert语句基本无计算,所以PolarDB-X计算节点开销不大,主要开销在PolarDB-X数据节点。并行度过小或者过大都会影响性能,影响并行度的值的因素包括节点个数、节点规格(核数和CPU)、线程池压力等,所以并行度很难得出一个确切的数字,推荐通过实践环境进行测试,找出适合该环境的最佳并行度。

  • 子场景一:测试4节点下,batch size为1000的不同并行度。

    参数配置:

    • batch size:1000

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    thread

    1

    2

    4

    8

    16

    32

    64

    80

    96

    PolarDB-X【分片数:32】

    性能(行每秒)

    40967

    80535

    151415

    246062

    367720

    431579

    478876

    499918

    487173

    总结:该配置下,64~80并发时性能达到峰值,大概50w行每秒。

  • 子场景二:不同节点个数下的并行度测试

    参数配置:

    • 2节点:2CN×2DN

    • batch size:20000

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    thread

    4

    8

    12

    16

    PolarDB-X【分片数:16】

    性能(行每秒)

    159794

    302754

    296298

    241444

    参数配置:

    • 3节点:3CN×3DN

    • batch size:20000

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    thread

    9

    12

    15

    18

    PolarDB-X【分片数:24】

    性能(行每秒)

    427212

    456050

    378420

    309052

    参数配置:

    • 4节点:4CN×4DN

    • batch size:20000

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    thread

    16

    32

    40

    64

    PolarDB-X【分片数:32】

    性能(行每秒)

    464612

    549990

    551992

    373268

    总结:节点数增加,最佳性能的并行度也需要增大。2节点下峰值为8并发30万、3节点下峰值为12并发45万、4节点下峰值为32并发55万,整体随着节点数的性能提升线性率为0.9~1左右。

  • 子场景三:不同节点规格下的并行度测试

    参数配置:

    • batch size:20000

    • 列:4

    • gsi:无

    • sequence:无

    测试项

    thread

    4

    8

    10

    12

    16

    PolarDB-X【4核16 GB】

    性能(行每秒)

    165674

    288828

    276837

    264873

    204738

    测试项

    thread

    8

    10

    12

    16

    PolarDB-X【8核32 GB】

    性能(行每秒)

    292780

    343498

    315982

    259892

    测试项

    thread

    16

    32

    40

    64

    PolarDB-X【16核64 GB】

    性能(行每秒)

    464612

    549990

    551992

    373268

    总结:节点规格升级,最佳性能的并行度也需要增大。4核16 GB下峰值为8并发28w、8核32 GB下峰值为10并发34w、16核64 GB下峰值为32并发55w,整体随着节点规格的性能提升线性率为0.5~0.6左右。

测试总结

  • 默认情况下,batch size建议为1000,并发度建议16~32并发,整体实例的并发性能和资源负载会比较优。

  • 追求数据批量导入的最大速度,可增大batch size,建议batch size=分片数×[100,1000],比如20000~50000。对应的单条Batch语句的大小控制2 MB~8 MB(默认最大包大小为16 MB),同时需要通过hint设置BATCH_INSERT_POLICY=NONE。

    重要

    单条SQL语句过大时,分布式下单个计算节点的压力会偏重,首先会带来一定的内存消耗风险,其次可能会出现多个节点之间的压力不均衡。

  • Batch的批量导入,消耗更多的是IOPS的资源,CPU和内存不是主要瓶颈。因此,如果需要做资源升配来提升性能时,可以优先考虑扩容节点数,其次再考虑升配单节点规格。

  • 线下文本数据的批量导入,建议使用PolarDB-X配套的导入导出工具Batch Tool,请参见使用Batch Tool工具导入导出数据