Parallel query

Prerequisites

For more information about how to view the cluster version, see Query the kernel version number.

Scenarios

Parallel query applies to most SELECT statements, such as queries on large tables, multi-table queries that use JOIN statements, and queries on a large amount of data. This feature does not benefit extremely short queries.

• Lightweight analysis

  Report queries are complex and time-consuming. The parallel query feature can accelerate individual queries.

• More available system resources

  More system resources are required if the parallel query feature is enabled. You can use the parallel query feature to improve resource usage and query efficiency only if your system has sufficient CPU resources, low I/O loads, and sufficient memory.

• Combination of online queries and offline queries

  You can use different endpoints to connect services to different database nodes. This ensures that the services do not affect each other. If you enable the parallel query feature for the endpoint of a single-node cluster, the read-only node that corresponds to this endpoint is accessed by using the endpoint that is used for online analytical processing (OLAP).

  

Use the parallel query feature

• Use system parameters

  PolarDB allows you to use the global parameter max_parallel_degree to specify the maximum number of threads that each SQL statement can use for parallel processing. The default value is 0. You can modify the parameter during parallel processing. After you modify the parameter, you do not need to restart the database. For more information, see Specify cluster parameters.

  We recommend that you configure the following parallel query settings:

  • Increase the value of the max_parallel_degree parameter in small increments. We recommend that you specify a value that does not exceed a quarter of the quantity of CPU cores. Enable the parallel query feature only if your cluster has at least eight CPU cores. Do not enable the parallel query feature for clusters with lower specifications. Before you use the parallel query feature, set the max_parallel_degree parameter to 2. After one day, check the CPU utilization. If the CPU utilization is low, increase the value. If the CPU utilization is high, do not increase the value.
  • To disable the parallel query feature, set the max_parallel_degree parameter to 0. If the max_parallel_degree parameter is set to 1, the parallel query feature is enabled and the DOP is 1.
  • In the PolarDB console, the max_parallel_degree parameter is prefixed with loose. This means that the parameter name is loose_max_parallel_degree. This way, the max_parallel_degree parameter can be supported by programs of other versions and is consistent with the default settings in the MySQL configuration file. This also ensures that no error is returned when programs of other versions use the parameter. For more information, see Program Option Modifiers.
  • When you enable the parallel query feature, set the innodb_adaptive_hash_index parameter to OFF. If the innodb_adaptive_hash_index parameter is set to ON, the performance of parallel query is degraded.
  You can modify the global parameter to adjust the DOP at the cluster level. You can also adjust the DOP for SQL statements in a specific session. For example, if you add a session-level environment variable to the setting of the Java Database Connectivity (JDBC) connection string, you can set the DOP for a specific application.

  set max_parallel_degree = n 

• Use hints

  You can optimize an individual statement by using hints. For example, if the parallel query feature is disabled by the system, you can use hints to accelerate a slow SQL query that is frequently executed. For more information, see Parallel hints.

• Use a threshold value to specify whether the optimizer can enable parallel query

  PolarDB provides two threshold values to specify whether the optimizer can enable parallel query. Parallel query is enabled by the optimizer if SQL statements meet one of the requirements that are specified by the thresholds.

  • records_threshold_for_parallelism

    If the number of scanned rows in the statement exceeds the value of records_threshold_for_parallelism, the optimizer enables parallel query. The default value is 10000. If your services require a small volume of data or low concurrency of complex queries, we recommend that you set the threshold value to a value greater than or equal to 2000.

    Note The number of scanned rows is estimated based on the statistics of tables. This number can be different from the actual number of rows.
  • cost_threshold_for_parallelism

    If the overhead of sequential queries exceeds the value of the cost_threshold_for_parallelism parameter, the optimizer enables parallel query. The default value is 50000.

Parameters and variables

Performance metrics

The following tests use 100 GB of data that is generated based on TPC Benchmark™H (TPC-H) to test the performance of a the ApsaraDB PolarDB MySQL-compatible edition 8.0 cluster. In the test, the PolarDB cluster has a 32-core CPU and 256 GB memory (Dedicated). The max_parallel_degree parameter is set to 32 or 0. For more information, see Parallel query performance in OLAP scenarios.

The test result shows that 95% of the SQL queries in TPC-H are accelerated. For 70% of the accelerated SQL queries, the query speed is more than eight times the speed of SQL queries that are not executed in parallel.

View parallel execution plans

For more information about how to execute the EXPLAIN statement to view parallel query information in execution plans, see View parallel execution plans.

Terms

• Parallel scans

  In a parallel scan, workers scan the data of a table in parallel. Each worker produces a partial result and returns the partial result to the leader thread. The leader thread gathers the results by using the Gather node and returns the final result to the client.

• Parallel joins on multiple tables

  If parallel query is enabled, the complete multi-table join operation is sent to workers for parallel processing. The PolarDB optimizer selects the optimal table for parallel scanning. Non-parallel scanning is performed on all other tables. Each worker produces a partial result and returns the partial result to a leader thread. The leader thread gathers the results by using the Gather node and returns the final result to the client.

• Parallel sorting

  The PolarDB optimizer sends the ORDER BY operation to all workers for parallel processing. Each worker produces a partial result. The leader thread gathers, merges, and sorts all partial results by using the Gather Merge node, and returns the final sorting result to the client.

• Parallel grouping

  The PolarDB optimizer sends the GROUP BY operation to all workers for parallel processing. Each worker performs the GROUP BY operation on a portion of data. Each worker produces a partial result of GROUP BY. The leader thread gathers the results from all workers by using the Gather node. The PolarDB optimizer checks whether to perform a GROUP BY operation in the leader thread again based on the query plan. For example, if a loose index scan is used to execute a GROUP BY statement, the leader thread does not perform a GROUP BY operation. If the loose index scan is not used, the leader thread performs a GROUP BY operation and returns the final result to the client.

• Parallel aggregation

  The execution of the aggregate function is sent to all parallel workers. PolarDB supports parallel aggregation in two stages. In the first stage, each worker in parallel query aggregates data. In the second stage, the leader thread gathers results from all workers by using the Gather or Gather Merge node. Then, the leader thread aggregates the results from all workers to generate the final result.

• Support for subqueries
  In a parallel query, subqueries can be run by using one of the following policies:

  • Sequential execution in the leader thread

    If subqueries do not support parallel processing, the subqueries in the leader thread are performed in sequence. For example, if two tables are joined by using a JOIN clause that references user-defined functions (UDFs), the subqueries are performed in sequence.

  • Parallel execution in the leader thread (Another group of workers is used)

    After a parallel plan is generated, the execution plan of the leader thread includes the subqueries that support parallel processing. These parallel subqueries cannot run in advance or run in parallel based on the shared access policy. For example, if the subqueries include window functions, the subqueries cannot run in parallel based on the shared access policy.

  • Shared access

    After a parallel plan is generated, if the execution plans of workers reference the subqueries that support parallel processing, the PolarDB optimizer runs the parallel subqueries in advance. This way, the workers can directly access the results of the subqueries.

  • Pushed down

    After a parallel plan is generated, if the execution plans of workers reference correlated subqueries, the workers run the correlated subqueries.