Parallel query

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

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.