When a PolarDB for MySQL HTAP cluster serves both OLTP and OLAP workloads, mixing the two traffic types on the same cluster endpoint can cause row store queries to compete with analytical queries for resources. To isolate the traffic, configure separate cluster endpoints — one scoped to row store nodes for OLTP and one scoped to column store nodes for OLAP — and point each application to its designated endpoint.
How routing works
PolarDB routes requests across three layers. Understanding the order helps you decide where to intervene when routing does not behave as expected.
| Layer | Mechanism | Scope |
|---|---|---|
| 1. Cluster endpoint | Restricts which nodes an endpoint can reach | Connection level |
| 2. Cost-based optimization | Selects a row store or column store execution plan based on estimated cost | Statement level (automatic) |
| 3. SQL hint | Forces a specific execution plan, overriding cost-based optimization | Statement level (manual) |
Under normal conditions, layer 1 separates OLTP and OLAP traffic, and layer 2 handles plan selection automatically. Use layer 3 only when the automatic selection produces unexpected results for a specific statement.
The default routing rules are:
OLTP write requests — always routed to the primary node.
OLTP read requests — routed to read-only row store nodes or the primary node.
OLAP read requests — routed to read-only column store nodes.
Configure cluster endpoints
Configure two cluster endpoints — one for OLTP traffic and one for OLAP traffic. For instructions on creating and managing cluster endpoints, see Manage the endpoints of a cluster.
OLTP endpoint
Choose one of the following modes:
| Mode | Node configuration |
|---|---|
| Read-only | Add only read-only row store nodes to the selected node list. |
| Read/Write (Automatic Read/Write Splitting) | Add at least one read-only row store node. If Primary Node Accepts Read Requests is set to Yes, read requests are also distributed to the primary node. |
In Read/Write (Automatic Read/Write Splitting) mode, all write requests go to the primary node regardless of whether the primary node is in the selected node list.
OLAP endpoint
OLAP workloads involve only read requests. Use Read-only mode and add at least one read-only column store node to the selected node list.
Override routing with SQL hints
After a statement reaches a read-only column store node, PolarDB compares the estimated execution cost against the loose_cost_threshold_for_imci parameter to decide the execution plan. If the cost exceeds the threshold, PolarDB uses the column store plan powered by the In-Memory Column Index (IMCI); otherwise, it uses the row store plan.
To override this automatic selection for a specific statement, add a SQL hint. A hint applies only to the statement it annotates — it has no effect on other statements in the same connection or in other connections.
For MySQL 5.7.7 and earlier, add the --comments option when connecting to the database engine, otherwise the client strips hints before sending the statement. Run mysql --version to check your client version.
Force a column store execution plan
Add /*+ SET_VAR(cost_threshold_for_imci=0) */ to set the cost threshold to 0, forcing the column store plan regardless of the actual estimated cost.
/*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;Inside /*+SET_VAR()*/, omit the loose_ prefix from the parameter name. Using loose_cost_threshold_for_imci in the hint causes it to have no effect; use cost_threshold_for_imci instead.
Force a row store execution plan
Add /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ to bypass the column store engine entirely.
EXPLAIN SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;