The SMP architecture uses abundant resources to obtain time. After the plan parallelism is executed, the resource consumption is added, including the CPU, memory, I/O, and network bandwidth 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. Adaptive SMP is provided to dynamically select the optimal parallel degree for each query based on the resource usage and query requirements. The following information describes the situations that the SMP affects theses 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.
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 degree of parallelism 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.
To execute a query in parallel, data exchange operators are added. Local Stream operators exchange data between threads within a DN. Data is exchanged in memory and network performance is not affected. Non-Local operators exchange data over the network and increase network load. If the capacity of a network resource becomes a bottleneck, parallelism may also increase the network load.
A parallel scan increases I/O resource consumption. It can improve performance only when I/O resources are sufficient.