PolarDB:Best practices and optimization for HTAP workloads

Components

HTAP clusters

The PolarDB-X primary instance handles general-purpose online business. To enable HTAP, purchase one or more read-only instances for the primary instance. For more information, see Read-only instances.

Routing

PolarDB-X supports two routing modes: intelligent routing and rule-based routing. Intelligent routing is on by default when you use the cluster endpoint—AP loads go to read-only instances automatically, with no configuration required. Use rule-based routing or hints only when you need to change this behavior.

mysql> EXPLAIN COST
  SELECT a.k, COUNT(*) cnt FROM sbtest1 a, sbtest1 b
  WHERE a.id = b.k AND a.id > 1000
  GROUP BY k HAVING cnt > 1300 ORDER BY cnt LIMIT 5, 10;

+-----------------------------------------------------------------------------------+
| TopN(sort="cnt ASC", offset=?2, fetch=?3): rowcount = 1.0, cpu = 37.0, ...       |
| ...                                                                               |
| WorkloadType: TP                                                                  |
+-----------------------------------------------------------------------------------+

-- Force AP classification
EXPLAIN COST /*+TDDL:WORKLOAD_TYPE=AP*/
  SELECT a.k, COUNT(*) cnt FROM sbtest1 a, sbtest1 b
  WHERE a.id = b.k AND a.id > 1000
  GROUP BY k HAVING cnt > 1300 ORDER BY cnt LIMIT 5, 10;

Execution modes

PolarDB-X selects an execution mode automatically based on workload type and whether a read-only instance is available:

Verify the execution mode

Run EXPLAIN PHYSICAL and look for ExecutorType in the first line of the output:

mysql> EXPLAIN PHYSICAL
  SELECT a.k, COUNT(*) cnt FROM sbtest1 a, sbtest1 b
  WHERE a.id = b.k AND a.id > 1000
  GROUP BY k HAVING cnt > 1300 ORDER BY cnt LIMIT 5, 10;

+-----------------------------------------------------------------------------------+
| ExecutorType: MPP                                                                 |
| The Query's MaxConcurrentParallelism: 2                                           |
| Fragment 1                                                                        |
|     Output partitioning: SINGLE [] Parallelism: 1                                 |
|     TopN(sort="cnt ASC", offset=?2, fetch=?3)                                     |
|   ...                                                                             |
| Fragment 0                                                                        |
|     Output partitioning: SINGLE [] Parallelism: 1 Splits: 16                     |
|     LogicalView(tables="[000000-000003].sbtest1_[00-15]", shardCount=16, ...)     |
+-----------------------------------------------------------------------------------+

ExecutorType: MPP in the first line confirms MPP is active. The output also shows the degree of parallelism for each fragment.

Override the execution mode

Force a specific execution mode with the EXECUTOR_MODE hint. Use this when the primary instance has spare capacity and you want to accelerate a query locally without routing it to a read-only instance:

-- Force single-node parallel mode
EXPLAIN PHYSICAL /*+TDDL:EXECUTOR_MODE=AP_LOCAL*/
  SELECT a.k, COUNT(*) cnt FROM sbtest1 a, sbtest1 b
  WHERE a.id = b.k AND a.id > 1000
  GROUP BY k HAVING cnt > 1300 ORDER BY cnt LIMIT 5, 10;

Override the degree of parallelism for MPP

MPP parallelism is calculated conservatively to handle high concurrency. To increase it manually, combine EXECUTOR_MODE=MPP with the MPP_PARALLELISM hint:

/*+TDDL:EXECUTOR_MODE=MPP MPP_PARALLELISM=8*/
  SELECT a.k, COUNT(*) cnt FROM sbtest1 a, sbtest1 b
  WHERE a.id = b.k AND a.id > 1000
  GROUP BY k HAVING cnt > 1300 ORDER BY cnt LIMIT 5, 10;

The degree of parallelism is calculated based on scanned row count, instance specifications, and number of table shards involved.

Scheduling policy

When multiple read-only instances are associated with a cluster endpoint, PolarDB-X schedules queries across all nodes based on resource load—not round-robin. It uses read-only instance latency as a reference metric to avoid sending queries to instances that have high latency. This distributes work evenly without manual intervention.

Feedback mechanism

Workload classification is statistics-based and occasionally misidentifies queries. When this happens, PolarDB-X can automatically correct the classification through adaptive feedback.

How adaptive feedback works:

• If a query classified as TP exceeds the thresholds for scanned rows and execution time, PolarDB-X reclassifies it as AP in Plan Management. The similar rule applies to a query that is classified as an AP load.

• The updated classification applies to all queries matching the same execution plan template.

Check workload assignments in Plan Management

BASELINE [Select Statement]

Manually reclassify a workload type

If adaptive feedback hasn't triggered yet, force a reclassification in Plan Management:

BASELINE FIX SQL /*+TDDL:WORKLOAD_TYPE=AP*/ [Select Statement]

After updating the workload type in Plan Management, the correct classification applies to all queries with the same plan template. For more information, see Execution plan management.

Read consistency

By default, global read consistency is enabled when you use the cluster endpoint. This ensures queries on read-only instances always see data that has already been committed to the primary instance—preventing stale reads caused by replication delay.

Disable read consistency for a session or query

If your workload tolerates replication delay and you want to reduce overhead, disable read consistency in one of two ways:

• On the Parameter settings page: set ENABLE_CONSISTENT_REPLICA_READ to false (applies instance-wide).

• Per query using a hint:

/*+TDDL:ENABLE_CONSISTENT_REPLICA_READ=false*/ [Select Statement]

Use the per-query hint when only specific queries can tolerate stale reads, rather than disabling consistency globally.

FAQ

Do I need to set `MASTER_READ_WEIGHT` after enabling intelligent routing?

Only if you also want to offload high-concurrency TP loads to read-only instances. Intelligent routing already moves AP loads to read-only instances automatically. If your TP workload is heavy enough to saturate the primary instance, lower MASTER_READ_WEIGHT below 100 to send some TP reads to read-only instances as well.

Is the read/write splitting based on the cluster endpoint compatible with the traditional read/write splitting by proportion?

PolarDB-X supports two modes: intelligent routing and rule-based routing. Rule-based routing is compatible with the traditional read/write splitting mode. The advantage of the read/write splitting of PolarDB-X is that the read concurrency mechanism is supported. This prevents inconsistency between the read data and the written data due to a replication delay on the read-only instance.

How do I check which workload type was assigned to a query, and how do I correct it?

Run EXPLAIN COST to see WorkloadType in the plan output. If the classification is wrong, use a WORKLOAD_TYPE hint for a one-time override, or use BASELINE FIX SQL to persist the correction in Plan Management for all future executions with the same plan template.

What are the advantages of PolarDB-X HTAP over a combined OLTP + DTS + OLAP setup?

PolarDB-X HTAP uses the database's native multi-replica capability to serve both OLTP and OLAP traffic. This eliminates the need to export data through Data Transmission Service (DTS), reduces operations and maintenance (O&M) overhead, and adds global read consistency with MPP acceleration—supporting real-time, scalable analytics on live data.

What's next

• HTAP

• Read-only instances

• Configure read/write splitting

• EXPLAIN

• Execution plan management