Configuring SMP

This section describes the usage restrictions, application scenarios, and configuration guide of symmetric multiprocessing (SMP).

SMP Application Scenarios and Restrictions

Context

The SMP feature improves the performance through operator parallelism and occupies more system resources, including CPU, memory, and I/O. Actually, SMP is a method consuming resources to save time. It improves system performance in appropriate scenarios and when resources are sufficient, but may deteriorate performance otherwise. SMP applies to analytical query scenarios where a single query takes a long time and the service concurrency is low. The SMP parallel technology can reduce the query delay and improve the system throughput performance. However, in a high transactional concurrency scenario, a single query has a short delay. In this case, using a multi-thread parallel technology increases the query delay and reduces the system throughput performance.

Application Scenarios

• Operators that support parallelism. The plan contains the following operators that support parallelism.

  • Scan: Row-store ordinary tables and row-store partitioned tables, and column-store ordinary tables and column-store partitioned tables can be sequentially scanned.
  • Join: HashJoin and NestLoop
  • Agg: HashAgg, SortAgg, PlainAgg, and WindowAgg (which supports only partition by, and does not support order by)
  • Stream: Local Redistribute and Local Broadcast
  • Others: Result, Subqueryscan, Unique, Material, Setop, Append, VectoRow, and RowToVec

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

  • Local Gather aggregates data of parallel threads within an instance.
  • Local Redistribute redistributes data based on the distributed key across threads within an instance.
  • Local Broadcast broadcasts data to each thread within an instance.
  • Local RoundRobin distributes data in polling mode across threads within an instance.

• The following uses the TPCH Q1 parallel plan as an example.

  

  In this plan, the Scan and HashAgg operators are processed in parallel, and the Local Gather operator is added for data exchange. Operator 3 is a Local Gather operator. "dop: 1⁄4" indicates that the degree of parallelism of the sender thread is 4 and the degree of parallelism of the receiver thread is 1. That is, the lower-layer HashAggregate operator 4 is executed based on the degree of parallelism 4, the upper-layer operators 1 and 2 are executed in serial mode, and operator 3 aggregates data of parallel threads within the instance.

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

Non-applicable Scenarios

1. Index scanning cannot be executed in parallel.
2. MergeJoin cannot be executed in parallel.
3. WindowAgg order by cannot be executed in parallel.
4. The cursor cannot be executed in parallel.
5. Queries in stored procedures and functions cannot be executed in parallel.
6. Subplans and initplans cannot be queried in parallel, and operators that contain subqueries cannot be executed in parallel, either.
7. Query statements that contain the median operation cannot be executed in parallel.
8. Queries with global temporary tables cannot be executed in parallel.
9. Updating materialized views cannot be executed in parallel.

Resource Impact on SMP Performance

The SMP architecture uses abundant resources to obtain time. After the plan parallelism is executed, the resource consumption is added, including the CPU, memory, and I/O resources. As the parallelism degree is expanded, the resource consumption increases. If these resources become a bottleneck, the SMP cannot improve the performance and the overall cluster performance may be deteriorated. The following information describes the situations that the SMP affects theses resources:

• CPU resources

  In a general customer scenario, the system CPU usage is not high. Using the SMP parallelism architecture will fully use the CPU resources 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 competition between multiple threads.

• Memory resources

  Query parallel causes memory usage growth, but the memory usage of each operator is still restricted by work_mem and other parameters. Assuming that work_mem is 4 GB and the degree of parallelism is 2, the memory usage of each thread in parallel is limited to 2 GB. When work_mem is small or the system memory is not sufficient, using SMP may flush data to disks. As a result, the query performance deteriorates.

• I/O resources

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

Other Factors Affecting SMP Performance

Besides resource factors, there are other factors that impact the SMP performance, such as uneven data distribution in a partitioned table and system parallelism degree.

• Impact of data skew on SMP performance

  Severe data skew deteriorates SMP 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 SMP performance.

• Impact of system parallelism degree on the SMP performance

  The SMP feature uses more resources, and remaining resources are insufficient in a high concurrency scenario. Therefore, enabling the SMP function will result in severe resource competition among queries. Once resource competition occurs, no matter the CPU, I/O, or memory resources, all of them will result in entire performance deterioration. In the high concurrency scenario, enabling the SMP function will not improve the performance and even may cause performance deterioration.

Suggestions for Using SMP

Limitations

To use the SMP feature to improve the performance, ensure that the following conditions are met:

The CPU, memory, and I/O resources are sufficient. SMP is a solution that uses abundant resources to exchange time. After plan parallel is executed, resource consumption is increased. When these resources become a bottleneck, the SMP feature cannot improve the performance and even may deteriorate the performance. In the case of a resource bottleneck, you are advised to disable the SMP feature.

Procedure

1. Observe the current system load situation. If resources are sufficient (the resource usage 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 in SMP Application Scenarios and Restrictions. If yes, go to step 3.

3. Set query_dop to value. The parallelism degree is 1 or value regardless of the resource usage and plan characteristics.

4. Before the query statement is executed, set query_dop to an appropriate value. After the statement is executed, set query_dop to disable the query. The following provides an example:

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

   NOTE:

   • If resources are sufficient, the higher the parallelism degree is, the better the performance improvement effect is.
   • The SMP parallelism degree supports session level settings. You are advised to enable the SMP feature before executing a query that meets the requirements. After the execution is complete, disable the SMP feature. Otherwise, SMP may affect services in peak hours.