PolarDB:Optimize batch insert queries

Test environment

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

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.

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.

/*+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.

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

  Test object	batch size	1	10	100	500	1000	2000	5000	10000
  A PolarDB-X database table that is not sharded	Performance (Unit: rows per second)	12804	80987	229995	401215	431579	410120	395398	389176

  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

  Test object	batch size	1000	2000	5000	10000	20000	30000	50000
  A PolarDB-X database table that contains 32 shards	Performance (Unit: rows per second)	431579	463112	490350	526751	549990	595026	685500

  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.

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

  Test object	thread	1	2	4	8	16	32	64	80	96
  A PolarDB-X database table that contains 32 shards	Performance (Unit: rows per second)	40967	80535	151415	246062	367720	431579	478876	499918	487173

  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 nodes

  Settings:

  • 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

  Test object	thread	4	8	12	16
  A PolarDB-X database table that contains 16 shards	Performance (Unit: rows per second)	159794	302754	296298	241444

  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

  Test object	thread	9	12	15	18
  A PolarDB-X database table that contains 24 shards	Performance (Unit: rows per second)	427212	456050	378420	309052

  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

  Test object	thread	16	32	40	64
  A PolarDB-X database table that contains 32 shards	Performance (Unit: rows per second)	464612	549990	551992	373268

  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 different

  Settings:

  • Batch size: 20,000 rows
  • The number of columns: 4
  • The number of global secondary indexes: N/A
  • The number of sequences: N/A

  Test object	thread	4	8	10	12	16
  A PolarDB-X database node that uses 4 cores and 16 GB memory	Performance (Unit: rows per second)	165674	288828	276837	264873	204738

  Test object	thread	8	10	12	16
  A PolarDB-X database node that uses 8 cores and 32 GB memory	Performance (Unit: rows per second)	292780	343498	315982	259892

  Test object	thread	16	32	40	64
  A PolarDB-X database node that uses 16 cores and 64 GB memory	Performance (Unit: rows per second)	464612	549990	551992	373268

  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.