PolarDB for MySQL:Overview

Prerequisites

For information about how to view the cluster version, see Query the engine version.

Scenarios

The elastic parallel query feature is applicable 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. The diverse parallel methods make the feature suitable for multiple scenarios:

• Analytic queries on vast amounts of data

  If medium or large amounts of data is involved, SQL statements for analytic queries are often complex and time-consuming. You can enable the elastic parallel query feature to linearly reduce the response time.

• Imbalanced resource loads
  The load balancing ability of PolarProxy can ensure that similar numbers of connections are created for nodes in a cluster. However, due to computing complexity in queries and differences in resource usage, load balancing based connections cannot completely avoid load imbalance between nodes. Like other distributed databases, hotspot nodes have a negative impact on PolarDB:

  1. If a hotspot read-only node causes slow queries, the primary node may not purge undo logs and disk bloating may occur.
  2. If a hotspot read-only node causes slow redo apply operations, the primary node may not flush data and its write throughput is impaired.

  Multi-node elastic parallel query introduces global resource views and adaptive task scheduling based on views. Based on the resource usage and data affinity values of each node, some or all query tasks are scheduled to nodes with idle resources to ensure the degree of parallelism (DOP) and balanced resource usage within the cluster.

• Elastic computing

  Elasticity is one of the core capabilities of PolarDB. Automatic scaling provides elasticity that is very friendly to short queries. However, it was not applicable to complex analytic queries because a single query still cannot be accelerated by adding nodes in large query scenarios. On clusters with the elastic parallel query feature enabled, newly scaled out nodes are automatically added to the cluster to share computing resources and enhance elasticity.

• Combination of online and offline services

  The most effective isolation method is to route the online transaction and offline analytic services to different node sets. However, this method increases costs. In many cases, online transaction and offline analytic services have different peak hours. An economical method is to share cluster resources between online transaction and offline analytic services and route them to different cluster endpoints. After the elastic parallel query feature is enabled, idle resources are distributed to the offline analytic service during off-peak hours of the online transaction service to reduce costs and improve efficiency.

Usage

• Enable the elastic parallel query feature

  In the Cluster Endpoint section on the Overview page of the console, click Modify. On the Configure Nodes page, set DOP and parallel engine. For more information, see the Parallel Query parameter in Configure PolarProxy.

  We recommend that you use the following parallel query settings:

  • Multi-node elastic parallel query supports adaptive task scheduling based on costs and real-time cluster loads. We recommend that you enable multi-node elastic parallel query for better acceleration.
  • Increase the value of the max_parallel_degree parameter in small increments. We recommend that the value should not exceed a quarter of the total number of CPU cores. For example, after you set the max_parallel_degree parameter to 2, you can check the CPU utilization on the next day. If the CPU utilization is low, increase the value of the max_parallel_degree parameter. If the CPU utilization is high, do not increase the value.
  • DOP specifies the maximum number of worker threads that can run simultaneously in a single compute node for a single query. If you select multi-node elastic parallel query, the maximum number of worker threads that can run simultaneously in a single query is the product of degree of parallelism and the number of nodes.
  • When you enable the elastic 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.
  Note

  • If you do not enable the elastic parallel query feature in the console but set max_parallel_degree parameter to a value greater than 0, the elastic parallel query feature is enabled by default.
  • If you configure the settings in the console and for the max_parallel_degree parameter, the settings in the console prevail those for the parameter. Therefore, we recommend that you enable the elastic parallel query feature in the console.
• Disable the elastic parallel query feature

  In the Cluster Endpoint section on the Overview page of the console, click Modify. On the Configure Nodes page, you can disable the elastic parallel query feature. For more information, see Configure PolarProxy.

  Note After you disable the elastic parallel query feature in the console, you must confirm that the max_parallel_degree parameter is set 0 to ensure that the elastic parallel query feature is completely disabled.
• Use hints

  You can optimize an individual SQL statement by using hints. For example, if the elastic 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 the elastic parallel query feature.

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

  • records_threshold_for_parallelism

    If the number of scanned rows exceeds the value of records_threshold_for_parallelism, the optimizer enables the elastic parallel query feature. The default value is 10000. If the majority of your queries are simple and involve only a small volume of data, we recommend that you set the threshold value to at least 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 cost of sequential queries exceeds the value of the cost_threshold_for_parallelism parameter, the optimizer enables the elastic parallel query feature. The default value is 50000.

• Set thresholds for adaptive task scheduling of multi-node elastic parallel query
  PolarDB provides two thresholds to select multi-node elastic parallel query. If an SQL statement meets one of the following conditions, multi-node elastic parallel query can be used.

  • records_threshold_for_mpp

    If the number of scanned rows of a table involved in a query statement exceeds this threshold, the optimizer considers using multi-node elastic parallel query and schedules parallel tasks to multiple nodes for computing. The default value is N times the records_threshold_for_parallelism value. The N value is the number of nodes in the current cluster endpoint.

  • cost_threshold_for_mpp

    If the sequential execution cost of a query statement exceeds this threshold, the optimizer considers using multi-node elastic parallel query. The default value is N times the cost_threshold_for_parallelism value. The N value is the number of nodes in the current cluster endpoint.

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 PolarDB for MySQL 8.0 cluster. In the test, the PolarDB cluster has four nodes that use 32-core CPUs and 256-GB memory (Dedicated). For single-node elastic parallel query, the max_parallel_degree parameter is set to 32 and 0. Compare the performance data for sequential query, single-node elastic parallel query with DOP of 32, and multi-node elastic parallel query with DOP of 128 and four nodes. For more information, see Performance test results in parallel query scenarios.

The test results show 100% of the SQL queries in TPC-H are accelerated. The query speed is 17 times faster on average and 56 times faster at maximum.

When multi-node elastic parallel query is enabled, the query speed is 59 times faster on average and 159 times faster at maximum.

View elastic parallel query execution plans

For more information about how to execute the EXPLAIN statement to view elastic parallel query information in execution plans, see View elastic parallel query 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 side.

• Parallel joins on multiple tables

  If the elastic parallel query feature 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, and does not perform parallel scanning for any 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 side.

• Parallel sorting

  The PolarDB optimizer sends the ORDER BY operation to all workers for parallel sorting. 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 side.

• Parallel grouping

  The PolarDB optimizer sends the GROUP BY operation to all workers for parallel grouping. 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 side.

• Parallel aggregation
  If the elastic parallel query feature is enabled, the aggregate function is sent to all parallel workers for parallel aggregation. The optimizer determines whether to perform serial execution, one-phase aggregation, or two-phase aggregation based on the cost.

  • One-phase aggregation: The optimizer distributes the aggregation operation to workers. Each worker contains all data in the groups. Therefore, the second-phase aggregation computing is not required. Each worker directly computes the final aggregation results of the groups to prevent the leader thread from performing a second aggregation.
  • Two-phase aggregation: In the first phase, each worker involved in elastic parallel query performs the aggregation. In the second phase, the gather or gather merge node returns the results generated by each worker to the leader thread. Then, the leader thread aggregates the results from all workers to generate the final result.
  • Two-phase shuffle aggregation: In the first phase, each worker in the elastic parallel query performs the aggregation. In the second phase, the repartition node distributes the result generated by each worker to multiple workers by grouped columns. The workers complete the final aggregation computing in parallel. Finally, the aggregation results are summarized to the leader thread.

  The PolarDB optimizer determines the specific aggregation method based on the cost.
• Parallel window function

  The PolarDB optimizer performs computing and distributes window functions to workers for parallel execution based on the cost. Each worker computes some data. The distribution method is based on the key of the PARTITION BY clause in window functions. If window functions do not contain the PARTITION BY clause, serial computing is performed. However, if parallel computing can still be performed later, subsequent computing tasks are distributed to multiple workers for execution based on the cost to ensure maximum parallelization.

• Support for subqueries
  In an elastic parallel query, you can use one of the following policies to run subqueries:

  • 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 an elastic parallel query plan is generated, if the execution plan of the leader thread includes the subqueries that support parallel processing, these parallel subqueries cannot be 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 an elastic parallel query 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 an elastic parallel query plan is generated, if the execution plans of workers reference correlated subqueries, the workers run the correlated subqueries.