In some scenarios, the optimizer cannot accurately estimate the number of rows to be scanned in a table, the predicate selection rate, and the cost of an execution plan. To resolve this issue, PolarDB for MySQL provides the adaptive execution plan switching feature to automatically adjust execution plans based on the actual information. This topic describes the adaptive execution plan switching feature.
Background information
The optimizer estimates the cardinality and selection rate based on statistics and parts of the sampled data. The estimated results may deviate from the actual information due to the sampling precision and sampling method. Statistics are also not collected in various scenarios. In this case, the optimizer estimates the statistics based on empirical values or a mathematical assumption, which may cause large deviations between the estimated values and the actual values. In scenarios in which the estimated results of the optimizer are inconsistent with the actual values and improper execution plans are selected, the optimizer cannot easily pre-collect the required information.
If you use the In-Memory Column Index (IMCI) feature, some slow queries may be routed to row store nodes when the estimated execution costs of the slow queries on row store nodes are comparatively low. For more information, see IMCIs and HTAP-based request distribution among row store and column store nodes.
To resolve the issues, you can use one of the following solutions:
Reduce the cost threshold of queries routed to column store nodes. However, if you use this solution, a large number of short queries are routed to column store nodes. As a result, loads on column store nodes become high and other queries are blocked.
Use the statement outline feature to route queries that have fixed templates to column store nodes. For example, you can add the outline described in the following sample statement to forcibly route the queries to the column store nodes. For more information about the statement outline feature, see Statement outline. However, this solution causes a significant increase in O&M costs.
CALL dbms_outln.add_optimizer_outline("<Schema_name>", "/*+ SET_VAR(cost_threshold_for_imci=0) SET_VAR(imci_ap_threshold=0) */","<query>");
The adaptive execution plan switching feature provided by PolarDB for MySQL can automatically switch slow queries that are incorrectly routed to row store nodes to column store nodes for execution. This improves the execution efficiency.
How it works
Databases use the adaptive execution plan switching feature to add query blocks and the total number of rows to be scanned to monitoring information during the optimization phase. If SQL statements are not executed on column store nodes after cost calculation, the optimizer calculates the threshold that triggers adaptive switching. In the execution phase, if a query block or the total number of rows to be scanned triggers the calculated threshold, the executor determines whether to switch the statements to column store nodes for execution. Before switching the statements to column store nodes for execution, the optimizer ensures that the execution result sets of the statements on row store nodes are not returned to the client. After the statements are switched to column store nodes for execution, PolarDB clears the cached result set.
Prerequisites
Your cluster runs PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.39 or later.
Usage notes
Enable or disable the adaptive execution plan switching feature
Log on to the PolarDB console. On the Parameters page of the cluster that you want to manage, set the loose_adaptive_plans_switch parameter to 'imci_chosen=on' to enable the adaptive execution plan switching feature. For more information, see Configure cluster and node parameters. The following table describes the parameters that you can configure.
Parameter | Level | Description |
loose_adaptive_plans_switch | Global/Session | Specifies whether to enable the adaptive execution plan switching feature. Valid values:
|
loose_adaptive_plans_max_time | Global/Session | The maximum execution time of an SQL statement that can be routed by using the adaptive execution plan switching feature. If the execution time of an SQL statement exceeds the specified period, the execution plan of the statement is not switched even if the threshold for execution plan switching is reached. Valid values: 0 to 1800000. Default value: 500. Unit: milliseconds. |
View the number of times adaptive switching has occurred in a database
You can execute the following statement to view the number of times adaptive switching has occurred in a database:
SHOW GLOBAL STATUS LIKE 'Adaptive_plan_used';The following table describes the Adaptive_plan_used variable.
Variable | Level | Description |
Adaptive_plan_used | Global | The number of adaptive switching actions that have been performed from the time when the execution plan switching feature is enabled. |