Configure HTAP Request Routing for Row and Column Stores - PolarDB

When a PolarDB for MySQL cluster handles both transactional (OLTP) and analytical (OLAP) workloads, sending all queries to the same node type creates a bottleneck: OLAP scans degrade OLTP latency, and OLTP row store engines are inefficient for large analytical queries. Automatic request distribution solves this by routing each query to the most appropriate node type — column store nodes for analytical reads, row store nodes or the primary node for transactional reads and writes — without requiring application-level changes.

How it works

The database proxy estimates how many rows an SQL statement will scan, then compares that count against a configurable threshold:

Exceeds the threshold — routed to a read-only column store node
Does not exceed the threshold — routed to a read-only row store node or the primary node

Request routing by workload type:

Workload	Write requests	Read requests
OLTP (Online Transactional Processing)	Primary node	Read-only row store nodes or primary node
OLAP (Online Analytical Processing)	—	Read-only column store nodes

Two supported topologies:

Primary + column store nodes only — Write and OLTP read requests go to the primary node; OLAP reads go to column store nodes.
Primary + row store nodes + column store nodes — Write requests go to the primary node; OLTP reads go to read-only row store nodes (and optionally the primary node); OLAP reads go to column store nodes.

Limits

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

Step 1: Enable automatic request distribution

Log on to the PolarDB console. In the upper-left corner, select the region where the cluster is deployed. In the left-side navigation pane, click Clusters, then click the cluster ID.
On the Basic Information page, go to the Enterprise Edition section. Find the cluster endpoint and click Modify.
Set the Read/Write mode to one of the following:
- Read/Write (Automatic Read/Write Splitting)
- Read-only, with the load balancing policy set to Active requests-based load balancing
In the Node Settings section, select the primary node and the read-only row store and column store nodes that will handle requests. In the HTAP Optimization section, enable Transactional/Analytical Processing Splitting. Click OK.

Note

Select at least one read-only column store node in Node Settings.
After enabling automatic request distribution among column store and row store nodes, you must add IMCIs to implement the feature.
In Read/Write (Automatic Read/Write Splitting) mode, all write requests go to the primary node regardless of the Node Settings selection.

Example 1: One primary node, one read-only row store node, and two read-only column store nodes are selected.

Write requests — primary node
OLAP reads — read-only column store nodes
OLTP reads — read-only row store node (and optionally the primary node, if Primary Node Accepts Read Requests in SLB Settings is set to Yes)

Example 2: One primary node and one read-only column store node are selected.

Write requests and OLTP reads — primary node
OLAP reads — read-only column store node

Step 2: Set the routing threshold

The threshold controls when a query is considered analytical and routed to a column store node. On the Parameters page of the cluster, set the following parameters:

Parameter	Default	Description
`loose_imci_ap_threshold`	50,000	Scanned-row threshold for routing SQL to column store nodes. The `loose_cost_threshold_for_imci` parameter is used instead of this parameter in 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.
`loose_cost_threshold_for_imci`	50,000	Scanned-row threshold for selecting a column store execution plan. Replaces `loose_imci_ap_threshold` in 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.

With the default value of 50,000, any statement estimated to scan more than 50,000 rows is routed to a column store node.

Verify routing with query cost

To check the estimated cost of the last SQL statement and decide whether to adjust the threshold, run:

SHOW STATUS LIKE 'Last_query_cost';

Expected output:

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

When connecting through a cluster endpoint, add the /*ROUTE_TO_LAST_USED*/ hint before the statement to query the cost on the same node that ran the previous statement:

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

To force a statement to use a column store execution plan (useful for threshold testing), set the threshold below the statement's cost:

8.0.1.1.38 and 8.0.2.2.22 or earlier: Set both loose_imci_ap_threshold and loose_cost_threshold_for_imci to 1.
8.0.1.1.39 and later (8.0.1.x) / 8.0.2.2.23 and later (8.0.2.x): Set loose_cost_threshold_for_imci to 1.

Override automatic routing with hints

If automatic routing does not produce the expected result for a specific statement, use SQL hints to override it. A hint applies only to the statement it is attached to and does not affect other statements in the same connection or in other connections.

If you use MySQL 5.7.7 or earlier, add the --comments option when connecting to the database engine. Run mysql --version to check your client version.

Force column store execution

8.0.1.1.38 and 8.0.2.2.22 or earlier:

Route to a column store node regardless of loose_imci_ap_threshold:

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

Force a column store execution plan regardless of loose_cost_threshold_for_imci:

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

8.0.1.1.39 and later (8.0.1.x) / 8.0.2.2.23 and later (8.0.2.x):

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

When using /*+SET_VAR()*/, omit the loose_ prefix from the parameter name. Use cost_threshold_for_imci, not loose_cost_threshold_for_imci. The hint does not take effect if the prefix is included.

Force row store execution

Disable the column store engine for a single statement:

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