ApsaraDB RDS:HTAP query routing

Instance types

• Row-oriented instance: The RDS for MySQL primary instance and regular read-only instances. These instances use the InnoDB engine and are ideal for processing high-concurrency OLTP read and write requests.

• Columnstore instance: The DuckDB-based analytical read-only instance. This instance uses the DuckDB engine and is suitable for complex OLAP analytical queries.

Routing methods

RDS for MySQL supports the following two query routing methods:

• Automatic HTAP query routing: Automatically routes OLAP and OLTP queries to columnstore or row-oriented instances based on the estimated execution cost of the SQL statement.

• Manual routing with a hint: If automatic routing is not effective, or if your MySQL version does not support cost estimation, you can use a hint to manually specify the routing destination.

SQL execution cost

When using automatic routing, you can run the SHOW STATUS LIKE 'Last_query_cost' command to check the estimated execution cost of an SQL statement. This helps you understand the basis for routing decisions. We recommend that you run the EXPLAIN statement before you check the execution cost.

Example

View the estimated execution cost of the SELECT COUNT(*) FROM sbtest5.sbtest1 statement:

EXPLAIN SELECT COUNT(*) FROM sbtest5.sbtest1;
SHOW STATUS LIKE 'Last_query_cost';

-- Example result:
+-------------------+---------------+
| Variable_name     | Value         |
+-------------------+---------------+
| Last_query_cost   | 84454.999000  |
+-------------------+---------------+

The result shows that the estimated execution cost of the SQL statement is 84454.999000. The system uses this cost value to determine whether to route the query to a columnstore instance.

Enable automatic query routing

1. Log on to the RDS console. In the navigation pane on the left, click Instances. In the top navigation bar, select the region of your instance.

2. On the Instances page, click the ID of the target primary instance. The primary instance is marked with an icon.

3. In the navigation pane on the left, click Database Proxy.

4. In the Connection Information section, find the target proxy endpoint and click Modify Configuration in the Actions column.

5. In the dialog box that appears, enable Automatic row and column separation for HTAP and click OK.

Allocate read weights for DuckDB instances

If your RDS for MySQL instance has multiple DuckDB-based analytical read-only instances, you can set a read weight for each instance based on your business requirements. By default, the read weight for the proxy endpoint is Automatic. A higher read weight means the instance processes more read requests.

1. Log on to the RDS console. In the navigation pane on the left, click Instances. In the top navigation bar, select a region.

2. On the Instances page, click the ID of the target primary instance, which is marked with an icon.

3. In the navigation pane on the left, click Database Proxy.

4. In the Connection Information section, find the target proxy endpoint and click Modify Configuration in the Actions column.

5. In the dialog box that appears, set Read Weight Allocation to Custom.

   After you set it to Custom, the read weight of any new DuckDB-based analytical read-only instance added to this primary instance defaults to 0 and must be configured manually.

6. In the DuckDB Analytical Read-Only Instance Read Weight Allocation section, set the read weight for each DuckDB-based analytical read-only instance as needed.

   For example, assume a primary instance and its three DuckDB-based analytical read-only instances have their read weights set to 0, 100, 200, and 200, respectively. This means the primary instance does not process read requests (write requests are still automatically sent to it), and the three read-only instances process read requests at a 1:2:2 ratio.

   Note

   • The read weight can be set to a value from 0 to 10,000.

   • If a DuckDB-based analytical read-only instance is deleted, its weight is automatically removed. The weights of the other instances remain unchanged.

   • Changes to this parameter take effect immediately and do not cause service interruptions. Existing connections are not dropped. Both new and existing connections are routed based on the new weights.

Configure the SQL execution cost threshold

When you enable automatic HTAP query routing, the SQL execution cost threshold defaults to 500,000. In most cases, you can use this default value to route database requests:

• Requests with an estimated cost below the SQL execution cost threshold are routed to a row-oriented instance (the primary instance or a regular read-only instance).

• Requests with an estimated cost above the SQL execution cost threshold are routed to a columnstore instance (a DuckDB-based analytical read-only instance).

If you need to set a custom threshold that better suits your business requirements, perform the following steps:

1. Estimate the SQL execution cost based on your business requirements.

2. Configure the SQL execution cost threshold:

   1. Log on to the RDS console. In the navigation pane on the left, click Instances. In the top navigation bar, select a region.

   2. On the Instances page, click the ID of the target primary instance, which is marked with an icon.

   3. In the navigation pane on the left, click Database Proxy.

   4. In the Connection Information section, find the target proxy endpoint and click Modify Configuration in the Actions column.

   5. In the dialog box, modify the SQL Execution Cost Threshold for HTAP Automatic Query Routing and click OK.