Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Parallel Query Usage

This section explains how to use the parallel query optimization feature, including how to set system parameters and use the hint syntax. In addition, this document describes the performance improvement brought by this feature.

You can use the parallel query tuning feature in either of the following ways:

  • Method 1: Set system parameters.

    The global parameter force_parallel_execute is used to determine whether to enable parallel query. The global parameter parallel_default_dop is used to control the number of threads used for parallel query. You can modify those parameters at any time without restarting the database.

    For example, if you want to enable parallel execution and the number of parallel threads is 4, run the following commands:

    1
2
    		 
    force_parallel_execute=on;
parallel_default_dop=4;

    You can change the value of parallel_cost_threshold based on the actual requirements. If the value is set to 0, all queries will be executed in parallel. If this parameter is set to a non-zero value, parallel query is performed when the estimated cost is greater than the value.

    If the MySQL parallel query tuning feature does not take effect after being enabled, see Solution.

  • Method 2: Use the hint syntax.

    The hint syntax can be used to control whether a single statement is executed in parallel. If parallel execution is disabled by default, the hint syntax can be used to accelerate a specific SQL statement. The degree of parallelism specified in the hint cannot be greater than parallel_max_threads. Otherwise, parallel query of SQL statements cannot be enabled. You can also restrict certain types of SQL statements from being executed in parallel.

    • SELECT /*+ PQ */ … FROM …: Use the default 4 threads to perform parallel queries.
    • SELECT /*+ PQ(8) */ … FROM …: Use 8 threads to perform parallel queries.
    • SELECT /*+ NO_PQ */ … FROM …: Do not use parallel queries.

Perform a TPC-H test to obtain the performance improvement data after the MySQL parallel query tuning feature is used. For details about the test, see TPC-H Test Guide (for MySQL).

According to the test data, the parallelism degree is improved after MySQL parallel query tuning is enabled, and the query performance is doubled. (The performance improvement is subject to the parallelism degree).