Classification and optimization of parallel execution

OceanBase Database supports parallel execution for statements of the following types:

• Parallel queries
• Parallel DML (PDML)
• Parallel DDL
• Parallel LOAD DATA

Parallel queries

Applicable scenarios

• SELECT statement and its subqueries
• Query part of DML statements (such as INSERT, UPDATE, and DELETE)
• Queries on external tables

Decision-making process

The decision-making process of a parallel query involves two aspects:

1. Determine whether to perform a parallel query

   • If a PARALLEL hint is used in the query, the parallel query feature is enabled for the current session, or the PARALLEL attribute is specified for a table, a parallel query will be performed.

2. Determine the degree of parallelism (DOP)

   • For a data flow operation (DFO) such as a base table scan or index scan, its DOP is determined by the PARALLEL hint, parallel query attribute of the session, or the PARALLEL attribute of the table.
   • If it is detected that the data accessed by a DFO is less than a macroblock, the DOP is partially decreased during running.
   • The DOP of an intermediate node such as a join is inherited from that of the left-side sub-DFO of the join.
   • Some DFOs, such as a node for calculating ROWNUM, do not support parallel execution. The DOPs of such DFOs are forcibly set to 1.

PDML

In most scenarios, PDML can significantly improve the data import, update, and deletion performance.

DOP

The DOP for DML is consistent with that of the query part. If PDML is enabled, parallel execution is automatically enabled for the query part. The data read is redistributed based on the partition locations in the table to be updated. The DML operations are executed in parallel by multiple threads. Each thread processes the data of multiple partitions.

• The optimal performance can be achieved if the DOP is a multiple of the number of partitions in the target table. When the DOP is greater than the number of partitions, multiple threads process the data in the same partition. When the DOP is smaller than the partition quantity, a single thread may process the data of multiple partitions and the partitions processed by a thread do not overlap with those processed by another thread.

  • When the DOP is greater than the number of partitions in the target table, we recommend that you set the DOP to an integral multiple of the number of partitions. Generally, the number of threads that concurrently insert data into the same partition cannot exceed 4. If more than four threads concurrently insert data into the same partition, the scalability is limited, log synchronization may become a bottleneck, and partition-level lock synchronization overheads are generated.
  • When the DOP is smaller than the number of partitions in the target table, we recommend that you set the DOP to a value which ensures that the number of partitions is an integral multiple of the DOP to avoid an imbalance in the insertion workloads.

Processing strategy for index tables

Automatic maintenance is supported for index tables during PDML.

• The storage layer automatically processes local indexes when the primary table is updated by using PDML.
• If the PDML framework is used, a corresponding plan is generated to maintain global indexes.

A sample processing process of two global indexes is as follows:

1. DFO 1 updates the primary table.
2. DFO 1 transfers the data required by global indexes 1 and 2 to DFO 2. Then, DFO 2 updates global index table 1.
3. DFO 2 transfers the data required by global index 2 to DFO 3. Then, DFO 3 updates global index table 2.

The preceding strategy applies to all INSERT, DELETE, and UPDATE statements. For the MERGE statement, index maintenance is concentrated in a single DFO.

1. DFO 1 updates the primary table.
2. DFO 1 transfers the data required by global indexes 1 and 2 to DFO 2. Then, DFO 2 maintains the two global indexes one by one.

Strategy for updating a partitioning key

When you use the UPDATE statement to update the partitioning key of a primary table or global index table, you must perform the Row Movement operation to delete the existing data from the original partition and insert new data into the new partition. During a Row Movement operation, an UPDATE operation is split into the DELETE and INSERT steps. The UPDATE DFO related to the Row Movement operation is split into two DFOs. The first DFO is responsible for the DELETE step and the second DFO is responsible for the INSERT step. To avoid primary key conflicts, the INSERT DFO can be executed only after the DELETE DFO is completed.

Transaction processing

Like regular DML statements in OceanBase Database, PDML statements also support transaction processing. A PDML statement can appear in the same transaction with another query statement. After the PDML statement is executed, the transaction does not need to be immediately committed. The execution result of the PDML statement can be read in the subsequent query statement.

In OceanBase Database of a version earlier than V4.1.0, if the execution time of a PDML statement is excessively long, you must set an appropriate value for the tenant-level parameter undo_retention. Otherwise, the error -4138 (OB_SNAPSHOT_DISCARDED) may occur and the SQL statements are repeatedly retried until a timeout.

• The undo_retention parameter specifies the checkpoint for retaining undo logs, namely, the time range of undo logs to be retained counting from the current time. In OceanBase Database, all multiversion data within the specified period is retained. If the execution time of a PDML statement exceeds the value of the undo_retention parameter, the multiversion data may be evicted. If any subsequent operation tries to access the evicted multiversion data, the error OB_SNAPSHOT_DISCARDED will be returned.
• The default value of the undo_retention parameter is 30 minutes. To be specific, if a PDML statement is not completed within 30 minutes, it can time out with an error returned, regardless of the timeout value specified for the statement. Generally, if the maximum PDML execution time is 2 h, you can set undo_retention to 2.5 h. You cannot set undo_retention to a very large value. Otherwise, the multiversion data cannot be recycled and the disk space will be used up.

Since OceanBase Database V4.1.0, the execution time of PDML statements is no longer controlled by the undo_retention parameter. Multiversion data is recycled based on the transaction version. The data that can be read based on the version of a transaction will not be recycled as long as this transaction is active. However, it makes an exception if the data disk is full. In this case, the multiversion data is forcibly recycled. The system returns the error OB_SNAPSHOT_DISCARDED for the PDML statement and automatically retries the whole statement.

Bypass import

Insufficient memory space will easily lead to an out of memory (OOM) error for PDML statements. A PDML statement that does not use a bypass import path first writes data to the MemTable, and then the data is written to the disk through minor and major compactions. If the PDML statement writes data to the MemTable at a speed higher than the minor compaction speed, the memory usage keeps increasing until an OOM error is returned.

To resolve this issue, OceanBase Database introduces the bypass import feature at the storage layer. If a PDML statement executes an INSERT operation in bypass import mode, data will be directly written to the disk without the need to be written to the MemTable first. This avoids the OOM error and improves the data import performance.

You can use an APPEND hint to enable the bypass import feature. Before you use bypass import, you must commit the previous transaction and set AUTOCOMMIT to 1. In OceanBase Database V4.2.0, bypass import must be used in combination with PDML. If PDML is not enabled by using a hint or for the session, the bypass import hint is automatically ignored. Here is a sample syntax:

SET AUTOCOMMIT = 1;
INSERT /*+ APPEND ENABLE_PARALLEL_DML PARALLEL(3) */ INTO t1 SELECT * FROM t2;

DML operations unsuitable for parallel execution

To ensure correct DML semantics, parallel execution is supported for the query part but not for the DML part in the following scenarios:

• The target table contains a local unique index.
• The INSERT ON DUPLICATE KEY UPDATE statement is used.
• The target table contains triggers or foreign keys.
• The target table of the MERGE INTO statement contains a global unique index.
• The IGNORE mode is enabled.

If parallel execution is not performed for a DML statement, you can execute the EXPLAIN EXTENDED statement and view the Note field in the return result for the reason.

Row movement operation

When you update the partitioning key of a partitioned table, data may be migrated from one partition to another. In Oracle mode, you can execute the following statement to disable cross-partition data migration:

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 3;
ALTER TABLE t1 DISABLE ROW MOVEMENT;

obclient> UPDATE t1 SET c1 = c1 + 100000000;
ORA-14402: updating partition key column would cause a partition change

However, PDML will ignore the ROW MOVEMENT attribute of tables and always allow partitioning key update.

Parallel DDL

All parallel DDL operations are completed by using specific PDML operations. For example, creating an index is to create an empty index table, retrieve data of index columns from the primary table in parallel, and insert the data into the index table in parallel. OceanBase Database supports parallel execution for the following DDL statements:

• CREATE TABLE AS SELECT
• ALTER TABLE
• CREATE INDEX

Specify a DOP by using a hint

At present, only the CREATE INDEX statement supports enabling parallel execution by using the PARALLEL hint. For other DDL statements, parallel execution can be enabled only by using a session variable or the PARALLEL attribute of the table.

The syntax is as follows:

CREATE /*+ PARALLEL(3) */ INDEX IDX ON t1(c2);

Specify a DOP by using a session variable

All the preceding DDL statements support specifying a DOP by using a session variable. After you specify a DOP, all DDL statements in the session are executed in parallel based on the specified DOP. The DOPs of the query part and modification part are the same.

• The sample syntax for using the ALTER SESSION statement to modify the DOP in Oracle mode is as follows:

  -- Create an index in parallel
  ALTER SESSION FORCE PARALLEL DDL PARALLEL 3;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
  CREATE INDEX IDX ON t1(c2);
  

  In OceanBase Database V4.2.0, you can use the ALTER SESSION FORCE PARALLEL DML PARALLEL statement rather than the ALTER SESSION FORCE PARALLEL DDL PARALLEL statement to enable parallel execution for the CREATE TABLE AS SELECT statement.

  -- Use the CREATE TABLE AS SELECT statement to create a table in parallel
  ALTER SESSION FORCE PARALLEL DML PARALLEL 3;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
  CREATE TABLE t2 AS SELECT * FROM t1;
  

• In MySQL mode, the syntax varies slightly. The SET statement is used to specify the DOP by using a session variable. Here is an example:

  -- Create an index in parallel
  SET _FORCE_PARALLEL_DDL_DOP = 3;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
  CREATE INDEX IDX ON t1(c2);
  

  In OceanBase Database V4.2.0, you can use the SET _FORCE_PARALLEL_DML_DOP statement rather than the SET _FORCE_PARALLEL_DDL_DOP statement to enable parallel execution for the CREATE TABLE AS SELECT statement in MySQL mode.

  -- Use the CREATE TABLE AS SELECT statement to create a table in parallel
  SET _FORCE_PARALLEL_DML_DOP = 3;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT);
  CREATE TABLE t2 AS SELECT * FROM t1;
  

Specify a DOP by using the PARALLEL attribute

If a table on which DDL operations are to be performed has the PARALLEL attribute, you can enable parallel DDL in the following ways:

• In Oracle mode, execute the ALTER SESSION statement to enable parallel execution. The syntax is as follows:

  -- Create an index in parallel
  ALTER SESSION ENABLE PARALLEL DDL;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT) PARALLEL = 3;
  CREATE INDEX IDX ON t1(c2) PARALLEL = 2;
  

  In OceanBase Database V4.2.0, you can use the ALTER SESSION FORCE PARALLEL DML PARALLEL statement rather than the ALTER SESSION FORCE PARALLEL DDL PARALLEL statement to enable parallel execution for the CREATE TABLE AS SELECT statement.

  -- Use the CREATE TABLE AS SELECT statement to create a table in parallel
  ALTER SESSION ENABLE PARALLEL DML;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT) PARALLEL = 3;
  CREATE TABLE t2 PARALLEL 2 AS SELECT * FROM t1;
  

• In MySQL mode, use the SET statement to set a session variable to enable parallel execution. Here is an example:

  -- Create an index in parallel
  SET _ENABLE_PARALLEL_DDL = 1;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT) PARALLEL = 3;
  CREATE INDEX IDX ON t1(c2) PARALLEL = 2;
  

  In OceanBase Database V4.2.0, you can use the SET _ENABLE_PARALLEL_DMLstatement rather than the SET _ENABLE_PARALLEL_DDL statement to enable parallel execution for the CREATE TABLE AS SELECT statement in MySQL mode.

  -- Use the CREATE TABLE AS SELECT statement to create a table in parallel
  SET _ENABLE_PARALLEL_DML = 1;
  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 INT) PARALLEL = 3;
  CREATE TABLE t2 PARALLEL 2 AS SELECT * FROM t1;
  

If two or all of the PARALLEL hint, session-level attribute FORCE SESSION PARALLEL, and table-level attribute PARALLEL are specified, their priorities are sorted as follows: PARALLEL hint > session-level attribute FORCE SESSION PARALLEL > table-level attribute PARALLEL

Bypass import is used for the CREATE INDEX statement, regardless of whether parallel execution is enabled for the statement.

Parallel LOAD DATA

LOAD DATA is not implemented based on PDML. Specifically, the system uses multiple threads to split the CSV file into multiple INSERT statements and then distributes the INSERT statements based on the specified DOP.

LOAD DATA /*+ parallel(2) */ infile "test.csv" INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"';

In the preceding statement, the PARALLEL attribute specifies the DOP for loading data. If the value of the PARALLEL attribute is not specified, LOAD DATA is executed based on the DOP of 4, which is default value of the PARALLEL attribute. We recommend that you set PARALLEL to a value within the range from 0 to the maximum number of CPU cores of the tenant.