SMP Parallel Execution

Application Scenarios and Constraints for SMP

Applicable Scenarios

• Operators supporting parallel processing are used.

  The execution plan contains the following operators:

  1. Scan: Parallel scanning for all types of external tables is supported.
  2. Join: HashJoin, NestLoop
  3. Agg: HashAgg, SortAgg, PlainAgg, and WindowAgg, which supports only partition by, and does not support order by.
  4. Stream: Redistribute, Broadcast
  5. Other: Result, Subqueryscan, Unique, Material, Setop, Append, VectoRow, RowToVec
• SMP-unique operators are used.

  To execute queries in parallel, Stream operators are added for data exchange for the SMP feature. These new operators can be considered as the subtypes of Stream operators.

  1. Local Gather aggregates data of parallel threads within a DN
  2. Local Redistribute redistributes data based on the distributed key across threads within a DN
  3. Local Broadcast broadcasts data to each thread within a DN.
  4. Local RoundRobin distributes data in polling mode across threads within a DN.
  5. Split Redistribute redistributes data across parallel threads on different DNs.
  6. Split Broadcast broadcasts data to all parallel DN threads in the cluster.

  Among these operators, Local operators exchange data between parallel threads within a DN, and non-Local operators exchange data across DNs.

• Example

  The TPCH Q1 parallel plan is used as an example.

  

  In this plan, implement the Foreign Scan and HashAgg operator parallel, and adds the Split Redistribute data exchange operator.

  Operator 5 is the Split Redistribute operator. dop: 1/4 indicates that the DOP of the sender and receiver threads of Split Redistribute is 4 and 1, respectively. That is, the lower-layer Hash Aggregate operator 6 is executed based on the DOP 4. The upper-layer operators 1 to 4 are executed in serial mode.

  You can view the parallelism situation of each operator in the dop information.

Non-Applicable Scenarios

Impact of Resource Availability on SMP Performance

The SMP architecture accelerates queries at the cost of additional resources. After the plan parallelism is executed, more resources are consumed, including the CPU, memory, I/O, and network bandwidth. As the DOP grows, the resource consumption also increases. If these resources become a bottleneck, SMP cannot improve performance. On the contrary, it may do exactly the opposite. Adaptive SMP is provided to dynamically select the optimal DOP for each query based on the resource usage and query requirements. The following information describes the situations that the SMP affects theses resources:

• CPU resources

  In a general customer scenario, the system CPU usage rate is not high. Using the SMP parallelism architecture will fully use the CPU resource to improve the system performance. If the number of CPU kernels of the database server is too small and the CPU usage is already high, enabling the SMP parallelism may deteriorate the system performance due to resource compete between multiple threads.

• Memory resources

  The query parallel causes memory usage growth, but the memory upper limit used by each operator is still restricted by work_mem. Assume that work_mem is 4 GB, and the DOP is 2, then the memory upper limit of each concurrent thread is 2 GB. When work_mem is small or the system memory is sufficient, running SMP parallelism may push data down to disks. As a result, the query performance deteriorates.

• Network bandwidth resources

  To execute queries in parallel, data exchange operators are added. Local stream operators exchange data between threads within a DN. Data is exchanged in memory, so it does not impact network performance. Non-local operators exchange data over the network and increase the network load. If the capacity of a network resource has already become a bottleneck, parallelism may hurt performance.

• I/O resources

  A parallel scan increases I/O resource consumption. It can improve performance only when I/O resources are sufficient.

Other Factors Impacting SMP Performance

Besides the resource factor, other factors may also impact SMP performance, such as uneven data distribution across tables and the degree of system parallelism.

• Impact of data skew on SMP performance

  Serious data skew deteriorates parallel execution performance. For example, if the data volume of a value in the join column is much more than that of other values, the data volume of a parallel thread will be much more than that of others after Hash-based data redistribution, resulting in the long-tail issue and poor parallelism performance.

• Impact of system DOP on SMP performance

  The SMP feature uses more resources, and unused resources are decreasing in a high concurrency scenario. Therefore, enabling the SMP parallelism will result in serious resource compete among queries. Once resource competes occur, no matter the CPU, I/O, memory, or network resources, all of them will result in entire performance deterioration. In the high concurrency scenario, enabling the SMP will not improve the performance effect and even may cause performance deterioration.

Suggestions for SMP Parameter Settings

To enable the SMP adaptation function, set query_dop to 0.

SMP Configuration Procedure

The CPU, memory, I/O, and network bandwidth resources are sufficient. In essence, SMP is a method that trades resources for time. After the plan parallelism is executed, resource consumption increases. When these resources become a bottleneck, SMP may deteriorate, rather than improve performance. In addition, it takes a longer time to generate SMP plans than serial plans. Therefore, in TP services that mainly involve short queries or in case resources are insufficient, you are advised to disable SMP by setting query_dop to 1.

Procedure:

1. Observe the current system load situation. If the resource is sufficient (the resource usage ratio is smaller than 50%), perform step 2. Otherwise, exit this system.
2. Set query_dop to 1 (default value). Use explain to generate an execution plan and check whether the plan can be used in scenarios described in Application Scenarios and Constraints for SMP. If the plan can be used, go to the next step.
3. Set query_dop=–value. The value range of the DOP is [1, value].
4. Set query_dop=value. The DOP is 1 or value.
5. Before the query statement is executed, set query_dop to an appropriate value. After the statement is executed, set query_dop to off. For example:

   1 2 3 4

   SET query_dop = 0; SELECT COUNT(*) FROM t1 GROUP BY a; ...... SET query_dop = 1;

   

   • If resources are sufficient, the higher the DOP, the better the performance improvement result.
   • The SMP DOP supports a session level setting and you are advised to enable SMP before executing queries that meet the requirements. After the execution is complete, disable SMP. Otherwise, SMP may affect services during peak hours.
   • SMP adaptability (query_dop ≤ 0) depends on resource management. If resource management is disabled (use_workload_manager is turned off), only plans with DOP of only 1 or 2 will be generated.