The hybrid optimizer's automatic request distribution mechanism analyzes each query. It evaluates the execution cost on both row store and column store nodes and selects the optimal execution plan. This process reduces query execution costs and assigns each query to the most suitable processing node.
Problem description
The default automatic request distribution mechanism uses a policy based on the estimated execution cost. If the estimated cost of an SQL statement exceeds a preset threshold, the database proxy (PolarProxy) automatically forwards the request to a read-only column store node. Otherwise, the request is processed by a read-only row store node or the primary node. The main drawback of this approach is that it does not compare the actual execution costs in row store and column store environments. Instead, it relies on a fixed threshold, which often results in inaccurate request distribution.
Version requirements
Your PolarDB cluster must use one of the following kernel versions:
PolarDB for MySQL 8.0.1, with revision 8.0.1.1.43 or later.
PolarDB for MySQL 8.0.2, with revision 8.0.2.2.26 or later.
For more information, see View the kernel version of a cluster.
Notes
Ensure that row/column store automatic request distribution is enabled on the cluster endpoint.
How it works
In PolarDB for MySQL, there are three types of nodes:
Row store node: Executes row store queries using the InnoDB engine.
Column store node: Uses the IMCI engine to process column store queries.
PolarProxy node: Forwards client requests to row store or column store nodes.
When a query request is submitted from a client to the cluster, it is processed as follows:
The PolarProxy node receives the query request and first forwards it to a row store node for processing.
The hybrid optimizer within the row store node analyzes the query. It evaluates the execution cost in both row store and column store environments to determine the optimal execution path.
If the query is better suited for the row store node to handle, it is processed on the local row store node.
If the column store node is determined to be better, the row store node forwards the query request to a column store node for processing.
The key to this process is the routing decision made by the hybrid optimizer. To make this decision accurately, the optimizer uses two distinct cost models:
The row store cost model estimates the execution cost based on the MySQL execution plan.
The column store cost model estimates the execution cost based on the IMCI execution plan.
Both cost models use a unified, physically meaningful measure that represents the estimated running time. By comparing the cost estimates for the row store and column store, the hybrid optimizer selects the best execution plan for optimal query performance.
Quota request process
The hybrid optimizer feature is in phased release. To enable this feature, go to Quota Center, find the quota named "PolarDB MySQL Hybrid Optimizer Quota" using the quota ID
polardb_mysql_hybrid_optimizer, and click Request in the Actions column.The process to disable the feature is similar. To disable the hybrid optimizer, select Deactivate for the quota request and wait for the request to be approved.
Go to Quota Center. Enter the quota ID, search for the quota, and then click Apply.
In the quota request dialog box, select Valid. Then, specify the effective time, reason, and configure the notification result.
Click OK. After your request is approved, the hybrid optimizer feature is enabled.