Optimize batch insert queries

You can use batch insert statements to write data to your PolarDB-X databases. PolarDB-X is compatible with the MySQL protocol and syntax. You can use the following syntax to define a batch insert statement:

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

The following factors can affect the performance of a batch insert query:

1. The batch size

2. The degree of parallelism (DOP)

3. The number of shards

4. The number of columns

5. The number of global secondary indexes

6. The number of sequences

You must specify the number of shards, the number of columns, the number of global secondary indexes, and the number of sequences for your tables based on your business requirements. In most cases, these factors also affect the performance of read queries. If you specify a large number of global secondary indexes, the performance of write queries decreases, and the performance of read queries increases. This topic does not describe the impacts of the factors. This topic describes how to specify an appropriate batch size and DOP based on your business requirements.

Test environment

The following table describes the settings that are used in the test.

You can use the following statement to create a table:

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_INSERT_POLICY=SPLIT

To ensure high concurrency for batch insert queries, PolarDB-X automatically optimizes batch insert queries. If the length of a batch insert statement exceeds 256 KB, PolarDB-X splits the SQL statement into multiple statements and divides the data that you specify in the statement into small batches. PolarDB-X executes the statement to insert the data batches in series. This feature is known as SPLIT.

You can specify BATCH_INSERT_POLICY=SPLIT to enable the automatic split feature. This feature helps you ensure the high performance of queries, reduce the PolarDB-X resource consumption of batch insert queries, and balance loads among multiple nodes that are deployed in distributed mode. Parameters:

1. BATCH_INSERT_POLICY: You can set the value to SPLIT or NONE. The default value is SPLIT. If the value of this parameter is set to SPLIT, the automatic split feature is enabled.

2. MAX_BATCH_INSERT_SQL_LENGTH: This parameter specifies the value of a size threshold for SQL statements. The default value is 256 and the unit is KB. If the default value is used, the system automatically splits batch insert statements whose size exceeds 256 KB.

3. BATCH_INSERT_CHUNK_SIZE_DEFAULT: This parameter specifies the maximum size of each batch after the statement is split. The default value is 200.

To disable the automatic split feature, you can use the /*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/ hint. After the automatic split feature is disabled, PolarDB-X does not automatically split a batch insert statement. You can specify a batch size of 2,000 rows, 5,000 rows, or 10,000 rows to verify the performance of batch insert queries. The test result shows that when the batch size exceeds 1,000 rows, the optimizer cannot optimize the query as expected.

Benchmark testing for the performance of a table that is not sharded

In distributed mode, a table that is not sharded is deployed on only one host. The performance of the table can be used as a baseline to evaluate the performance of a table shard. Data in a sharded table is evenly distributed on multiple hosts. Therefore, you can evaluate the performance of a sharded table based on the test result.

In this test, a batch insert statement is used to insert data into a PolarDB-X database table that is not sharded. Data on a table that is not sharded is stored on only one data node. In this case, PolarDB-X writes the data to the data node that corresponds to the specified table. Scenario 1: Test query performance based on different batch sizes Settings:

• DOP: 16

• The number of columns: 4

• The number of global secondary indexes: N/A

• The number of sequences: N/A

Scenario 2: Test query performance based on different DOP values Settings:

• Batch size: 1,000 rows

• The number of columns: 4

• The number of global secondary indexes: N/A

• The number of sequences: N/A

Summary To test the performance of a table that is not sharded, we recommend that you specify a batch size of 1,000 rows and set the DOP to a value that is in the range of 16 to 32. If you want to test the performance of batch insert queries that write 2,000 rows, 5,000 rows, or 10,000 rows of data at one time, you must use a hint to disable the automatic split feature. The result of the test shows that when the batch size exceeds 1,000 rows, the optimizer cannot optimize the query as expected. The following hint statement is used to disable the automatic split feature:

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

Benchmark testing for the performance of sharded tables

The batch size and DOP affect the performance of batch insert queries. The tests that are described in this section test query performance based on batch size and DOP. Scenario 1: Test query performance based on different batch sizes

When a batch insert statement is executed on a sharded table, the sharding function that is used to shard the table shards the values that are specified in the statement. The batch size of the data that is pushed down to data nodes is different from the specified batch size.

A batch insert statement that is executed on a sharded table can contain a large size of data. We recommend that you specify all data that you want to insert into a physical table in one batch insert statement. After the sharding function shards the specified data, the batch size of the data that is distributed on each table shard is an appropriate value. This way, the performance of the data nodes can be improved.

• Scenario: BATCH_INSERT_POLICY=SPLIT Settings:

  • BATCH_INSERT_POLICY: enabled

  • DOP: 32

  • The number of shards: 32

  • The number of columns: 4

  • The number of global secondary indexes: N/A

  • The number of sequences: N/A

Note: When the batch size is greater than or equal to 2,000, the BATCH_INSERT_POLICY policy is triggered.

• Scenario: BATCH_INSERT_POLICY=NONE Settings:

  • BATCH_INSERT_POLICY: disabled

  • DOP: 32

  • The number of shards: 32

  • The number of columns: 4

  • The number of global secondary indexes: N/A

  • The number of sequences: N/A

Summary:

1. In the BATCH_INSERT_POLICY=SPLIT scenario, when the batch size is set to 1,000 rows, the performance reaches 430,000 rows per second. In this case, the performance of a sharded table is twice the performance of a table that is not sharded.

2. In the BATCH_INSERT_POLICY=NODE scenario, the Hash function is used to shard data and the data is distributed on table shards at random. Data is almost evenly distributed among table shards by using the Hash function. Theoretically, if the batch size is a value that is calculated by using the format: Number of shards × 1,000, the query performance can be ensured. The maximum batch size is 50,000 rows. When the size of the data that you want to insert reaches 50,000 rows, the performance reaches 680,000 rows per second.

Scenario 2: Test query performance based on different DOP values

If the CPU utilization or IOPS of your PolarDB-X data nodes reach the highest value, the specified DOP value is the appropriate value. Batch insert statements do not require compute operations and do not consume large amounts of resources of PolarDB-X compute nodes. Most of the overhead costs are incurred on PolarDB-X data nodes. If you specify an inappropriate value for DOP, the performance of the table is affected. You can specify a value based on the following factors: the number of nodes, the node specifications including CPU cores and memory size, and the maximum load of the thread pool. You cannot calculate an exactly appropriate value for DOP. You must test in a practical environment to find an appropriate value based on your business scenario.

• Test scenario 1: A batch insert statement is executed on a sharded table that is deployed on four nodes and the batch size is 1,000 rows. Different values are specified for DOP. Settings:

  • Batch size: 1,000 rows

  • The number of columns: 4

  • The number of global secondary indexes: N/A

  • The number of sequences: N/A

Summary: In the test, the performance reaches the peak value when the value of DOP is in the range of 64 to 80. The highest performance is approximately 500,000 rows per second.

• Test scenario 2: A batch insert statement is executed on different sharded tables that are deployed on different groups of nodesSettings:

  • Two-node database: two compute nodes and two data nodes

  • Batch size: 20,000 rows

  • The number of columns: 4

  • The number of global secondary indexes: N/A

  • The number of sequences: N/A

Settings:

• Three-node database: three compute nodes and three data nodes

• Batch size: 20,000 rows

• The number of columns: 4

• The number of global secondary indexes: N/A

• The number of sequences: N/A

Settings:

• Four-node database: four compute nodes and four data nodes

• Batch size: 20,000 rows

• The number of columns: 4

• The number of global secondary indexes: N/A

• The number of sequences: N/A

Summary: When the number of database nodes increases, the value of DOP needs to be increased to ensure high performance. If the statement is executed on a table that is deployed on two nodes, the system reaches the highest performance when the DOP is set to 8. The highest performance is approximately 300,000 rows per second. If the statement is executed on a table that is deployed on three nodes, the system reaches the highest performance when the DOP is set to 12. The highest performance is approximately 450,000 rows per second. If the statement is executed on a table that is deployed on four nodes, the system reaches the highest performance when the DOP is set to 32. The highest performance is approximately 550,000 rows per second. The linear rate of the performance increase is approximately from 90% to 100% when the number of database nodes increases by 1.

• Test scenario 3: a batch insert statement is executed on different tables that are deployed on database nodes whose specifications are differentSettings:

  • Batch size: 20,000 rows

  • The number of columns: 4

  • The number of global secondary indexes: N/A

  • The number of sequences: N/A

Summary: If the node specifications are upgraded, the value of DOP needs to be increased to ensure high performance. If the statement is executed on a node whose specification is 4 cores and 16 GB memory, the system reaches the highest performance when the DOP is set to 8. The highest performance is approximately 280,000 rows per second. If the statement is executed on a node whose specification is 8 cores and 32 GB memory, the system reaches the highest performance when the DOP is set to 10. The highest performance is approximately 340,000 rows per second. If the statement is executed on a node whose specification is 16 cores and 64 GB memory, the system reaches the highest performance when the DOP is set to 32. The highest performance is approximately 550,000 rows per second. The linear rate of the performance increase is approximately from 50% to 60% when the specification of the node is upgraded to the nearest specification.

Summary

• We recommend that you specify a batch size of 1,000 rows and set the DOP to a value in the range of 16 to 32 to ensure high concurrency and resource usage of your PolarDB-X instance.

• If you want to write data at the highest rate, specify a large batch size. We recommend that you specify a value that is calculated by using the format: Number of shards × A value from 100 to 1,000. For example, you can specify a value that is in the range of 20,000 to 50,000. Make sure that the size of each statement that is used to insert a batch of data is in the range of 2 MB to 8 MB. The maximum size is 16 MB. If you do not want to use the automatic split feature, you must use a hint to specify BATCH_INSERT_POLICY=NONE. If you use an SQL statement to query a large size of data, high workloads exist on each compute node in a distributed system and issues such as high memory usage and unbalanced loads among multiple nodes may occur.

• Batch insert queries consume IOPS resources. CPU utilization and memory usage are not the primary factors that cause performance bottleneck issues. If you want to increase the resources of your PolarDB-X instance to improve the performance of queries, we recommend that you add nodes to your PolarDB-X instance.

• If you want to import data from an on-premises file to a PolarDB-X database, we recommend that you use Batch Tool that is provided by PolarDB-X. You can use Batch Tool to import data to and export data from PolarDB-X databases. For information about how to use Batch Tool, see Use Batch Tool to export and import data.