Ustore SMP Parallel Scanning

Availability

This feature is available since MogDB 5.0.8.

Introduction

MogDB's SMP parallel technology utilizes the multi-core CPU architecture of computers to achieve multi-threaded parallel computing, fully leveraging CPU resources to enhance query performance. Previously, SMP parallel technology only supported the Astore storage engine. MogDB 5.0.8 introduces parallel capabilities for the Ustore engine.

Description

In complex query scenarios, the execution of a single query takes a long time, and the system concurrency is low. By implementing operator-level parallelism through SMP parallel execution technology, the query execution time can be effectively reduced, enhancing query performance and resource utilization.

This feature includes the following scenarios for SMP parallel capabilities in the Ustore engine:

1. Parallel Seq Scan

2. Parallel Index Scan

3. Parallel Index Only Scan

4. Parallel Bitmap Scan

Performance Comparison with Parallelism Enabled or Disabled

• Seq Scan

  

  With parallelism enabled, the performance of sequential queries increases with the degree of parallelism. The best performance is observed in Agg scenarios, where a 12 to 13 times faster query performance is achieved compared to serial execution at a parallel degree of 16.

• Index Scan

  

  With parallelism enabled, the performance of Index Scan queries improves with the degree of parallelism. The best performance is seen in Agg scenarios, where a 11 to 15 times faster query performance is achieved compared to serial execution at a parallel degree of 16.

• Index Only Scan

  

  With parallelism enabled, the performance of Index Only Scan queries improves with the degree of parallelism. The best performance is seen in Agg scenarios, where a 5 to 13 times faster query performance is achieved compared to serial execution at a parallel degree of 16. Moreover, the Ustore's Index Only Scan operator outperforms Astore in both serial and parallel scenarios.

• Bitmap Scan

  Bitmap scans perform well in scenarios with random reads and large volumes of data. At a parallel degree of 16, the BitmapScan operator sees a 70% to 100% performance improvement. However, the end-to-end performance improvement is not significant, especially when the data volume is small.

Constraints

• Cursors do not support parallel execution.
• Queries within stored procedures and functions do not support parallel execution.
• Subqueries (subplan) and initplan do not support parallelism, nor do operators containing subqueries.
• Queries with median operations do not support parallel execution.
• Queries involving global temporary tables do not support parallel execution.

Usage Guidance

Usage Restrictions

To leverage SMP for improved query performance, consider the following conditions:

• Resource Impact on SMP Performance

  Sufficient CPU, memory, I/O, and network bandwidth resources are required. The SMP architecture is a scheme that uses surplus resources to save time. After planning for parallelism, resource consumption will inevitably increase. When the aforementioned resources become bottlenecks, SMP cannot improve performance and may even lead to performance degradation. In cases of resource bottlenecks, it is recommended to disable SMP.

• Other Factors Impacting SMP Performance

  Parallelism performs poorly when there is severe data skew in the data. For example, if a join column in a table has a value with a data volume much larger than other values, enabling parallelism will cause a hash redistribution of the table data based on the join column's value. This can result in one parallel thread having much more data than other threads, leading to a long-tail problem and poor parallel performance.

  The SMP feature increases resource usage, and in high-concurrency scenarios, fewer resources are available. Therefore, in high-concurrency scenarios where SMP parallelism is enabled, especially when the amount of data to be processed is small, it can lead to severe resource competition among queries. Once resource competition occurs, whether it be CPU, I/O, or memory, it can lead to an overall performance decline. Thus, in high-concurrency scenarios, enabling SMP often fails to improve performance and may even cause performance degradation.

Configuration Steps

1. Observe the current system load. If system resources are abundant (resource utilization is less than 50%), proceed to step 2; otherwise, exit.

2. Configure: Enable parallelism by setting query_dop = ${thread_num};, default is 1.

3. After executing the query statement, set query_dop to 1.

   

   In this plan, parallelism of the Seq Scan operator is achieved, and a Local Gather data exchange operator is added. The "dop: 1/2" marked on the Local Gather operator indicates that the sender thread's parallel degree is 2, while the receiver thread's parallel degree is 1. That is, the lower-level Seq Scan operator executes with a parallel degree of 2, and the Streaming operator achieves data aggregation of parallel threads within the instance.

   With resource permission, the higher the degree of parallelism, the better the performance improvement effect. It's not always the case that higher parallelism is better; beyond a certain point, the performance improvement may not be significant.

Related Pages

Configuring Ustore, query_dop