PolarDB:Configure automatic request distribution

How it works

The database proxy in PolarDB for MySQL uses the estimated query cost of an SQL statement to decide its route. By comparing this cost against a configurable threshold, the proxy routes the request to a row store or a column store node, optimizing performance for both.

Limitations

The service nodes must include at least one read-only column store node and one row store node (either the primary node or a read-only row store node).

Step 1: Enable automatic request distribution

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters, select the region where your cluster is deployed, and then click the cluster ID.

2. On the Basic Information page, in the PolarProxy section, click Modify next to the target cluster endpoint.

3. Select a Read/Write based on your requirements.

   • 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.

4. In the Node Settings settings, select the primary node and the read-only row and column store nodes. In the HTAP Optimization section on the right, enable Transactional/Analytical Processing Splitting. Click OK.

   Note

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

   • After you enable Transactional/Analytical Processing Splitting, you must also create an IMCI for the feature to take effect.

   • After you enable Transactional/Analytical Processing Splitting, the change applies only to new connections. Existing connections are not affected; to apply the change, you must disconnect and then reconnect.

   Example 1: In addition to the primary node, the service nodes include one read-only row store node and two read-only column store nodes. If you enable automatic request distribution:

   • Write requests are routed to the primary node.

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

   • OLTP read requests are routed to the read-only row store node. If Primary Node Accepts Read Requests is set to Yes in the Load Balancing Settings, the proxy may also route requests to the primary node.

   Example 2: The service nodes include the primary node and one read-only column store node. If you enable automatic request distribution, write requests and OLTP read requests are routed to the primary node, and OLAP read requests are routed to the read-only column store node.

   Note

   In Read/Write (Automatic Read/Write Splitting) mode, the proxy sends all write requests to the primary node, regardless of whether the primary node is selected as a service node.

Step 2: Configure the distribution threshold

After enabling automatic request distribution, you must set the query cost threshold. The database proxy uses this threshold to route requests. If a request's query cost exceeds the threshold, the proxy routes it to a column store node. Otherwise, the proxy routes it to a row store node.

The query cost threshold is controlled by the parameters in the following table. You can modify these values on the Parameters page of your cluster to fine-tune automatic request distribution.

You can run the SHOW STATUS LIKE 'Last_query_cost_for_imci' command to check the query cost of the last SQL statement. This information helps you adjust the parameter values.

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

For example, run the following statement to view the query cost of the previous SQL statement:

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

The output is as follows:

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

The query cost of the SQL statement is 2.

To route this SQL query to a column store node for column-store execution, set the parameter values as follows:

• For PolarDB for MySQL versions 8.0.1.1.38, 8.0.2.2.22, and earlier

  Set both the loose_imci_ap_threshold and loose_cost_threshold_for_imci parameters to 1.

• For PolarDB for MySQL versions 8.0.1.1.39, 8.0.2.2.23, and later

  Set the loose_cost_threshold_for_imci parameter to 1.

Force execution plans with hints

If automatic request distribution does not meet your expectations, you can use hints to force a row-store or column-store execution plan.

• Force a column-store execution plan.

  • For PolarDB for MySQL versions 8.0.1.1.38, 8.0.2.2.22, and earlier

    When using the database proxy for automatic request distribution, you can use a hint to force the database proxy to route an SQL statement to a column store node, regardless of the loose_imci_ap_threshold setting. Add /* FORCE_IMCI_NODES */ before the SQL keyword. For example:

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

    An SQL statement that is routed to a column store node is still affected by the loose_cost_threshold_for_imci setting. To force an SQL statement to use a column-store execution plan, you can use a hint to lower the value of loose_cost_threshold_for_imci. For example:

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

  • For PolarDB for MySQL versions 8.0.1.1.39, 8.0.2.2.23, and later

    Use a hint 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 modify a threshold, you must remove the loose_ prefix from the parameter name. Otherwise, the hint does not take effect.

• Force a row-store execution plan.

  You can use a hint to set the value of USE_IMCI_ENGINE to OFF. This forces the SQL statement to use a row-store execution plan. For example:

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