PolarDB：Automatic request distribution among row store and column store nodes

Automatic distribution solution

The database proxy of PolarDB for MySQL determines whether to distribute an SQL request to a column store node based on whether the estimated number of rows scanned by the request exceeds the specified threshold. This can fully leverage the performance of the row store and column store nodes.

Limits

Your cluster contains at least one read-only column store node and one row store node.

Step 1: Enable automatic request distribution among column store and row store nodes

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region where the cluster is deployed.

3. Find the cluster and click its ID.

4. In the Enterprise Edition section of the Basic Information page, find the cluster endpoint and click Modify on the right side of the cluster endpoint.

5. Select the appropriate Read/Write.

   • The read/write mode of the cluster endpoint is set to Read/Write (Automatic Read/Write Splitting).

   • The read/write mode of the cluster endpoint is set to Read-only, and the load balancing policy is set to Active requests-based load balancing.

6. In the Node Settings section, select the primary node and read-only row store and column store nodes that are used to process requests. In the HTAP Optimization section, enable Transactional/Analytical Processing Splitting. Click OK.

   Note

   You must select at least one read-only column store node in Node Settings.

   Example 1: In the following figure, one primary node, one read-only row store node, and two read-only column store nodes are selected. In this case, the system distributes queries based on the following policy:

   • Write requests are distributed to the primary node.

   • OLAP read requests are distributed to the read-only column store nodes.

   • OLTP read requests are distributed to the read-only row store node. When Primary Node Accepts Read Requests in the SLB Settings section is set to Yes, OLTP read requests may also be distributed to the primary node.

   

   Example 2: In the following figure, one primary node and one read-only column store node are selected. When the automatic request distribution feature is enabled, write requests and OLTP read requests are distributed to the primary node, and OLAP read requests are distributed to the read-only column store node.

   

   Note

   In Read/Write (Automatic Read/Write Splitting) mode, all write requests are distributed only to the primary node, regardless of whether the primary node is selected in the Node Settings section.

Step 2: Specify the thresholds for automatic request distribution

After you enable automatic request distribution, you must specify the threshold for the number of scanned rows for an SQL statement. After the threshold is specified, the database proxy determines to which nodes requests are distributed based on this threshold. When the estimated number of rows scanned by an SQL request exceeds the threshold, the request is distributed to a column store node. When the estimated number of rows scanned by an SQL request does not exceed the threshold, the request is distributed to a row store node or the primary node.

The parameters described in the following table determine the thresholds related to the execution costs. On the Parameters page of the cluster, you can modify the values of the parameters.

You can execute the SHOW STATUS LIKE 'Last_query_cost statement to query the estimated execution cost of the last SQL statement and use the queried value to modify the parameter values.

/*ROUTE_TO_LAST_USED*/SHOW STATUS LIKE 'Last_query_cost';

Example:

SHOW STATUS LIKE 'Last_query_cost';

The following result is returned:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Last_query_cost      | 2     |
+----------------------+-------+
1 row in set (0.01 sec)

The query result indicates that the execution cost of the SQL statement is 2.

To distribute the SQL statement to a column store node and execute it by using a column store execution plan, make the following configurations:

• PolarDB for MySQL 8.0.1.1.38 and 8.0.2.2.22 or earlier

  Set the loose_imci_ap_threshold and loose_cost_threshold_for_imci parameters to 1.

• PolarDB for MySQL 8.0.1.x versions that are equal to or later than 8.0.1.1.39 and 8.0.2.x versions that are equal to or later than 8.0.2.2.23.

  Set the loose_cost_threshold_for_imci parameter to 1.

Use hints to forcibly execute a request by using a column store execution plan or a row store execution plan

If the outcome of automatic request distribution does not meet your expectations, you can use hints to forcibly execute a request by using a column store execution plan or a row store execution plan.

• Forcibly execute a statement by using a column store execution plan

  • PolarDB for MySQL 8.0.1.1.38 and 8.0.2.2.22 or earlier

    If you want to forcibly distribute a request to a column store node regardless of the loose_imci_ap_threshold value, you can add the /* FORCE_IMCI_NODES */ hint before the SQL keyword. Sample statement:

    /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

    If you want to forcibly execute a statement by using a column store execution plan regardless of the value of the loose_cost_threshold_for_imci parameter, you can change the threshold value to 0 by adding the /*+SET_VAR(cost_threshold_for_imci=0)*/ hint to the statement. Sample statement:

    /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

  • PolarDB MySQL 8.0.1.x versions that are equal to or later than 8.0.1.1.39 and 8.0.2.x versions that are equal to or later than 8.0.2.2.23.

    Use hints to set the loose_cost_threshold_for_imci parameter to 0.

    EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

  Note

  When you use /*+SET_VAR()*/ to change the threshold value, you must omit the loose_ prefix of the parameter name. Otherwise, the hint does not take effect.

• Forcibly execute a statement by using a row store execution plan

  If you want to forcibly execute a statement by using a row store execution plan, you can add the /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ hint to the statement. Sample statement:

  EXPLAIN SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;