Classification and optimization of parallel execution

OceanBase Database supports the following types of parallel execution:

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

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 operator (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, as shown in the following figure.

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 row movement to delete the existing data from the original partition and insert new data into the new partition. During row movement, an UPDATE operation is split into the DELETE and INSERT steps. That is, the UPDATE DFO related to row movement 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 hours, you can set undo_retention to 2.5 hours. 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.

Direct load

Insufficient memory space will easily lead to an out of memory (OOM) error for PDML statements. A PDML statement that does not use a direct load 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 direct load feature at the storage layer. If a PDML statement executes an INSERT operation through direct load, 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 direct load feature. Before you use direct load, you must commit the previous transaction and set AUTOCOMMIT to 1. In OceanBase Database V4.2.0, direct load must be used in combination with PDML. If PDML is not enabled by using a hint or for the session, the direct load 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 DML part cannot be executed in parallel if the IGNORE mode is enabled.
• INSERT ALL, UPDATE, DELETE, and MERGE INTO statements involving multiple tables cannot be executed in parallel.
• Optimization for array binding batches is not supported.
• DBLink is not supported.
• INSERT...VALUES..., INSERT UPDATE, or REPLACE statements cannot be executed in parallel.
• Columns with the ON UPDATE CURRENT_TIMESTAMP attribute cannot be executed in parallel.

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

When you update the partitioning key of a partitioned table, data may be migrated from one partition to another. In Oracle-compatible 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;
OBE-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-compatible 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-compatible 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-compatible 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-compatible 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-compatible 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_DML statement rather than the SET _ENABLE_PARALLEL_DDL statement to enable parallel execution for the CREATE TABLE AS SELECT statement in MySQL-compatible 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.

Direct load 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.

Concurrent DDL

Comparison with parallel DDL

The parallel DDL feature uses multiple threads to execute a single DDL operation more efficiently, but different DDL operations are still executed in series. In other words, multiple threads can work in parallel within the same DDL operation, but different DDL statements cannot run simultaneously.

Unlike parallel DDL, concurrent DDL allows different DDL statements to run concurrently without affecting each other. This feature greatly increases the cluster utilization and the overall DDL execution efficiency.

By distinguishing between concurrent DDL and parallel DDL, we hope you can choose the appropriate strategy for optimizing DDL performance in specific scenarios.

Feature description

OceanBase Database introduced the concurrent DDL feature in V4.1.0. This feature is implemented by adding DDL threads for the RootService node. It splits a DDL operation into several steps and executes some of these steps concurrently. In this case, the RootService node can process multiple non-conflicting DDL requests, and the number of its new DDL threads is associated with the cluster scale. A larger cluster or tenant scale results in better DDL execution performance.

OceanBase Database supports concurrent execution for the following DDL statements:

• TRUNCATE TABLE: Supported in OceanBase Database V4.1.0 and later
• CREATE TABLE: Supported in OceanBase Database V4.2.1 and later

Considerations

The concurrent table creation feature is automatically enabled when a cluster is deployed with OceanBase Database V4.2.1 or later, or when it is upgraded from V4.1.0 to V4.2.1 or later.

You can execute the following SQL statement to query the version status of the current tenant:

SHOW PARAMETERS LIKE "compatible";

Take note of the following considerations when using the concurrent DDL feature:

• Mutual exclusion: Logically, concurrent DDL is mutually exclusive with serial DDL. To maximize the throughput of concurrent DDL, you need to ensure that no DDL operations are executed in series on the cluster during concurrent DDL execution.

• Node optimization: DDL requests are always processed on the RootService node. To reduce network overheads, RootService and the tenant leader should be deployed on the same node if possible.

• Resource consumption: Increasing the concurrency level of concurrent DDL operations, especially table creation operations, will raise system load and resource consumption.

• Requirements on threads: The sys tenant requires sufficient threads to process new DDL requests. To achieve the maximum throughput, the sys tenant requires at least an additional 6 CPU cores.

• User tenant resources: A user tenant also requires sufficient threads to process DDL requests and writes to internal tables. To achieve the optimal DDL performance, the user tenant requires at least an additional 14 CPU cores.

• Requirements on memory and storage: Concurrent DDL operations, particularly concurrent table creation operations, write a large amount of data to internal tables in a short period, requiring larger storage capacity and more memory resources. When the storage capacity is insufficient or the available memory is not adequate to support quick data persistence, the write speed may be limited. This directly lowers the efficiency of concurrent DDL execution and affects the processing of normal business requests.