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:

   openGauss=# SET query_dop = 4;
   openGauss=# SELECT COUNT(*) FROM t1 GROUP BY a;
   ......
   openGauss=# 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.