PolarDB for MySQL 8.0 launches a parallel query framework. When the amount of queried data reaches a specific threshold, the parallel query framework is automatically enabled. This helps to exponentially reduce the time that is required to run the query.

At the storage layer, data is split into different threads. Multiple threads perform parallel computing and return the results to the leader thread. Finally, the leader thread performs simple merging of the results and returns the final result to the user. This helps improve the speed and accuracy of queries.

Parallel query is achieved using the parallel processing capability of multi-core CPUs. The following figure shows how parallel processing works on a node that has an 8-core CPU and 32 GB of memory:

示意图

Scenarios

Parallel query is applicable to most SELECT statements, such as queries on large tables, multi-table queries that use JOINs, and queries that require a large amount of computing. The effect of parallel query is not significant for short queries.

  • Lightweight analysis businesses

    Report queries are usually complex and time-consuming. Enabling parallel query can accelerate a single query.

  • More available system resources

    Parallel queries consume more system resources. Only when the system has more CPU resources, low I/O load, and sufficient memory.

Performance advantages

For more information, see Examples of parallel query.

Methods of using parallel query

  • Use system parameters to control parallel query

    PolarDB uses the global parameter max_parallel_degree to control the maximum number of threads that can be used for the parallel processing of each SQL statement. The default value is 4. You can modify the parameter value at any time during use (see Set cluster parameters) without the need to restart the database.

    In addition to changing the degree of parallelism (DOP) in the cluster level by modifying global parameters, you can also adjust the DOP for SQL queries in a specific session. For example, if you add a session-level environment variable to the setting of the JDBC connection string, you can set the DOP for a specific application.
    set max_parallel_degree = n 
  • Use hints
    Use a hint to enable parallel query (n represents the highest DOP, that is, the maximum number of workers):
     SELECT /*+ SET_VAR(max_parallel_degree=n) */  *  FROM ...
    Note To achieve optimal performance, parallel query does not take effect on all SQL statements. The PolarDB optimizer can generate an efficient query plan based on the specific SQL statements.
  • Force the optimizer to select parallel execution
    The PolarDB optimizer may not run queries in parallel. However, if you want the optimizer to ignore the cost and instead select parallel scheduling for most cases, you can configure the following parameters:
    set force_parallel_mode = on
    Note This is a debugging parameter. We recommend that you do not use it in production environments. Due to the limits of parallel query, in some cases, the optimizer may not run queries in parallel even if this parameter is set.

Parameters and variables

Parameter Level Description
max_parallel_degree Session and global

The highest DOP for a single query, that is, the maximum number of workers used to run a query in parallel.

  • Valid values: 0 to 1024. A value of 0 indicates that parallel computing is disabled.
  • Default value: 0
Note The PolarDB optimizer may run the main query and its subqueries in parallel. If the queries are run in parallel, the maximum number of workers cannot exceed the value of max_parallel_degree. The total number of workers is the sum of the number of workers used by the main query and those used by subqueries.
force_parallel_mode Session

Specifies whether to force the PolarDB optimizer to ignore the cost and use parallel query for most cases.

  • Valid values: ON and OFF
  • Default value: OFF
Notice This is a debugging parameter. After you set the value to ON, the PolarDB optimizer may run queries in parallel for most cases. However, it cannot be guaranteed that the optimizer will always run queries in parallel.
Parallel_workers_created Session and global The number of parallel workers that are generated since the start of the session.
Gather_records Session and global The total number of records that are gathered.
PQ_refused_over_memory_soft_limit Session and global The number of queries that are not run in parallel due to memory limitations.
PQ_refused_over_total_workers Session and global The number of queries that are not run in parallel due to the limit on the total number of workers.
Total_used_query_memory Global The amount of memory (virtual memory) used for the query.
Total_running_parallel_workers Global The number of parallel workers that are running.

Parallel query plans

The following section describes specific parallel query plans displayed in the EXPLAIN output.

  • Parallel scans

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

  • Parallel joins on multiple tables

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

  • Parallel sorting

    The PolarDB optimizer divides the ORDER BY operation among workers for parallel processing. Each worker produces a partial result. The leader 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 divides the GROUP BY operation among workers for parallel processing. Each worker is responsible for the GROUP BY operation on a portion of the data. Each worker produces a partial result of GROUP BY. The leader thread gathers the results from all workers by using the Gather node. Based on the query plan, the PolarDB optimizer determines whether to also perform a GROUP BY operation in the leader. For example, if Loose Index Scan is used to run a GROUP BY query, the leader does not perform a GROUP BY operation. If Loose Index Scan is not used, the leader performs a GROUP BY operation and returns the final result to the client.

  • Parallel aggregation

    The execution of the aggregate function is divided among the parallel workers. PolarDB supports parallel aggregation in two stages. First, each worker that participates in the parallel portion of the query performs an aggregation step. Second, the leader gathers results from all workers by using the Gather or Gather Merge node. Finally, the leader re-aggregates the results from all workers to produce the final result.

Example of a parallel query plan

The following example uses the pq_test table to test parallel query.

The table structure is as follows:

mysql> SHOW CREATE TABLE pq_test\G
*************************** 1. row ***************************
       Table: pq_test
Create Table: CREATE TABLE `pq_test` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `help_topic_id` INT(10) UNSIGNED NOT NULL,
  `name` CHAR(64) NOT NULL,
  `help_category_id` SMALLINT(5) UNSIGNED NOT NULL,
  `description` TEXT NOT NULL,
  `example` TEXT NOT NULL,
  `url` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21495809 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The following shows the size of the table.

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: pq_test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 20064988
 Avg_row_length: 1898
    Data_length: 38085328896
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 21495809
    Create_time: 2019-07-30 01:35:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

SQL statement:

SELECT COUNT(*) FROM pq_test;
  • EXPLAIN displays the following output when parallel query is not used:
    mysql> SET max_parallel_degree=0; EXPLAIN SELECT COUNT(*) FROM pq_test\G
    Query OK, 0 rows affected (0.02 sec)
    *************************** 1. row ***************************
               Id: 1
      Select_type: SIMPLE
            Table: pq_test
      Partitions: NULL
             Type: index
    Possible_keys: NULL
              Key: PRIMARY
          Key_len: 8
              Ref: NULL
             Rows: 20064988
         Filtered: 100.00
            Extra: Using index
    1 row in set, 1 warning (0.03 sec)
  • EXPLAIN displays the following output when parallel query is in use:
    mysql> EXPLAIN SELECT COUNT(*) FROM pq_test\G
    *************************** 1. row ***************************
               Id: 1
      Select_type: SIMPLE
            Table: <gather2>
       Partitions: NULL
             Type: ALL
    Possible_keys: NULL
              Key: NULL
          Key_len: NULL
              Ref: NULL
             Rows: 20064988
         Filtered: 100.00
            Extra: NULL
    *************************** 2. row ***************************
               Id: 2
      Select_type: SIMPLE
            Table: pq_test
       Partitions: NULL
             Type: index
    Possible_keys: NULL
              Key: PRIMARY
          Key_len: 8
              Ref: NULL
             Rows: 10032494
         Filtered: 100.00
            Extra: Parallel scan (2 workers); Using index
    2 rows in set, 1 warning (0.00 sec)

As shown in the EXPLAIN output, the parallel plan includes a Gather operation. Gather is implemented to gather the partial results produced by all workers. In addition, information in the Extra field shows that a parallel scan is performed on the pq_test table. The plan is expected to run a simultaneous scan by using two workers.