All Products
Search

This Product

    PolarDB:How to optimize Batch Insert

    Document Center

    PolarDB:How to optimize Batch Insert

    Last Updated:Mar 30, 2026

    PolarDB-X supports the standard INSERT ... VALUES syntax for batch inserts. Use the following syntax to insert multiple rows in one statement:

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

    Six factors affect batch insert performance: batch size, degree of parallelism (DOP), number of shards, number of columns, number of global secondary indexes, and number of sequences. The last four are determined by table design and are outside the scope of real-time tuning — increasing global secondary indexes improves read performance but reduces write throughput.

    This topic focuses on the two factors you can tune at query time: batch size and DOP.

    Quick reference

    Goal Batch size DOP Policy
    Balanced concurrency and resource efficiency 1,000 rows 16–32 SPLIT (default)
    Maximum write throughput (32 shards) 20,000–50,000 rows 64–80 NONE
    Scale throughput Add nodes (90%–100% linear gain) Increase with node count —

    How automatic split works

    PolarDB-X automatically splits large batch insert statements to maintain high concurrency and balance load across nodes. When a statement exceeds 256 KB, PolarDB-X splits it into smaller batches and executes them serially. This behavior is controlled by three parameters:

    Parameter Default Unit Description
    BATCH_INSERT_POLICY SPLIT — Set to SPLIT to enable automatic splitting, or NONE to disable it
    MAX_BATCH_INSERT_SQL_LENGTH 256 KB Size threshold that triggers automatic splitting
    BATCH_INSERT_CHUNK_SIZE_DEFAULT 200 rows Maximum rows per batch after splitting

    To disable automatic splitting, add the following hint to your statement:

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

    Use BATCH_INSERT_POLICY=NONE only when testing large batch sizes or maximizing throughput with manual batch size control. With automatic splitting disabled, PolarDB-X does not split statements — large statements increase memory usage and can cause unbalanced load across compute nodes.

    Test environment

    All benchmarks in this topic use the following setup:

    Parameter Value
    Kernel version polarx-kernel_5.4.11-16279028_xcluster-20210802
    Node specifications 16 cores, 64 GB memory
    Number of nodes 4

    Table schema used in all tests:

    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;

    Non-sharded table performance

    A non-sharded table stores all data on a single data node. Its performance provides a useful baseline — the write throughput of a sharded table scales approximately linearly with shard count.

    Effect of batch size (DOP = 16)

    Batch size (rows) 1 10 100 500 1,000 2,000 5,000 10,000
    Throughput (rows/s) 5,397 45,653 153,216 211,976 210,644 215,103 221,919 220,529

    Performance levels off at 1,000 rows. Larger batch sizes show no meaningful gain — the optimizer cannot optimize the query as expected — and require disabling automatic splitting with BATCH_INSERT_POLICY=NONE.

    Effect of DOP (batch size = 1,000)

    DOP (threads) 1 2 4 8 16 32 64 128
    Throughput (rows/s) 22,625 41,326 76,052 127,646 210,644 223,431 190,138 160,858

    Performance peaks at DOP 16–32. Beyond 32 threads, throughput degrades.

    Recommendation for non-sharded tables: Use a batch size of 1,000 rows and set DOP to 16–32.

    Sharded table performance

    Effect of batch size

    For sharded tables, the sharding function distributes rows across shards, so the effective per-shard batch size is smaller than the statement-level batch size. Sending all rows in a single large statement lets the sharding function distribute them evenly — maximizing per-shard batch sizes and improving data node performance.

    With BATCH_INSERT_POLICY=SPLIT (DOP = 32, 32 shards)

    Batch size (rows) 1 10 100 500 1,000 2,000 5,000 10,000
    Throughput (rows/s) 12,804 80,987 229,995 401,215 431,579 410,120 395,398 389,176
    Note

    Automatic splitting triggers at batch sizes >= 2,000 rows.

    At 1,000 rows, throughput reaches ~430,000 rows/s — about twice the non-sharded baseline.

    With BATCH_INSERT_POLICY=NONE (DOP = 32, 32 shards)

    Batch size (rows) 1,000 2,000 5,000 10,000 20,000 30,000 50,000
    Throughput (rows/s) 431,579 463,112 490,350 526,751 549,990 595,026 685,500

    With automatic splitting disabled, throughput continues to improve as batch size increases, reaching ~680,000 rows/s at 50,000 rows.

    Effect of DOP

    Batch insert workloads are IOPS-bound, not CPU-bound. Most overhead falls on data nodes, so DOP must be tuned to match your node count and node specifications. There is no formula for the exact value — test in your environment to find the optimum.

    Four-node cluster (32 shards, batch size = 1,000)

    DOP (threads) 1 2 4 8 16 32 64 80 96
    Throughput (rows/s) 40,967 80,535 151,415 246,062 367,720 431,579 478,876 499,918 487,173

    Performance peaks at DOP 64–80, reaching approximately 500,000 rows/s.

    Scaling by node count (batch size = 20,000)

    Node count Shards Peak DOP Peak throughput
    2 (2 compute + 2 data) 16 8 ~300,000 rows/s
    3 (3 compute + 3 data) 24 12 ~450,000 rows/s
    4 (4 compute + 4 data) 32 32 ~550,000 rows/s

    Adding nodes improves throughput at approximately 90%–100% linear scaling per additional node.

    Scaling by node specifications (batch size = 20,000)

    Node specifications Peak DOP Peak throughput
    4 cores, 16 GB memory 8 ~280,000 rows/s
    8 cores, 32 GB memory 10 ~340,000 rows/s
    16 cores, 64 GB memory 32 ~550,000 rows/s

    Upgrading node specifications improves throughput at approximately 50%–60% per specification tier. Adding nodes delivers better throughput gains than upgrading specifications.

    Recommendations

    For balanced concurrency and resource efficiency:

    • Batch size: 1,000 rows

    • DOP: 16–32

    • This achieves ~430,000 rows/s on a 4-node, 32-shard cluster with predictable resource usage.

    For maximum write throughput:

    • Batch size: number of shards × 100 to number of shards × 1,000 (for example, 20,000–50,000 rows on a 32-shard cluster)

    • Target statement size: 2 MB–8 MB; maximum: 16 MB

    • Disable automatic splitting: add /*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/ to your statement

    • Set DOP based on your node count and specifications (see tables above)

    For scaling write throughput:

    • Add nodes rather than upgrading node specifications. Node scaling delivers 90%–100% linear improvement per additional node; specification upgrades deliver only 50%–60%.

    • Batch insert workloads are IOPS-bound. CPU utilization and memory are not the primary bottlenecks.

    For importing data from on-premises files: