PolarDB:バッチ挿入クエリの最適化

テスト環境

次の表に、テストで使用される設定を示します。

    'id' int (11) NOT NULL,
    'k' int (11) NOT NULL DEFAULT '0',
    'c' char(120) NOT NULL DEFAULT ''、
    'pad'char (60) NOT NULL DEFAULT ''、
    主要なキー ('id') 、
    キー 'k_1 ' ('k')
) エンジン=InnoDBデフォルト料金セット=utf8mb4; 

BATCH_INSERT_POLICY=分割

バッチ挿入クエリの同時実行性を高めるために、PolarDB-Xはバッチ挿入クエリを自動的に最適化します。 バッチ挿入ステートメントの長さが256 KBを超える場合、PolarDB-XはSQLステートメントを複数のステートメントに分割し、ステートメントで指定したデータを小さなバッチに分割します。 PolarDB-Xはステートメントを実行して、データバッチを連続して挿入します。 この特徴は分割として知られている。

自動分割機能を有効にするには、BATCH_INSERT_POLICY=SPLITを指定します。 この機能は、クエリの高パフォーマンスを確保し、バッチ挿入クエリのPolarDB-Xリソース消費を削減し、分散モードでデプロイされた複数のノード間の負荷を分散するのに役立ちます。

自動分割機能を無効にするには、/* + TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/ ヒントを使用します。 自動分割機能を無効にすると、PolarDB-Xはバッチ挿入ステートメントを自動的に分割しません。 2,000行、5,000行、または10,000行のバッチサイズを指定して、バッチ挿入クエリのパフォーマンスを確認できます。 テスト結果は、バッチサイズが1,000行を超えると、オプティマイザが期待どおりにクエリを最適化できないことを示しています。

シャーリングされていないテーブルのパフォーマンスのベンチマークテスト

分散モードでは、シャードされていないテーブルは1つのホストにのみデプロイされます。 テーブルのパフォーマンスは、テーブルシャードのパフォーマンスを評価するためのベースラインとして使用できます。 シャードテーブルのデータは、複数のホストに均等に分散されます。 したがって、テスト結果に基づいてシャードテーブルのパフォーマンスを評価できます。

このテストでは、バッチ挿入ステートメントを使用して、シャードされていないPolarDB-Xデータベーステーブルにデータを挿入します。 シャードされていないテーブルのデータは、1つのデータノードにのみ格納されます。 この場合、PolarDB-Xは、指定されたテーブルに対応するデータノードにデータを書き込みます。

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

シャードテーブルのパフォーマンスのベンチマークテスト

バッチサイズとDOPは、バッチ挿入クエリのパフォーマンスに影響します。 このセクションで説明するテストでは、バッチサイズとDOPに基づいてクエリのパフォーマンスをテストします。

バッチ挿入ステートメントがシャードテーブルで実行されると、テーブルをシャードするために使用されるシャーディング関数は、ステートメントで指定された値をシャードします。 データノードにプッシュされるデータのバッチサイズは、指定されたバッチサイズとは異なります。

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

  説明 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.

概要

• PolarDB-Xインスタンスの同時実行性とリソース使用率を高めるために、バッチサイズを1,000行に指定し、DOPを16〜32の範囲の値に設定することを推奨します。
• 最も高いレートでデータを書き込む場合は、大きなバッチサイズを指定します。 シャード数 × 100から1,000までの値の形式で計算される値を指定することを推奨します。 たとえば、20,000〜50,000の範囲の値を指定できます。 データのバッチを挿入するために使用される各文のサイズが2 MBから8 MBの範囲であることを確認してください。 最大サイズは16 MBです。 自動分割機能を使用しない場合は、ヒントを使用してBATCH_INSERT_POLICY=NONEを指定する必要があります。 SQL文を使用して大量のデータをクエリすると、分散システムの各計算ノードに高いワークロードが存在し、メモリ使用量の増加や複数ノード間の負荷の不均衡などの問題が発生する可能性があります。
• 一括挿入クエリはIOPSリソースを消費します。 CPU使用率とメモリ使用率は、パフォーマンスのボトルネックの問題を引き起こす主な要因ではありません。 クエリのパフォーマンスを向上させるためにPolarDB-Xインスタンスのリソースを増やしたい場合は、PolarDB-Xインスタンスにノードを追加することを推奨します。
• オンプレミスファイルからPolarDB-Xデータベースにデータをインポートする場合は、PolarDB-Xが提供するバッチツールを使用することをお勧めします。 バッチツールを使用して、PolarDB-Xデータベースにデータをインポートおよびエクスポートできます。 バッチツールの使用方法については、「バッチツールを使用したデータのエクスポートとインポート」をご参照ください。